tdbc::resultset(n)

🫶 tdbc::resultset — the TDBC result set object, explained 💅

okay so bestie, this is a page from Tcl Database Connectivity (that's the tdbc manual, btw — that's the manual name, you're welcome ✨). Sit down, hydrate, and let's talk about the little object that holds all your query rows. 🫶

✨ SYNOPSIS ✨

package require tdbc 1.0
package require tdbc::driver version

tdbc::driver::connection create db ?-option value...?

set stmt [db prepare sql-code]
set resultset [$stmt execute ?args...?]

$resultset columns
$resultset rowcount
$resultset nextrow ?-as lists|dicts? ?--? varname
$resultset nextlist varname
$resultset nextdict varname
$resultset nextresults
$resultset allrows ?-as lists|dicts? ?-columnsvariable name? ?--?
$resultset foreach ?-as lists|dicts? ?-columnsvariable name? ?--?
       varname script
$resultset close

🌸 DESCRIPTION 🌸

okay so here's the tea ☕ — every single database driver for TDBC (Tcl DataBase Connectivity) implements a result set object, and that object is literally the whole vibe of "here's what came back from running SQL in your database." 💖 You never make one yourself with new or whatever — instances of this object get created when you run the execute object command on a statement object. That's the origin story, periodt.

🎀 The object commands, one by one

• columns — returns a list of the names of the columns in the result set. And the way that they come back? Same order they appeared in the SQL statement that ran your query. 🦋 Slay. Now — important caveat, bestie — if your SQL statement doesn't return a set of columns (think: an INSERT, UPDATE, or DELETE), then columns hands you an empty list. Not an error, just ✨nothing✨.
• rowcount — returns the number of rows in the database that got affected by executing an INSERT, UPDATE or DELETE statement. And for a SELECT statement? The row count is unspecified. No cap. Don't rely on it there. 💀
• nextlist — sets the variable you named in varname, in the caller's scope, to the next row of the results, expressed as a list of column values. NULL values get replaced by empty strings. The columns of the result row appear in the same order they appeared in the SELECT statement. The return of nextlist is 1 if the operation succeeded, and 0 if you've hit the end of the result set. 🌈
• nextdict — same idea but a different fit: sets the variable given by varname in the caller's scope to the next row of the results, expressed as a dictionary. The dictionary's keys are column names, and the values are the values of those columns in that row. Here's the twist though — if a column's value in the row is NULL, its key is omitted from the dictionary entirely (not set to empty, just... gone, ghosted 🙈). The keys appear in the dictionary in the same order in which the columns appeared on the SELECT statement. Return is 1 on success, 0 at the end of the result set.
• nextrow — this one is precisely equivalent to the nextdict or nextlist object command, depending on whether you pass -as dicts (which is the default, iykyk 👑) or -as lists. That's it, that's the whole thing.
• nextresults — okay so some databases support the idea of a single statement that returns multiple sets of results. Iconic of them, honestly. 🧚 nextresults is what you run — typically after nextlist or nextdict has returned 0 — to advance to the next result set. It returns 1 if there's another result set to process, and 0 if the result set you just processed was the last one. After calling nextresults and getting 1 back, you may once again call columns to get the column descriptions of the next result set, and then go right back to calling nextdict or nextlist to chew through the rows of that next result set. 🍓

⚠️ big warning bestie: it is an error to call columns, nextdict, nextlist or nextrow after nextresults has returned 0. Like, do not. It's giving undefined behaviour energy. 💀

• allrows — sets the variable designated by the -columnsvariable option (if present) to the result of the columns object command. Then it executes the nextrow object command repeatedly until the end of the result set is reached. If nextresults returns a nonzero value, it runs those above two steps again — columns followed by iterated nextrow calls — as long as further results are available. All those rows returned by nextrow get assembled into a Tcl list, and that list becomes the return value of the allrows command. And the -columnsvariable? What the application ends up seeing in it is the last value returned from columns. 🦋
• foreach — sets the variable designated by the -columnsvariable option (if present) to the result of the columns object command. Then it executes the nextrow object command repeatedly until the end of the result set, storing the successive rows in the variable designated by varName. For each row, it executes the given script. Now the script control flow, because this bit matters fr fr:
  • If the script terminates with an error, the error is reported by the foreach command, and iteration stops.
  • If the script performs a break operation, the iteration terminates prematurely.
  • If the script performs a continue operation, iteration recommences with the next row.
  • If the script performs a return, results are the same as if a script outside the control of foreach had returned. 👀
  • Any other unusual return code terminates the iteration and is reported from the foreach.

Once nextrow returns 0, the foreach object command tries to advance to the next result set using nextresults. If nextresults returns 1, the above steps (columns and nextrow, with script invocation) are repeated as long as more result sets remain. And the script will always see the correct description of the columns of the current result set in the variable designated by -columnsvariable. At the end of the call, that variable will hold the description of the columns of the last result set. 🌟

• close — deletes the result set and frees any associated system resources. Clean up after yourself, bestie, it's self-care. 💅

🔥 SEE ALSO 🔥

encoding(n), tdbc(n), tdbc::connection(n), tdbc::statement(n), tdbc::tokenize(n)

👑 KEYWORDS 👑

TDBC, SQL, database, connectivity, connection, resultset, statement, bound variable, stored procedure, call

🌈 COPYRIGHT 🌈

Copyright (c) 2008 by Kevin B. Kenny.

okay that's the whole page, you ate that 🫶 so this one's from the Tcl manual, version 8.6, tdbc::resultset(n) — go forth and iterate responsibly, mwah 💖✨🎀