LLBLGen 2.0.0.0 FINAL Demo, Nov 6th, 2006 Release .NET Framework 1.1 Visual Studio.NET 2003 IIS 5.1 generating against MySql 4.1.22: CREATE TABLE tutors ( tid mediumint( 8 ) unsigned NOT NULL auto_increment, //...

LLBLGen adapter template produces code which throws invalid cast exception workaround uses Convert.ToInt64(), but modifies generated code

public virtual System.Int64 Tid { get { object valueToReturn = base.GetCurrentFieldValue((int)TutorsFieldIndex.Tid); if(valueToReturn == null) { valueToReturn = TypeDefaultValue.GetDefaultValue(typeof(System.Int64)); } bad>> return (System.Int64)valueToReturn; good>> return Convert.ToInt64(valueToReturn); } set { SetNewFieldValue((int)TutorsFieldIndex.Tid, value); } }

Please let me know if additional code is required to fix issue.

Our driver sees mediumint as an int and unsigned ints are returned as 64bit values by Corelab's mysql provider.

Workaround: define the type as a signed int in the db.

I'll check to see if our mysql driver needs some tweaking in this area.

I can't reproduce it with a nullable mediumint(8 ) unsigned. (which results in Nullable<Int64>)

TypetesttableEntity t = new TypetesttableEntity();
t.Fbyte = 1;
t.FmediumInt = 10;
Debug.Assert(t.Save());

t = new TypetesttableEntity(1);
Debug.Assert(t.FmediumInt == 10);

I'll now try with a non-nullable field.

(edit): same effect: it works.

I connect to a MySql 4.1.7 instance using 3.20.9 of corelab's mysql provider. Could you check for me what the .net type is of the CurrentValue property in that property right before the cast?

OK, will do as you request - thanks for getting on this so quickly - valueToReturn is of type:

System.Int32

Also, your post reminded me that i neglected to provide the Corelab.MySql version i'm using:

3.50.15.0

Oh d***...

They really changed the type? Could you please change the type in the db to signed? If possible? Or change it to int unsigned ? That will make the code work.

I've to change the driver for this to make it detect which provider version it's working with... very unfortunate they changed this...

It's unfortunately not my database to do the type assignment, but i will make that recommendation to the client. Just to confirm that i've got it absolutely clear, you're suggesting that i simply change:

mediumint( 8 ) -- note the spaces here are only to thwart the forum's happy-faces

to:

int(10)

right?

They've only got 22 fields of this type in their database...

Otis wrote:

Oh d***...

They really changed the type? Could you please change the type in the db to signed? If possible? Or change it to int unsigned ? That will make the code work.

I've to change the driver for this to make it detect which provider version it's working with... very unfortunate they changed this...

the 3.20 and earlier versions of the corelab provider made mediumint (8 ) UNsigned an int64 value, similar to int(10) UNsigned.

It made mediumint(8 ) SIGNED an int32 value, similar to int(10) SIGNED.

So you have three options: change the unsigned bit to signed (and keep it to medium int), or change the type to int(10) unsigned, or change it to int(10) signed.

To test if it works, I'd change one table (the one you've tested it with) and see if the value is now read OK. I know that they kept int(x) types the same.

This is really unfortunate. I'll add a fix for this to the driver, so it tests for provider version and based on that decides how to map the mediumint types. We had to do similar things before with the Oracle ODP.NET provider for 10g which had all of a sudden different mappings for number(x,y) starting with a given version.

I hope to have the fix into the driver by monday afternoon. You can also wait for that of course.

ok thanks for being so responsive - the client seems to be ok with changing from mediumint to int - i'm more comfortable staying away from signed integers for primary keys - not sure what your experience is with that, but it sounds rather esoteric to me

since your update is forthcoming, i'll probably wait for it, but may i also ask why your generated code doesn't use an explicit cast (Convert.ToInt64(aValue)) like my workaround rather than the implicit cast ((System.Int64) aValue)?

A cast is much faster than a convert, and as the types are known, a convert is not necessary, a cast should do. Unless, of course, the provider vendor suddenly changes something without notice...

I've attached build 2.0.12112006 of the MySql driver to this post.

