Issue Performing an insert using prepared statements with PDO and an ODBC driver gives the following error when at least one of the parameters is over 30 characters: Inserts work for any bound string that is <= 30 characters in length. I have no issues with SELECT queries. Using INSERT with isql and sqlcmd does not produce an error, but

Error during insert with parameter(s) over 30 characters

Issue

Performing an insert using prepared statements with PDO and an ODBC driver gives the following error when at least one of the parameters is over 30 characters:

SQLSTATE[HY010]: Function sequence error: 0 
[unixODBC][Driver Manager]Function sequence error (SQLExecute[0] at /usr/src/builddir/ext/pdo_odbc/odbc_stmt.c:254)

Inserts work for any bound string that is <= 30 characters in length.

I have no issues with SELECT queries.

Using INSERT with isql and sqlcmd does not produce an error, but the column values are truncated in the database if they are over 30 characters.

It appears to be a driver issue.

Any ideas on what is causing the issue, and how it can be solved?

Example

Below is a minimal example duplicating the error on my system in PHP, isql, and sqlcmd.

The table used (called table) has three columns:

colvarchar varchar(70)
colnvarchar nvarchar(40)
colnchar nchar(60)

And the code that produces the error:

<?php
$dns = 'odbc:testdb';
$username = 'user';
$password = 'pass';

$pdo = new PDO($dns, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = <<<'QUERY'
        INSERT INTO table
            (colvarchar, colnvarchar, colnchar)
        VALUES
            (CAST(:colvarchar AS varchar)                 
             CAST(:colnvarchar AS nvarchar),
             CAST(:colnchar AS nchar));
QUERY;

$prepStmt = $pdo->prepare($sql);

// Add one more characters to any of the following strings to cause the error
$prepStmt->bindValue('colvarchar', '012345678901234567890123456789');
$prepStmt->bindValue('colnvarchar', '012345678901234567890123456789');
$prepStmt->bindValue('colnchar', '012345678901234567890123456789');

$prepStmt->execute();
?>

Adding an additional characters to any of the 30 character strings will cause the following error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY010]: Function sequence error: 0 [unixODBC][Driver Manager]Function sequence error (SQLExecute[0] at /usr/src/builddir/ext/pdo_odbc/odbc_stmt.c:254)' in ...

Using isql and sqlcmd from command line performs the insert, but a select on the table shows the strings are truncated to 30 characters.

slqcmd:

sqlcmd -D -S testdb -U user -P pass -q “INSERT INTO table (colvarchar, colnvarchar, colnchar) VALUES (CAST(‘012345678901234567890123456789xxx’ AS varchar), CAST(‘012345678901234567890123456789xxx’ AS nvarchar), CAST(‘012345678901234567890123456789xxx’ AS nchar))”

(1 rows affected)

isql:

isql testdb -U user -P pass

SQL> INSERT INTO table (colvarchar, colnvarchar, colnchar) VALUES (CAST(‘012345678901234567890123456789xxx’ AS varchar), CAST(‘012345678901234567890123456789xxx’ AS nvarchar), CAST(‘012345678901234567890123456789xxx’ AS nchar))”

SQLRowCount returns 1

Result:

SELECT colvarchar, colnvarchar, colnchar FROM table;
colvarchar | colnvarchar | colnchar
012345678901234567890123456789 | 012345678901234567890123456789 | 012345678901234567890123456789
012345678901234567890123456789 | 012345678901234567890123456789 | 012345678901234567890123456789

Research

This post details a similar issue with inserts that do not conform to max column width, timestamp format, or column type.

Timestamp format has been tested and does work, since it is less than 30 characters.
Test strings over 30 characters have been checked to make sure their length is less than the max width of their column.
The datatype of the problematic columns are varchar.

System Setup

OS: Debian wheezy (64bit)
Database: Microsoft SQL Server 2014
Webserver: Apache 2.2.22 (64bit)
PHP 5.6.24 with Zend thread safety (64bit)
Microsoft ODBC Driver 11.0.2270.0 (Red Hat Linux) (64bit)
- created appropriate environment to work with Debian according to this blog and spiceworks
unixODBC 2.3.0 (64bit)
- required by the MS ODBC driver on linux

UPDATE: I believe this to be a unixODBC issue, as the 30 character truncation was present when I was using FreeTDS and unixODBC (which was changed to MS ODBC and unixODBC because of this issue). The difference was there was no error message when using FreeTDS; it failed silently like isql and sqlcmd are doing presently.

Changed unixODBC version to 2.3.0 from 2.3.4 for its compatibility with MS ODBC 11, as shown here. Issue persists.

All programs that required ODBC shared libraries were linking with versions that were from 2011. They now all link with the update to date shared libraries from unixODBC. Issue persists.

Answer

The issue is with the SQL CAST expressions.

From MSDN – CAST & CONVERT:

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.

The documentation lists specific exceptions that are guaranteed not to be truncated. However, it does not detail what length data will be truncated too.

Specify the data type length to avoid the truncation:

CAST (colvarchar  AS varchar(70))
CAST (colnvarchar AS nvarchar(40))
CAST (colnchar    AS nchar(60))

Advertisement

Answer