Question:
When attempt to run multiple SQL queries using WinSQL product via CA Datacom Server, the queries fail with WinSQL "BAD SYNTAX" errors. Running one request at a time has no problem.
Does Datacom SQL not handle multiple queries in succession?
For example, with these SQL queries:
SELECT * FROM PROD.PO_SIZE WHERE NUM_PO = 309888;
SELECT * FROM PROD.PO_LOT WHERE NUM_LT = '145981';
Result are errors:
Error: BAD SYNTAX: <CURSOR FOR SELECT * FROM PROD.PO_SIZE WHERE NUM_PO = 309888; (State:42601, Native Code: FFFFFFEC)
Error: BAD SYNTAX: <CURSOR FOR SELECT * FROM PROD.PO_SIZE WHERE NUM_PO = 309888; (State:42601, Native Code: FFFFFFEC)
Answer:
CA Datacom SQL and CA Datacom Server do not have any restriction for submitting multiple SQL requests.
Use a Query Terminator in WinSQL to break the scripts into multiple queries before submitting it to the back-end database.
WinSQL Query Terminator allow users to write a script like:
select * from table1
go
select * from table2
In this case, WinSQL will first send a query to fetch records from table1 and then send a second request for table2.
By default, WinSQL uses the word "GO" as a Query Terminator.
To change this value click Edit/Options on the main menu and change the value for Query Terminator String.
Change the string to ";" terminator.
Additional Information:
An internet search for "WinSQL Query Terminator" provides good information.