Multiline statements via ODBC fail

Question

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') 

then

SELECT @@IDENTITY

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...

Answer 1

You need semicolon before SELECT:

INSERT INTO MyTable VALUES( '1','2','3'); SELECT @@IDENTITY

After you execute the statements with SQLExecute, call SQLMoreResults and then SQLFetch - this will bring IDENTITY value from the SELECT.