SQLITE

Jim,

Within tcl, the SQLite commands will return the full result set as a big list; you then need to work with the returned list (i.e. foreach ).

FYI – I found a nice little freebie GUI tool for working with SQLite databases if you want to play around with it; it’s SQLite Expert – Personal Edition.

If you issue a query like:

      DBCMD eval $query RSLTS {

                 Process here

      }

You will enter this loop once for each result.   The array will have an index for each column and a * index which is a list of columns.  I usually do something like:

array unset RSLTS, array unset QRYRSLTS

set cnt 1

catch {DBCMD eval $query RSLTS {

       # Get the column names once

       if {[lempty $cols]} {set cols $RSLTS(*)}

       set klst {}

       foreach c $cols {keylset klst $c $RSLTS($c)}

       set QRYRSLTS($cnt) $klst

       incr cnt

       array unset RSLTS

   }

} err

The array QRYRSLTS will be indexed numerically for each result and contain a keyed list of results where the key is the column name.  Note you can lsort -integer on array names QRYRSLTS and process any results in the order received.  I think this pretty well emulates the cursor action of ODBC calls.

FWIW, there is an ODBC driver available for SQLITE for Windows and Linux.  Probably for other Unix as well but would require comiplation.

Charlie,

I’m assuming the code snippet you posted is for sqlite.  Would you have a similar example for SQL Server, where you can retrieve row data one row at a time?

Found the following in the ODBC documentation that came with Cloverleaf, but I can’t say I completely understand it…

odbc SQLFetchScroll $hstmt SQL_FETCH_NEXT 0

If the following statement attributes set prior to a call to SQLFetch or

SQLFetchScroll, during the fetch, rgfRowStatus is set to a Tcl array

indexed starting from

First of all – I apoligize for hijacking this thread – it was about sqlite, not SQL Server.

But – if anyone’s interested, here’s a code snippet I wrote today (and tested) that loops through rows and processes each row.  I’ve cut out some error handling for brevity.

==============================================

   set sql “select LWMRN, FacilityCode from lwOpenAccounts'”

   if {$debug} { echo “sqlstmt->”$sql”” }

   # allocate a handle for the SQL statement

   set errno [catch {odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt} result]

   # execute the query

   set errno [catch { odbc SQLExecDirect $hstmt $sql SQL_NTS } result]

   # bind the data

   set errno [catch

       { odbc SQLBindCol $hstmt 1 SQL_C_CHAR mrn 10 mrnlen } result]

   set errno [catch

       { odbc SQLBindCol $hstmt 2 SQL_C_CHAR fac 10 faclen } result]

   set i 1

   # process row data until no rows left

   while { 1 } {

       # fetch a row

       set errno [catch { odbc SQLFetch $hstmt } result]

       if {![cequal $result “SQL_SUCCESS”] &&

               ![cequal $result “SQL_SUCCESS_WITH_INFO”]} {

           break;

       }

       # process each row

       echo “MRN = $mrn $mrnlen Facility = $fac $faclen $i”

       incr i

   }