Subquery error in SQL Server

[Giorgio Bravi]

TD 7.3.3 32bit / SQLServer
Hi all
I'm migrating a really big project ( +500 apps ) from 5.2 to 7.3 and have discovered a big problem:
Populating a child table or simply executing a SqlPrepare + SqlExecute or SqlPrepareAndExecute with a statement like this:

SELECT TAB1.COL1, TAB1.COL2 INTO :MyField1, :MyField2 FROM TAB1 WHERE TAB1.COL1 IN ( SELECT TAB2.COLX FROM TAB2 WHERE TAB2.COLY = :MyVar )

results in an "statement not prepared" error probably because :MyVar is not replaced by the relative value.
All was running well in 5.2
Has anyone had the same problem?
Thanks for the attention

Giorgio Bravi

[Giorgio Bravi]

Just for info: replacing :MyVar with a fixed value
SELECT TAB1.COL1, TAB1.COL2 INTO :MyField1, :MyField2 FROM TAB1 WHERE TAB1.COL1 IN ( SELECT TAB2.COLX FROM TAB2 WHERE TAB2.COLY = 'XXX' )
eliminates the error but is not an applicable solution

[Steve Leighton]

Please specify OLEDB or ODBC , so can try and replicate like for like.

[Giorgio Bravi]

OLEDB

You can replicate the problem populating a Child table or a grid using SalTblPopulate

[Giorgio Bravi]

Here some help to re-create a test case.

testcase.zip

[Steve Leighton]

.
Firstly, I see you are using a v-e-r-y old OLEDB driver ( decommissioned in fact ).
You should be using MSOLEDBSQL.1 , regardless of the issues you are having.
I have been using v18.3 on TD 6.2 thru 7.3.0 with no issues ( apart from tuning tweaks )

OLEDB Provider.JPG

Downloadable here: https://docs.microsoft.com/en-us/sql/co ... rver-ver15 and Read about it here: https://accessexperts.com/blog/2018/09/ ... db-driver/
so it could be worth a try swapping to a supported version.

Secondly, you have declared variable :sMyVar as a global. Never seen that before !
Have you tried it as a local and fully qualified it. i.e. 'WHERE TAB2.COLY = :hWndForm.frm1.grid1.sMyVar'
I understand this is a mute point, but anything to help the parser find the bind variable would help.

Also, as a bye-the-bye, you could try different settings in your connection string, which will r-e-a-l-l-y help with performance if need be.
Suggest adding 'Properties="CLIENTCURSOR=TRUE;SSPROP_INIT_PACKETSIZE=8192";Packet Size=8192;' - and tweaking various combinations of these settings to see which works best.
e.g. changing packet size. But I found this Packet size improving performance by many times.

In the meantime , I only have TD7.3.0 ( works fine with that ver. , using the newer driver ) , so will have to download 7.3.3. and try with that, unless someone beats me to it.

An update on how you get on with the newer driver, and using a local fully qualified variable, may save a bit of work in the meantime.

[Giorgio Bravi]

Thank you for the suggestions Steve,
updating the driver and setting the parameters as you suggested gives a small performance gain but I have yet to test it on a production database. In these cases any improvement is always appreciated

Just to update you on test case: declaring the variable local don't solve the problem so it seems a regression on TD 7.3.2/7.3.3

[Steve Leighton]

Hello Giorgio

I installed TD7.3.3 and ran your exact supplied sample code and it worked fine A-Ok using the bind variable. ':sMyVar' receiving the value 'TEST'

The only 2 differences I applied , was:
1) Check first the SqlConnect() works.
2) The SqlUDL string is fetched from a file ( not harcoded ), and obviously is different to yours.

Is it possible your connection string is causing the SQLConnect to fail first, as you don't seem to trap that. Hence the SqlPrepare would also fail.

Here is the connection string I used to test: 'Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=TESTDB;Data Source=ZEUS\MSSQLSERVER2017;Extended Properties="CLIENTCURSOR=TRUE;SSPROP_INIT_PACKETSIZE=8192";Packet Size=8192;Application Name=TEST;Initial File Name="";Trust Server Certificate=True;Server SPN="";Authentication="";Access Token=""

TD7.3.3.JPG

Result.JPG

[Giorgio Bravi]

Yes!
You are right. The problem was the connection string and the old driver we use.
I mistakenly thought the problem was a regression as it worked in version 7.3.1
Thanks for your help.