Pdoexception' with Message 'Sqlstate[22001]: String Data, Right Truncated: 0

PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0

Unfortunately,

It's a PDO_ODBC 64-bit incompatibility problem (#61777, #64824) and without any doubts you are on a 64-bit build which doesn't allow you to bind parameters.

Fortunately,

It has a patch that was first included in the 5.6 release:

This bug is also referenced in
#61777 and is still present
in the latest stable release of the 5.5 branch. I see two tickets
exist for this problem already, and I'm just submitting these changes
via github as a reminder that this is a serious problem for anyone
using PDO_ODBC on the x64 builds.

What is wrong with your PHP's shipped PDO_ODBC?

By looking at one of those recommended patches:

diff --git a/ext/pdo_odbc/odbc_stmt.c b/ext/pdo_odbc/odbc_stmt.c
index 8b0ccf3..1d275cd 100644
--- a/ext/pdo_odbc/odbc_stmt.c
+++ b/ext/pdo_odbc/odbc_stmt.c
@@ -551,7 +551,7 @@ static int odbc_stmt_describe(pdo_stmt_t *stmt, int colno TSRMLS_DC)
    struct pdo_column_data *col = &stmt->columns[colno];
    RETCODE rc;
    SWORD   colnamelen;
-   SDWORD  colsize;
+   SQLULEN colsize;
    SQLLEN displaysize;

We see the only thing that's changed is SDWORD (16-bit signed integer) which is substituted with new ODBC type SQLULEN that is 64 bits in a 64-bit ODBC application and 32 bits in a 32-bit ODBC application.

I believe committer wasn't aware of colsize data type only since in the very next line SQLLEN is defined properly.

What should I do now?

1. Upgrade to PHP version >= 5.6
2. Stick with odbc_* functions as a working solution.
3. Compile a PHP v5.5.9 with provided patches.
4. Build your own PDO wrapper as recommended by @GordonM

What does the SQL Server Error String Data, Right Truncation mean and how do I fix it?

Either the parameter supplied for ZIP_CODE is larger (in length) than ZIP_CODEs column width or the parameter supplied for CITY is larger (in length) than CITYs column width.

It would be interesting to know the values supplied for the two ? placeholders.

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'ans' at row 1 (SQL: insert into `faqs` (`title`, `ans`, `updated_at`,

Change the ans type to text:

$table->text('ans');

and do a fresh migrate:

php artisan migrate:fresh

NOTE: by executing the migrate:fresh command, you will lose all your DB data.

String data, right truncated: 1406 Data too long on Bit Column

The data type BIT only accepts binary values. You specified the length to be 1 bit long but you try to input a string of value '1' which in binary is 00110001 and it obviously overflows.

The recommended practice for storing booleans in database is to use nullable DateTime data type. e.g.

`admin_approved` DATETIME DEFAULT NULL

Then if the value is populated you know the boolean state is on, and if the value is NULL the boolean state is off. Additionally you know when the flag was switched on.

You could also use tinyint and store 1 or 0. However, from experience I can say it is not very useful.

I'm getting String data, right truncation errors from PHP using ODBC and connecting to a Microsoft SQL Server 2008R2 instance

After much tweaking and searching, and a whole lot of shot-in-the-dark troubleshooting, I finally decided that this is an ODBC driver problem.

Specifically, I was using a driver downloaded from Microsoft, supposedly designed to work with PHP and unixODBC on RHEL/CentOS6. It's known as "Microsoft ODBC Driver 11 for SQL Server" in its own README file, and comes in a file called msodbcsql-11.0.2270.0.tar.gz. (These details provided for the benefit of anyone else trying to do the same thing)

In light of my experience, I do not recommend this driver.

I downloaded, compiled, and installed the latest "stable" version of FreeTDS instead. If it matters to you, the version I got is 0.91 (the download file doesn't say this, but it unpacks into a directory with this number). This had/has its own minor configuration problems, but ultimately seems to be working much better than the Microsoft-provided driver. I don't know if this is still being actively maintained, as the most recent timestamps in the distribution were August 17, 2011.

Silly me, thinking that I should use the Microsoft driver to access a Microsoft database server, and expect it to actually do what it says it will do.

Laravel: String data, right truncated: 1406 Data too long for column

You need to create a new migration, register it with composer du command and run php artisan migrate command to change type of the column:

Schema::table('the_table_name', function (Blueprint $table) {
    $table->string('hotel', 255)->change();
});

Error 'String or binary data would be truncated' in Microsoft SQL

Based on this link: http://www.sql-server-performance.com/2007/string-or-binary-data-truncated/

This error message appears when you try to insert a string with more characters than the column can maximal accommodate.

---

Related Topics