PDO lastInsertId primary bigInt, after id 999999999 return ''

[Litiano]

+## Driver version or file name
+v5.2.0 TS
+
+## SQL Server version
+13.0.4206.0
+
+## Client operating system
+Windows 10 x64.
+
+## PHP version
+7.2.3 x86
+
+## Microsoft ODBC Driver version
+13 (13.1.14414.46).
+
+## Table schema
CREATE TABLE [dbo].[big_int](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[name] nvarchar NOT NULL,
[created_at] [datetime] NULL,
[updated_at] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
+
+## Problem description
+PDO lastInsertId no work correctly with primary bigInt, after id 999999999 return ''
+

[yitam]

Hi @Litiano,
Did you mean lastinsertid() failed after inserting 999999999 records? If you like, please provide a simple PHP script to repro this problem. Thanks!

[Litiano]

Hi @yitam .

I used 'DBCC checkident ('big_int', reseed, 999999000)' to speed up the process.

$conn = new PDO( "sqlsrv:server=$serverName ; Database = $database", $uid, $pwd); for ($i = 0; $i <= 1000; $i++) { $d = $conn->query("INSERT INTO [big_int] ([name]) VALUES ('name')"); if($conn->lastInsertId() === '') { echo "Error i = {$i}"; exit(); } }

Table:
CREATE TABLE [dbo].[big_int]( [id] [bigint] IDENTITY(1,1) NOT NULL, [name] [nvarchar](255) NOT NULL, [created_at] [datetime] NULL, [updated_at] [datetime] NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

[yitam]

Thanks @Litiano
I can reproduce the problem and will do some investigation. We will keep you posted.

[yitam]

Hi @Litiano, the buffer allocated in our driver when returning the lastinsertid() has the size of a maximum integer (10 digits), and in your case, a bigint over 999999999 has exceeded the limit.

[Litiano]

Hi @yitam ,

How can we solve this?

[yitam]

Yes @Litiano we can fix it, but will check the plan when to release a preview. Please stay tuned.

[yitam]

Fixed in 5.2.1-preview. Please reopen the issue if otherwise, @Litiano