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.

asked 02 Jul, 06:23

ilyas_khametov's gravatar image

ilyas_khametov
31116
accept rate: 100%

edited 02 Jul, 14:23

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.

(02 Jul, 10:38) Volker Barth
Replies hidden

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?

(02 Jul, 12:37) ilyas_khametov

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.

(02 Jul, 16:25) Volker Barth

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.

(04 Jul, 07:40) Vlad

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 :)

(04 Jul, 08:50) 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 :)

(04 Jul, 08:54) Breck Carter
showing 1 of 6 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×105
×53
×31
×14

question asked: 02 Jul, 06:23

question was seen: 544 times

last updated: 04 Jul, 08:54