Parameterized SQL statement: Difference between revisions

Parameterized SQL statement (view source)

Revision as of 16:00, 19 March 2022

15,084 bytes added , 2 years ago

m

→‎{{header|Phix}}: syntax coloured, included the nicer pretty print routines and named parameters

Petelomax

7,805

edits

Revision as of 15:27, 19 March 2022 (view source) Petelomax (talk \| contribs) m (Petelomax moved page Parametrized SQL statement to Parameterized SQL statement: spelling) ← Older edit		Revision as of 16:00, 19 March 2022 (view source) Petelomax (talk \| contribs) m (→‎{{header\|Phix}}: syntax coloured, included the nicer pretty print routines and named parameters) Newer edit →
Line 916: =={{header\|Phix}}== {{libheader\|SQLite}} <!--<lang Phix>(notonline)--> <span style="color: #000080;font-style:italic;">-- ~~-- demo\rosetta\Parametrized_SQL_statement.exw~~ -- demo\rosetta\Parameterized_SQL_statement.exw -- -- ============================================ ~~include pSQLite.e~~ -- </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> ~~sqlite3 db = sqlite3_open(":memory:")~~ <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> ~~integer res = sqlite3_exec(db,`create table players (name, score, active, jerseyNum)`)~~ <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> ~~res = sqlite3_exec(db,`insert into players values ('Roethlisberger, Ben', 94.1, 1, 7 )`)~~ <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> ~~res = sqlite3_exec(db,`insert into players values ('Smith, Alex', 85.3, 1, 11)`)~~ ~~res = sqlite3_exec(db,`insert into players values ('Doe, John', 15, 0, 99)`)~~ <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> ~~res = sqlite3_exec(db,`insert into players values ('Manning, Payton', 96.5, 0, 123)`)~~ <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> ~~pp({"Before",sqlite3_get_table(db, "select * from players")},{pp_Nest,2})~~ <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> ~~sqlite3_stmt pStmt = sqlite3_prepare(db, `update players set name=?, score=?, active=? where jerseyNum=?`)~~ <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> ~~sqlite3_bind_text(pStmt,1,"Smith, Steve")~~ <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> ~~sqlite3_bind_double(pStmt,2,42)~~ <span style="color: #004080;">string</span> <span style="color: #000000;">text</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">""</span> ~~sqlite3_bind_int(pStmt,3,true)~~ <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> ~~sqlite3_bind_int(pStmt,4,99)~~ <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> ~~res = sqlite3_step(pStmt);~~ <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> ~~if res!=SQLITE_DONE then ?9/0 end if~~ <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> ~~if sqlite3_finalize(pStmt)!=SQLITE_OK then ?9/0 end if~~ <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> ~~pp({"After",sqlite3_get_table(db, "select * from players")},{pp_Nest,2})~~ <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> ~~sqlite3_close(db)</lang>~~ <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> ~~<small>(The distributed version of this code displays nicer formatted output, but is nearly twice as long.)</small>~~ <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> <!--</lang>--> {{out}} <pre> {"Before",: ~~{{"~~ name":Roethlisberger, "Ben score",:94.1 "active",: 1 "jerseyNum~~"},~~: 7 name:Smith, Alex score:85.3 active: 1 jerseyNum: 11 ~~{"Roethlisberger, Ben", "94.1", "1", "7"},~~ name:Doe, John score: 15 active: 0 jerseyNum: 99 ~~{"Smith, Alex", "85.3", "1", "11"},~~ name:Manning, Payton score:96.5 active: 0 jerseyNum: 123 ~~{"Doe, John", "15", "0", "99"},~~ After: ~~{"Manning, Payton", "96.5", "0", "123"}}}~~ name:Roethlisberger, Ben score:94.1 active: 1 jerseyNum: 7 ~~{"After",~~ ~~{{"~~ name":Smith, "Alex score",:85.3 "active",: 1 "jerseyNum~~"},~~: 11 name:Smith, Steve score: 42 active: 1 jerseyNum: 99 ~~{"Roethlisberger, Ben", "94.1", "1", "7"},~~ name:Manning, Payton score:96.5 active: 0 jerseyNum: 123 ~~{"Smith, Alex", "85.3", "1", "11"},~~ ~~{"Smith, Steve", "42.0", "1", "99"},~~ ~~{"Manning, Payton", "96.5", "0", "123"}}}~~ </pre>