It is possible to use parameters with SQL statements, parameters should be used if the same SQL statement is executed multiple times but only with different parameter values. Below there is an example of a 'SELECT" statement with a single parameter for the customer identification number.
| select * from orders where customerID = ? |
If the SQL statement above is executed directly a SQL error message is
generated. A statement with a parameter must first be prepared (=compiled by the
database), the parameters must be assigned values and then the prepared SQL statement can
be executed. A SQL statement is prepared by calling the method prepare/3.
Setting the value of a parameter is done by calling the method putParameter/3, executing a prepared statement is
done by calling execute/2 of the query class.
| _Query<-close, _Query<-prepare(SQL, true, ErrorCode), _Query<-putParameter(0, 'ANTON', true), %Assign value to first parameter _Query<-execute(Result, ErrorCode), |
The parameters are identified by their position, the position number for the first parameter is 0, the following is 1, etc. The same SQL statement can be executed, after it has been prepared, again by assigning new values to the parameters and calling execute/2.
| _Query<-putParameter(0, 'BLAUS', true), %Assign
another value to first parameter _Query<-execute(Result, ErrorCode), |
Parameters are legal only in certain places in SQL statements. They are not allowed in the select list of a SELECT statement, nor are they allowed as both operands of a binary operator, like the equal sign (=).
There are also output parameters that get a value after executing a prepared SQL statement or a stored procedure. To retrieve the value of an output parameter use the method parameter/3