SQL: Preparation of SQL query fails. But is executed when done manually

Question

I am getting an error which can be seen below when I am preparing the query:

SQL-ERR:Preparation of INSERT Query Failed: Ora-Err: -1756 ORA-01756: 
quoted string not properly terminated

The query is as follows:

EXEC SQL declare INSDTA STATEMENT;
EXEC SQL PREPARE INSDTA FROM :stmt;
if(sqlca.sqlcode < 0)
{
    DEBUG_LOG("SQL-ERR:Preparation of INSERT Query Failed: Ora-Err: %d %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
    DEBUG_LOG("The Query is: %s\n", insertQuery);
    return PREPARATION_FAILURE;
}

And the query from the log file is:

INSERT INTO TABLENAME
VALUES (
    '00000001',
    '00004467',
    '0',
    'R56565',
    '03404395',
    '20110601',
    '999',
    '87685785',
    '2017-01-10-23.05.26.000000',
    'KRMAR',
    'KRMAR',
    '77898878',
    '03',
    '00000001',
    'U',
    '01',
    '1',
    '87685785',
    'R56565',
    '89878988',
    'cde',
    'Andr\351',
    '[email protected]',
    '01192966',
    'HGJF',
    '00000000',
    '',
    '900429',
    '1',
    '98989897',
    '',
    'Aargau / Solothurn (CIC)',
    'VCD',
    'RB9',
    'VCD',
    'Observer'
    )

If I execute it manually, the data is getting inserted.

But programatically it is failing for many such rows.

Note that the input text for insert query contains special chars like é, ü.

Also, the same program is working on development system perfectly. But on the production, it is failing.

Manual insertion is working on production properly.

What might be the issue? Any configuration issues?

Thanks in advance.

Downatheel

can you try null instead of empty strings ? usually two quote for oracle os to escape one quote. – Zebadiah

okay, will try. Do you guess any other issues? – Downatheel

doesn't seems so. – Zebadiah

try as raw strings? – Accentuate

I think \351 might be interpreted into something as backslash is the escape character – Mccain

Does your original stmt contain the escaped \351 or the actual é character? And what is NLS_LANG set to on the development and production systems? I'd guess one has a character set that understands the character (e.g. UTF8 or WE8ISO8859P1), and the other has something that doesn't (like US7ASCII). – Gimbals

I would start by looking for the field that is causing the issue by removing fields one by one from you query and find which one contains the problem, there could even be several ones! Then reduce your dataset until you find the exact data that is causing troubles. There could be an escape char anywhere or a quote or double quote. you can do a quick check on your data if you suspect certain columns to contain esc chars or quotes. Make also sure that you are looking at the same data on production and test – Intense

replace \ its escape character – Kokoschka

hey @Downatheel , could you share the fields and expect data types in your table? Am thinking that a mismatched data-type could be the culprit- for example, your first field I assume is an ID field but you are passing it a string.. could be right but wanted to check. – Gonnella

Answer 1

Since \ is the escape character, I think the error comes from 'Andr\351' which should probably be 'André'.

Remove backslash characters from your query just to check if this is the real cause.

Wilma

But the same query is working on local system. Let check the logs in the local dev system on how the special chars are getting treated. Will get back to you on this. Thanks for the reply. – Downatheel

@Downatheel it could be that you are not connected with the same parameters/configuration (user, connection string parameters, etc.) – Mccain

Answer 2

There is a fundamental difference between executing a query manually, and using dynamic SQL to prepare a query with bind variables for executing multiple times with different parameters.

Here's a good overview on Dynamic SQL Statements. What you might find especially useful is the section on Preparing Dynamic SQL Statements - specifically the details about placeholders in Oracle, and the section on Executing Dynamic SQL Statements.

It's hard to tell what the problem might be without seeing the dynamic SQL statement you are using, and how you are declaring your placeholders.

Some pointers:

• It seems you are using one variable while attempting to insert multiple values. In Oracle, at the time the statement is executed, there must be one variable for each placeholder.

• The procedure you are showing is not complete.

  • What does the query in :stmt look like?
  • What do your placeholders look like? Are strings properly quoted?

  • What are the values in the variables you are using for the placeholders?

  • Your statement is missing END-EXEC

Example taken from above source:

move "INSERT INTO publishers " &
           "VALUES (?,?,?,?)" to stmtbuf
 EXEC SQL
     PREPARE stmt1 FROM :stmtbuf
 END-EXEC
  ...
 EXEC SQL
     EXECUTE stmt1 USING :pubid,:pubname,:city,:state
 END-EXEC

Selinski

Answer 3

remove all (' ') from integral values

Heavensent

Answer 4

Your query is being failed because forward space in 'Aargau / Solothurn (CIC)' column value. Backspace is special character so you need take care of either use backspace or remove it at all. Then you will be able to execute it successfully.

Toothpaste