I have written an ODBC wrapper in C++ and use it to execute queries, bind results and return data sets. Everything appears to work fine and I have been using it for a long time for single line statements like: SELECT * FROM MyTable WHERE ...
Recently I started trying to run multi-line statements and am having no luck. A practical example would be something like:
INSERT INTO MyTable VALUES( '1','2','3') SELECT @@IDENTITY
I submit this as a string just as shown with SQLPrepare() and then call SQLNumResultCols() to figure out how many result columns to bind. This function returns zero. with the error "Invalid cursor state"
If I execute the two queries individually ie:
INSERT INTO MyTable VALUES( '1','2','3')
I get the correct return values.
Another example would be a statement like:
DECLARE @TableName NVARCHAR(MAX) OPEN Tables FETCH NEXT FROM Tables INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN EXEC( 'DROP TABLE ' + @TableName ) FETCH NEXT FROM Tables INTO @TableName END
Which returns a syntax error:
Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'TABLE'.
I suspect I am missing some information about the ODBC SQL parsing engine...