Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.