Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Designer> DB2+ LINQ: Problem with Any()
 

Pages: 1
Designer
DB2+ LINQ: Problem with Any()
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 24-Jul-2012 10:58:16.  
Hi,

I have a problem with executing an Any() linq statement on DB2, it's a pretty straightforward query:

Code:
var exists = linq.Opdracht.Any(o => o.NummerLm == 1 && o.Opdrachtnr == 1);


The resulting error is this:

Code:
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred
Message=An exception was caught during the execution of a retrieval query: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "HTNR" = ?)))) THEN 1". Expected tokens may include: "END". SQLSTATE=42601
. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
RuntimeBuild=11292011
RuntimeVersion=3.1.0.0
QueryExecuted=
    Query: SELECT CASE WHEN EXISTS (SELECT "LPLA_1"."WERKBONNUMMER" AS "Werkbonnummer" FROM "RDCINST1"."OPDRACHT" "LPLA_1" WHERE ( ( ( "LPLA_1"."NUMMER_LM" = ?) AND ( "LPLA_1"."OPDRACHTNR" = ?)))) THEN 1; ELSE 0; END CASE AS "LPFA_1" FROM "RDCINST1"."OPDRACHT" "LPLA_1" FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS
    Parameter: @p1 : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 943588.
    Parameter: @p2 : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 61.

StackTrace:
     at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 136
InnerException: IBM.Data.DB2.DB2Exception
     Message=ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "HTNR" = ?)))) THEN 1". Expected tokens may include: "END". SQLSTATE=42601

     Source=IBM.Data.DB2
     ErrorCode=-2147467259
     StackTrace:
            at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows)
            at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method)
            at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
            at IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior)
            at SD.Tools.OrmProfiler.Interceptor.ProfilerDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 112
     InnerException:


It's easy to solve with a Count() > 0, but still Regular Smiley it seems like a bug, or am I just using a DB2 version that's too old?

-W
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37860 posts
# Posted on: 24-Jul-2012 11:26:02.  
When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 24-Jul-2012 14:44:56.  
Otis wrote:
When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...


Nope, running the query without ';' in DB2 gives the following error:

Code:

Error:ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "AS" was found following "EN 1 ELSE 0 END CASE". Expected tokens may include: ",". SQLSTATE=42601


However when I just remove the last CASE statement as well as the semicolons it seems to work...
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 24-Jul-2012 14:49:12.  
wtijsma wrote:
Otis wrote:
When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...


Nope, running the query without ';' in DB2 gives the following error:

Code:

Error:ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "AS" was found following "EN 1 ELSE 0 END CASE". Expected tokens may include: ",". SQLSTATE=42601


However when I just remove the last CASE statement as well as the semicolons it seems to work...


I'm in no way a DB2 expert, but it seems the link you specified is only for using CASE in an update statement (the docs state it can only be used in a stored procedure), in a normal select you don't need 'END CASE' you just specify 'END' (I think this is on par with ANSI SQL-92?)


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37860 posts
# Posted on: 24-Jul-2012 19:01:01.  
Hmm, I think you're right:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023458.htm

If you remove the 'case' after 'end' the query works? It's defined as the IIF() function mapping, so it's easy to fix.

However I'm very confused about the manual page linked above: no END CASE, just END, but also... no ';'... Dissapointed

edit: Ah ! found the explanation:
http://www.sqlpl-guide.com/CASE

Quote:
It's important to know that the CASE statement is different to the CASE expression which will be used in SELECT statements.

I.o.w.: the function mappings we have now are wrong in some cases (pun intended). I'll update the DB2 DQE for you for testing.
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:
37860 posts
# Posted on: 24-Jul-2012 19:15:02. Goto attachments  
Could you try the attached dll?

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 03-Aug-2012 11:34:24.  
Otis wrote:
Could you try the attached dll?


Sorry for the late response, I can't test the DLL as we're still on version 3.1, and we're not really in a position at the moment to update to 3.5...
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37860 posts
# Posted on: 04-Aug-2012 10:45:30.  
I'll upload an updated v3.1 on monday.

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:
37860 posts
# Posted on: 06-Aug-2012 17:14:11. Goto attachments  
Please try the attached DQE (for v3.1)
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Aug-2012 13:39:07.  
Otis wrote:
Please try the attached DQE (for v3.1)


It works! Thanks Regular Smiley


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 23-Aug-2012 14:47:40.  
wtijsma wrote:
Otis wrote:
Please try the attached DQE (for v3.1)


It works! Thanks Regular Smiley


Will the fix be included in a new 3.1 build? If yes, when is the next build planned?

Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37860 posts
# Posted on: 23-Aug-2012 15:32:22.  
3.1 is in maintenance mode, so there will only be new builds when a fix was released and not that frequently. We'll release a new build tomorrow for v3.1, as your fix hasn't been available online yet.

We use 1 'current' code base per version so your fix is in all following builds Regular Smiley (you have a release build, so you can keep using that)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 23-Aug-2012 16:36:18.  
Otis wrote:
3.1 is in maintenance mode, so there will only be new builds when a fix was released and not that frequently. We'll release a new build tomorrow for v3.1, as your fix hasn't been available online yet.

We use 1 'current' code base per version so your fix is in all following builds Regular Smiley (you have a release build, so you can keep using that)


Thanks!
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.