Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Sybase ASE - Cannot fetch by primary key
 

Pages: 1
Bugs & Issues
Sybase ASE - Cannot fetch by primary key
Page:1/1 

  Print all messages in this thread  
Poster Message
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# Posted on: 16-May-2008 07:54:48.  
LLBLGen Pro v2.5 Final (April 23rd, 200Cool

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.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14572 posts
# 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


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# 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?


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# 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:
Quote:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# 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!


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# 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. Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# 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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# Posted on: 20-May-2008 01:29:45.  
Thanks for looking in to this ... Regular Smiley

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# 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 Shocked

I'll add some switch/case for this, it's just a minor group of types worth considering
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# Posted on: 20-May-2008 11:19:53.  
Ok, fixed it Regular Smiley

Code:

[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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kerpal
User



Location:

Joined on:
16-May-2008 07:32:34
Posted:
14 posts
# Posted on: 20-May-2008 14:01:11.  
Thanks for the quick response ... If only Sybase were as responsive! Regular Smiley

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 ...
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37830 posts
# Posted on: 20-May-2008 15:30:28.  
kerpal wrote:
Thanks for the quick response ... If only Sybase were as responsive! Regular Smiley

Indeed Regular Smiley 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.

Quote:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.