odbc question

[Mike Campbell]

I am trying to run a called TCL proc from inside an Xlate.

Basically, I’m querying a database for a primary MRN and a merge MRN.

The first MRN passed works fine.  It queries the database and returns the expected results.  The next execution of the TCL proc doesn’t work.  It is like the SQL process is still active.  After each result we are issuing:

  # free ODBC handles

    set err [odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt]

    set err [odbc SQLDisconnect $hDBConnection]

    set err [odbc SQLFreeHandle SQL_HANDLE_DBC $hDBConnection]

    set err [odbc SQLFreeHandle SQL_HANDLE_ENV $henv]

What else do we need to do to free up the connection?

Thanks.

Topic

[Jim Kosloskey]

Mike,

Those are the handles I freee at disconnect.

Are you checking the return with each FreeHandle to make sure it worked?

Also, are you realocating and reconnecting with each invocation?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Mike Campbell]

I sure thought I was.  I’ll check the status after each instruction to make sure it works.  Thanks for the ideas.  I’ll let you know what I discover.

[Mike Campbell]

I echo the results after all of the FreeHandle instructions, and all return the SQL_SUCCESS.  

Next question, exactly what is meant by “realocating and reconnecting with each invocation.”?  I’ve tried to add the ‘mode’ start, run, etc. but it doesn’t like it.

[Jim Kosloskey]

Mike,

What I was asking was whether you allocated handles and connected every time the proc was executed.

I see that you do.

Are multiple queries being done for one mesage or is this proc only executed once within an Xlate per message?

What exactly do you see in the log? I ask because it looks like you are displaying when there is no data found as a result of the query and you also potentially echo out the return from the query command.

Is the query successful but no data was found when the problem arises or is there some other pattern?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Mike Campbell]

The xlate passes first the primary MRN.  Then the database is queried for the merged MRN, or secondary one.    

The results are:

Starting BloodBank – Cerner proc

Primary MRN:  6106635

test1 results is: SQL_SUCCESS

test2 results is: SQL_SUCCESS

test3 results is: SQL_SUCCESS

SQLConnect :  SQL_SUCCESS

First conneect stmt: SQL_SUCCESS

Second conneect stmt: SQL_SUCCESS

Third conneect stmt: SQL_SUCCESS

Forth conneect stmt: SQL_SUCCESS

SQLConnect2 SQL_SUCCESS

Fifth conneect stmt: SQL_SUCCESS

SQLALLOCHANDLE : SQL_SUCCESS

results is:  SQL_SUCCESS

err is >SQL_NO_DATA_FOUND<

Nothing found for MRN of > 6106635 <

RESULTS found is > FAIL <

Free handle is: SQL_SUCCESS

Connection is: SQL_SUCCESS

Handle DBC is: SQL_SUCCESS

Handle ENV is: SQL_SUCCESS

Send back message is: >FAIL<

Starting the merge query: 6106635

Starting the Secondary merge query: 6126467

Secondary query returned: 6126467

When the secondary query is started [from the same message] it just doesn’t seem to work…

[David Barr]

You can try calling SQLGetDiagRec to get more detailed information about the failure:

Code:

odbc SQLGetDiagRec SQL_HANDLE_DBC $hdbc 1 SqlState NativeError MessageText 511 TextLength
puts $MessageText

[Author]

Replies