ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:learn_bind:bind_vars

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
v5:userguide:learn_bind:bind_vars [2016/03/23 01:48] – mnewnhamv5:userguide:learn_bind:bind_vars [2016/03/23 02:04] (current) – mnewnham
Line 1: Line 1:
 +<-  ^ v5:userguide:userguide_index|List Of Tutorials ^ v5:userguide:learn_bind:portability|Portability ->
 +~~NOTOC~~
 ====== Using Bind Variables ====== ====== Using Bind Variables ======
 ===== Description ===== ===== Description =====
Line 27: Line 29:
 Note that the number of variable in the ''$bindVars'' array must match the bind placeholders (?) Note that the number of variable in the ''$bindVars'' array must match the bind placeholders (?)
  
-===== Bind Placeholders Across Databases ===== 
-Bind placeholders vary from one database to another, so ADOdb provides a method [[v5:reference:connection:param|param()]] that returns a database specific placeholder. Using the above example, we can write it as a portable statement: 
  
-<code php> +If an ADOdb method does support binding, the syntax definition in the documentation will appear similar to this:
-$bindVars = array('A','B','C'); +
- +
-$col1Ph = $db->param('col1'); +
-$col2Ph = $db->param('col2'); +
-$col2Ph = $db->param('col3'); +
- +
-$sql = "SELECT * FROM some_table WHERE col1=$col1Ph AND col2=$col2ph AND col3=$colPh"; +
-$result = $db->execute($sql,$bindVars); +
-</code> +
- +
-In the above example, you can see that it is necessary to obtain a unique placeholder for each bind variable. This is because in some databases, the placeholder is uniquely associated with the column. So in the above example would appear as follows: +
-==== MySQL ==== +
-  SELECT * FROM some_table WHERE col1=? AND col2=? AND col3=? +
-==== Oracle ==== +
-  SELECT * FROM some_table WHERE col1=:col1 AND col2=:col2 AND col3=:col3 +
-==== PostgreSQL ==== +
-  SELECT * FROM some_table WHERE col1=$1 AND col2=$2 AND col3=$3 +
-   +
-===== Databases Without Bind Support ===== +
-You can still use the bind form of execute in ADOdb if the database does not support binding. In this case ADOdb simply rewrites the query statement for you back into the non-bind form. +
- +
- +
-===== Bulk Binding ===== +
-Some databases, Oracle in particular, offer massive performance improvements in insertion performance when used with 2 dimensional bind arrays. This technique is known as ''Bulk Binding''. +
- +
-==== Concept ==== +
-Using our previous example, let us assume that we need to insert multiple ''some_table'' records, and that we can determine the values to be inserted prior to the execution of all the statements +
- +
-Traditionally, we might write our insert statement like this: +
- +
-<code php> +
- +
-$sql = "INSERT INTO some_table(col1,col2,col3) values ('A','B','C')"; +
-$db->execute($sql); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values ('D','E','F')"; +
-$db->execute($sql); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values ('G','H','I')"; +
-$db->execute($sql); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values ('J','K','L')"; +
-$db->execute($sql); +
-etc...... +
-</code> +
- +
-We can also use bind variables like this, in this case we may obtain speed improvements over the previous execution: +
- +
-<code php> +
-$bindVars = array('A','B','C'); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)"; +
-$db->execute($sql,$bindVars); +
-$bindVars = array('D','E','F'); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)"; +
-$db->execute($sql,$bindVars); +
-etc...... +
-</code> +
- +
-If we now turn our ''$bindVars'' into a 2 dimensional array, we can create multiple records using a single SQL statement: +
- +
-<code php> +
-$bindVars = array(array('A','B','C'), +
-                  array('D','E','F'), +
-                  array('G','H','I'), +
-                  array('J','K','L')); +
-$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)"; +
-$db->execute($sql,$bindVars) +
-</code> +
-===== Portable Support ===== +
-In databases without Bulk Binding support, ADOdb simply iterates through the $bindVars array, and executes them individually. +
- +
-===== Enabling Bulk Bind Support ===== +
-Bulk Binding using 2D arrays is disabled by default in ADOdb. It must be deliberately enabled in the driver using the following syntax: +
- +
-<code php> +
-include 'adodb/adodb.inc.php'; +
- +
-$db = newAdoConnection('drivername'); +
- +
-$db->connect('parameters'); +
- +
-/* +
-* Enable bulk binding +
-*/ +
-$db->bulkBind = true; +
-</code>                 +
  
 +<WRAP box 350px>
 +== Syntax ==   
 +  mixed someMethod
 +  (
 +           string $sqlStatement,
 +   optional mixed $bindVars
 +  )
 +</WRAP>
 + 
  
  
v5/userguide/learn_bind/bind_vars.1458694085.txt.gz · Last modified: (external edit)