JDBC Receiver CC - sqlBindMode with Oracle sequence

[Former Member]

Hello experts,

I have receiver JDBC CC with XML SQL notation.

I have usual requirement - store whole XML payload in DB table.

Problem is, that many of that payloads are bigger than 4000 chars (maximum for oracle statement) so I used sqlBindMode in Advanced Mode of CC.

But after that change new error came out -

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

Primary key of table AUDIT (table where I store XML payload) are column ID (number(19,0)) - which have to be filled by an Oracle sequence S_AUDIT.

I usually put there S_AUDIT.nextval string which fills the right value in DB processing.

But it cannot be done with combination of that sqlBindMode, because that error ORA-01722 appears. It seems to perceive that string (S_AUDIT.nextval) as plain string not the value of sequence.

How can I handle that?

[Former Member · Accepted solution]

I have solution for this - but it`s more workaround that solution.

I have found http://www.oracle-base.com/articles/misc/autonumber-and-identity.php article how to make Oracle to fill that column from sequence by itself.

If somebody will set that trigger in DB, you can send SQL statement (Insert in my case) without defining oracle sequence in ID column and that will resolve the issue.

[Former Member]

"Hi Vladimir,

Are you using any SQL function in your Insert statement like TODATE?

What is the type of the large XML fied in ORACLE?

Have you tried with TOAD or similar to insert a register with more than 4000 character, only to assure that there is not trigger or similar that avoid it.

On the other hand if you can't find any solution with your actual statement you can try with SQL_DML statment.

Regards."

No - I`m not using any SQL function - only that S_AUDIT.nextval

Type of field is STRING (in XML) and CLOB (in DB)

I made that INSERT (for test) in java using PreparedStatement and it worked without any problem. Even with messages over 4000 chars. So I think, that there is no problem in data types configuration and compatibility.

What is SQL_DML. Can you please give a link or describe how can I use it for my case?

Thanks

[iaki_vila]

Hi Vladimir,

Are you using any SQL function in your Insert statement like TODATE?

What is the type of the large XML fied in ORACLE?

Have you tried with TOAD or similar to insert a register with more than 4000 character, only to assure that there is not trigger or similar that avoid it.

On the other hand if you can't find any solution with your actual statement you can try with SQL_DML statment.

Regards.

[Former Member]

I redone that INSERT using SQL_DML, but unfortunately it the same problem. JDBC adapter throws validation error on that ID field - java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

I think it should be problem in JDBC adapter, because when I put that string into ID field using PreparedStatement programatically, everything was ok.

Even if I turn of sqlBindMode parameter, it doesnt have problem with that string in ID field. But I have to trim my payload to max 4000 chars. Which is not convenient.

[iaki_vila]

Hi Vladimir,

A few cents, according to SAP notes you have to consider the next points:

- Database Auto-Commit-Enabled(No Transaction Handling must not be selected.

- You must access/modify the same columns in every statement.

- You can only use UPDATE, INSERT, DELETE and SELECT statements.

Regards.

[Former Member]

Hi Inaki,

Thanks for your notes.

I`m using it to INSERT data into DB (sorry for not mention it explicitly) so this should not be a problem.

I`m using all columns of table in every INSERT statement so this shouldnt be a problem too.

And autocommit is enabled by default in receiver JDBC adapter - so also not a problem.

But my original problem still persists. When I put sequence value into ID field - it says, that its invalid number.

If I switch off sqlBindMode, everything is ok - except, that I cannot insert xml longer than 4000 characters.