[SOLVED] Strange behaviour

Posts   
1  /  2
 
    
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 10-Feb-2004 14:33:32   

Hi,

I discovered some strange behaviour in my application.

I use a context to encapsulate the access to the database. All objects are read or created within this context and the last actions on the context can only be a dispose or a save.

After the save all changes are fired to my GUI part of the application.

The GUI tries in another context to read the values of new created objects, but these objects can not be found in the database (they are there, I know it).

I always thought that the save command acts synchron on the database and after it has finished all changed/newly created objects can be found afterwards.

Any clue what's going on?

Christoph

Btw, I use the adapter version of LLBLGEN

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Feb-2004 15:08:57   

So, after the save, which pushes all changed/new objects to the database indeed, the records are in the database (so a query in QUeryAnalyzer will show them) but when you try to refetch them the records aren't there/the fetches do not return a value?

Could you please post a codesnippet, or if that's too big, mail me a snippet on support@llblgen.com? Thanks.

Frans Bouma | Lead developer LLBLGen Pro
jeffreygg
User
Posts: 805
Joined: 26-Oct-2003
# Posted on: 10-Feb-2004 22:34:49   

You wouldn't happen to be using uniqueidentifiers as PKs would you? If so, make sure to set the PK property to GUID.NewGuid before saving. If you don't, a sync issue occurs because the default value for the PK will be different than the auto-generated one created by the database on Save() (LLBL won't save the default generated GUID because it's treated as a NULL value). If you then use the default one created by LLBL to do a read, it won't find it.

Jeff...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Feb-2004 23:34:03   

jeffreygg wrote:

You wouldn't happen to be using uniqueidentifiers as PKs would you? If so, make sure to set the PK property to GUID.NewGuid before saving. If you don't, a sync issue occurs because the default value for the PK will be different than the auto-generated one created by the database on Save() (LLBL won't save the default generated GUID because it's treated as a NULL value). If you then use the default one created by LLBL to do a read, it won't find it.

Jeff...

Good point, Jeff.

Always set your fields to a value if you want them to have a value. Never rely on the values set by default, as they will never be saved into the database.

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 09:41:36   

Hi,

I use for all my tables an id as PK, which is of type uniqueidentifier.

Hmm, I try to set up a small example in the next days which tries to show this behaviour.

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 10:24:08   

ChBaeumer wrote:

Hi,

I use for all my tables an id as PK, which is of type uniqueidentifier.

Hmm, I try to set up a small example in the next days which tries to show this behaviour.

You shouldn't do this: CustomerEntity c = new CustomerEntity(); Guid theID = c.CustomerID; c.OtherProperty = value; ...

c.Save();

// reincarnate the entity somewhere else which received 'theID': CustomerEntity c = new CustomerEntity(theID);

You should set the ID to Guid.NewGuid(), then save.

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 12:17:00   

Hi Otis,

I just do that. For each newly created entity instance, the value(s) of the PK(s) are set with Guid.NewGuid().

It get's even stranger.

I have views on my tables. In some cases reading the views delivers wrong data. It seems that the result of the wanted view is the result of a view readed prior to this view.

Hmm, I guess there are some problems with the transactions and the amount of open connections to the database.

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 13:02:58   

ChBaeumer wrote:

I just do that. For each newly created entity instance, the value(s) of the PK(s) are set with Guid.NewGuid().

Ok, then that can't be the problem simple_smile

It get's even stranger. I have views on my tables. In some cases reading the views delivers wrong data. It seems that the result of the wanted view is the result of a view readed prior to this view.

Hmmm. I just create a connection and fill the datatable with a data-adapter from .NET, so no weird code going on there....

Hmm, I guess there are some problems with the transactions and the amount of open connections to the database. Christoph

Could you please step into the query construction code, to check whether the query actually executed is correct? (and the parameters have the right values)

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 13:13:03   

Hi,

I trace the database with the sql profiler from the enterprise edition. When I look at the executed code I can't see anything wrong.

In the next step I will bind the source code of llblgen to my solution.

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 13:26:17   

ChBaeumer wrote:

I trace the database with the sql profiler from the enterprise edition. When I look at the executed code I can't see anything wrong.

So the executed query is as expected, however apparently the rows returned aren't ending up in the right object?

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 14:06:07   

Hmm,

the executed query is correct, but the sql server delivers the wrong data.

A short explanation:

I have following tables (more or less ;-)

Table Element( Id UNIQUEIDENTIFIER PRIMARY KEY, ElementType UNIQUEIDENTIFIER, some other info )

