odbc qestion

[Rick Pritchett]

I cant get my variable to bind to the columns any ideas

for example mrn.  The handle is set further up in the proc.

set select “SELECT * FROM NOTIFICATION”

set select_call [odbc SQLExecDirect $hstmt $select SQL_NTS]

echo select_call $select_call

set select_result [odbc SQLGetCursorName $hstmt cursorName 20 pcbcursor]

echo select_result $select_result

echo cur $cursorName

odbc SQLBindCol $hstmt 1 SQL_C_SLONG mrn 12 pcbValue1

odbc SQLBindCol $hstmt 2 SQL_C_SLONG fin 0 pcbValue2

odbc SQLBindCol $hstmt 3 SQL_C_CHAR lname 50 pcbValue3

odbc SQLBindCol $hstmt 4 SQL_C_CHAR fname 50 pcbValue4

odbc SQLBindCol $hstmt 5 SQL_C_SLONG dob 0 pcbValue5

odbc SQLBindCol $hstmt 6 SQL_C_CHAR sex 50 pcbValue6

odbc SQLBindCol $hstmt 7 SQL_C_SLONG ssn 0 pcbValue7

Topic

[Ron Archambault]

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.

[Rick Pritchett]

still not able to echo the value tells me that that the varible mrn doe not exist

[Ron Archambault]

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

echo $mrn

Should work for you

[Rick Pritchett]

it echoed out “SQL_SUCCESS” instead of a value.  Is that what is should do?

[Ron Archambault]

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.

[Rick Pritchett]

still returns “SQL_SUCCESS”

[Ron Archambault]

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

[Rick Pritchett]

do you have a complete example to compare to

[Ron Archambault]

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.

[Jim Kosloskey]

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.

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

[Rick Pritchett]

odbc SQLBindCol $hstmt 1 SQL_C_SLONG mrn 12 pcbValue1

i replaced the last three variables in this line with the last two in yours and it works now

thank you

and i may have more questions later just a heads up

[Ron Archambault]

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

check your odbc command return values for

“SQL_SUCCESS” or “SQL_SUCCESS_WITH_INFO”

[Tom Gilbert]

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}

[Jim Kosloskey]

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.

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

[Tom Gilbert]

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

[Max Drown (Infor)]

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

Code:

package require odbc

set usr sa
set pass
set dsn test_dev

puts [odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv]
puts [odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0]
puts [odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc]

set retries 3
set sleep_int 5
set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]
while {$retries && [cequal $err SQL_ERROR]} {
   sleep $sleep_int
   puts [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pass SQL_NTS]
   incr retries -1
}

catch {odbc SQLFreeStmt $hstmt SQL_DROP}
puts [odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt]
set sql “select * from MedHistText.dbo.tblTest01”
puts “SQL: $sql”
puts [odbc SQLExecDirect $hstmt $sql SQL_NTS]

puts [odbc SQLBindCol $hstmt 1 SQL_C_CHAR id 255 255]
puts [odbc SQLBindCol $hstmt 2 SQL_C_CHAR name 255 255]
puts [odbc SQLBindCol $hstmt 3 SQL_C_CHAR nickname 255 255]

set i 0
set err [odbc SQLFetch $hstmt]
while {$err == “SQL_SUCCESS” || $err == “SQL_SUCCESS_WITH_INFO”} {
   incr i
   puts [format “%02s” $i]|$name|$nickname|$id
   set err [odbc SQLFetch $hstmt]  
}

catch {odbc SQLDisconnect $hdbc}
catch {odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc}
catch {odbc SQLFreeHandle SQL_HANDLE_ENV $henv}

-- Max Drown (Infor)

[Ed Mastascusa]

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

[Max Drown (Infor)]

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 Drown (Infor)

[Jim Kosloskey]

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

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

[David Barr]

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.

[Max Drown (Infor)]

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

Code:

       # Bind columns for results
       set cnt 0
       while { $cnt < $num_results } {
           incr cnt
           odbc SQLBindCol $hstmt $cnt SQL_C_CHAR data($cnt) 50000 SQL_NTS
           odbc SQLBindCol $hstmt $cnt SQL_C_CHAR data($cnt) 50000 pcb$cnt
       }

       # Fetch the results
       set results {}
       while {[odbc SQLFetch $hstmt] eq "SQL_SUCCESS"} {

           foreach id [lsort -integer [array names data]] {
               # check the returned length variable – will tell us if value is null
               set pcbValue [subst $[subst pcb$id]]
               if {$pcbValue == "SQL_NULL_DATA"} {
                   lappend results {}
               } else {
                   lappend results $data($id)
               }
           }
       }

-- Max Drown (Infor)

[Author]

Replies