ODBC SQLConnect – Success with INFO – but no INFO

[Peter Heggie]

My SQL code is working but it bothers me that I get SQL_SUCCESS_WITH_INFO on the SQLConnect call. I tried executing this statement immediately afterward:

odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 1024 TextLength

which works, but when echoing SqlState, NativeError, MessageText and TextLength, I get values of blank, 0, blank and 0.

I don’t know why I got a Success with INFO on the connect. Even though I can then execute SQL on the connection / statement handle, I’d like to be able to handle real connection errors if there are any. Has anyone gotten a result of SUCCES_WITH_INFO and been able to get diagnostic data back from SQLGetDiagRec ?

Code:

# use ‘blank’ entry in odbc.ini with overriding attributes of address, uid, pass, db
set inconn “DSN=SQLServer Wire Protocol;ADDRESS=$addr,$port;UID=$user;PWD=$pass;DB=CLOVERSQL_TEST”
set linconn [string length $inconn]

set SqlState “”
set NativeError 0
set MessageText “”
set TextLength 0

# initialize ODBC environment
set err [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
if {$debug} {puts “[gts] DEBUG: SQLAllocHandle ODBC err: $err”}

# Set the most current ODBC version (3.0)
set err [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
if {$debug} {puts “[gts] DEBUG: SQLSetEnvAttr err: $err”}

# Allocate connection handle
set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]
if {$debug} {puts “[gts] DEBUG: SQLAllocHandle CONN err: $err”}

# set timeout
set err [odbc SQLSetConnectAttr $hdbc SQL_ATTR_LOGIN_TIMEOUT 3 1]  
if {$debug} {puts “[gts] DEBUG: SQLConnectAttr Timeout err: $err”}

# Make a connection
set err [odbc SQLDriverConnect $hdbc NULL $inconn $linconn NULL NULL NULL SQL_DRIVER_NOPROMPT]
if {$debug} {puts “[gts] DEBUG: SQLConnect err: $err”}

if {$err eq “SQL_ERROR” || $err eq “SQL_SUCCESS_WITH_INFO”} {
 odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 1024 TextLength
 echo “[gts] state $SqlState NativeError $NativeError MessageText $MessageText TextLength $TextLength”
 catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
 catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}
 exit
}

Result:

Code:

12/04/02 09:01:03.169 DEBUG: SQLAllocHandle ODBC err: SQL_SUCCESS  
12/04/02 09:01:03.170 DEBUG: SQLSetEnvAttr err: SQL_SUCCESS        
12/04/02 09:01:03.170 DEBUG: SQLAllocHandle CONN err: SQL_SUCCESS  
12/04/02 09:01:03.170 DEBUG: SQLConnectAttr Timeout err: SQL_SUCCESS
12/04/02 09:01:03.183 DEBUG: SQLConnect err: SQL_SUCCESS_WITH_INFO  
12/04/02 09:01:03.183 state  NativeError 0 MessageText  TextLength 0

Peter Heggie
PeterHeggie@crouse.org

Topic

[Todd Horst]

So i forget whether ive been able to get the info out or not. But i use the query directly in Sql server management studio and view the info there. That led me to the fix

[Peter Heggie]

I’ve got sql server management studio – how do I make the ODBC calls inside it? And you can see the SUCCESS WITH INFO result and you can see the details of the INFO?

Peter Heggie
PeterHeggie@crouse.org

[Todd Horst]

execute the same query you were in the tcl code, in sql management. in the results pane there are 2 tabs, look on the messages tab.

Ps i found your question because im looking on how to return multiple result sets to a tcl script…have you ever done that?

[Peter Heggie]

no I haven’t done that; saw a lot of verbiage about multiple result sets in sql server, but not from ODBC. You could maybe force multiple result sets into a single result set – it would be really ugly – then preprocess the results to break out the different sets of data.

each row could look like this:

If flag = 0 then rs1.a,b,c would have data and rs2.a,b,c would be null, and if flag = 1, then rs1.a,b,c would be null and rs2.a.b.c would have data. That is so ugly. Sorry, not much help.  

You could use functions that return a temporary table to a calling stored procedure, and in the stored procedure, select values off the table into the final result set.

Peter Heggie
PeterHeggie@crouse.org

[Author]

Replies