Table Name( Id UNIQUEIDENTIFIER PRIMARY KEY, ElementId UNIQUEIDENTIFIER FOREIGN KEY, some other info )

Now I defined serveral views:

View ViewOfA: SELECT Id, ElementType FROM Element WHERE ElementType = '<uniqueidentifier of type>'

View ViewOfAWithName SELECT Id, ElementType, Name FROM ViewOfA INNER JOIN Name ON Name.ElementId = ViewOfA.Id

In the code I try to read the ViewOfAWithName view. The query from llblgen is correct but the sql server delivers wrong data for the ViewOfA query. The selected elements of this view are not of the assumed type but of a different type.

Christoph

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 14:46:52   

Hi,

when I disable the connection pooling all works as aspected but the application crawls.

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 15:06:27   

ChBaeumer wrote:

Hi, when I disable the connection pooling all works as aspected but the application crawls. Christoph

Hmm. Pooling should just work out of the box...

You use .NET 1.1 with sqlserver 2000 and with the latest service pack, as wel with MDAC 2.8 or higher?

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 15:07:25   

ChBaeumer wrote:

View ViewOfA: SELECT Id, ElementType FROM Element WHERE ElementType = '<uniqueidentifier of type>'

View ViewOfAWithName SELECT Id, ElementType, Name FROM ViewOfA INNER JOIN Name ON Name.ElementId = ViewOfA.Id

In the code I try to read the ViewOfAWithName view. The query from llblgen is correct but the sql server delivers wrong data for the ViewOfA query. The selected elements of this view are not of the assumed type but of a different type.

The viewofA is an sqlserver view I presume, and the where clause is not added to that view? or is it added to the view?

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 15:23:29   

Hi,

both are sql server views. As I said, disabling connection pooling does help.

Hmm, I'm just thinking about the connection pool. I believe that the connection pool itself is not threadsafe. So in a GUI environment, when you have different controls (which are running more or less in their own thread) several threads trying to connect to the database and want to read data. As I explained, I use a context class, which is not shared itself between GUI components. But what happens to the connection pool? Does the connection pool itself takes care of some thread id?

Christoph

ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 11-Feb-2004 15:24:32   

Ups I forgot to mention that there WHERE clause is on the first view on which the second view does the look up.

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Feb-2004 15:37:58   

