 |
|
|
 | Manipulating Variable-length Types in PHP |  | PHP Interface | Auxiliary Functions |  |
You can execute a SQL statement in the PHP module by using either a "traditional" function wb_exec or set of functions for preparing and executing SQL statements. We call these functions PSQL functions.
The first advantage over wb_exec is the ability to repeatedly execute the prepared (compiled) SQL statement.
The second advantage is better control over parameters passed to SQL statement when executed. The SQL Server tells the PHP module the expected types of all parameters in the statement when preparing a SQL statement. This allows the PHP module to convert values more precisely when reading an output parameter from the SQL Server or when sending an input parameter to the SQL Server. These functions allow you to set an input parameter value to NULL. This cannot be done using the statement executed by the wb_exec function.
A disadvantage to this is the necessity to call at least two functions, one for preparing the SQL statement, and one for the execution.
Basic functions are wb_psql_prepare and wb_psql_execute. The wb_psql_prepare function prepares (compiles) a SQL statement and returns a handle (identifier of the prepared SQL statement that is passed to other PSQL functions that manipulate this SQL statement). The wb_psql_execute function executes the prepared SQL statement. A PSQL statement may be closed if it is not needed (frees the resources allocated) using the standard function wb_close. However, it is not necessary to call this function, since PHP monitors statement inactivity and will automatically closes a prepared SQL statements if it is not needed. This is similar to finding and closing an inactive cursor.
Example: Execute a SQL statement that has no parameters
$psql_handle=wb_psql_prepare($connection,"DELETE FROM Test_table;"); wb_psql_execute($psql_handle); wb_close($psql_handle);
You must mark parameters in a SQL statement with the question mark ? (unlike parameters in SQL statements executed by the wb_exec function). The parameters can be IN, OUT or INOUT. You can pass the parameter list to the wb_psql_execute function. The IN and INOUT parameter values are passed to the SQL Server, the SQL statement is executed and the OUT and INOUT parameter values are read and written to the specified PHP variables.
Example: A SQL statement with one IN and one OUT parameter. The IN parameter value is entered as a constant, the OUT parameter value will be written to the $name_var variable. We have to initialize the PHP variable we want to assign the OUT parameter value into (it is sufficient to assign any value to the PHP variable, just so the variable will exist before the wb_psql_execute function is executed).
$psql_handle=wb_psql_prepare($connection,"SELECT name INTO ? FROM Test_table WHERE id=?"); $name_var=null; wb_psql_execute($psql_handle,$name_var,4);
The SQL statement parameters can be passed by independent functions. Unlike the previous option, you can pass the value of long IN parameters in parts and read the value of long OUT parameters in parts as well. This is important mainly for BLOB and CLOB type parameters.
Example: Execution of a SQL statement when passing parameters using independent PSQL functions.
$psql_handle=wb_psql_prepare($connection,"SELECT name INTO ? FROM Test_table WHERE id=?"); wb_psql_send_param($psql_handle,1,4); wb_psql_execute($psql_handle); $name_var=wb_psql_get_out_param($psql_handle,0);
You can find more information about how to use the PSQL functions in the following chapters:
Description of parameter value passing in the prepared SQL statement between a PHP script and a SQL Server can be found on the page passing parameters in prepared SQL statements.
List of topics:
| Manipulating Variable-length Types in PHP | | PHP Interface | Auxiliary Functions | |