odbc_execute

(PHP 4, PHP 5)

odbc_executeExécute une requête SQL préparée

Description

bool odbc_execute ( resource $result_id [, array $parameters_array ] )

Exécute une requête SQL préparée par odbc_prepare().

Liste de paramètres

result_id

L'identifiant de résultat, depuis la fonction odbc_prepare().

parameters_array

Les valeurs du paramètre parameter_array seront substituées dans les variables de requêtes de la requête préparée. Les éléments de ce tableau seront convertis en chaînes de caractères en appelant cette fonction.

Tout paramètre de parameter_array qui commence et termine par des guillemets simples sera considéré comme un nom de fichier à lire et envoyé à la base de données, avec la variable de requête appropriée.

Si vous voulez stocker une chaîne de caractères qui commence et se finit réellement par des guillemets, vous devez ajouter un espace au début ou à la fin de la chaîne, pour éviter que ce paramètre soit confondu avec un nom de fichier. Si ce n'est pas possible dans le cadre de votre application, vous devrez passer par la fonction odbc_exec().

Valeurs de retour

Cette fonction retourne TRUE en cas de succès ou FALSE si une erreur survient.

Exemples

Exemple #1 Exemple avec odbc_execute() et odbc_prepare()

Dans le script suivant, $success ne sera possible que si les trois paramètres de maproc sont des paramètres de type IN :

<?php
$a 
1;
$b 2;
$c 3;
$stmt    odbc_prepare($conn'CALL maproc(?,?,?)');
$success odbc_execute($stmt, array($a$b$c));
?>

Si vous devez appeler une procédure stockée en utilisant des paramètres INOUT ou OUT, la solution est d'utiliser une extension native de votre base de données (par exemple, mssql pour MS SQL Server, ou oci8 pour Oracle).

Historique

Version Description
4.2.0 La lecture du fichier est maintenant sujette au safe mode et aux restrictions open-basedir dans le paramètre parameters_array.

Voir aussi

add a note add a note

User Contributed Notes 16 notes

up
2
alvaro at demogracia dot com
2 years ago
When odbc_execute() fails it returns FALSE and triggers a warning but it will not necessarily feed odbc_error() and odbc_errormsg().
up
2
mjs at beebo dot org
8 years ago
Don't miss the part where it says that if your string starts and ends with a single quote, the string is interpreted as a filename!

This means that you can't do:

$sth = odbc_prepare($dbh, "INSERT INTO people(name) VALUES(?)");
$res = odbc_execute($sth, array($name));

without checking the value of $name--if $name is, say, '\\'c:\\passwords.txt\\'' the contents of c:\\passwords.txt get inserted into your database as a "name".

Also, despite what the documentation suggests, there (incredibly) doesn't appear to be any way to escape your single quotes (via experimentation, and from reading the source): if your string starts and ends with a single quote you cannot use odbc_execute to insert it into the database.
up
1
Marco Napetti
2 years ago
To use prepared with select queries, the right way is:
<?PHP

$rConnection
= odbc_connect('AS400', 'QSECOFR', 'QSECOFR');
if(
$rConnection === false) {
    throw new
ErrorException(odbc_errormsg());
}

$rResult = odbc_prepare($rConnection, 'SELECT * FROM KMNSH00F WHERE SHTMST > ?');
if(
$rResult === false) {
    throw new
ErrorException(odbc_errormsg());
}

if(
odbc_execute($rResult, array('0001-01-01 00:00:00.000000')) === false) {
    throw new
ErrorException(odbc_errormsg());
}

odbc_result_all($rResult);

odbc_free_result($rResult);

odbc_close($rConnection);

?>
up
1
tcmleung at yahoo dot com
12 years ago
odbc has a maximum buffer size, that means it only stores and retrieves a limited size of data to/from database each time. The maximum buffer size is 4096 and set in php.ini (odbc.defaultlrl). You can set it to higher value for larger data access.
up
1
sjericson at mindspring dot com
13 years ago
I don't think odbc_prepare and odbc_execute support output parameters for stored procedures on MSSQL.  An example of output parameters for MSSQL is at  http://support.microsoft.com/support/kb/articles/q174/2/23.asp

Also, my MSSQL driver seems happy only when I use the following incantation:

...code removed...
$stmt=odbc_prepare($conn_id, "exec my_proc_name ?,?,?");
$parms=array("fred","password",5);
if (!odbc_execute($stmt, &$parms)) die("odbc_execute failed");
up
1
wntrmute at tampabay dot rr dot com
16 years ago
Solid Issue:
Solid defines CHAR, VARCHAR, LONG VARCHAR, BINARY, VARBINARY, and LONG VARBINARY to be a maximum of 2G in length.  However, when creating your tables for use with PHP one should choose LONG VARCHAR or LONG VARBINARY for these kinds of fields if you are planning on storing really large or lengthy data.  IE: Data exceeding 64k in length such as GIF/JPG, or really huge text areas.
up
0
anonymous
5 years ago
if you can't use php_mssql module in your environment (suse linux, apache2, php5, FreeTDS, unixODBC) an alternative is to use sql server functions instead of procedures. here is my sample code.

