PHP ODBC insert multiple rows at once into MSSQL returns error

Question

I have PHP code where I do odbc_prepare and odbc_execute and I'm trying to insert multiple rows at once and I'm getting this strange error:

SQL error: [Microsoft][ODBC SQL Server Driver]Insert value list does not match column list, SQL state 21S01 in SQLDescribeParameter ..

Backtrace (I formatted the output for better readability):

PHP   4. models\Tapas->query($query = 'INSERT INTO tzeit ([pers_id],[card_nr],[time],[type],[terminal_nr],[tzeit_id]) VALUES (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)', 
  $params = [
    0 => '0450', 
    1 => '0450', 
    2 => '2021-07-20 11:33:16.000', 
    3 => 1, 
    4 => 41, 
    5 => 1641816, 
    6 => '0405', 
    7 => '0405', 
    8 => '2021-07-20 11:36:49.000', 
    9 => 2, 
    10 => 41, 
    11 => 1641826, 
    12 => '0330', 
    13 => '0330', 
    14 => '2021-07-20 11:49:54.000', 
    15 => 1, 
    16 => 41, 
    17 => 1641836, 
    18 => '2069', 
    19 => '2069', 
    20 => '2021-07-20 11:49:58.000', 
    21 => 1, 
    22 => 41, 
    23 => 1641837, 
    24 => '0222', 
    25 => '0222', 
    26 => '2021-07-20 12:03:46.000', 
    27 => 2, 
    28 => 41, 
    29 => 1641851, 
    30 => '0390',
    31 => '0390', 
    32 => '2021-07-20 12:07:13.000',
    33 => 2, 
    34 => 41, 
    35 => 1641854, 
    36 => '0098', 
    37 => '0098', 
    38 => '2021-07-20 12:17:02.000', 
    39 => 2, 
    40 => 41, 
    41 => 1641861, 
    42 => '0405', 
    43 => '0405', 
    44 => '2021-07-20 12:23:51.000', 
    45 => 1, 
    46 => 41, 
    47 => 1641869
  ]) E:\msf\www\cas\models\A_MSSQL.php:116
PHP   5. odbc_prepare($connection_id = resource(17) of type (odbc link), $query = 'INSERT INTO tzeit ([pers_id],[card_nr],[time],[type],[terminal_nr],[tzeit_id]) 
  VALUES (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?)') 
  E:\msf\www\cas\models\A_MSSQL.php:69

I tried to count columns and values multiple times and the result is equal. I have 8 rows for insert, each row has 6 values. Total 48 values. $params array has 48 values. Query contains 8 value groups with 48 question marks in total.

My target table has very simple structure (development purpose):

CREATE TABLE [dbo].[tzeit](
    [pers_id] [varchar](4) NOT NULL,
    [card_nr] [varchar](4) NOT NULL,
    [time] [datetime] NOT NULL,
    [type] [int] NOT NULL,
    [terminal_nr] [int] NOT NULL,
    [tzeit_id] [bigint] NOT NULL
)

I've tried to insert each row one by one and it worked. I just don't understand why it doesn't work with multiple rows at once. It's there maybe a limit of maximum placeholders?

Perhaps you have a trigger or something on the table that's "misbehaving"? — Thom A, Aug 19 '21 at 10:36
@Larnu nothing. Target database it's completely new and contains only this one table. — oLDo, Aug 19 '21 at 10:49
Is it valid to send `datetime` as text such as `'2021-07-20 12:23:51.000'` — Charlieface, Aug 19 '21 at 13:17
@Charlieface i believe it is, because how I wrote, when I'm doing these insert one by one it works. — oLDo, Aug 19 '21 at 13:33

score 0 · Answer 1 · answered Sep 17 '21 at 14:36

0

It's because is really old SQL Server and there is different syntax.

SQL Server 2005: Insert multiple rows with single query

answered Sep 17 '21 at 14:36

oLDo

487
1
3
15

PHP ODBC insert multiple rows at once into MSSQL returns error

1 Answers1