Common Table Expression in SQL-commands under TD 5.2

Discussions specific to Team Developer 5.2.

Common Table Expression in SQL-commands under TD 5.2

Postby GruberW » Sat Aug 22, 2015 3:30 am

Hello,

For the first time, i try to use common table expression(CTE) (with clause in SQL-select) under TD 5.2. Database is SQL-server 2008 R2. The select runs fine under SQL Server management studio and runs fine from excel by ODBC-connection.
From within TD i always receive error 113 on executing the SqlPrepareAndExecute command.
The Sql-command looks like:

Code: Select all
Set strSql = '
with   inci as
   (select qseko.ENDPRODUKTID, qseko.ROHPRODUKTID,   qsrin.HWI_NUMMER,   SUM(qseko.menge_in_g) as Prozent
   from   QSEKO, QSRIN
   where   qsrin.ROHPRODUKTID = qseko.ROHPRODUKTID
   group by   qseko.endproduktid, qseko.ROHPRODUKTID, qsrin.HWI_NUMMER)
select    inci.rohproduktid,   inci.hwi_nummer,   QSROH.BEZEICHNUNG,
   inci.prozent      
into   :udvListe[i].nrRohproduktId,   :udvListe[i].nrHwinummer,   :udvListe[i].strHandelsname,
   :udvListe[i].nrProzent   
from    inci, qsroh
where    inci.endproduktid = :nrRezepturId
and   qsroh.ROHPRODUKTID = inci.ROHPRODUKTID
'
GruberW
 
Posts: 3
Joined: Sat Aug 22, 2015 3:20 am
Location: Rheinzabern, Germany

Re: Common Table Expression in SQL-commands under TD 5.2

Postby Jeff @ PC Design » Sat Aug 22, 2015 1:21 pm

Well, according to DBERROR.EXE (shipped with TD) error 133 is:
Reason: The application program is attempting to perform a bind operation and has exceeded the number of binds necessary.

so that is the first place I looked. This part of the WHERE clause looks to be the problem:
and qsroh.ROHPRODUKTID = inci.ROHPRODUKTID


'inci.ROHPRODUKTID' does not look to be a qualified var. in the appl. (the colon ':' is also missing) because 'inci' is listed in your FROM <tables> clause. You will need to make that a SUB-SELECT in the WHERE or first have a select that fetches the ID into a local var, then use that var in the WHERE clause instead.

AFTER NOTE: I looked at your query again and it's more complex than I first saw. The query may be too complex for TD because when TD parses out a query via SqlPrepare() it needs to find an INTO clause to match your SELECT <column list>; in this case the:
select qseko.ENDPRODUKTID, qseko.ROHPRODUKTID, qsrin.HWI_NUMMER, SUM(qseko.menge_in_g) as Prozent...

and there isn't one.

A test to run is to use SQLTalk to run that query, after removing the "into... " clause. Can SQLTalk run that query? That fact that the query...
runs fine under SQL Server management studio and runs fine from excel by ODBC-connection