Could you please try this dll out? (You should make a copy of the current driver dll in the \drivers\MySql folder first and then place the attached dll in that folder, overwriting the old one. When you start llblgen pro, and create a new project, you should see in the create project dialog box 2.0.12112006 as version for the mysql driver. MediumInt unsigned will be mapped similar to SmallInt unsigned. MediumInt signed will be mapped similar to integer signed. This because 3.20 doesn't have a notion of medium int so mysqltypes enum in the crlab assembly doesn't know about medium int. I can't install 3.50 as I then first have to uninstall 3.20 and then alter on install 3.20 again, as the driver has to be build against 3.20 for now, as a lot of the mysql users still use 3.20.

When you start llblgen pro, and create a new project, you should see in the create project dialog box 2.0.12112006 as version for the mysql driver.

everything generates fine with a new project, but then i get the following runtime failure when using CoreLab.MySql version 3.50:

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMValueTypeMismatchException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: The value 7 is of type 'System.Int64' while the field is of type 'System.Int32'

Do i need to reference MySql version 3.20 rather than 3.50 with my code?

kbelange wrote:

When you start llblgen pro, and create a new project, you should see in the create project dialog box 2.0.12112006 as version for the mysql driver.

everything generates fine with a new project, but then i get the following runtime failure when using CoreLab.MySql version 3.50:

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMValueTypeMismatchException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: The value 7 is of type 'System.Int64' while the field is of type 'System.Int32'

Do i need to reference MySql version 3.20 rather than 3.50 with my code?

No, that should be fine, though how can it be the value is of type int64? I thought mediumint unsigned mapped to int32?

i know, this is exactly the opposite of what we saw before, right?

i took a look through the generated code for Int64 usage related to my entities, and i noted that the PersistenceInfoProvider class didn't change on regeneration:

    private void InitTutorsEntityMappings()
    {
        base.AddElementMapping( "TutorsEntity", "test", @"Default", "tutors", 20 );
        base.AddElementFieldMapping( "TutorsEntity", "Tid", "tid", false, (int)MySqlType.Int, 0, 0, 8, true, "LAST_INSERT_ID()", null, typeof(System.Int64), 0 );

Shouldn't the last line above map to System.Int32 instead of Int64?

By the way, i know your docs recommend against it, but in case it matters, i'm generating Predicate, SortClause, & Validator classes, as i'm trying to make minimal changes to an existing code base and it's not a large project. If we get a second round contract to make enhancements & changes, which could include this sort of update to current LLBLGen best-practices.

Otis wrote:

No, that should be fine, though how can it be the value is of type int64? I thought mediumint unsigned mapped to int32?

kbelange wrote:

i know, this is exactly the opposite of what we saw before, right?

i took a look through the generated code for Int64 usage related to my entities, and i noted that the PersistenceInfoProvider class didn't change on regeneration:

    private void InitTutorsEntityMappings()
    {
        base.AddElementMapping( "TutorsEntity", "test", @"Default", "tutors", 20 );
        base.AddElementFieldMapping( "TutorsEntity", "Tid", "tid", false, (int)MySqlType.Int, 0, 0, 8, true, "LAST_INSERT_ID()", null, typeof(System.Int64), 0 );

Shouldn't the last line above map to System.Int32 instead of Int64?

Indeed, isn't the type of the field in the entity changed to int32?

If you create a new project on the same database, do you get an entity with a field of type int32? the .net type should be updated due to the refresh of the catalog.

i've now regenerated and fixed the mappings which now correctly show Int32 but still get the same runtime error - i'll keep looking for anything else that might have missed being regenerated cleanly:

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMValueTypeMismatchException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: The value 7 is of type 'System.Int64' while the field is of type 'System.Int32'

In case it helps, here's the code where the type is assigned:

CoreLab.MySql.MySqlCommand cmd = new CoreLab.MySql.MySqlCommand(sqlQuery, conn); reader = cmd.ExecuteReader(); if (reader.Read()) { fieldValue = reader[fieldName];

// where sqlQuery is:

"SELECT MIN(Tid) AS pKey FROM tutors"

Is it possible that the type is being determined by the MIN() function rather than the field type? i.e., could the pKey alias have a different type than the field: tutors.Tid?

I'll try modifying my test query to use a where clause and return the field rather than the MIN() value and get back to you...

... no dice - this is quickly becoming more trouble than it's worth - i'm going to revert to your original suggestion and change all mediumint(8 ) to int(10) unsigned

thanks again - sorry we can't resolve this, but you can't win 'em all, eh? - K.

Otis wrote:

kbelange wrote:

i know, this is exactly the opposite of what we saw before, right?

i took a look through the generated code for Int64 usage related to my entities, and i noted that the PersistenceInfoProvider class didn't change on regeneration:

    private void InitTutorsEntityMappings()
    {
        base.AddElementMapping( "TutorsEntity", "test", @"Default", "tutors", 20 );
        base.AddElementFieldMapping( "TutorsEntity", "Tid", "tid", false, (int)MySqlType.Int, 0, 0, 8, true, "LAST_INSERT_ID()", null, typeof(System.Int64), 0 );

Shouldn't the last line above map to System.Int32 instead of Int64?

Indeed, isn't the type of the field in the entity changed to int32?

If you create a new project on the same database, do you get an entity with a field of type int32? the .net type should be updated due to the refresh of the catalog.

I'm confused now, as an entity fetch doesn't use MIN() etc.. Could you post code which fetches an entity which fails? You now use a piece of code which uses plain corelab code, though I thought you had problems fetching entities. So please show me that code and the exception + stacktrace so we can go from there.

MIN()/MAX() should give the type of the column they're operating on, but as this is MySql, anything is possible so it might be they'll always report an unsigned int which then thus results in an int64. However, as that kind of code is fetched in either dynamic lists or scalar queries, you won't run into the type mismatch issues.

Sorry to confuse you - the application (written for my client by a different contractor several years ago) uses a strange mix of Entity & direct Mysql queries.

The failures i'm experiencing depend on which bit of code i hit first (entity or sql).

As i've said, after receiving the go-ahead from my client to change mediumint(8 ) to int(10), i can't justify spending any more time on this, but thanks again for your help.

Otis wrote:

I'm confused now, as an entity fetch doesn't use MIN() etc.. Could you post code which fetches an entity which fails? You now use a piece of code which uses plain corelab code, though I thought you had problems fetching entities. So please show me that code and the exception + stacktrace so we can go from there.

MIN()/MAX() should give the type of the column they're operating on, but as this is MySql, anything is possible so it might be they'll always report an unsigned int which then thus results in an int64. However, as that kind of code is fetched in either dynamic lists or scalar queries, you won't run into the type mismatch issues.

Ok, I'll close the thread then

The saga continues: sorry to ask you to re-open this after asking you to close it, but after following your suggestion and converting all mediumints to integers, i'm still getting the same entity error as above, unless i use the Convert method workaround, as below:

public virtual System.Int64 Tid { get { object valueToReturn = base.GetCurrentFieldValue((int)TutorsFieldIndex.Tid); if(valueToReturn == null) { valueToReturn = TypeDefaultValue.GetDefaultValue(typeof(System.Int64)); } return Convert.ToInt64(valueToReturn);

I've double and triple-checked the db schema scripts and via MySQL admin, i've also restored the original MySQL driver under LLBLGen, in case the new one you sent me was the issue. There are no references whatsoever in the generated code to Tid being anything but a System.Int64

(i.e., help! this was not supposed to happen today)

Otis wrote:

Ok, I'll close the thread then

as another related question, when purchasing the CoreLab.MySQL license, do i have to be careful to purchase the Professional rather than Standard version for "ASP.NET 2.0 provider" support, or does LLBLGen work independently of "ASP.NET 2.0 provider" technology, even if i generate against .NET 2.0 ?

(see http://www.crlab.com/mysqlnet/faq.html#q0)

kbelange wrote:

The saga continues: sorry to ask you to re-open this after asking you to close it, but after following your suggestion and converting all mediumints to integers, i'm still getting the same entity error as above, unless i use the Convert method workaround, as below:

public virtual System.Int64 Tid { get { object valueToReturn = base.GetCurrentFieldValue((int)TutorsFieldIndex.Tid); if(valueToReturn == null) { valueToReturn = TypeDefaultValue.GetDefaultValue(typeof(System.Int64)); } return Convert.ToInt64(valueToReturn);

I've double and triple-checked the db schema scripts and via MySQL admin, i've also restored the original MySQL driver under LLBLGen, in case the new one you sent me was the issue. There are no references whatsoever in the generated code to Tid being anything but a System.Int64

(i.e., help! this was not supposed to happen today)

This is impossible, please REFRESH the catalog and REGENERATE the code. Unsigned int types are mapped to int64, Signed int types are mapped to int32. If your type is an unsigned int, it IS int64, it simply doesn't even fit in an int32 and corelab will return an int64.

And just use the standard version. ASP.NET providers are add-ins for asp.net you write yourselfs, like a role provider to provide data for asp.net's role based security model soyou can write generic code utilizing different role persistence models for example. If you're not going to use that, use the standard version.

(edit): I get perfectly fine mapped fields, so I think something went wrong on your side: be sure that when you regenerate code, the files aren't readonly. A file which shouldn't be readonly but is readonly during code generation will result in an error in the report shown afterwards.

Hi - thanks again for your response - i agree that this seems impossible, but i've regenerated to an absolutely new project, as i wanted to make sure the catalog was being refreshed.

I will try once more, for the sake of my own sanity as much as for understanding. I think, however, that the generated code is actually fine - i can't find any reference in it to anything other than Int64 - it seems to me that its the @#$%@# CoreLab driver version i have (3.50). They have since released a more recent version (3.55), which i will test.

Thanks for your patience - if you have any hints which would allow me to find an error in the generation (i.e., a method or member ofn one of the DatabaseSpecific classes), please let me know - tho' i'm not prepared to spend any time supporting mediumints, i'd really like to understand why ints aren't working!

I just regenerated again to a totally new project, and i still get the same error.

(Edit OK, i uploaded a screenshot of the watch window so that you know i'm not insane.

Would it help you if i could isolate the single table that's failing into a smaller test solution? I'll go ahead and do that.

Otis wrote:

This is impossible, please REFRESH the catalog and REGENERATE the code. Unsigned int types are mapped to int64, Signed int types are mapped to int32. If your type is an unsigned int, it IS int64, it simply doesn't even fit in an int32 and corelab will return an int64.

And just use the standard version. ASP.NET providers are add-ins for asp.net you write yourselfs, like a role provider to provide data for asp.net's role based security model soyou can write generic code utilizing different role persistence models for example. If you're not going to use that, use the standard version.

(edit): I get perfectly fine mapped fields, so I think something went wrong on your side: be sure that when you regenerate code, the files aren't readonly. A file which shouldn't be readonly but is readonly during code generation will result in an error in the report shown afterwards.

OK, i'm not trying to be a smart ass here, just to understand. I went back to the MySQL Manual for my version (4.1) which has the attached table of data types, which seem to indicate that everything but a BIGINT would fit into a 32-bit integer. (BIGINT is 8 bytes which is 64 bits), no? (Edit I've also found a section in the MySQL manual on storage requirements:

11.5. Data Type Storage Requirements ...

Storage Requirements for Numeric Types

Data Type Storage Required TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT, INTEGER 4 bytes (i.e., 32 bits (Ed.)) BIGINT 8 bytes (i.e., 64 bits (Ed.))

FLOAT(p) 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 FLOAT 4 bytes DOUBLE [PRECISION], REAL 8 bytes DECIMAL(M,D), NUMERIC(M,D) Varies; see following discussion

It's the SIGNED and UNSIGNED option for an int that's the problem.

UNSIGNED int's don't fit in a 32bit int32 .net type according to Corelab, so they'll return an int64. SIGNED int's will fit in a 32bit int32 .net type so they'll return an int32.

That's not my invention , that's what corelab does, and what we thus use as mappings too.

And it works OK here, but what's important is that you check if your int is SIGNED or UNSIGNED, that crucial.

What's odd is that apparently llblgen pro sees the schema and sees that "the type is unsigned int" so the field in the entity gets as type int64, and then at runtime, the provider reads the UNsigned int value (entity fetch, I hope that I can assume you're just reading a simple entity here), and returns it as an int32 instead... all on the same schema...

(Edit To answer your question, the code i've quoted is all generated for the entity class.

In case it matters, the call being made one step up the stack is trying to assign the value returned by the TutorsEntity.Tid property to a variable of the ASP control class:

**da.SaveEntity(theTutor);

//update static variable to hold tutor id tutid = theTutor.Tid;**

My apologies if i'm quoting scripture to the converted - i realize you're not making things up, but i'm trying to understand why this is happening and am not getting anywhere.

Please see the attached screen capture which shows that the field is, in fact, an unsigned int.

Otis wrote:

It's the SIGNED and UNSIGNED option for an int that's the problem.

UNSIGNED int's don't fit in a 32bit int32 .net type according to Corelab, so they'll return an int64. SIGNED int's will fit in a 32bit int32 .net type so they'll return an int32.

That's not my invention , that's what corelab does, and what we thus use as mappings too.

And it works OK here, but what's important is that you check if your int is SIGNED or UNSIGNED, that crucial.

What's odd is that apparently llblgen pro sees the schema and sees that "the type is unsigned int" so the field in the entity gets as type int64, and then at runtime, the provider reads the UNsigned int value (entity fetch, I hope that I can assume you're just reading a simple entity here), and returns it as an int32 instead... all on the same schema...