• Creator
  • #50651
    James Sedlmeyer

    I’ve been creating and storing information into an SQLITE database using tcl with no problem. Now I’m trying to extract data and I’m trying to figure out how to select a row and process each row before selecting the next row. Unlike Oracle and other DBMS where you can build a cursor and select each row and process it, there is no cursor functionality in SQLITE. When I use the select statement in SQLITE I get all rows I define in my select returned to me. I do see an array “parray” parameter in the sqlite documentation. Do I select into an array then loop through the array using tcl statements. I’m still researching for a select and process one row at a time solution. Does anybody out there have any ideas or a tcl script that selects rows and processes each row as it is read form the db.

Viewing 5 reply threads
  • Author
    • #66971
      James Cobane


      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.

    • #66972
      James Sedlmeyer

      Hi Jim

      List processing. Thats the direction I started going in. I was very surprised that there was no cursor functionality in sqlite but I’m not complaining because it’s free. Thanks for your response.

    • #66973
      Charlie Bursell

      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.

    • #66974
      James Sedlmeyer

      Thanks Charlie. Great Stuff

    • #66975
      Steve Pringle


      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

    • #66976
      Steve Pringle

      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”]} {



             # process each row

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

             incr i


Viewing 5 reply threads
  • The forum ‘Cloverleaf’ is closed to new topics and replies.

Forum Statistics

Registered Users
Topic Tags