does not mean that TD can handle a complex DB-specific syntax like this too. You may need to break that query up into a 2 SELECTs, each with its own INTO clause.
Jeff Luther/PC Design http://www.jeffluther.net/TD/
Image
Come join us at Dave Rabelink's "Team Dev. Community Forum".
All TD, TDMobile & SQLBase users welcome!
(http://tdforum.daverabelink.net/index.php)
Jeff @ PC Design
 
Posts: 206
Joined: Fri Feb 08, 2013 2:40 pm
Location: Palm Springs, California

Re: Common Table Expression in SQL-commands under TD 5.2

Postby GruberW » Sun Aug 23, 2015 11:47 pm

Hello Jeff,

thank you very much for your detailled answer.
The error message was 113 - not 133. inic.ROHPRODUKTID is a colomn of the CTE-expression.
I broke the query up into 2 SELECTs and my application works fine.
Nevertheless for future development i'm interested to know, whether TD supports CTE's (the with clause). Are there any guys from Gupta, who can confirm, whether TD supports CTE's or not.
Unfortunatly i'm not very familiar with TD and i never used SqlTalk. I think - as a next step - it's a good idea, to set up my SQLTalk environment and test the query there.

Wolfgang Gruber
GruberW
 
Posts: 3
Joined: Sat Aug 22, 2015 3:20 am
Location: Rheinzabern, Germany

Re: Common Table Expression in SQL-commands under TD 5.2

Postby lrcuess » Mon Aug 24, 2015 6:56 am

Hi,

I also had troubles when using common table expressions with TD.
It seems that TD analyzes the SQL string to detect the kind of command to be executed, maybe by comparing the first word with "SELECT", "INSERT", "UPDATE" and "DELETE".
As CTEs start with the word "WITH" TD seems to have problems!

Workaround:
If you have influence on the database you use, you may want to create a table valued function that contains the CTE.
From TD you'll do a simple "SELECT .... FROM [MyTableValuedFunction](:param1, :param2, :param3) INTO :var1, :var2, ....".

If Gupta enhances their database router to support CTEs, please note, that CTEs can also be used with "UPDATE", "INSERT" and "DELETE" commands.
So "WITH" doesn't necessarily mean that the command is used to query data!

Max
Markus Eßmayr
RACON Software GmbH
http://www.racon.at
User avatar
lrcuess
 
Posts: 1163
Joined: Mon May 07, 2007 5:58 am
Location: Linz, Austria

Re: Common Table Expression in SQL-commands under TD 5.2

Postby GruberW » Mon Aug 24, 2015 7:40 am

Hi Max,

thank you for your answer. Yes, it seems that TD 5.2 doesn't support CTE's. Do you know about support in actual TD-releases?
My workaround is not to use CTE's. :(
GruberW
 
Posts: 3
Joined: Sat Aug 22, 2015 3:20 am
Location: Rheinzabern, Germany

Re: Common Table Expression in SQL-commands under TD 5.2

Postby lrcuess » Mon Aug 24, 2015 7:48 am

You're welcome!
I'm sorry, I don't know about CTE support and I didn't try with newer versions yet.
You could try to download a newer TD version and perform a simple test.

Hopefully, Gupta also comments on this!

Max
Markus Eßmayr
RACON Software GmbH
http://www.racon.at
User avatar
lrcuess
 
Posts: 1163
Joined: Mon May 07, 2007 5:58 am
Location: Linz, Austria

Re: Common Table Expression in SQL-commands under TD 5.2

Postby Jeff @ PC Design » Mon Aug 24, 2015 9:16 am

Wolfgang: Thanks for the '133' vs. '113' correction. I had typed in 113 into DBERROR to get the error info; I just mistyped '133' into my reply ;-(

On a side note -- a couple thoughts:
>> Yes, Wolfgang, you would need to get the new TD v6.3 demo to see if his CTE issue can be used. Docs for the newer TD versions should include that feature, though I doubt it is supported. And it would never be added to an older (already released) TD version.

Generally, you need to have purchased or upgraded to the current TD v6.x and have gotten access to the hidden TD v6.x forum area. Then, if TD doesn't support CTE, you might be able to make a case for getting a feature enhancement for CTE by posting a msg. into that hidden forum area.

See this forum page for more info. on the hidden TD v6.x forum area: viewtopic.php?f=1&t=6589

>> Gupta never adds features or enhancements to old(er) versions of TD; only for a future version.
Jeff Luther/PC Design http://www.jeffluther.net/TD/
Image
Come join us at Dave Rabelink's "Team Dev. Community Forum".
All TD, TDMobile & SQLBase users welcome!
(http://tdforum.daverabelink.net/index.php)
Jeff @ PC Design
 
Posts: 206
Joined: Fri Feb 08, 2013 2:40 pm
Location: Palm Springs, California


Return to Team Developer 5.2

Who is online

Users browsing this forum: No registered users and 2 guests