Query Updates with Escape logic to the apostrophe (i.e. double-up the single quote character) causing maxlength error.

Question

Question

When I use Escape logic when inserting or updating Oracle Table I'm getting Max-Length exceeded error. With the original value the length is good but after I add Escape Logic, it causes value to be greater than max-length. The original value was "I visited O'Brien before heading out of town." and after added Escape logic it was "I visited O''Brien before heading out of town." Max-Length is 45.

INSERT INTO MyText
    (text)
VALUES
    ('I visited O''Brien before heading out of town.')
                 /\
             right here

Any ideas around this?

SQL %Execute QUERY ERROR - %SQLCODE=-400 - %Message=ORA-12899: value too large for column "SCHOOL"."ADDRESS"."NAME" (actual: 47, maximum: 40)

- PrepStmtData="Monika''''''''''''''''''''''''''''''''s Test AC" - SQL ERROR CODE SCHOOL.ADDRESS

Product version: IRIS 2023.1

Discussion (5)3

Log in or sign up to continue

score 1 · Answer 1 · 2024-02-16T05:34:23-05:00

It seems that NAME is defined as 40 char but you are inserting a string with 47 chars.

In fact your string is 45 chars that is out of space defined for the column.

In order to check the escaping logic you should try with a shorter strings.

score 0 · Answer 2 · 2024-02-19T11:11:04-05:00

I know the Escape Logic works; that is not the issue. When I use the Escape Logic in increases the string length when I update the table. I'm trying to figure out if there is a way so that Escape logic doesn't get counted as part of the string length

score 0 · Answer 3 · 2024-02-20T07:12:35-05:00

Try inserting an apostrophe through concatenation, something like

INSERT INTO MyText
    (text)
VALUES
    ('I visited O' || CHAR(39) || 'Brien before heading out of town.')

score 2 · Answer 4 · 2024-02-20T07:33:10-05:00

The best and most correct solution would be to use parameters in SQL and pass a value as a parameter

INSERT INTO MyText
    (text)
VALUES
    (?)

While you have Oracle somewhere behind, Oracle should support parameters as well, but I don't know how it will work in this case

The increase may appear due to passing this query to the next database, and it escaped again. Passing as parameters may solve this issue.

But still even without this apostrophe the string is too long

score 0 · Answer 5 · 2024-03-13T10:04:41-04:00

Hi Jim.

You are using parameterized sql query statement against your Oracle linked table, in ObjectScript context, string delimiter is double quote, can you try just put one single quote in your PrepStmtData variable and bind it to the prepared statement against your Oracle table?

PrepStmtData="Monika's Test AC"