JDBC Receiver Adapter -- Synch Select. ERROR

[Former Member]

Hello,

We have implemented the scenario described by Bhavesh Kantilal in blog 3928:

JDBC Receiver Adapter -- Synchronous Select – Step by Step

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

but modified to our requirements.

An Idoc is coming in, mapped to a sync receiver jdbc as described to collect data from a jdbc table.

Result when connecting the oracle database with the jdbc receiver adapter:

Error processing request in sax parser: Error when executing statement for table/stored proc. 'T_ST_MAINDRIVER' (structure 'STATEMENT'): java.sql.SQLException: ORA-00942: table or view does not exist

Oh, like to complete the information by the payload from the jdbc rec. connect:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_JDBC_LOOPUP_ID xmlns:ns0="http://www.xxx.org/excel">

- <STATEMENT>

- <TABLENAME ACTION="SELECT">

<TABLE>T_ST_MAINDRIVER</TABLE>

<ACCESS />

- <KEY>

<MAINDRIVER compareOperation="=">Optomechanical</MAINDRIVER>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_LOOPUP_ID>

The table exists on the database. So, what is our mistake? Do you have any ideas?

Best regards

Dirk

Message was edited by:

Dirk Meinhard

Message was edited by:

Dirk Meinhard

[Former Member · Accepted solution]

Hi Dirk,

Check the owner of the table and try giving the name of the table as owner.tablename

Eg: if the owner is dbo and the table is MyTable, then give the tablename as <b>dbo.MyTable.</b>

Hope this helps.

Anil

[Former Member · Accepted solution]

Thank you for your answers, Anil.

Today I have no access to our XI. I will work on your hints tomorrow and will come back (rewarding helpful answers or with more questions) !

Regards

Dirk

[Former Member · Accepted solution]

Hi Anil,

I am back on my JDBC problem and I am one step further!

Thank you for this hint. Looks like this is the solution.

So I like to add my next question !

My new error is resulting from my query command.

I have set "=" to find a specific entry as compare operation.

Error when executing statement for table/stored proc. 'IRIS.T_ST_MAINDRIVER' (structure 'STATEMENT'): java.sql.SQLException: FATAL ERROR document format in structure 'TABLENAME': unexpected value '=' for attribute 'compare' found

Looks like this is not ok .

This is my xml of this query:

<ns0:MT_JDBC_LOOPUP_ID xmlns:ns0="http://www.xxx.org/excel">

<STATEMENT>

<TABLENAME ACTION="SELECT">

<TABLE>IRIS.T_ST_MAINDRIVER</TABLE>

<ACCESS/>

<KEY>

<MAINDRIVER compareOperation="=">Optomechanical</MAINDRIVER>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_LOOPUP_ID>

Can you, or anybody else, give the helping idea?

regards

Dirk

[Former Member · Accepted solution]

Dirk,

Just dont give any value for compareOperation attribute. The default value is equals. Also try "EQ", this is the value for =.

Your XML should look some thing like this

<KEY>

<MAINDRIVER compareOperation=<b>EQ</b>>Optomechanical</MAINDRIVER>

</KEY>

Regards,

Jai Shankar

Message was edited by:

Jai Shankar

[Former Member · Accepted solution]

Thank you guys for your help!

It works!

That´s great!

Regards

Dirk

[Former Member · Accepted solution]

Dirk,

Values for compareOperation

Attribute Value and Check

EQ Equals (default value)

NEQ Does not equal

LT Less than

LTEQ Less than or equal to

GT Greater than

GTEQ Greater than or equal to

LIKE

Like (strings). In the corresponding value, the SQL placeholders “%” or “_” can then also be used.

Regards,

Jai Shankar

[Former Member · Accepted solution]

Hi again,

questions, questions....

This is my select on the JDBC rec.connection:

I think this is a select with an AND (two criteria).

How does it look for an OR ?

At the moment we have another error:

Error when executing statement for table/stored proc. 'IRIS.T_ST_MAINDRIVER' (structure 'STATEMENT'): java.sql.SQLException: ORA-00936: missing expression

Hello experts,

what is this? I don´t think that a select calls a stored procedure which is the only result for threads when searching in this forum!

regards

Dirk

[Former Member]

Hi Dirk,

Trz giving "T_ST_MAINDRIVER" at the place of TABLENAME in the statement <<b>TABLENAME</b> ACTION="SELECT">.

Anil

[Former Member]

Hi dirk,

make sure that the table that you use in your SQL statement really exists.

It is also case sensitive!

Regards Mario