When using the following source code

double result = Convert(0.75);
public double Convert(double value)
{
      long sourceUnit = (long)UnitEnumeration.Millilitre;
      long destinationUnit = (long)UnitEnumeration.Litre;

      LinqMetaData metaData = new LinqMetaData(Adapter);
      var query = from unitConversion in metaData.UnitConversion
                         where unitConversion.FkUnitIdSource == sourceUnit
                              && unitConversion.FkUnitIdDestination == destinationUnit
                         select (value * unitConversion.MultiplyBy) / unitConversion.DivideBy;

      return query.Single();
}

MySQL throws the following exception

An exception was caught during the execution of a retrieval query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE))) / (CAST(`LPLA_1`.`DivideBy` AS DOUBLE)) AS `LPFA_1` FROM `unit_convers' at line 1.

The generated sql is this one :

SELECT (0.75 * (CAST(`LPLA_1`.`MultiplyBy` AS DOUBLE))) / (CAST(`LPLA_1`.`DivideBy` AS DOUBLE)) AS `LPFA_1` FROM `unit_conversion`  `LPLA_1` WHERE ( ( ( ( ( ( ( `LPLA_1`.`FK_unit_Id_Source` = 5) AND ( `LPLA_1`.`FK_unit_Id_Destination` = 3))))))) LIMIT 1

Of course, if I execute this sql command directly in MySql Browser, the same error occurs.

In fact, the matter is that DOUBLE isn't a correct value for the CAST function. See MySql documentation.

 CAST(expr AS type)

The CAST() function takes a value of one type and produce a value of another type, similar to CONVERT(). See the description of CONVERT() for more information.

 CONVERT(expr,type), CONVERT(expr USING transcoding_name)

The CONVERT() and CAST() functions take a value of one type and produce a value of another type.

The type can be one of the following values:

BINARY[(N)]

CHAR[(N)]

DATE

DATETIME

DECIMAL[(M[,D])]

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

Please, I need help

Please provide the RTLs build numbers. (ORMSupportClasses & LinqSupportClasses) Please check this thread for more info: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7719

As asked :

• LLBLGen Pro version : 3.0 Final (January 3rd, 2011)
• SD.LLBLGen.Pro.ORMSupportClasses version : 3.0.10.1111
• SD.LLBLGen.Pro.LinqSupportClasses.NET35 version : 3.0.10.1105
• Template group + .NET version : Adapter, .NET 3.5
• Database version : MySql 5.0.89

mmm. that's a one year old. I couldn't find any changelog entry for this, so a good probability this issue still exists in recent build. But it would be of a great help if you can try it out with v.3.1, please.

I will try it (tomorrow) but the idea is just that this is not a LLBLGEN error but a MySQL error because DOUBLE isn't supported by CAST function which I REALLY REALLY DON'T UNDERSTAND. It's just crazy.

So, I was thinking that you (or one of your customer) already encountered this type of error.

Actually, I find a horrible workaround that consist to cast the "double" variable in a "decimal" variable (I know that decimal isn't double but what can I do?). Because MySQL CAST function supports decimal, all is ok but this is very ugly.

Ugh... I feel your pain... MySql never fails to amaze with its silly quirks.

I found someone who has a workaround: https://plus.google.com/103639388493914610148/posts/CwjZgLMBosy

POW(X, 1) does the trick it seems.

We map Math.Pow() for MySQL, so select (value * unitConversion.MultiplyBy) / unitConversion.DivideBy;

should be select (Math.Pow(value, 1) * unitConversion.MultiplyBy) / unitConversion.DivideBy;

does that do the trick? (yes, it's ugly, just to assure it's doing the trick). If so, we'll change the runtime mappings, as the current one doesn't work at all anyway. Please confirm if this works for you. (as we can't overlook side effects at this point with respect to this 'pseudo conversion').

Not

select (Math.Pow(value, 1) * unitConversion.MultiplyBy) / unitConversion.DivideBy;

but

select (value * Math.Pow(unitConversion.MultiplyBy,1)) / Math.Pow(unitConversion.DivideBy,1);

and yes, this should works.

But in fact, I can't really test it because this is the generated sql that must take it into account.

You will find a sample application (with 3.1 version of LLBLGEN) as an attachment where you just need to :

• create a mysql_cast_test schema
• run the sql scripts (table creation and data import)
• change the connection string (in the TestButton_Click event handler)

I removed the LLBLGEN dll because the file was too big to be uploaded.

sybjeb wrote:

Not

select (Math.Pow(value, 1) * unitConversion.MultiplyBy) / unitConversion.DivideBy;

but

select (value * Math.Pow(unitConversion.MultiplyBy,1)) / Math.Pow(unitConversion.DivideBy,1);

and yes, this should works.

But in fact, I can't really test it because this is the generated sql that must take it into account.

I don't follow what you mean exactly. Your linq query results in a cast to double, and the Math.Pow() will do the double cast for you using Pow(x, 1). Math.Pow is mapped onto POW(x, y), so it will generate proper SQL. So if the above code didn't work, I really want to know what exactly didn't work?

Anyway, we'll try to test it ourselves as well, I was just after a real-world scenario where it worked OK. (unit tests are 'ok' but I really want real-life scenario's as well) We hope to have results on / before monday.

(removed attachment because it contains security information)

I reproduced it locally: var metaData = new LinqMetaData(adapter); var q = from t in metaData.Tinyinttest where (t.IntSigned * value) > 6.0 select t;

'value' is a double.

The math.pow workaround indeed doesn't work, as it implies it accepts a double so the implicit conversion int -> double done by .net is in the expression tree and therefore ending up in as a cast to double.

We'll change the runtime and see whether that fixes things.

Using POWER(x, 1) results in the wanted query. We've changed this in the DQE. I've attached the new DQE for you to test. Could you please test the attached DQE in your project (so you can get rid of the Math.Pow(x, 1) code, just use the query you had).

Yes, the workaround does its job.

Thanks to you.

Don't tell me that the guys of LLBLGEN aren't efficient

sybjeb wrote:

Yes, the workaround does its job.

Good!

sybjeb wrote:

Thanks to you.

Don't tell me that the guys of LLBLGEN aren't efficient

Thanks for the feedback!