Invalid descriptor index on LAST_INSERT_ID after insert

0
votes

Strange situation with my ODBC code ( called from a C library ). Basically, I have the following sequence of events:

  1. Create insert statement ( just a string )
  2. Call SQLPrepare with that insert statement string
  3. Bind the various parameters ( column values ), using SQLBindParameter
  4. Call SQLExecute to insert the row ( this works, by the way, as I can see the row in the MySQL DB )

  5. Create "select last_insert_id()" statement string

    NOTE: if in SQL Server mode, we would create a "select @@identity" statement

  6. Bind column using SQLBindCol - this is where I get the "Invalid descriptor index" error

    NOTE: if in SQL Server mode, this works fine, with no error

  7. Call SQLExecDirect to get the last insert id - this never happens because of SQLBindCol error

Does the standard MySQL ODBC connector require something special in this situation? Does anyone have an ODBC example of this type of "insert" then "get last insert id" behavior? Maybe I need to call "SQLPrepare" before step 6 ( where I bind the column )? Another way to ask this: Should there be an SQLPrepare call for each SQLExecute or SQLExecDirect call?

I know it works directly in SQL, so the problem is my C ODBC code.

Thanks.

1
mysqlcodbc

1 Answers

0
votes

For those who are interested, I ended up changing the above steps by adding an SQLPrepare call between creating the "select last_insert_id()" ( step 5 ) and calling SQLBindCol ( step 6 ). Not sure if that would work for others, but it seems to be working rather well for me.

As for research, I looked all over the place online and never found a really good or clear answer. Most comments were about the SQL involved, not ODBC. And the references to ODBC were vague and did not seem to apply to my situation, from what I could see.

My assumption is that the SqlServer ODBC driver I am using handles the missing prepare statement differently ( maybe even better, but that is debatable ) than my MySql ODBC driver.

  • SQL Server ODBC driver was the one provided by Easysoft
  • MySql ODBC driver was the one provided with the standard CentOS install of MySql

Hopefully this will help people. Obviously, if people have a better idea, please tell us.