ODBC bind by name instead of number

[Todd Horst]

Code:

odbc SQLBindCol $hstmt 5 SQL_C_CHAR office_code 10 pcbValue1

So in all my odbc script is bind to a column using the above code. Up until this point I didnt mind since I controlled the db i was looking at.

However a new project has me looking into a db i dont own, and cant control. I would feel more comfortable if i could bind to a column by its name.

So, for the example above I’d like to replace the 5 with “office_code”. I’ve tried this and it fails.

Is this possible?

Topic

[David Barr]

I’m pretty sure that the numbers (5 in this case) correspond to the order of columns in the result of the statement, not on the order of the columns in the table, so even if the table columns change, your numbers wouldn’t have to change.  Of course if you are using wildcards (“select *”), then a table change could affect your query column order.  So, don’t use wildcards and you’ll be OK.

[Todd Horst]

That’s exactly the issue, the stored procedure I’m calling does do a *. Also even if they specifically called the fields in a predefined order then they could always change that without me knowing.

Like I said I don’t have full control over this, or rather I have no control over this, so if I can I would like to use the column name, to be safe. If that’s not possible then I will obviously make due.

[David Barr]

It looks like ODBC has a method called ResultSet.findColumn that you can use to translate column names to integer positions, but I can’t find anything like this in the TCL interface to ODBC.

A workaround is to loop through each of the columns and call SQLDescribeCol to get the column name and put them in a list.

[Author]

Replies