Prepares SQL request.
resource i5_ prepare (resource query [, resource connection])
Parameters
|
query |
SQL request to prepare |
|
connection |
Connection ID (optional) |
Return
Returns prepared request ID in case of success, FALSE in other case.
|
I5_ERR_PHP_HDLDFT |
256 |
No default connection found. |
|
I5_ERR_PHP_HDLCONN |
257 |
This resource has no connection active. |
|
I5_ERR_PHP_RESOURCE_BAD |
261 |
No resource found. |
|
I5_ERR_PHP_TYPEPARAM |
262 |
Type of element x in parameter -1 must be y. Type z was provided. |
|
I5_ERR_PHP_NBPARAM_BAD |
263 |
Wrong parameter count |
Details
There are three main advantages using prepared requests in your script:
Performance: While preparing a request, database server creates a return optimized path to collect the requested data. Later, when the i5_prepare prepared request is sent, it will use the path avoiding processor overload with each request sent.
Safety: While preparing a request, it is possible to set markers for entry values. Processing the prepared request with entry values, Easycom checks each entry value to make sure that their type match with the column or the description parameters.
Warning, connections and resources are transient and disappear with each script end, then, the above notes are only in force with set of process appearing in the same script.
Example 1
Parameter name is used within the i5_prepare command:
$nom = 'C-04';
//Prepared request creation
$req = i5_prepare('SELECT FIRSTNAME, LASTNAME FROM EASYCOMXMP/SP_CUST WHERE CUST_ID=:custid');
if ($req)
{
/* Set input parameter value */
$ret = i5_setparam($req,
"custid", $nom);
if (!$ret)
{
print_r("i5_setparam error : ".i5_errormsg().'<br/>');
}
/* Request execution */
$ret = i5_execute($req);
if (!$ret)
{
print_r("i5_execute error : ".i5_errormsg().'<br/>');
}
/* Values reading */
$ret = i5_fetch_row($req);
if (is_bool($ret))
{
if (i5_errno () == I5_ERR_BEOF) {
echo "No data, cursor before or end of file<BR>";
}
else{
print_r("i5_fetch_row error : ".i5_errormsg().'<br/>');
}
}
else
{
printf('Firstname : %s - Lastname %s<BR>', $ret[0], $ret[1]);
}
/* Request execution with new parameter value, using
array */
$newRecord = array('C-03');
$ret = i5_execute($req,$newRecord);
if (!$ret)
{
print_r("i5_execute error : ".i5_errormsg().'<br/>');
}
else
{
/* Values reading */
$ret = i5_fetch_row($req);
if (is_bool($ret))
{
if (i5_errno () == I5_ERR_BEOF) {
echo "No data, cursor before or end of file<BR>";
}
else{
print_r("i5_fetch_row error : ".i5_errormsg().'<br/>');
}
}
else
{
printf('Firstname : %s - Lastname %s<BR>', $ret[0], $ret[1]);
}
}
/* Request execution with new parameter value, using parameter value */
$newRec = "C-05";
$ret = i5_execute($req,$newRec);
if (!$ret)
{
print_r("i5_execute error : ".i5_errormsg().'<br/>');
}
else
{
/* Values reading */
$ret = i5_fetch_row($req);
if (is_bool($ret))
{
if (i5_errno () == I5_ERR_BEOF) {
echo "No data, cursor before or end of file<BR>";
}
else{
print_r("i5_fetch_row error : ".i5_errormsg().'<br/>');
}
}
else
{
printf('Firstname : %s - Lastname %s<BR>', $ret[0], $ret[1]);
}
}
/* Command closing */
$ret = i5_free_query($req);
if (!$ret)
{
print_r("i5_free_query error : ".i5_errormsg().'<br/>');
}
}
else
{
print_r("i5_prepare error : ".i5_errormsg().'<br/>');
}
Example 2
Question mark ‘?’ is used within the i5_prepare command, for a stored procedure having this prototype:
CR.CUSTOUTX3 (IN firstnameI CHAR(5), OUT lastnameO CHAR(10), OUT cityO CHAR(10))
$req = i5_prepare('call CR/CUSTOUTX3(?,?,?)');
if ($req)
{
$firstname = 'Louis';
$ret = i5_setparam($req, 0, $firstname);
if (!$ret)
{
print_r("i5_setparam error : ".i5_errormsg().'<br/>');
}
/* Request execution */
$ret = i5_execute($req);
if (!$ret)
{
print_r("i5_execute error : ".i5_errormsg().'<br/>');
}
else
{
$P0 = i5_getparam($req, 0);
if (is_bool($P0) && $P0 == FALSE)
{
print_r("i5_getparam error : ".i5_errormsg().'<br/>');
}
else
echo 'P0 = ' . $P0 .'<br/>';
}
$P1 = i5_getparam($req, 1);
if (is_bool($P1) && $P1 == FALSE)
{
print_r("i5_getparam error : ".i5_errormsg().'<br/>');
}
else
{
echo 'P1 = ' . $P1 .'<br/>';
}
$P2 = i5_getparam($req, 2);
if (is_bool($P2) && $P2 == FALSE)
{
print_r("i5_getparam error : ".i5_errormsg().'<br/>');
}
else
{
echo 'P2 = ' . $P2 .'<br/>';
}
}
}
else
{
print_r("i5_prepare error : ".i5_errormsg().'<br/>');
}
See also