Weirdness with ODBC API

Hi, guys,

Code:

---

                                    for( int count  = 0; count < i; count++ )
                                    {
                                        ret = SQLColAttribute( stmt_col, (SQLUSMALLINT ) count + 1, SQL_DESC_AUTO_UNIQUE_VALUE, NULL, 0, NULL, &autoincrement );
                                        if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                                        {
                                            GetErrorMessage( errorMsg, 1, stmt_col );
                                            result = 1;
                                            SQLFreeHandle( SQL_HANDLE_STMT, stmt_col );
                                            stmt_col = 0;
                                            SQLDisconnect( hdbc_col );
                                            SQLFreeHandle( SQL_HANDLE_DBC, hdbc_col );
                                        }
                                        else
                                        {
                                            if( autoincrement )
                                            {
                                                ret = SQLColAttribute( stmt_col, (SQLUSMALLINT ) count + 1, SQL_DESC_BASE_COLUMN_NAME, NULL, 0, NULL, &autoincrement );
                                                if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
                                                {
                                                    GetErrorMessage( errorMsg, 1, stmt_col );
                                                    result = 1;
                                                    SQLFreeHandle( SQL_HANDLE_STMT, stmt_col );
                                                    stmt_col = 0;
                                                    SQLDisconnect( hdbc_col );
                                                    SQLFreeHandle( SQL_HANDLE_DBC, hdbc_col );
                                                }
                                                else
                                                    autoinc_fields.push_back( fieldName );
                                            }
                                        }
                                    }
                                }

---

For some reason this code above fails for SQLColAttribute().

The value of the count is 29 and the value of i is 51 and the error is "Invalid descriptor index", which according to the MSDN means "column is out of bounds".

I am trying to connect to MS SQL Server 10.0 from MSVC 2017 on Windows 8.1. It is for "INFORMATION_SCHEMA.ROUTINES" table. Server is running on the same machine.

Can anyone please explain this?

Thank you.

How did you get "the value of i is 51"?

VictorN

Quote:

---

Originally Posted by VictorN

How did you get "the value of i is 51"?

---

I'm looking at the debugger.

Thank you.

Quote:

---

Originally Posted by OneEyeMan

VictorN


I'm looking at the debugger.

Thank you.

---

LOL!
And who, how and where did set this value to be 51?

Hi,

Quote:

---

Originally Posted by VictorN

LOL!
And who, how and where did set this value to be 51?

---

Code:

---

                                ret = SQLColumns();
                                ret = SQLBindColumn();
                                ret = SQLBindColumn();
                                SQLLEN autoincrement;
                                int i = 0;
                                for( ret = SQLFetch( stmt_col ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( stmt_col ) )
                                {
                                    // save the info about column
                                    i++;
                                }

---

The error checking and the parameters are omitted for simplification.

Thank you.

Why is SQLBindColumn called twice?

Hi,

Quote:

---

Originally Posted by Arjay

Why is SQLBindColumn called twice?

---

Its actually called many times - for every column that SQLColumns() return.

Thank you.

Quote:

---

Originally Posted by OneEyeMan

For some reason this code above fails for SQLColAttribute().

The value of the count is 29 and the value of i is 51 and the error is "Invalid descriptor index", which according to the MSDN means "column is out of bounds".

I am trying to connect to MS SQL Server 10.0 from MSVC 2017 on Windows 8.1. It is for "INFORMATION_SCHEMA.ROUTINES" table. Server is running on the same machine.

---

And what is the column name at which the error happens?

Quote:

---

Originally Posted by OneEyeMan

Hi,


Its actually called many times - for every column that SQLColumns() return.

Thank you.

---

Yes, but I expect it to be called in a loop with once per column. Not like the following code where the 2nd call eats the return call of the first:

Code:

---

ret = SQLColumns();
ret = SQLBindColumn();
ret = SQLBindColumn();

---

Hi,

Quote:

---

Originally Posted by Arjay

Yes, but I expect it to be called in a loop with once per column. Not like the following code where the 2nd call eats the return call of the first:

Code:

---

ret = SQLColumns();
ret = SQLBindColumn();
ret = SQLBindColumn();

---

---

I can't call this in a loop, because I'm binding to a different variable names.

And I did say that error checking is omitted.

Thank you.

Hi,

Quote:

---

Originally Posted by VictorN

And what is the column name at which the error happens?

---

It is a 29th column - something about catalog. I will post a name tonight when I get home.

Thank you.

Quote:

---

Originally Posted by OneEyeMan

Hi,


It is a 29th column - something about catalog. I will post a name tonight when I get home.

Thank you.

---

Could you post a code snippet reproducing your problem, so we would run and test it?

Quote:

---

Originally Posted by OneEyeMan

Hi,


I can't call this in a loop, because I'm binding to a different variable names.

And I did say that error checking is omitted.

Thank you.

---

Have you ever seen the signs advertising office space, "If you worked from here, you would be home by now"?

That's what coding up raw ODBC is like vs. using an ORM. Sure, there might be an initial learning curve but you won't have to endure the pain associated with having to write code that maps every column to the correct data type, and remember to free the handles and release the memory. Pure drudgery (and not to mention error prone).

ODB - C++ Object-Relational Mapping (ORM)

Hi

Quote:

---

Originally Posted by VictorN

Could you post a code snippet reproducing your problem, so we would run and test it?

---

Code:

---

int i = 0;
SQLSMALLINT numColumns;
ret = SQLColumns( stmt_col, catalog_name, SQL_NTS, L"INFORMATION_SCHEMA", SQL_NTS, L"ROUTINES", SQL_NTS, NULL, 0 );
ret = SQLNumResultCols( stmt_col, &numColumns );
for( ret = SQLFetch( stmt_col ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( stmt_col ) )
{
// save the info about column
i++;
}
for( int count  = 0; count < i; count++ )
{
    ret = SQLColAttribute( stmt_col, (SQLUSMALLINT ) count + 1, SQL_DESC_AUTO_UNIQUE_VALUE, NULL, 0, NULL, &autoincrement );
    if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
    {
    }
}

---

Now trying to look at this and trying to put this code together I now understand where 29 is coming from.

The 29 is coming from the variable numColumns.

And so I think the only way to run this is to execute "SELECT * FROM INFORMATION_SCHEMA.ROUTINES", get the number of columns and run that loop.

Thank you.

What info about INFORMATION_SCHEMA.ROUTINES would you like to get from this raw ODBC code?