ADOdb

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.

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

Bulk Binding using 2D arrays is disabled by default in ADOdb. It must be deliberately enabled in the driver using the following syntax:

include 'adodb/adodb.inc.php';
 
$db = newAdoConnection('drivername');
 
$db->connect('parameters');
 
/*
* Enable bulk binding
*/
$db->bulkBind = true;

Traditionally, we might write our insert statement like this:

$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......

We can also use bind variables like this, in this case we may obtain speed improvements over the previous execution:

$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......

If we now turn our $bindVars into a 2 dimensional array, we can create multiple records using a single SQL statement:

$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)

In databases without Bulk Binding support, ADOdb simply iterates through the $bindVars array, and executes the prepared statement individually.