Solved Subquery error in SQL Server

Discussion forum about all things Gupta, OpenText and the community.
Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Subquery error in SQL Server

Post by Giorgio Bravi » 27 Oct 2020, 10:29

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
DOLPHIN Software & Thinkware
www.dolphin.it

Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Re: Subquery error in SQL Server

Post by Giorgio Bravi » 27 Oct 2020, 10:37

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
Giorgio Bravi
DOLPHIN Software & Thinkware
www.dolphin.it

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Subquery error in SQL Server

Post by Steve Leighton » 27 Oct 2020, 21:53

Please specify OLEDB or ODBC , so can try and replicate like for like.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Re: Subquery error in SQL Server

Post by Giorgio Bravi » 28 Oct 2020, 17:09

OLEDB

You can replicate the problem populating a Child table or a grid using SalTblPopulate
Giorgio Bravi
DOLPHIN Software & Thinkware
www.dolphin.it

Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Re: Subquery error in SQL Server

Post by Giorgio Bravi » 28 Oct 2020, 17:16

Here some help to re-create a test case.
testcase.zip
You do not have the required permissions to view the files attached to this post.
Giorgio Bravi
DOLPHIN Software & Thinkware
www.dolphin.it

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Subquery error in SQL Server

Post by Steve Leighton » 28 Oct 2020, 21:57

.
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.
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Re: Subquery error in SQL Server

Post by Giorgio Bravi » 29 Oct 2020, 15:31

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
Giorgio Bravi
DOLPHIN Software & Thinkware
www.dolphin.it

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Subquery error in SQL Server

Post by Steve Leighton » 01 Nov 2020, 06:05

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
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Giorgio Bravi
Italy
Posts: 81
Joined: 09 Mar 2017, 18:08
Location: Bergamo, Italy

Re: Subquery error in SQL Server

Post by Giorgio Bravi » 02 Nov 2020, 09:32

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.
Giorgio Bravi
DOLPHIN Software & Thinkware
www.dolphin.it

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests