"Right truncation of string data" error on multiple inserts with prepared statements

[Former Member]

I'm currently developing web app (PHP 7, Lumen framework 5) that communicates with SQL Anywhere database. I have succesfully created an SQL Anywhere database (version 17), and now I'm trying to insert data into database tables, but no luck. I have the following solution which works correctly (no errors during execution), but stores data into tables row by row:

$query = "INSERT INTO $table_name ($cols) " .
    "VALUES (" . implode(",", array_fill(0, $cols_count, "?")) . ")";

foreach ($rows as $row) {
    $stmt = sasql_prepare($conn, $query);

    foreach (array_values($row) as $index => &$value) {
        sasql_stmt_bind_param_ex(
            $stmt, $index, $value,
            $value ? gettype($value)[0] : 's',
            $value === null
        );
    }

    sasql_stmt_execute($stmt);
    sasql_stmt_close($stmt);
}

However, when I try the next solution I receive the following error "sasql_stmt_execute(): SQLAnywhere: [-638] Right truncation of string data" (logic stays the same, but rows are stored all at once):

$row_placeholder = implode(",", array_fill(0, $cols_count, "?"));
$placeholders = implode(",", array_fill(0, count($rows), "($row_placeholder)"));

$query = "INSERT INTO $table_name ($cols) VALUES $placeholders";

$stmt = sasql_prepare($conn, $query);

$param_number = 0;
foreach ($rows as $row) {
    foreach ($row as &$value) {
        sasql_stmt_bind_param_ex(
            $stmt, $param_number++, $value,
            $value ? gettype($value)[0] : 's',
            $value === null
        );
    }
}

sasql_stmt_execute($stmt);
sasql_stmt_close($stmt);

I don't know what can cause this error, as well as was not able to find a proper solution...Need help here.

[VolkerBarth]

SQLCODE -638 usually means exactly that:

Probable cause
Non-space characters were truncated upon the assignment of string data.

I'd guess the according data type of your column is too small to hold the full string data.

[Former Member]

Ok, but why exactly the same rows (strings, integers, etc.) are normally stored (without errors) in database tables when inserting it one by one instead of inserting all at once?

[VolkerBarth]

Can't tell that without knowing more details (commit behaviour, table schema, data) - however, you can temporarily ignore that error via setting the string_rtruncation option to Off, and then check what values might have been silently truncated - and additionally check whether the behaviour is different between the two code samples.

[Vlad]

I’d like to know the table Schema, but I remember that one smart guy said once that all (or maybe some) fixed length strings have a varying length. I couldn’t find the evidence in the documentation, but only this one:

CHAR is semantically equivalent to VARCHAR, although they are different types. In SQL Anywhere, CHAR is a variable-length type. In other relational database management systems, CHAR is a fixed-length type, and data is padded with blanks to max-length bytes of storage. SQL Anywhere does not blank-pad stored character data.

I agree with the database server. If you pass a string that is longer than a column, the further assumptions what the server does must be avoided. It is the application, that must truncate / validate the input. The DB server just warns you, and you have to fix it.

[Breck_Carter]

Ok, but why exactly the same rows (strings, integers, etc.) are normally stored (without errors) in database tables when inserting it one by one instead of inserting all at once?

Vlad is correct that the message probably means what it says.

Can you display exactly what $query looks like in the second example? ...I don't know how PHP works so I can't figure it out for myself 🙂

[Breck_Carter]

Another (possibly better) suggestion: Run dbsrv17.exe with the -zr ALL option to see exactly WHAT statements are being sent to the server... something is awry with the second example 🙂