tdbc::statement(3tcl)

💖 tdbc::statement — the TDBC statement object, bestie edition 💖

okay so. this is tdbc::statement(3tcl), part of Tcl Database Connectivity (TDBC), and it's genuinely one of the most useful pages in the whole TDBC family. pull up a chair, grab a boba, we're learning about statement objects 🧚✨

---

✨ NAME ✨

tdbc::statement — TDBC statement object. that's it, that's the whole vibe. 💅

---

🌸 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 stmt [db preparecall call]

$stmt params
$stmt paramtype ?direction? type ?precision? ?scale?
$stmt execute ?dict?
$stmt resultsets
$stmt allrows ?-as lists|dicts? ?-columnsvariable name? ?--? ?dict
$stmt foreach ?-as lists|dicts? ?-columnsvariable name? ?--? varName
       ?dict? script
$stmt close

look at all those methods. she's got range. 👑

---

🦋 DESCRIPTION 🦋

Every single database driver for TDBC (Tcl DataBase Connectivity) implements a statement object that represents a SQL statement in a database. no cap. 🗄️

You don't just make these out of thin air — instances of this object get created by running the prepare or preparecall object command on a database connection. that's the origin story, periodt.

the prepare object command 💜

When you run prepare against the connection, it accepts arbitrary SQL code to be executed against the database. your SQL can contain bound variables, and here's exactly what those look like:

• they're strings of alphanumeric characters or underscores
• the first character of the string may not be numeric
• they're prefixed with a colon (:) — iconic, honestly

If a bound variable shows up in the SQL statement, and it's not inside a string set off by single or double quotes, and it's not in a comment introduced by --, then it becomes a value that gets substituted when the statement is executed. one bound variable = one single value (string or numeric) in the resulting statement.

🚨 and listen — drivers are responsible for ensuring that the mechanism for binding variables prevents SQL injection. that's not a vibe check, that's a security requirement, fr fr. 🔥

the preparecall object command 🎀

This one accepts a stylized statement. two possible forms, bestie:

procname (?:varname? ?,:varname...?)

or

varname = procname (?:varname? ?,:varname...?)

This represents a call to a stored procedure named procname in the database. The variable name to the left of the equal sign (if there is one), plus all variable names that are parameters inside the parentheses, all become bound variables. slay. 💅

params — meet your bound variables 🌈

The params method on a statement object enumerates the bound variables that appear in the statement.

The result is a dictionary where:

• the keys are the names of the bound variables, listed in the order in which the variables first appear in the statement
• the values are dictionaries

those subdictionaries include at least these keys (and database drivers may add extra keys that aren't in this list — so don't be shocked if you see more 👀):

• direction — contains one of the keywords in, out or inout, depending on whether the variable is an input to or output from the statement. Only stored procedure calls will have out or inout parameters.
• type — contains the data type of the column, generally chosen from this set: bigint, binary, bit, char, date, decimal, double, float, integer, longvarbinary, longvarchar, numeric, real, time, timestamp, smallint, tinyint, varbinary, and varchar. (If the variable has a type that can't be represented as one of the above, type will contain a driver-dependent description of the type instead.)
• precision — contains the precision of the column, measured in bits, decimal digits, or the width in characters, according to the type.
• scale — contains the scale of the column (that's the number of digits after the radix point), for types that support the concept.
• nullable — contains 1 if the column can contain NULL values, and 0 otherwise.

paramtype — you get to be the boss 💪

The paramtype object command lets your script specify the type and direction of parameter transmission of a variable in a statement.

why does this exist? because some databases provide no method to determine this information automatically and just... place the burden on the caller to do so. rude, but okay. 😭

The direction, type, precision, scale, and nullable arguments have the same meaning as the corresponding dictionary values in the params object command. same energy, different door.

execute — the main character moment ⭐

The execute object command executes the statement. But before it does, values get provided for the bound variables that appear in it. Here's the whole flow, step by step:

1. If the dict parameter is supplied, it gets searched for a key whose name matches the name of the bound variable.
   • If the key is present, its value becomes the substituted variable.
   • If it's not present, the value of the substituted variable becomes a SQL NULL.
2. If the dict parameter is not supplied, the execute object command searches for a variable in the caller's scope whose name matches the name of the bound variable.
   • If one is found, its value becomes the bound variable's value.
   • If none is found, the bound variable is assigned a SQL NULL as its value.
3. Once substitution is finished, the resulting statement is executed.

The return value is a result set object — see tdbc::resultset for details. 🫶

resultsets — the receipts 🧾

The resultsets method returns a list of all the result sets that have been returned by executing the statement and have not yet been closed. so if you forgot to close something... yeah, it'll show up here. 🙈

allrows — give me everything, no crumbs 🍓

The allrows object command executes the statement as with the execute object command, accepting an optional dict parameter giving bind variables.

After executing, it uses the allrows object command on the result set (see tdbc::resultset) to construct a list of the results. Finally, the result set is closed.

The return value is the list of results. one shot, whole table, done. 🔥

foreach — row by row, cute and tidy 🦋

The foreach object command executes the statement as with the execute object command, accepting an optional dict parameter giving bind variables.

After executing, it uses the foreach object command on the result set (see tdbc::resultset) to evaluate the given script for each row of the results. Finally, the result set is closed.

⚠️ and this is important: the result set gets closed even if the given script results in a return, an error, or an unusual return code. no leaks. she cleans up after herself. 💅

close — the goodbye 👋

The close object command removes a statement and any result sets that it has created. All system resources associated with the objects are freed. iconic behavior, honestly.

---

🌟 EXAMPLES 🌟

This code would look up a telephone number in a directory, assuming an appropriate SQL schema:

package require tdbc::sqlite3
tdbc::sqlite3::connection create db phonebook.sqlite3
set statement [db prepare {
    select phone_num from directory
    where first_name = :firstname and last_name = :lastname
}]
set firstname Fred
set lastname Flintstone
$statement foreach row {
    puts [dict get $row phone_num]
}
$statement close
db close

notice how :firstname and :lastname just... pick up the variables from the caller's scope?? that's the whole magic trick and it ate. 💖

---

👀 SEE ALSO 👀

• encoding(3tcl)
• tdbc(3tcl)
• tdbc::connection(3tcl)
• tdbc::resultset(3tcl)
• tdbc::tokenize(3tcl)

go read them. they're all part of the same friend group. 🫶

---

🔑 KEYWORDS 🔑

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

---

💜 COPYRIGHT 💜

Copyright (c) 2008 by Kevin B. Kenny.

---

and that's the whole page, bestie!! ✨ this one ships with Tcl 8.6, it's the tdbc::statement(3tcl) manual page, and yes — Kevin B. Kenny wrote the copyright in 2008 and we thank him for his service. 🎀 now go forth and prepare your statements, bind your variables, and close your result sets. no cap. 💖🌸🦋