Most of us here follow the good practice of executing our SQL statements directly against the database (using our favorite SQL editor, off course) before using them within an SQLExec in PeopleCode, a view or an SQL actions in App Engines. Even after following this, we might get hit in some situations. In today’s post we will go through such a scenario.
We had an App Engine that inserts the contents of a file into a record and it was failing with the below error.
SQL error. Function: SQLExec Error Position: 0 Return: 8601 – [Microsoft][SQL Server Native Client 10.0]String data, right truncation (SQLSTATE 22001) 0
Naturally, the first thing to do was to check the size of the field. In our case the field in question was a Long Character filed with length set to 0 (maximum). So it should easily be able to take up the contents of the file. That got ruled out!
The next thing to do was take the SQL out of the App Engine and execute it directly against the database. I took the SQL statement out and ran it with sample values – values similar to the ones the failed App Engine was trying to insert. One row got inserted successfully. That got ruled too!
It was at this point that %TextIn construct flashed in my mind. %TextIn is something that’s mandatory for inserting / updating Long Character fields on all databases except Informix. Got the bind variable for the Long Character placed within %TextIn as shown below. The App Engine ran successfully when it was restarted after this change was made.
SQLExec("INSERT INTO PS_SGK_PRCSCHK VALUES (:1,%TextIn(:2))", SGK_PSRQST_AET.PRCSINSTANCE.Value, &SGK_filecontent);