Sybase ASE - Cannot fetch by primary key

Posts   
 
    
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 16-May-2008 07:54:48   

LLBLGen Pro v2.5 Final (April 23rd, 200sunglasses

I have created the following table ...

create table test ( test_id int identity , test varchar(50) null
) lock allpages on 'default' go

... and generated the code for it using the Adapter pattern.

Whenever I try to load an entity using FetchEntity, it always returns false and I get the error ORMEntityOutOfSyncException when trying to view the entity properties.

If I try to save the entity,

TestEntity te = new TestEntity(); te.Test = "Help me";

using (DataAccessAdapter adapter = new DataAccessAdapter()) { bool res = adapter.SaveEntity(te, true); Assert.IsTrue(res); }

the record is saved successfully but SaveEntity returns false and the entity cannot be accessed (again, ORMEntityOutOfSyncException).

It seems that Fetching and Refetching is not working.

Please help as development has come to a standstill trying to resolve this issue!

Thanks.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-May-2008 11:30:16   

I think your issue has something to do with a bug in the Sybase ado.net provider, please check the following thread which pretty much seems relevant: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11677

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-May-2008 11:44:29   

Be sure your PK field is indeed markt as an Identity field in the LLBLGen pro DESIGNER, as well as in the database you're saving to.

Enable DQE tracing (See troubleshooting/debugging in the manual) and check which query is executed.

Frans Bouma | Lead developer LLBLGen Pro
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 19-May-2008 01:14:47   

Thanks for the replies.

I have checked that the PK has been marked as such in the LLBL designer. However, LLBL detects it as an int rather than a numeric (decimal). Other decimal fields (FKs etc) are detected correctly.

Any ideas?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-May-2008 13:46:31   

kerpal wrote:

Thanks for the replies.

I have checked that the PK has been marked as such in the LLBL designer. However, LLBL detects it as an int rather than a numeric (decimal). Other decimal fields (FKs etc) are detected correctly.

Any ideas?

erm.. it IS an int:

create table test ( test_id int identity , test varchar(50) null )

so, why do you want to see it as a decimal, while it is an int?

Frans Bouma | Lead developer LLBLGen Pro
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 19-May-2008 14:08:06   

Sorry, all the other tables have a PK of numeric(19,0). My test table was defined as an integer to see if it made any difference to my issue, which it didn't.

Apologies for the confusion.

Any ideas what to do about this issue? I have searched in vain for old versions of the Sybase Provider ...

We just purchased a few licences of LLBL Gen and I'm going to look really bad if we can't use them!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-May-2008 14:12:20   

Then take a few steps back and redefine the problem because I have no idea what the problem is.

Give a CLEAR DDL SQL table definition of a table which you use in your project, REAL CODE, no artificial names etc.

You say your PK is of type decimal. Identity fields aren't decimals, so your pk field isn't an identity field. You therefore have to set the PK with a value.

Please show this code where you use the entity: where you fill it and save it. The entity mapped on the table definition I mentioned above. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 19-May-2008 14:35:08   

The existing database I have to work with has all the PK and FK fields defined as numeric(19,0).

Here's a real table definition exported from our dev database using Sybase Central:

create table audit_log (id numeric(19,0) identity not null, version numeric(19,0) not null, status varchar(40) null, user_name varchar(10) null, user_role varchar(2) null, date_changed datetime null, object_id varchar(255) null, audit_type varchar(20) null, primary key (id));

I am trying to load a record by primary key. I have verified that this PK exists in the database.

AuditLogEntity audit = new AuditLogEntity(1);

using (DataAccessAdapter adapter = new DataAccessAdapter()) { bool res = adapter.FetchEntity(audit); Assert.IsTrue(res); //Always returns false }

LLBL detects the primary key value (id) as an int (system.int32) and the version column as a numeric (system.decimal), despite them both having the same type.

I'm not sure if the two issues are related at all.

Please let me know if you require any further clarification. I am using the latest Sybase Developer Edition to test this.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-May-2008 17:21:30   

Could be a driver issue, will look into it (it's likely a hard-wired setting to set the sequence field's type to int32, in our driver)

Frans Bouma | Lead developer LLBLGen Pro
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 20-May-2008 01:29:45   

Thanks for looking in to this ... simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-May-2008 10:28:04   

It's indeed hard-coded in the driver. I'll remove that and will run some tests to see if the identity value is indeed obtainable from sybase.

(edit) The query used is the sp_columns stored proc in sybase to obtain meta-data. The problem comes from the fact that that proc has a 'type_name' column, which contains always 'numeric identity' for an identity field, be it a numeric, int, smallint etc. field. Our driver looked at that first word for the type. This isn't correct, it should look at the data_type value, which is 4 for an int and 2 for a numeric field. This should control the typename. So it's not always safe to rely on sybase metadata procs wink .

Stay tuned.

(edit) Hmm... 'date_type' is a numeric value which is an ODBC type value, which is obtainable through sp_datatype_info but ... more than 1 type maps to a numeric odbc type frowning

I'll add some switch/case for this, it's just a minor group of types worth considering

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-May-2008 11:19:53   

Ok, fixed it simple_smile


[Test]
public void InsertRetrieveAuditEntityWithDecimalIdentityKey()
{
    AuditLogEntity toInsert = new AuditLogEntity();
    toInsert.Version = 10.0M;
    toInsert.UserName = "FooBar";
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        Assert.IsTrue(adapter.SaveEntity(toInsert, true));
    }

    Assert.IsTrue(toInsert.Id > 0.0M);
}

I've attached the new sybase ase driver to this post. Place it in the Drivers\SybaseAse folder and refresh your catalog -> regenerate the code.

Frans Bouma | Lead developer LLBLGen Pro
kerpal
User
Posts: 14
Joined: 16-May-2008
# Posted on: 20-May-2008 14:01:11   

Thanks for the quick response ... If only Sybase were as responsive! simple_smile

I managed to fix the other issue by changing the ORMSupportClasses code. I replaced any occurrences of CommandBehavior.SingleRow to CommandBehavior.SingleResult. It seems to be working so far with no adverse effects ...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-May-2008 15:30:28   

kerpal wrote:

Thanks for the quick response ... If only Sybase were as responsive! simple_smile

Indeed simple_smile THe only responses I always get from them is spam mails for seminars.

I also saw a horrific bug in 15.0 btw. a couple of left joins with a where filter on a field which was also selected so it wasn't null (simple query). I got no filtering, thus 4 results instead of 1. The 3 rows which had this field, id, as null, had it filled with the value of the last row! When I removed the where, I got 4 rows with the id field as null for 3 of the 4 rows. bizarre.. It's shocking that these fundamental issues still pop up in a system at version 15.

I managed to fix the other issue by changing the ORMSupportClasses code. I replaced any occurrences of CommandBehavior.SingleRow to CommandBehavior.SingleResult. It seems to be working so far with no adverse effects ...

You should add just a couple of lines of code, to RetrievalQuery.cs, Execute method. There you should add an if statement for SingleRow to change it in SingleResult.

Saves you the trouble of updating the code regularly. I can't add a more flexible fix for you for this sybase issue...

Frans Bouma | Lead developer LLBLGen Pro