JDBC Receiver Prepared Statement (sqlBindMode): how to convert date ?

[Former Member]

Hello!

I try to use PreparedStatement in JDBC receiver adapter (sqlBindMode) for performance improvement (see SAP note 1404778) in SAP PI 7.11 EHP 6.

I`v faced with problem: I can`t use Oracle function TO_DATE to convert date format. The JDBC driver returned "ORA-01858: a non-numeric character was found where a numeric was expected" for fields type DATE.

I guess the cause is that values of SQL statement are filled by setString function while Oracle are expecting DATE type. And there is no way to typify elements in JDBC INSERT statement.

Does anyone know how to use PreparedStatement mode with DATE type fields?

Regards, Sergey.

[anupam_chakraborty · Accepted solution]

Hi Sergey,

Can you please check 1983983 - Using Date functions with SQL Bind Mode

Thanks

Anupam

[Former Member · Accepted solution]

Hi.

Thanks. This note had been released after my incident to SAP.

Best regards,

Sergey.

[ambrish_mishra]

Hi Sergey,

What is the value you are sending from PI and what value is DB expecting ?

Ambrish

[Former Member]

The PI is sending value like 20130307113000. DB is expecting this value as DATE type. I try to convert value with TO_DATE function.

The example of xml:

<?xml version="1.0" encoding="utf-8" ?> 
<ns:mt_Test xmlns:ns="Test">
    <Statement>
        <StatementName action="INSERT">
            <table>TABLE_TEST</table> 
            <access>
                <DATE_TEST hasQuot="No">to_date(&apos;20130307113000&apos;, &apos;YYYYMMDDHH24MISS&apos;)</DATE_TEST> 
            </access>
        </StatementName>
    </Statement>
</ns:mt_Test>

The JDBC receiver adapter with sqlBindMode gerenates SQL statement:

INSERT INTO TABLE_TEST (DATE_TEST) VALUES (?)

and after that returns error ORA-01858: a non-numeric character was found where a numeric was expected

Without TO_DATE convertion DB returns error ORA-01861: literal does not match format string.

Attribute hasQuot=Yes|No doesn`t affect at all.

[ambrish_mishra]

Hi Sergey,

have you taken a look at SAP note 1039779. Please take a look.

Ambrish

[Former Member]

Hi, Amdrish.

Thanks, but this is not my case.

Sergey.

[former_member194612]

Hi Sergey,

Can you check the below thread

http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCPreparedStatement.html

Thanks,

Satish

[Former Member]

Hi, Satish.

I`ve already come up with the problem is in incorrect typifying. It seems like SAP forgot about it. I`m trying to use standart parameter of JDBC receiver adapter called "sqlBindMode" and I don`t want to code custom adapter.

So I just want to get to know how to use standart realization of PreparedStatement with fields type DATE (ok, no convertion) without custom development?