Parameterized SQL statement: Difference between revisions
Added FreeBASIC
(Added FreeBASIC) |
|||
(48 intermediate revisions by 23 users not shown) | |||
Line 2:
Using a SQL update statement like this one (spacing is optional):
<
SET name = 'Smith, Steve', score = 42, active = true
WHERE jerseyNum = 99</
<blockquote cite="http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/">Non-parameterized SQL is the GoTo statement of database programming. Don't do it, and make sure your coworkers don't either.</blockquote>
=={{header|8th}}==
<syntaxhighlight lang="forth">\ assuming the var 'db' contains an opened database with a schema matching the problem:
db @
"UPDATE players SET name=?1,score=?2,active=?3 WHERE jerseyNum=?4"
Line 22 ⟶ 21:
\ execute the query
db @ swap db:exec</syntaxhighlight>
=={{header|Ada}}==
<syntaxhighlight lang="ada">-- Version for sqlite
with GNATCOLL.SQL_Impl; use GNATCOLL.SQL_Impl;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
Line 64 ⟶ 61:
Free (Conn);
Free (DB_Descr);
end Prepared_Query;</syntaxhighlight>
=={{header|Arturo}}==
<syntaxhighlight lang="rebol">; Helper functions
createTable: function [][
query db {!sql DROP TABLE IF EXISTS users}
query db {!sql
CREATE TABLE users (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL,
age INTEGER
)
}
]
addUser: function [name, email, age][
query.id db .with:@[name,email,age] {!sql
INSERT INTO users (username, email, age)
VALUES (?,?,?)
}
]
findUser: function [name][
query db .with:@[name] ~{!sql
SELECT *
FROM users
WHERE username=?
}
]
db: open.sqlite "users.db"
createTable
print ["added user with id:" addUser "JohnDoe" "jodoe@gmail.com" 35]
print ["added user with id:" addUser "JaneDoe" "jadoe@gmail.com" 14]
print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]
close db</syntaxhighlight>
{{out}}
<pre>added user with id: 1
added user with id: 2
getting user with name: JohnDoe => [[1 JohnDoe jodoe@gmail.com 35]] </pre>
=={{header|BASIC}}==
==={{header|BASIC256}}===
<syntaxhighlight lang="basic"># open database file
dbopen "players.sqlite3"
# delete old players table - trap error if new database
onerror errortrap
# delete old database if it exists
dbexecute "DROP TABLE IF EXISTS players;"
# create database table with sample data
# include the IF NOT EXISTS clause to avoid an error if the table already exists.
dbexecute "CREATE TABLE IF NOT EXISTS players (name TEXT NOT NULL, score INTEGER, active BIT, jerseyNum INTEGER);"
dbexecute "INSERT INTO players VALUES ('Jones, Bob',0,'N',99)"
dbexecute "INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)"
dbexecute "INSERT INTO players VALUES ('Jello, Frank',0,'N',101)"
dbexecute "UPDATE players SET name = 'Smith, Steve', score = 42, active = 'TRUE' WHERE jerseyNum = 99;"
dbopenset "SELECT * FROM players;"
while dbrow()
print "['" + dbstring(0) + ", '" + dbstring(1) + ", '" + dbstring(2) + "', " + dbstring(3) + "]"
end while
dbcloseset
offerror
# wrap everything up
dbclose
end
errortrap:
# accept error - display nothing - return to next statement
return</syntaxhighlight>
{{out}}
<pre>Same as PureBasic entry.</pre>
==={{header|FreeBASIC}}===
<syntaxhighlight lang="vbnet">#include once "sqlite3.bi"
#define NULL 0
Dim As sqlite3 Ptr db
Dim As sqlite3_stmt Ptr stmt
Dim As Integer rc
' Open database file
rc = sqlite3_open("players.sqlite3", @db)
If rc <> SQLITE_OK Then
Print "No se puede abrir la base de datos: "; *sqlite3_errmsg(db)
sqlite3_close(db)
End 1
End If
' Delete old database if it exists
sqlite3_exec(db, "DROP TABLE IF EXISTS players;", NULL, NULL, NULL)
' Create database table with sample data
sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS players (name TEXT NOT NULL, score INTEGER, active BIT, jerseyNum INTEGER);", NULL, NULL, NULL)
sqlite3_exec(db, "INSERT INTO players VALUES ('Jones, Bob',0,'N',99)", NULL, NULL, NULL)
sqlite3_exec(db, "INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)", NULL, NULL, NULL)
sqlite3_exec(db, "INSERT INTO players VALUES ('Jello, Frank',0,'N',101)", NULL, NULL, NULL)
' Update name and score of player with jersey number 99
sqlite3_exec(db, "UPDATE players SET name = 'Smith, Steve', score = 42, active = 'TRUE' WHERE jerseyNum = 99;", NULL, NULL, NULL)
' Query and display all player table records
Dim As sqlite3_stmt Ptr stmt2
Dim As String query3 = "SELECT * FROM players"
rc = sqlite3_prepare_v2(db, Strptr(query3), -1, @stmt2, 0)
While sqlite3_step(stmt2) = SQLITE_ROW
Print "['"; *Cptr(ZString Ptr, sqlite3_column_text(stmt2, 0)); "', ";
Print *Cptr(ZString Ptr, sqlite3_column_text(stmt2, 1)); ", ";
Print *Cptr(ZString Ptr, sqlite3_column_text(stmt2, 2)); ", ";
Print sqlite3_column_int(stmt2, 3); "]"
Wend
' Close the database
sqlite3_finalize(stmt2)
sqlite3_close(db)
Sleep</syntaxhighlight>
{{out}}
<pre>Same as PureBasic entry.</pre>
==={{header|PureBasic}}===
<syntaxhighlight lang="purebasic">UseSQLiteDatabase()
Procedure CheckDatabaseUpdate(database, query$)
result = DatabaseUpdate(database, query$)
If result = 0
PrintN(DatabaseError())
EndIf
ProcedureReturn result
EndProcedure
If OpenConsole()
If OpenDatabase(0, ":memory:", "", "")
;create players table with sample data
CheckDatabaseUpdate(0, "CREATE table players (name, score, active, jerseyNum)")
CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jones, Bob',0,'N',99)")
CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)")
CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jello, Frank',0,'N',101)")
Define name$, score, active$, jerseynum
name$ = "Smith, Steve"
score = 42
active$ ="TRUE"
jerseynum = 99
SetDatabaseString(0, 0, name$)
SetDatabaseLong(0, 1, score)
SetDatabaseString(0, 2, active$)
SetDatabaseLong(0, 3, jerseynum)
CheckDatabaseUpdate(0, "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?")
;display database contents
If DatabaseQuery(0, "Select * from players")
While NextDatabaseRow(0)
name$ = GetDatabaseString(0, 0)
score = GetDatabaseLong(0, 1)
active$ = GetDatabaseString(0, 2)
jerseynum = GetDatabaseLong(0, 3)
row$ = "['" + name$ + "', " + score + ", '" + active$ + "', " + jerseynum + "]"
PrintN(row$)
Wend
FinishDatabaseQuery(0)
EndIf
CloseDatabase(0)
Else
PrintN("Can't open database !")
EndIf
Print(#CRLF$ + #CRLF$ + "Press ENTER to exit"): Input()
CloseConsole()
EndIf</syntaxhighlight>
Sample output:
<pre>['Smith, Steve', 42, 'TRUE', 99]
['Jesten, Jim', 0, 'N', 100]
['Jello, Frank', 0, 'N', 101]</pre>
==={{header|Run BASIC}}===
{{incorrect|Run BASIC|Executing a NON-parameterized update DML. This solution is exactly the opposite of the task. This example is what is explicitly warned in the task.}}
<syntaxhighlight lang="runbasic">sqliteconnect #mem, ":memory:"
#mem execute("CREATE table players (name, score, active, jerseyNum)")
#mem execute("INSERT INTO players VALUES ('Jones, Bob',0,'N',99)")
#mem execute("INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)")
#mem execute("INSERT INTO players VALUES ('Jello, Frank',0,'N',101)")
sql$ = "
UPDATE players
SET name = 'Smith, Steve',
score = 42,
active = 'TRUE'
WHERE jerseyNum = 99"
#mem execute(sql$)
#mem execute("SELECT * FROM players ORDER BY jerseyNum")
WHILE #mem hasanswer()
#row = #mem #nextrow()
name$ = #row name$()
score = #row score()
active$ = #row active$()
jerseyNum = #row jerseyNum()
print name$;chr$(9);score;chr$(9);active$;chr$(9);jerseyNum
WEND
end</syntaxhighlight>
<pre>Output
Smith, Steve 42 TRUE 99
Jesten, Jim 0 N 100
Jello, Frank 0 N 101</pre>
=={{header|C}}==
{{libheader|SQLite}}
{{trans|Pascal}}
Compile with:
gcc example.c -lsqlite3
Tested with gcc version 4.9.2 (Raspbian 4.9.2-10) and SQLite 3.8.7.1
<syntaxhighlight lang="c">#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static const char* db_file = ":memory:"; // Create an in-memory database.
void check_error(int result_code, sqlite3 *db);
int select_callback(void* data, int column_count, char** columns, char** column_names);
int main(void) {
sqlite3 *db;
int result_code;
char *sql;
char *insert_statements[4];
sqlite3_stmt *compiled_statement;
int i;
// Open the database.
result_code = sqlite3_open(db_file, &db);
check_error(result_code, db);
// Create the players table in the database.
sql = "create table players("
"id integer primary key asc, "
"name text, "
"score real, "
"active integer, " // Store the bool value as integer (see https://sqlite.org/datatype3.html chapter 2.1).
"jerseyNum integer);";
result_code = sqlite3_exec(db, sql, NULL, NULL, NULL);
check_error(result_code, db);
// Insert some values into the players table.
insert_statements[0] = "insert into players (name, score, active, jerseyNum) "
"values ('Roethlisberger, Ben', 94.1, 1, 7);";
insert_statements[1] = "insert into players (name, score, active, jerseyNum) "
"values ('Smith, Alex', 85.3, 1, 11);";
insert_statements[2] = "insert into players (name, score, active, jerseyNum) "
"values ('Manning, Payton', 96.5, 0, 18);";
insert_statements[3] = "insert into players (name, score, active, jerseyNum) "
"values ('Doe, John', 15, 0, 99);";
for (i=0; i<4; i++) {
result_code = sqlite3_exec(db, insert_statements[i], NULL, NULL, NULL);
check_error(result_code, db);
}
// Display the contents of the players table.
printf("Before update:\n");
sql = "select * from players;";
result_code = sqlite3_exec(db, sql, select_callback, NULL, NULL);
check_error(result_code, db);
// Prepare the parametrized SQL statement to update player #99.
sql = "update players set name=?, score=?, active=? where jerseyNum=?;";
result_code = sqlite3_prepare_v2(db, sql, -1, &compiled_statement, NULL);
check_error(result_code, db);
// Bind the values to the parameters (see https://sqlite.org/c3ref/bind_blob.html).
result_code = sqlite3_bind_text(compiled_statement, 1, "Smith, Steve", -1, NULL);
check_error(result_code, db);
result_code = sqlite3_bind_double(compiled_statement, 2, 42);
check_error(result_code, db);
result_code = sqlite3_bind_int(compiled_statement, 3, 1);
check_error(result_code, db);
result_code = sqlite3_bind_int(compiled_statement, 4, 99);
check_error(result_code, db);
// Evaluate the prepared SQL statement.
result_code = sqlite3_step(compiled_statement);
if (result_code != SQLITE_DONE) {
printf("Error #%d: %s\n", result_code, sqlite3_errmsg(db));
sqlite3_close(db);
return result_code;
}
// Destroy the prepared statement object.
result_code = sqlite3_finalize(compiled_statement);
check_error(result_code, db);
// Display the contents of the players table.
printf("After update:\n");
sql = "select * from players;";
result_code = sqlite3_exec(db, sql, select_callback, NULL, NULL);
check_error(result_code, db);
// Close the database connection.
sqlite3_close(db);
return EXIT_SUCCESS;
}
/*
Checks the result code from an SQLite operation.
If it contains an error code then this function prints the error message,
closes the database and exits.
*/
void check_error(int result_code, sqlite3 *db) {
if (result_code != SQLITE_OK) {
printf("Error #%d: %s\n", result_code, sqlite3_errmsg(db));
sqlite3_close(db);
exit(result_code);
}
}
/* This callback function prints the results of the select statement. */
int select_callback(void* data, int column_count, char** columns, char** column_names) {
int i;
for (i=0; i<column_count; i++) {
printf(columns[i]);
if (i < column_count-1) printf(" | ");
}
printf("\n");
}</syntaxhighlight>
{{out}}
<pre>
Before update:
1 | Roethlisberger, Ben | 94.1 | 1 | 7
2 | Smith, Alex | 85.3 | 1 | 11
3 | Manning, Payton | 96.5 | 0 | 18
4 | Doe, John | 15.0 | 0 | 99
After update:
1 | Roethlisberger, Ben | 94.1 | 1 | 7
2 | Smith, Alex | 85.3 | 1 | 11
3 | Manning, Payton | 96.5 | 0 | 18
4 | Smith, Steve | 42.0 | 1 | 99
</pre>
=={{header|C sharp|C#}}==
<syntaxhighlight lang="csharp">using System.Data.Sql;
using System.Data.SqlClient;
Line 79 ⟶ 432:
static void Main(string[] args)
{
tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";
Line 93 ⟶ 445:
}
}
}</syntaxhighlight>
=={{header|C++}}==
{{libheader|Qt}}
This example uses the Qt SQL module to access an ODBC data source.
<syntaxhighlight lang="cpp">#include <QtSql>
#include <iostream>
// Command line arguments: data-source user password
// Obviously in a real application the password would be obtained in a secure manner.
int main(int argc, char *argv[]) {
if (argc != 4) {
std::cerr << "Usage: " << argv[0] << " data-source user password\n";
return 1;
}
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(argv[1]);
if (!db.open(argv[2], argv[3])) {
auto error = db.lastError();
std::cerr << "Cannot connect to data source: " << error.text().toStdString() << '\n';
} else {
std::cout << "Connected to data source.\n";
QSqlQuery query(db);
query.prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");
query.bindValue(0, "Smith, Steve");
query.bindValue(1, 42);
query.bindValue(2, true);
query.bindValue(3, 99);
if (!query.exec()) {
auto error = db.lastError();
std::cerr << "Cannot update database: " << error.text().toStdString() << '\n';
} else {
std::cout << "Update succeeded.\n";
}
}
return 0;
}</syntaxhighlight>
=={{header|Clojure}}==
<syntaxhighlight lang="clojure">(require '[clojure.java.jdbc :as sql])
; Using h2database for this simple example.
(def db {:classname "org.h2.Driver"
Line 107 ⟶ 494:
; As an alternative to update!, use execute!
(sql/execute! db ["UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?" "Smith, Steve" 42 true 99])</syntaxhighlight>
=={{header|F_Sharp|F#}}==
{{trans|C#}}
<
[<EntryPoint>]
Line 128 ⟶ 514:
tCommand.ExecuteNonQuery() |> ignore
0</
=={{header|Go}}==
<
import (
Line 163 ⟶ 549:
}
rows.Close()
}</
{{out}}
<pre>
Line 169 ⟶ 555:
100 0 false
</pre>
=={{header|Haskell}}==
Example uses the [http://hackage.haskell.org/package/HDBC <tt>HDBC</tt>] package:
<syntaxhighlight lang="haskell">module Main (main) where
import Database.HDBC (IConnection, commit, run, toSql)
updatePlayers :: IConnection a => a -> String -> Int -> Bool -> Int -> IO Bool
updatePlayers conn name score active jerseyNum = do
rowCount <- run conn
"UPDATE players\
\ SET name = ?, score = ?, active = ?\
\ WHERE jerseyNum = ?"
[ toSql name
, toSql score
, toSql active
, toSql jerseyNum
]
commit conn
return $ rowCount == 1
main :: IO ()
main = undefined</syntaxhighlight>
You'll need an instance of a type with an instance for the <tt>IConnection</tt> type class in order to use this function, such as [http://hackage.haskell.org/package/HDBC-postgresql-2.3.2.5/docs/Database-HDBC-PostgreSQL.html#t:Connection <tt>Connection</tt>] from [http://hackage.haskell.org/package/HDBC-postgresql <tt>HDBC-postgresql</tt>].
=={{header|Huginn}}==
<syntaxhighlight lang="huginn">import Database as db;
import Algorithms as algo;
import FileSystem as fs;
main() {
dbPath = "/tmp/parametrized-sql.sqlite";
fs.remove( dbPath );
fs.open( dbPath, fs.OPEN_MODE.WRITE );
conn = db.connect( "sqlite3:///" + dbPath );
// Setup...
conn.query(
"CREATE TABLE Players (\n"
"\tname VARCHAR(64),\n"
"\tscore FLOAT,\n"
"\tactive INTEGER,\n"
"\tno VARCHAR(8)\n"
");"
).execute();
conn.query(
"INSERT INTO Players VALUES ( 'name', 0, 'false', 99 );"
).execute();
conn.query(
"INSERT INTO Players VALUES ( 'name', 0, 'false', 100 );"
).execute();
// Demonstrate parameterized SQL...
parametrizedQuery = conn.query(
"UPDATE Players SET name=?, score=?, active=? WHERE no=?"
);
for ( i, v : algo.enumerate( ( "Smith, Steve", 42, true, 99 ) ) ) {
parametrizedQuery.bind( i + 1, string( v ) );
}
parametrizedQuery.execute();
// and show the results...
for ( record : conn.query( "SELECT * FROM Players;" ).execute() ) {
print( "{}\n".format( record ) );
}
return ( 0 );
}</syntaxhighlight>
=={{header|Java}}==
<syntaxhighlight lang="java">
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class DBDemo{
private String protocol; //set this to some connection protocol like "jdbc:sqlserver://"
Line 181 ⟶ 638:
private String password;
PreparedStatement query;
public int setUpAndExecPS(){
try {
Connection conn = DriverManager.getConnection(protocol + dbName, username, password);
query = conn.prepareStatement(
"UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");
query.setString(1, "Smith, Steve");//automatically sanitizes and adds quotes
query.setInt(2, 42);
query.setBoolean(3, true);
query.setInt(4, 99);
//there are similar methods for other SQL types in PerparedStatement
return query.executeUpdate();//returns the number of rows changed
//PreparedStatement.executeQuery() will return a java.sql.ResultSet,
//execute() will simply return a boolean saying whether it succeeded or not
} catch (Exception e) {
}
return 0;
}
}
</syntaxhighlight>
=={{header|Julia}}==
{{works with|Julia|0.6}}
Uses the SQLite package.
<syntaxhighlight lang="julia">using SQLite
name = "Smith, Steve"
jerseys = Dict("Smith, Steve" => 99)
sqlbool(tf::Bool) = if(tf) "TRUE" else "FALSE" end
db = SQLite.DB() # no filename given, so create an in-memory temporary
SQLite.execute!(db, "create table players (id integer primary key,
name text,
score number,
active bool,
jerseynum integer)")
SQLite.query(db, "INSERT INTO players (name, score, active, jerseynum) values ('Jones, James', 9, 'FALSE', 99)")
SQLite.query(db, "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseynum = ?";
values = ["Smith, Steve", 42, sqlbool(true), jerseys[name]])
tbl = SQLite.query(db, "SELECT * from players")
println(tbl)</syntaxhighlight>
{{output}}<pre>
1×5 DataFrames.DataFrame
│ Row │ id │ name │ score │ active │ jerseynum │
├─────┼────┼────────────────┼───────┼────────┼───────────┤
│ 1 │ 1 │ "Smith, Steve" │ 42 │ "TRUE" │ 99 │
</pre>
=={{header|Kotlin}}==
<syntaxhighlight lang="scala">// Version 1.2.41
import java.sql.DriverManager
import java.sql.Connection
fun main(args: Array<String>) {
val url = "jdbc:mysql://localhost:3306/test"
val username = "example"
val password = "password123"
val conn = DriverManager.getConnection(url, username, password)
val query = conn.prepareStatement(
"UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?"
)
with (query) {
setString(1, "Smith, Steve")
setInt(2, 42)
setBoolean(3, true)
setInt(4, 99)
val rowCount = executeUpdate()
if (rowCount == 0) println("Update failed")
close()
}
conn.close()
}</syntaxhighlight>
=={{header|M2000 Interpreter}}==
<syntaxhighlight lang="m2000 interpreter">
Module Parametrized_Sql {
Base "rosetta" ' warning erase database if found it in current directory
Execute "rosetta", {create table players (name VarChar(64), score Float, active Integer, jerseyNum Integer);}
Append "rosetta", "players","name",0,FALSE,99
sql$={
UPDATE players
SET name = '{0}', score = {1}, active = {2}
WHERE jerseyNum = {3}
}
Execute "rosetta", format$(sql$,"Smith, Steve", 42,TRUE, 99)
Retrieve "rosetta","players",1,"jerseyNum",99
Read how_many
Read Name$,score, active,jerseynum
Print Name$="Smith, Steve", score=42, active=True, jerseynum=99 ' true true true true
}
Parametrized_Sql
</syntaxhighlight>
=={{header|Mathematica}}/{{header|Wolfram Language}}==
<
conn=OpenSQLConnection[JDBC["ODBC(DSN)",
CloseSQLConnection[conn];</
=={{header|NetRexx}}==
Using an [http://db.apache.org/derby/ Apache Derby] embedded database:
<
options replace format comments java crossref symbols nobinary
Line 347 ⟶ 890:
method isFalse() public static returns boolean
return \isTrue
</syntaxhighlight>
=={{header|Nim}}==
Using an SQLite in memory database and "db_sqlite" high level binding from standard library.
<syntaxhighlight lang="nim">import db_sqlite
let db = open(":memory:", "", "", "")
# Setup
db.exec(sql"CREATE TABLE players (name, score, active, jerseyNum)")
db.exec(sql"INSERT INTO players VALUES (?, ?, ?, ?)", "name", 0, "false", 99)
# Update the row.
db.exec(sql"UPDATE players SET name=?, score=?, active=? WHERE jerseyNum=?",
"Smith, Steve", 42, true, 99)
# Display result.
for row in db.fastRows(sql"SELECT * FROM players"):
echo row
db.close()</syntaxhighlight>
{{out}}
<pre>@["Smith, Steve", "42", "true", "99"]</pre>
=={{header|Objeck}}==
<
use ODBC;
Line 368 ⟶ 934:
};
}
}</
=={{header|Pascal}}==
Line 374 ⟶ 940:
{{libheader|SQLite}}
Tested with Free Pascal 2.6.4 (arm) and SQLite 3.8.7.1
<
uses
sqlite3, sysutils;
Line 497 ⟶ 1,063:
// Close the database connection.
sqlite3_close(db);
end.</
{{out}}
<pre>
Line 513 ⟶ 1,079:
=={{header|Perl}}==
<
my $db = DBI->connect('DBI:mysql:mydatabase:host','login','password');
Line 519 ⟶ 1,085:
$statment = $db->prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");
$rows_affected = $statment->execute("Smith, Steve",42,'true',99);</
=={{header|Phix}}==
{{libheader|SQLite}}
<!--<syntaxhighlight lang="phix">(notonline)-->
<span style="color: #000080;font-style:italic;">--
-- demo\rosetta\Parameterized_SQL_statement.exw
-- ============================================
-- </span>
<span style="color: #008080;">without</span> <span style="color: #008080;">js</span> <span style="color: #000080;font-style:italic;">-- (pSQLite)</span>
<span style="color: #008080;">include</span> <span style="color: #000000;">pSQLite</span><span style="color: #0000FF;">.</span><span style="color: #000000;">e</span>
<span style="color: #000080;font-style:italic;">--<some pretty printing, not really part of the demo></span>
<span style="color: #008080;">constant</span> <span style="color: #0000FF;">{</span><span style="color: #000000;">coltypes</span><span style="color: #0000FF;">,</span><span style="color: #000000;">colfmts</span><span style="color: #0000FF;">,</span><span style="color: #000000;">colrids</span><span style="color: #0000FF;">}</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">columnize</span><span style="color: #0000FF;">({</span>
<span style="color: #0000FF;">{</span><span style="color: #000000;">SQLITE_INTEGER</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"%4d"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlite3_column_int</span><span style="color: #0000FF;">},</span>
<span style="color: #0000FF;">{</span><span style="color: #000000;">SQLITE_FLOAT</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"%4g"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlite3_column_double</span><span style="color: #0000FF;">},</span>
<span style="color: #0000FF;">{</span><span style="color: #000000;">SQLITE_TEXT</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"%-20s"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlite3_column_text</span><span style="color: #0000FF;">}})</span>
<span style="color: #008080;">procedure</span> <span style="color: #000000;">show</span><span style="color: #0000FF;">(</span><span style="color: #004080;">string</span> <span style="color: #000000;">what</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"%s:\n"</span><span style="color: #0000FF;">,{</span><span style="color: #000000;">what</span><span style="color: #0000FF;">})</span>
<span style="color: #000000;">sqlite3_stmt</span> <span style="color: #000000;">pStmt</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_prepare</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"SELECT * FROM players;"</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">while</span> <span style="color: #000000;">1</span> <span style="color: #008080;">do</span>
<span style="color: #004080;">integer</span> <span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_step</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">res</span><span style="color: #0000FF;">=</span><span style="color: #000000;">SQLITE_DONE</span> <span style="color: #008080;">then</span> <span style="color: #008080;">exit</span> <span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">res</span><span style="color: #0000FF;">=</span><span style="color: #000000;">SQLITE_ROW</span><span style="color: #0000FF;">)</span>
<span style="color: #004080;">string</span> <span style="color: #000000;">text</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">""</span>
<span style="color: #008080;">for</span> <span style="color: #000000;">c</span><span style="color: #0000FF;">=</span><span style="color: #000000;">1</span> <span style="color: #008080;">to</span> <span style="color: #000000;">sqlite3_column_count</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">)</span> <span style="color: #008080;">do</span>
<span style="color: #004080;">integer</span> <span style="color: #000000;">ctype</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_column_type</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">c</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">cdx</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">find</span><span style="color: #0000FF;">(</span><span style="color: #000000;">ctype</span><span style="color: #0000FF;">,</span><span style="color: #000000;">coltypes</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">rid</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">colrids</span><span style="color: #0000FF;">[</span><span style="color: #000000;">cdx</span><span style="color: #0000FF;">]</span>
<span style="color: #004080;">string</span> <span style="color: #000000;">name</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_column_name</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">c</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">data</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">sprintf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">colfmts</span><span style="color: #0000FF;">[</span><span style="color: #000000;">cdx</span><span style="color: #0000FF;">],</span><span style="color: #000000;">rid</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">c</span><span style="color: #0000FF;">))</span>
<span style="color: #000000;">text</span> <span style="color: #0000FF;">&=</span> <span style="color: #7060A8;">sprintf</span><span style="color: #0000FF;">(</span><span style="color: #008000;">" %s:%s"</span><span style="color: #0000FF;">,{</span><span style="color: #000000;">name</span><span style="color: #0000FF;">,</span><span style="color: #000000;">data</span><span style="color: #0000FF;">})</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">for</span>
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"%s\n"</span><span style="color: #0000FF;">,{</span><span style="color: #000000;">text</span><span style="color: #0000FF;">})</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">while</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_finalize</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>
<span style="color: #000080;font-style:italic;">--</pretty printing></span>
<span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_open</span><span style="color: #0000FF;">(</span><span style="color: #008000;">":memory:"</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">`create table players (name, score, active, jerseyNum)`</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">`insert into players values ('Roethlisberger, Ben', 94.1, 1, 7 )`</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">`insert into players values ('Smith, Alex', 85.3, 1, 11)`</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">`insert into players values ('Doe, John', 15, 0, 99)`</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">`insert into players values ('Manning, Payton', 96.5, 0, 123)`</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">show</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"Before"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
<span style="color: #000080;font-style:italic;">--pp({"Before",sqlite3_get_table(db, "select * from players")},{pp_Nest,2})
-- For comparison against some other entries, this is how you would do numbered parameters:
--/*
sqlite3_stmt pStmt = sqlite3_prepare(db, `update players set name=?, score=?, active=? where jerseyNum=?`)
sqlite3_bind_text(pStmt,1,"Smith, Steve")
sqlite3_bind_double(pStmt,2,42)
sqlite3_bind_int(pStmt,3,true)
sqlite3_bind_int(pStmt,4,99)
--*/
-- However, ordinarily I would prefer named parameters and sqlbind_parameter_index() calls:</span>
<span style="color: #000000;">sqlite3_stmt</span> <span style="color: #000000;">pStmt</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_prepare</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">`update players set name=:name, score=:score, active=:active where jerseyNum=:jerseyn`</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">constant</span> <span style="color: #000000;">k_name</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_bind_parameter_index</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">":name"</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">k_score</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_bind_parameter_index</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">":score"</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">k_active</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_bind_parameter_index</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">":active"</span><span style="color: #0000FF;">),</span>
<span style="color: #000000;">k_jerseyn</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_bind_parameter_index</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">":jerseyn"</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">k_name</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"Smith, Steve"</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_double</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">k_score</span><span style="color: #0000FF;">,</span><span style="color: #000000;">42</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_int</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">k_active</span><span style="color: #0000FF;">,</span><span style="color: #004600;">true</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_int</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">k_jerseyn</span><span style="color: #0000FF;">,</span><span style="color: #000000;">99</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_step</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_DONE</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">assert</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3_finalize</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pStmt</span><span style="color: #0000FF;">)=</span><span style="color: #000000;">SQLITE_OK</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">show</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"After"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
<span style="color: #000080;font-style:italic;">--pp({"After",sqlite3_get_table(db, "select * from players")},{pp_Nest,2})</span>
<span style="color: #000000;">sqlite3_close</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
<!--</syntaxhighlight>-->
{{out}}
<pre>
Before:
name:Roethlisberger, Ben score:94.1 active: 1 jerseyNum: 7
name:Smith, Alex score:85.3 active: 1 jerseyNum: 11
name:Doe, John score: 15 active: 0 jerseyNum: 99
name:Manning, Payton score:96.5 active: 0 jerseyNum: 123
After:
name:Roethlisberger, Ben score:94.1 active: 1 jerseyNum: 7
name:Smith, Alex score:85.3 active: 1 jerseyNum: 11
name:Smith, Steve score: 42 active: 1 jerseyNum: 99
name:Manning, Payton score:96.5 active: 0 jerseyNum: 123
</pre>
=={{header|PHP}}==
<
"WHERE `jerseyNum` = ?";
$dbh = new PDO( "mysql:dbname=db;host=localhost", "username", "password" );
Line 545 ⟶ 1,193:
// alternatively pass parameters as an array to the execute method
$updateStatement = $dbh->prepare( $updatePlayers );
$updateStatement->execute( array( "Smith, Steve", 42, 1, 99 ) );</
=={{header|PicoLisp}}==
As PicoLisp uses normal function calls for DB manipulations, parameters are always treated as plain data and are not executed.
<
(put!> P 'name "Smith, Steve")
(put!> P 'score 42)
(put!> P 'active T) )</
=={{header|Python}}==
{{trans|Ruby}}
<
db = sqlite3.connect(':memory:')
Line 639 ⟶ 1,228:
# and show the results
for row in db.execute('select * from players'):
print(row)</
outputs
<pre>(u'Smith, Steve', 42, 1, 99)
Line 645 ⟶ 1,234:
=={{header|Racket}}==
{{works with|PostgreSQL}}
{{libheader|sql db-lib}}
<syntaxhighlight lang="racket">
#lang racket/base
(require sql db)
(define pgc
; Don't actually inline sensitive data ;)
(postgresql-connect #:user "resu"
#:database "esabatad"
#:server "example.com"
#:port 5432
#:password "s3>r37P455"))
(define update-player
(parameterize ((current-sql-dialect 'postgresql))
(update players
#:set [name ?] [score ?] [active ?]
#:where [jerseyNum ?])))
(apply query
pgc
update-player
'("Smith, Steve" 42 #t 99))
</syntaxhighlight>
=={{header|Raku}}==
(formerly Perl 6)
<syntaxhighlight lang="raku" line>use DBIish;
my $db = DBIish.connect('DBI:mysql:mydatabase:host','login','password');
my $update = $db.prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");
my $rows-affected = $update.execute("Smith, Steve",42,'true',99);</syntaxhighlight>
=={{header|Ruby}}==
Using the {{libheader|sqlite3-ruby}} gem
[[Category:SQLite]]
<
db = SQLite3::Database.new(":memory:")
Line 692 ⟶ 1,306:
# and show the results
db.execute2('select * from players') {|row| p row}</
outputs
<pre>["name", "score", "active", "jerseyNum"]
Line 699 ⟶ 1,313:
["Robert'; DROP TABLE players--", "3", "true", "101"]</pre>
=={{header|
===Using [http://slick.lightbend.com/doc/3.2.3/introduction.html Slick] FRM===
{{Out}}Best seen running in your browser [https://scastie.scala-lang.org/fJKRDaydSsGGlZQXJUhvxw Scastie (remote JVM)].
<syntaxhighlight lang="scala">import slick.jdbc.H2Profile.api._
import slick.sql.SqlProfile.ColumnOption.SqlType
import scala.concurrent.Await
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
object PlayersApp extends App {
lazy val playerRecords = TableQuery[PlayerRecords]
val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
// Pre-compiled parameterized statement
val compiledUpdate = Compiled { jerseyN: Rep[Int] =>
for {c <- playerRecords if c.jerseyNum === jerseyN} yield (c.name, c.score, c.active)
}
def setup = DBIO.seq(
playerRecords.schema.create,
playerRecords ++= Seq( // JDBC batch update
(7, "Roethlisberger, Ben", 94.1f, true),
(11, "Smith, Alex", 85.3f, true),
(18, "Manning, Payton", 96.5f, false),
(99, "Doe, John", 15f, false))
)
def queryPlayers(prelude: String) = {
println("\n " +prelude)
println(
"│ Name │Scor│ Active │Jerseynum│\n" +
"├───────────────────────────────┼────┼────────┼─────────┤"
)
DBIO.seq(playerRecords.result.map(_.map {
case (jerseyN, name, score, active) =>
f"$name%32s $score ${(if (active) "" else "in") + "active"}%8s $jerseyN%8d"
}.foreach(println)))
}
// Definition of the PLAYERS table
class PlayerRecords(tag: Tag) extends Table[(Int, String, Float, Boolean)](tag, "PLAYER_RECORDS") {
def active = column[Boolean]("ACTIVE")
def jerseyNum = column[Int]("JERSEY_NUM", O.PrimaryKey)
def name = column[String]("NAME", SqlType("VARCHAR2(32)"))
def score = column[Float]("SCORE")
def * = (jerseyNum, name, score, active)
}
println(s"The pre-compiled parameterized update DML:\n${compiledUpdate(0).updateStatement}")
Await.result(db.run(
for { // Using the for comprehension
_ <- setup
_ <- queryPlayers("Before update:")
n <- compiledUpdate(99).update("Smith, Steve", 42f, true)
_ <- queryPlayers("After update:")
} yield n), Duration.Inf)
}</syntaxhighlight>
=={{header|Seed7}}==
Line 740 ⟶ 1,386:
A column from a result row is retrieved with the function [http://seed7.sourceforge.net/libraries/sql_base.htm#column%28in_sqlStatement,in_integer,attr_integer%29 column].
<
include "sql_base.s7i";
Line 771 ⟶ 1,417:
execute(testDb, "drop table players");
close(testDb);
end func;</
{{out}}
<pre>Smith, Steve 42 TRUE 99
Jesten, Jim 0 FALSE 100
Jello, Frank 0 FALSE 101</pre>
=={{header|SQL}}==
{{works with|Oracle}}
<syntaxhighlight lang="sql">-- This works in Oracle's SQL*Plus command line utility
VARIABLE P_NAME VARCHAR2(20);
Line 822 ⟶ 1,465:
commit;
select * from players;</syntaxhighlight>
{{Out}}
<pre>SQL> SQL>
NAME SCORE ACTIV JERSEYNUM
-------------------- ---------- ----- ----------
Line 840 ⟶ 1,481:
NAME SCORE ACTIV JERSEYNUM
-------------------- ---------- ----- ----------
Smith, Steve 42 TRUE 99</pre>
=={{header|SQL PL}}==
{{works with|Db2 LUW}}
The following example is indeed parameterized SQL with named placeholders and it prevents SQL injections, and the SQL performs very well, because the execution plan is also precompiled.
<syntaxhighlight lang="sql pl">
--#SET TERMINATOR @
CREATE TABLE PLAYERS (
NAME VARCHAR(32),
SCORE INT,
ACTIVE SMALLINT,
JERSEYNUM INT
) @
CREATE PROCEDURE UPDATE_PLAYER (
IN PLAYER_NAME VARCHAR(32),
IN PLAYER_SCORE INT,
IN PLAYER_ACTIVE SMALLINT,
IN JERSEY_NUMBER INT
)
BEGIN
UPDATE PLAYERS
SET NAME = PLAYER_NAME, SCORE = PLAYER_SCORE, ACTIVE = PLAYER_ACTIVE
WHERE JERSEYNUM = JERSEY_NUMBER;
END @
INSERT INTO PLAYERS VALUES ('Pele', '1280', 0, 10) @
CALL UPDATE_PLAYER ('Maradona', '600', 1, 10) @
SELECT * FROM PLAYERS @
</syntaxhighlight>
Output:
<pre>
db2 -td@
db2 => CREATE TABLE PLAYERS (
NAME VARCHAR(32),
SCORE INT,
ACTIVE SMALLINT,
JERSEYNUM INT
) @
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE UPDATE_PLAYER (
IN PLAYER_NAME VARCHAR(32),
IN PLAYER_SCORE INT,
IN PLAYER_ACTIVE SMALLINT,
IN JERSEY_NUMBER INT
)
BEGIN
UPDATE PLAYERS
SET NAME = PLAYER_NAME, SCORE = PLAYER_SCORE, ACTIVE = PLAYER_ACTIVE
WHERE JERSEYNUM = JERSEY_NUMBER;
END @
DB20000I The SQL command completed successfully.
db2 => INSERT INTO PLAYERS VALUES ('Pele', '1280', 0, 10) @
DB20000I The SQL command completed successfully.
db2 => CALL UPDATE_PLAYER ('Maradona', '600', 1, 10) @
Return Status = 0
db2 => SELECT * FROM PLAYERS @
NAME SCORE ACTIVE JERSEYNUM
-------------------------------- ----------- ------ -----------
Maradona 600 1 10
1 record(s) selected.</pre>
=={{header|Tcl}}==
{{works with|Tcl|8.6}}
<
# These next two lines are the only ones specific to SQLite
Line 866 ⟶ 1,573:
# With apologies to http://xkcd.com/327/
setPlayer $db 76 -> "Robert'; DROP TABLE players--" 0 false
$db close</
=={{header|Wren}}==
{{libheader|Wren-sql}}
An embedded program using our SQLite wrapper.
<syntaxhighlight lang="wren">import "./sql" for Connection
var db = Connection.open("rc.db")
var createSql = [
"DROP TABLE IF EXISTS players",
"CREATE table players (name, score, active, jerseyNum)",
"INSERT INTO players VALUES ('Roethlisberger, Ben', 94.1, TRUE, 7)",
"INSERT INTO players VALUES ('Smith, Alex', 85.3, TRUE, 11)",
"INSERT INTO players VALUES ('Doe, John', 15, FALSE, 99)",
"INSERT INTO players VALUES ('Manning, Payton', 96.5, FALSE, 123)"
]
for (sql in createSql) db.exec(sql)
var widths = [22, 7, 7, 9]
System.print("Before update:\n")
db.printTable("SELECT * FROM players", widths)
var updateSql = "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = 99"
var ps = db.prepare(updateSql)
ps.bindText(1, "Smith, Steve")
ps.bindDouble(2, 42)
ps.bindBool(3, true)
ps.bindInt(4, 99)
ps.step()
System.print("\nAfter update:\n")
db.printTable("SELECT * FROM players", widths)</syntaxhighlight>
{{out}}
<pre>
Before update:
name score active jerseyNum
------------------------------------------------
Roethlisberger, Ben 94.1 1 7
Smith, Alex 85.3 1 11
Doe, John 15 0 99
Manning, Payton 96.5 0 123
After update:
name score active jerseyNum
------------------------------------------------
Roethlisberger, Ben 94.1 1 7
Smith, Alex 85.3 1 11
Smith, Steve 42.0 1 99
Manning, Payton 96.5 0 123
</pre>
{{omit from|360 Assembly}}
{{omit from|6502 Assembly}}
{{omit from|6800 Assembly}}
{{omit from|68000 Assembly}}
{{omit from|80386 Assembly}}
{{omit from|8051 Assembly}}
{{omit from|8086 Assembly}}
{{omit from|ARM Assembly}}
{{omit from|ARM Assembly}}
{{omit from|Computer/zero Assembly}}
{{omit from|DIV Games Studio}}
{{omit from|Jacquard Loom}}
{{omit from|LC3 Assembly}}
{{omit from|Maxima}}
{{omit from|Minimal BASIC|No network access or built-in SQL}}
{{omit from|MIPS Assembly}}
{{omit from|OASYS Assembler}}
{{omit from|Palo Alto Tiny BASIC|No network access or built-in SQL}}
{{omit from|PARI/GP}}
{{omit from|PDP-11 Assembly}}
{{omit from|PDP-11 Assembly}}
{{omit from|Pentium Assembly}}
{{omit from|PL/0|No network access or built-in SQL}}
{{omit from|TI-83 BASIC|No network access or built-in SQL}}
{{omit from|TI-89 BASIC|No network access or built-in SQL}}
{{omit from|Tiny BASIC|No network access or built-in SQL}}
{{omit from|Unlambda|No network access or built-in SQL}}
{{omit from|VAX Assembly}}
{{omit from|X86 Assembly}}
{{omit from|XSLT 1.0}}
{{omit from|Z80 Assembly}}
|