odbc qestion

Doesn’t look like anything you’re doing is wrong. You might want to start off simple, by getting one field at a time.

select mrn from .

Then bind to the one field to make sure you get what you expect for that data element.

set mrn [odbc SQLBindCol $hstmt 1 SQL_C_SLONG mrn 12 pcbValue1 ]

echo $mrn

Should work for you

Sorry I knew that. Just got ahead of myself. You can take the set mrn out, but you might want to use it as an error routine.

Now you need to fetch the data

set rVal [odbc SQLFetch $hstmt]

I always check the return val from about for “SQL_SUCCESS.

In you case, echo rVal should be the mrn.

I may have missed a step, but you should be able to echo $mrn.

odbc SQLBindCol $hstmt 1 SQL_C_CHAR mrn 64 Len

  set err [odbc SQLFetch $hstm]

  echo “$err”

this should be SQL_SUCCESS if not then you should have an error handler

  echo “mrn: $mrn”

should be your value.

Rick,

I think you still need to specify the variable where you want the result of the fetch to go on the bind statement:

set err[odbc SQLBindCol $hstmt 1 SQL_C_SLONG mrn 12 pcbValue1 ]

echo $err

At this point you have bound the variable mrn to the resultset that the sqlfetch will return. The err variable is the return from ODBC for the success/failure/etc of the bind. It is a good idea to check this result because if you do not bind, you won’t get a result.

Then the fetch can also return a success/fail return code but after the fetch any columns you have bound will be in their respective variable (mrn in the example).

set err[odbc SQLFetch $hstmt]

$err will have ‘SQL_SUCCESS’ or whatever value is returned for the execution of the fetch command that tells you if that worked or not. Check the Microsoft technical library for all of the possible return values from an sqlfetch and account for them appropriately.

If you got the success then (given the example) the variable $mrn should have the value of the currently returned column that mrn was bound to for the tuple returned.

All of the above is more than I want to do inside my Tcl – that is a big reason we invoke Stored Procedures – let the DB/Application people fool with that nonsense.

Of course, given I don’t do much of the Sql stuff anymoer – what I have described above is my recollection and is not based on what I currently do.

Also, don’t assume everything works all the time. Always

check your odbc command return values for

“SQL_SUCCESS” or “SQL_SUCCESS_WITH_INFO”

I am getting the same problem and when it is does the set out …  it gives the error ‘can’t read “insertdatetime”: no such variable”.  This occurs when ‘set out …’ is executed.

Please advice…

 package require odbc

 set usr pcis

 set pass bada#bing01

 set dsn “SQL Cloverleaf Query”

 echo [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]

#

# Set the most current ODBC version (3.0)

#

echo [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]

# Allocate connection handle

     set err [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]

     # Make a connection

     set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

#If connection failed… will attempt to connect 3 times (5sec sleep between each retry)

     set retries 3

     set sleep_int 5

     while {$retries && [cequal $err SQL_ERROR]} {

       sleep $sleep_int

       # Make a connection

       set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]

       incr retries -1

    }

    catch {odbc SQLFreeStmt $hstmt SQL_DROP}

    set err [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]

# build SQL statement

    set select “SELECT top 5 * FROM labresult where attendingdoctoridnumber = ‘997’ and chartnumber = ‘2363434’”

     # Prepare & Execute – SQL statement

    set err [odbc SQLExecDirect $hstmt $select SQL_NTS]

echo test 1

    echo bind1 [odbc SQLBindCol $hstmt 1 SQL_C_CHAR alternatecode 255 len]

    echo bind1 [odbc SQLBindCol $hstmt 2 SQL_C_CHAR chartnumber 255 len]

    echo bind1 [odbc SQLBindCol $hstmt 3 SQL_C_CHAR visitid 255 len]

    echo bind1 [odbc SQLBindCol $hstmt 4 SQL_C_LONG insertdatetime 255 len]

    echo bind1 [odbc SQLBindCol $hstmt 5 SQL_C_CHAR AttendingDoctorIDNumber 255 len)]

echo test 2

    set i 0

echo test 3

    set err [odbc SQLFetch $hstmt]

echo test 4

     while {$err == “SQL_SUCCESS” || $err == “SQL_SUCCESS_WITH_INFO”} {

incr i

echo test 5

        set out “”

        set out “$alternatecode|$chartnumber|$visitid|$insertdatetime|$AttendingDoctorIDNumber”

echo $i = $outn

        # Build a new message for each row retrieved

        #set new_mh [msgcreate -class engine -type data $out]

        #lappend dispList “CONTINUE $new_mh”

        # Retrieve data from bound columns and advance to the next row of data

        set err [odbc SQLFetch $hstmt]  

     }

# Terminate connection/statement handle

      catch {odbc SQLDisconnect $hdbc}

      catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}

      catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}

Tom,

I am guessing your fetch is not populating that variable and since I do not se it being set prior to the fetch that variable does not exist when you are trying to use it.

Hello Jim,

Would it be ok to give me a call and review this with me and see where I am missing the boat…🙂  Eileen of KDDH here in town said you are a good source…

My number is 1-559-738-7500 x5648

Thanks,

Tom

Here’s my script that works. Got this from Goutham and modded it a bit.

Tom,

Whenever its possible to select no rows in your fetch you have to initialize all your bound variables first.

The SQLBindCol function will associate a column with a variable name but if the data set resulting from your SQL fetch is empty  (or you don’t actually call SQLFetch) then the bound variables won’t get set in the fetch. In other words, if there’s nothing to fetch then the fetch does nothing.

hopefully this helps

For you ODBC guru’s out there, I have a question about NULL values. Ron and I have been struggling with this one for awhile.

The database elements can either have a normal value, be empty, or be NULL. When the element is NULL, our code seems to “remember” the value of the element from the previous row when iterating over multiple rows from a select statement.

Ideally, it would be nice if we could simply check for NULL by doing something like “if {$string eq NULL}”, but Tcl does not have a concept of true NULL values. It just has empty strings.

Has anyone run into this before? If so, how did you handle the NULL elements?

Max,

Using Data Integrator, we only invoke Stored Procedures. We require the Stored Procedure to return null entities as empty strings so the work is done in the Stored procedure.

This falls in line with our philosophy that such activity is a part of the Business Rules and thoe belong outside of the engine.

However, I seem to recall in my very distant past that there is a way using SQL statements to verify if a column of a recordset is null (or not null). This would require knowledge of the DB schema (what columns are configured to allow nulls) as well as a concious execition of SQl (not Tcl) to determine the truth of the ‘nullness’ of the column that populated the Tcl variable.

Again the above to us are business rules that belong with the application

The fetch doesn’t set the variable if the value is null, so you could initialize the variable to the word NULL (set string NULL), then your string comparison against NULL would work.  However, it’s possible that you have the word NULL stored in your database.

Another option is to unset your variable first (unset string) and then check to see if the variable exists after the fetch (if { [info exists string] }).

Other database API’s that I’ve worked with have a feature called a “null indicator” that you can check to see if a field is NULL.  I looked for this in TCL a few weeks ago but couldn’t find anything.

Ron came up with a good solution, too. Here is the code fragment for future reference.