Bind Parameter Issues With SQL Server and PHP

php

You’re all set to send heaps of data to SQL Server in your shiny new PHP app and suddenly you run into this error:

1
Import error on line: xxx SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters....

Eek! What does that mean? Well, basically, you have probably tried to do a huge database INSERT statement which comprises of multiple rows, and their total number of columns exceeds 2100.

The fix is quite straight-forward but nonetheless, a little irritating. Why 2100? I’m not sure, but given an array of items which each look like this:

1
2
3
4
5
$dataItems[] = array(
    'Field1' => $field1,
    'Field2' => $field2,
    ...
);

break up the insert into chunks using the array_chunk function.

1
2
3
foreach (array_chunk($dataItems, (2100/ModelName::NUMBER_OF_FIELDS) - 2) as $chunk) {
    ModelName::insert($chunk);
}

Here, replace ModelName with the name of your model (if using Eloquent/Laravel), and divide 2100 by the number of fields. In my example, I stored that in a constant within the model. I then subtract 2 for luck and to avoid any edge case problems.

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com