f2s_esql_set_param¶
Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers
Syntax¶
f2s_esql_set_param {Parameter Number} to {value} [using {variant}]
Parameter |
Description |
|---|---|
Parameter Number |
The parameter number (from 1 to iNum). |
value |
The value of the SQL Parameter. |
variant |
Optional variant type variable that will receive a statement interface. |
Description¶
This command sets a SQL Parameter for a SQL statement. Setting Parameters in SQL Queries helps prevent SQL Injection attacks also also helps optimize the performance of the SQL Execution.
The optional “using” syntax allows you to pass around the interface created by this command. The variable passed MUST be a variant. This allows for multiple statements to be active concurrently in some cases. If you use the “using” syntax, it must be used on all subsequent commands. Mixing the using syntax is not supported. So for instance, if you plan on having multiple statements active concurrently, all active statements must make use of the using syntax.
Note
Oracle and PostgreSQL will require slightly different syntax when writing the SQL statement. Instead of ? begin used as the placeholder for the parameter value, Oracle needs a :parameter name and PostgreSQL needs $parameter number. Using a ? will throw a syntax error. Please see the examples below for these two database servers.
Examples¶
Example (not for Oracle or PostgreSQL)
Variant[] vRow String sState Number nCreditLimit Move "CA" to sState Move 2000 to nCreditLimit f2s_esql_create_stmt "select customer_number,name,state,credit_limit from customer where state = ? and credit_limit >= ? order by credit_limit desc" f2s_esql_prepare_stmt f2s_esql_set_param 1 to sState f2s_esql_set_param 2 to nCreditLimit f2s_esql_execute_stmt Repeat f2s_esql_move_next If (Found) Begin f2s_esql_get_row vRow Showln vRow[0] ' - ' vRow[1] ' - ' vRow[2] ' - ' vRow[3] End Until (not(Found)) f2s_esql_close_stmt
Example Oracle
Variant[] vRow
String sState
Number nCreditLimit
Move "CA" to sState
Move 2000 to nCreditLimit
f2s_esql_create_stmt "select customer_number,name,state,credit_limit from customer where state = :state and credit_limit >= :creditlimit order by credit_limit desc"
f2s_esql_prepare_stmt
f2s_esql_set_param 1 to sState
f2s_esql_set_param 2 to nCreditLimit
f2s_esql_execute_stmt
Repeat
f2s_esql_move_next
If (Found) Begin
f2s_esql_get_row vRow
Showln vRow[0] ' - ' vRow[1] ' - ' vRow[2] ' - ' vRow[3]
End
Until (not(Found))
f2s_esql_close_stmt
Example PostgreSQL
Variant[] vRow
String sState
Number nCreditLimit
Move "CA" to sState
Move 2000 to nCreditLimit
f2s_esql_create_stmt "select customer_number,name,state,credit_limit from customer where state = $1 and credit_limit >= $2 order by credit_limit desc"
f2s_esql_prepare_stmt
f2s_esql_set_param 1 to sState
f2s_esql_set_param 2 to nCreditLimit
f2s_esql_execute_stmt
Repeat
f2s_esql_move_next
If (Found) Begin
f2s_esql_get_row vRow
Showln vRow[0] ' - ' vRow[1] ' - ' vRow[2] ' - ' vRow[3]
End
Until (not(Found))
f2s_esql_close_stmt