Rosetta Code

Parameterized SQL statement: Difference between revisions

Page Discussion

Parameterized SQL statement (view source)

Revision as of 02:31, 31 March 2024

3,133 bytes added ,  1 month ago
Added FreeBASIC
m (syntax highlighting fixup automation)
(Added FreeBASIC)
 
(3 intermediate revisions by 3 users not shown)
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 1,027 ⟶ 1,201:
(put!> P 'score 42)
(put!> P 'active T) )</syntaxhighlight>
 
=={{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|Python}}==
Line 1,198 ⟶ 1,312:
["John Doe", "-1", "false", "100"]
["Robert'; DROP TABLE players--", "3", "true", "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|Scala}}==
Line 1,494 ⟶ 1,578:
{{libheader|Wren-sql}}
An embedded program using our SQLite wrapper.
<syntaxhighlight lang="ecmascriptwren">import "./sql" for Connection
 
var db = Connection.open("rc.db")
Line 1,559 ⟶ 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}}
Jjuanhdez
2,122

edits

Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/328071...363084"