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
– Thom A ♦, Commented Aug 19, 2021 at 10:36
@Larnu nothing. Target database it's completely new and contains only this one table. — oLDo
– oLDo, Commented Aug 19, 2021 at 10:49
Is it valid to send datetime as text such as '2021-07-20 12:23:51.000' — Charlieface
– Charlieface, Commented Aug 19, 2021 at 13:17
@Charlieface i believe it is, because how I wrote, when I'm doing these insert one by one it works. — oLDo
– oLDo, Commented Aug 19, 2021 at 13:33

oLDo · Accepted Answer · 2021-09-17 14:36:38Z

1

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, 2021 at 14:36

oLDo

5451 gold badge3 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP ODBC insert multiple rows at once into MSSQL returns error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related