Parameterized SQL statement: Difference between revisions
Added FreeBASIC
m (Petelomax moved page Parametrized SQL statement to Parameterized SQL statement: spelling) |
(Added FreeBASIC) |
||
(6 intermediate revisions by 6 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}}==
<
db @
"UPDATE players SET name=?1,score=?2,active=?3 WHERE jerseyNum=?4"
Line 21:
\ execute the query
db @ swap db:exec</
=={{header|Ada}}==
<
with GNATCOLL.SQL_Impl; use GNATCOLL.SQL_Impl;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
Line 61:
Free (Conn);
Free (DB_Descr);
end Prepared_Query;</
=={{header|Arturo}}==
<
createTable: function [][
Line 103:
print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]
close db</
{{out}}
Line 110:
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}}==
Line 119 ⟶ 293:
Tested with gcc version 4.9.2 (Raspbian 4.9.2-10) and SQLite 3.8.7.1
<
#include <stdlib.h>
#include <sqlite3.h>
Line 232 ⟶ 406:
}
printf("\n");
}</
{{out}}
Line 249 ⟶ 423:
=={{header|C sharp|C#}}==
<
using System.Data.SqlClient;
Line 258 ⟶ 432:
static void Main(string[] args)
{
tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";
Line 272 ⟶ 445:
}
}
}</
=={{header|C++}}==
{{libheader|Qt}}
This example uses the Qt SQL module to access an ODBC data source.
<
#include <iostream>
Line 309 ⟶ 482:
}
return 0;
}</
=={{header|Clojure}}==
<
; Using h2database for this simple example.
(def db {:classname "org.h2.Driver"
Line 321 ⟶ 494:
; As an alternative to update!, use execute!
(sql/execute! db ["UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?" "Smith, Steve" 42 true 99])</
=={{header|F_Sharp|F#}}==
{{trans|C#}}
<
[<EntryPoint>]
Line 341 ⟶ 514:
tCommand.ExecuteNonQuery() |> ignore
0</
=={{header|Go}}==
<
import (
Line 376 ⟶ 549:
}
rows.Close()
}</
{{out}}
<pre>
Line 387 ⟶ 560:
Example uses the [http://hackage.haskell.org/package/HDBC <tt>HDBC</tt>] package:
<
import Database.HDBC (IConnection, commit, run, toSql)
Line 406 ⟶ 579:
main :: IO ()
main = undefined</
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}}==
<
import Algorithms as algo;
import FileSystem as fs;
Line 451 ⟶ 624:
}
return ( 0 );
}</
=={{header|Java}}==
<
import java.sql.DriverManager;
import java.sql.Connection;
Line 490 ⟶ 663:
}
}
</syntaxhighlight>
=={{header|Julia}}==
Line 496 ⟶ 669:
Uses the SQLite package.
<
name = "Smith, Steve"
Line 514 ⟶ 687:
tbl = SQLite.query(db, "SELECT * from players")
println(tbl)</
Line 525 ⟶ 698:
=={{header|Kotlin}}==
<
import java.sql.DriverManager
Line 548 ⟶ 721:
}
conn.close()
}</
=={{header|M2000 Interpreter}}==
<syntaxhighlight lang="m2000 interpreter">
Module Parametrized_Sql {
Base "rosetta" ' warning erase database if found it in current directory
Line 568 ⟶ 741:
}
Parametrized_Sql
</syntaxhighlight>
=={{header|Mathematica}}/{{header|Wolfram Language}}==
<
conn=OpenSQLConnection[JDBC["ODBC(DSN)","testdb"],"Username"->"John","Password"->"JohnsPassword"];
SQLExecute[conn,"UPDATE players SET name = `1`, score = `2`, active = `3` WHERE jerseyNum = `4`", {SQLArgument["Smith, Steve",42,True,99]}]
CloseSQLConnection[conn];</
=={{header|NetRexx}}==
Using an [http://db.apache.org/derby/ Apache Derby] embedded database:
<
options replace format comments java crossref symbols nobinary
Line 717 ⟶ 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.
<
let db = open(":memory:", "", "", "")
Line 737 ⟶ 910:
echo row
db.close()</
{{out}}
Line 743 ⟶ 916:
=={{header|Objeck}}==
<
use ODBC;
Line 761 ⟶ 934:
};
}
}</
=={{header|Pascal}}==
Line 767 ⟶ 940:
{{libheader|SQLite}}
Tested with Free Pascal 2.6.4 (arm) and SQLite 3.8.7.1
<
uses
sqlite3, sysutils;
Line 890 ⟶ 1,063:
// Close the database connection.
sqlite3_close(db);
end.</
{{out}}
<pre>
Line 906 ⟶ 1,079:
=={{header|Perl}}==
<
my $db = DBI->connect('DBI:mysql:mydatabase:host','login','password');
Line 912 ⟶ 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>
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, 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 976 ⟶ 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 1,071 ⟶ 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 1,079 ⟶ 1,236:
{{works with|PostgreSQL}}
{{libheader|sql db-lib}}
<
#lang racket/base
(require sql db)
Line 1,102 ⟶ 1,259:
'("Smith, Steve" 42 #t 99))
</syntaxhighlight>
=={{header|Raku}}==
(formerly Perl 6)
<syntaxhighlight lang="raku"
my $db = DBIish.connect('DBI:mysql:mydatabase:host','login','password');
Line 1,112 ⟶ 1,269:
my $update = $db.prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");
my $rows-affected = $update.execute("Smith, Steve",42,'true',99);</
=={{header|Ruby}}==
Using the {{libheader|sqlite3-ruby}} gem
[[Category:SQLite]]
<
db = SQLite3::Database.new(":memory:")
Line 1,149 ⟶ 1,306:
# and show the results
db.execute2('select * from players') {|row| p row}</
outputs
<pre>["name", "score", "active", "jerseyNum"]
Line 1,155 ⟶ 1,312:
["John Doe", "-1", "false", "100"]
["Robert'; DROP TABLE players--", "3", "true", "101"]</pre>
=={{header|Scala}}==
===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)].
<
import slick.sql.SqlProfile.ColumnOption.SqlType
Line 1,245 ⟶ 1,372:
} yield n), Duration.Inf)
}</
=={{header|Seed7}}==
Line 1,259 ⟶ 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 1,290 ⟶ 1,417:
execute(testDb, "drop table players");
close(testDb);
end func;</
{{out}}
Line 1,299 ⟶ 1,426:
=={{header|SQL}}==
{{works with|Oracle}}
<
VARIABLE P_NAME VARCHAR2(20);
Line 1,338 ⟶ 1,465:
commit;
select * from players;</
{{Out}}
<pre>SQL> SQL>
Line 1,359 ⟶ 1,486:
{{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.
<
--#SET TERMINATOR @
Line 1,386 ⟶ 1,513:
SELECT * FROM PLAYERS @
</syntaxhighlight>
Output:
<pre>
Line 1,425 ⟶ 1,552:
=={{header|Tcl}}==
{{works with|Tcl|8.6}}
<
# These next two lines are the only ones specific to SQLite
Line 1,446 ⟶ 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.
<
var db = Connection.open("rc.db")
Line 1,479 ⟶ 1,606:
System.print("\nAfter update:\n")
db.printTable("SELECT * FROM players", widths)</
{{out}}
Line 1,516 ⟶ 1,643:
{{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}}
|