<?php
  $connect       
= odbc_connect($myDB, $myUser, $myPass);

 
$query = "SELECT dbo.<function>(<column>,<text>) alias";

 
// perform the query
 
$result = odbc_exec($connect, $query);

  while(
odbc_fetch_row($result)) {
   
$Var1    = odbc_result($result, <column alias>);
   
//echo "Var1: " . $Var1 . "<br>";

        // add additional logic

 
}
?>

Once I figured this out, my app worked perfectly.
up
0
jeroen at pyromanic dot nl
6 years ago
If you want to use stored procedures with MSSQL over ODBC, please read

http://www.sitepoint.com/article/php-microsoft-sql-server/2

It can you save lots of time ;)
up
0
traynor at uni hyphen hannover dot de
6 years ago
Obdc_prepare and obdc_execute can only be used as an alternative to odbc_exec in limited circumstances:

$con = obdc_connect ($dsn, $user, $pass);
$sql = "select * from TABLE";

$result = obdc_exec ($con, $sql); //this line can be replaced as blow
//then to see results:

odbc_result_all ($result);
odbc_free_result ($result);
odbc_close ($con);

gives the same result with the middle line as:

$result = odbc_prepare ($con, $sql);
odbc_execute ($result);

as long as $sql contains a well formed and complete query.

There is no point in trying to convert this into a parameter query with question marks as placeholders, since code like this will result only in error messages:

$sql = "select * from TABLE where needle = ?";
$result = odbc_prepare ($con, $sql);
for ($i = 0; $i < 4; $i++)
{
  odbc_execute ($result, array ($i));
  // and whatever you want to do with the result
  // but all you get is "parameter expected" or "count does not match"
}

The lack of documentation for such functions should have been an alarm signal.
up
0
a dot brock at hhv-rheinklang dot de
8 years ago
I have a solution for the problem with the strings beeing interpreted as filename because of the single quotes:

Just add a blank to the end of the string:

<?php
function odbc_escape_params ($params) {
if (!
is_array($params) or empty($params)) {
  return array();
}
foreach (
$params as $key=>$val) {
  if (
strlen($val) > 1 and $val{0} == "'" and $val{strlen($val)-1} == "'") {
  
$params[$key] .= ' ';
  }
}
return
$params;
}
?>
up
0
russell dot brown at removethis dot insignia dot com
10 years ago
In reply to tcmleung at yahoo dot com (09-Nov-2001), I would add a caveat that I've found, which is that the odbc.defaultlrl/odbc_longreadlen() values may only apply to odbc->php conversion and not php->odbc (though this may be database-specific). Hence, if you want to post binary data the 4096 byte limit still stands. So you stand a better chance of being able to post binary data using the quoted filename upload procedure described above, rather than using the prepare... execute method with data held in a php variable.
up
0
svemir_AT_baltok.com
12 years ago
In reply to cpoirier's note from 04-Mar-2001 03:30:

Currently, Access 2000 DOES support parametrized queries via ODBC. It still seems to have a problem with Memo and OLE fields, but "normal" types work just fine.
up
0
reaganpr at hotmail dot com
13 years ago
When running the CGI version of 4.0.6 under windows, I came across this error when trying to call a stored procedure in SQL Server using odbc_execute w/ the parameter array set:

FATAL:  emalloc():  Unable to allocate 268675669 bytes

Scary error, huh? In my case it just meant that SQL Server couldn't find the stored procedure.  Totally my fault, but a rather nondescript error message.

p.
up
0
cpoirier at shelluser dot net
13 years ago
A quick note in hopes that my pain will save someone else:  Microsoft Access ODBC drivers do not support parameterized queries.
up
0
edrenth at thematec dot nl
14 years ago
When used with parameters and the statement fails, you cannot use different arguments anymore, the same arguments as with the failed statement will be used.
up
0
tony dot wood at revolutionltd dot com
15 years ago
For a simple database insert to a database that has no password and $from and $to are predefined variables.

<?php
/* get connection */
$conn=odbc_connect("mydb","","");

/* run insert */
$stmt = odbc_prepare($conn, "INSERT INTO mytable (jor_from, jor_to) VALUES('$from', '$to');" );

/* check for errors */
if (!odbc_execute($stmt)) {
   
/* error  */
   
echo "Whoops";
}

/* close connection */
odbc_close($conn);
?>
To Top