(PS I assume you're using a winforms app)

ChBaeumer wrote:

Hmm, I'm just thinking about the connection pool. I believe that the connection pool itself is not threadsafe. So in a GUI environment, when you have different controls (which are running more or less in their own thread) several threads trying to connect to the database and want to read data. As I explained, I use a context class, which is not shared itself between GUI components. But what happens to the connection pool? Does the connection pool itself takes care of some thread id?

Gui components run in the same thread as the rest of the gui, which is single threaded. That is, unless you start new threads yourself of course simple_smile That's also the reason why gui controls are not updated if you run something time consuming in the main thread: the message pump is not read/handled.

The connection pool is a low level ado.net pool which is totally unreachable by .NET code. So when you instantiate an SqlConnection, a connection from the pool is mapped into the SqlConnection instance, ado.net decides if this physical connection is a new one or one from the pool.

All objects in Selfservicing start new connections, and release them a.s.a.p. This thus relies heavily on a connection pool, and can work even with a low amount of connections in the pool. retrieving an existing connection from the pool is very fast.

As we're talking typed views here, the code is even much simpler. The order is as follows: - the typed view class calls its DAO class' GetMultiAsDataTable() method. This method first creates a new SqlConnection using DbUtils, passes it to the SqlServer DQE which creates a command with it and stores that command (with the select query) in the RetrievalQuery object. - it then creates an SqlDataAdapter object using DbUtils. This is then passed to the base class of the DAO object, together with the RetrievalQuery. - It executes this simple code:


((IDbDataAdapter)dataAdapterToUse).SelectCommand = queryToExecute.Command;
dataAdapterToUse.Fill(tableToFill);

In other words: a simple select using the data-adapter which opens the connection and also closes it.

The behaviour you're seeing is very weird, also because the connections in the pool should be completely resetted by ado.net.

Frans Bouma | Lead developer LLBLGen Pro
ChBaeumer
User
Posts: 175
Joined: 23-Oct-2003
# Posted on: 13-Feb-2004 10:33:01   

Hi,

It was my mistake. Problem solved flushed

I have asynchronous calls to the GUI from the network layer, and didn't take care of checking if the GUI components need an InvokeRequired in their methods. So actually it was an thread problem.

Thank you for your time

Christoph

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 13-Feb-2004 10:41:29   

ChBaeumer wrote:

Hi,

It was my mistake. Problem solved flushed

I have asynchronous calls to the GUI from the network layer, and didn't take care of checking if the GUI components need an InvokeRequired in their methods. So actually it was an thread problem.

simple_smile ok, I'm glad it's solved now. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
bonder
User
Posts: 36
Joined: 23-Sep-2003
# Posted on: 03-Mar-2004 18:08:45   

I'm running into the same problem. I'm curious as to why the framework can't return the newid() that would be generated by default? This works fine with int PKs.

From one of my guys:

here the detailed description of the issue in business objects generation. Lets take a look

in two samples. ----------------- first sample ------------------------


CustomerEntity c=new CustomerEntity() 

c.Name="Nobody"; 

new DatabaseAccessAdapter().SaveEntity(c,true); 
// true means that framework should refetch entity after saving 

Debug.WriteLine(c.CustomerID); 
//fails with exception that CustomerEntity is out of sync 

In this case llblgen framework generates following SQL code:


INSERT INTO Customer (blah, blah, ...) VALUES (blah, blah, ...) 

But the right SQL is:


INSERT INTO Customer (blah, blah, ...) VALUES (blah, blah, ...) SELECT 

@CustomerID=@@IDENTITY 


----------------- second sample ------------------------


CustomerEntity  c=new CustomerEntity() 
AddressEntity a=new AddressEntity(); 

c.Name="Nobody"; 
c.Address = a; 

new DatabaseAccessAdapter().SaveEntity(c,true,true); 
// last true value means that framework should save entity recursively 
// fails with exception ' INSERT statement conflicted with COLUMN FOREIGN KEY constraint 

...' 


In this case llblgen framework generates following SQL code:


INSERT INTO Address (blah, blah, ...) VALUES (blah, blah, ...) 

INSERT INTO Customer (blah, blah, ..., AddressID) VALUES (blah, blah, ..., @AddressID) 

And framework fails because @AddressID is empty or even wrong. The right SQL is:


INSERT INTO Address (blah, blah, ...) VALUES (blah, blah, ...) SELECT @AddressID=@@IDENTITY 

INSERT INTO Customer (blah, blah, ..., AddressID) VALUES (blah, blah, ..., @AddressID) 

SELECT @CustomerID=@@IDENTITY 

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Mar-2004 20:14:08   

Erm, your examples suggest there is a problem with identity values which are not read back, is that correct? Because that would be odd, as this feature works (here at least).

Guid's have to be set by hand like any other value by using Guid.NewGuid(). newid() is a function, like getdate(), or a user defined function, which perhaps can supply default values.

Frans Bouma | Lead developer LLBLGen Pro
bonder
User
Posts: 36
Joined: 23-Sep-2003
# Posted on: 03-Mar-2004 21:45:05   

We have newid() set as the default value for the PK columns of all entity tables in our database. All PK columns use uniqueidentifiers.

We are running into the sync issues when we rely upon the database to supply these items, because the PKs are not "coming back" to us when we save.

The issue goes away if we manually set the PK column to Guid.NewGuid() in our code instead of relying on the database.

Is doing it in code the required way, or are we not doing something correctly, do you think? simple_smile

--Bruce

bertcord avatar
bertcord
User
Posts: 206
Joined: 01-Dec-2003
# Posted on: 03-Mar-2004 22:30:35   

bonder wrote:

Is doing it in code the required way, or are we not doing something correctly, do you think? simple_smile

--Bruce

Yes

=@@IDENTITY only returns an Identity value , not New Guid()

bert

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Mar-2004 22:42:03   

bonder wrote:

We have newid() set as the default value for the PK columns of all entity tables in our database. All PK columns use uniqueidentifiers.

We are running into the sync issues when we rely upon the database to supply these items, because the PKs are not "coming back" to us when we save.

The issue goes away if we manually set the PK column to Guid.NewGuid() in our code instead of relying on the database.

Is doing it in code the required way, or are we not doing something correctly, do you think? simple_smile

You have to set the column manually. simple_smile

retrieving newid() is not supported because when it set as a default constraint for a column, I can't retrieve the value, it is not stored in a variable or something. Therefore I have to set the column manually anyway. To be consistend with other values, I decided it is best to set the column by hand, as any other column. Sequences are different, as these can't be set by hand to a reliable value. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
bonder
User
Posts: 36
Joined: 23-Sep-2003
# Posted on: 03-Mar-2004 23:24:33   

Thanks, guys!

So it seems that I should remove the newid() from the default property of my PK columns. This will force the developers to set the guid in code. If they don't set it, they will throw an exception.

--Bruce

PS: LLBLGen Pro rocks! simple_smile

1  /  2