- This topic has 6 replies, 4 voices, and was last updated 15 years, 7 months ago by
.
-
Topic
-
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.
-
Replies
-
-
-
-
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
}
-
- The forum ‘Cloverleaf’ is closed to new topics and replies.
