Problem with ORACLE database

[chitti_prakash]

Hi all,

I am facing some problem with ORACLE database while configuring one JDBC scenario in the receiver side. I have configured one JDBC scenario like FILE TO JDBC. As JDBC is at receiver side i have provided the input file with the following format ,

<root>

<StatementName1>

<dbTableName action="INSERT">

<table>employee</table>

<access>

<flag>NO</flag>

<ID>567</ID>

<NAME>134</NAME>

</access>

</dbTableName>

</StatementName1>

</root>

But in the ORACLE database all my columns are in the uppercase.

So when i have tried to send this input file to process through JDBC receiver side then i have faced the below error :

FATAL ERROR: Column 'flag' does not exist in table 'employee'

Then I have tried to check with sender side processing of JDBC adapter. By using the SQL statements as

SELECT * FROM EMPLOYEE WHERE flag='NO'

UPDATE EMPLOYEE SET Flag='YES' WHERE flag='NO'

Here it worked fine.

Now my question is,

If the same adapter is working when we are writing a query directly(sender side), then we must also allow case independence in the auto generated SQL part(receiver side).

The JDBC drivers for both SQL server and Oracle, supports the same java code, for accessing the DB, irrespective of the case of the column names.

Please clarify this doubt as soon as possible.

Thanks,

Soorya.

[dharamveer_gaur2 · Accepted solution]

Hi

With this similar problem

[chitti_prakash · Accepted solution]

Hi Dharmaveer,

When I go with the following input file,

<root>

<StatementName1>

<dbTableName action="INSERT">

<table>EMPLOYEE</table>

<access>

<FLAG>NO</FLAG>

<ID>666</ID>

<NAME>SSS</NAME>

</access>

</dbTableName>

</StatementName1>

</root>

I will get the following query with succesfull processing.

INSERT INTO EMPLOYEE (FLAG, ID, NAME) VALUES (NO, 666, SSS)

when i go with this input file,

<root>

<StatementName1>

<dbTableName action="INSERT">

<table>EMPLOYEE</table>

<access>

<flag>NO</flag>

<ID>777</ID>

<NAME>TTTT</NAME>

</access>

</dbTableName>

</StatementName1>

</root>

Then i am facing this problem,

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMPLOYEE' (structure 'StatementName1'): java.sql.SQLException: FATAL ERROR: Column 'flag' does not exist in table 'EMPLOYEE'

Here I cant get any SQL statement as it is not successfull.

I have gone through your link but it also says to modify the column in the input file which is not possible for me. Can you please help me in this regard?

Thanks,

Soorya

[Former Member · Accepted solution]

HI Chitti,

Try to change the 'flag' to 'FLAG' in the receiver JDBC structure and execute it. It will not create any problems in runtime.

Then check the generated statement. You don't need to change the column name in the file..

Regards

Goli SRidhar

[chitti_prakash · Accepted solution]

Hi Sridhar,

I have done everything which you have mentioned. But the thing here is I would like to know why the receiver JDBC adapter is behaving like this when it is working fine with Sender JDBC adapter.

What is the reason behind this limitation in the receiver side JDBC adapter to use the column names in

uppercase only.

Thanks,

SOorya

[chitti_prakash · Accepted solution]

Hi Sridhar and Dharmaveer,

I am waiting for you reply for my lst update.

can you please help me out in this with a specific reason , why it is not working in receiver side as it is wokring in sender side.

Thanks,

Soorya

[Former Member]

---> I know that ORACLE database is case sensitive.

-

---

>>>>> No, its not......

But why the JDBC receiver adapter i behaving like that.

-

---

>>>>>> Already explained.....

Regards,

[dharamveer_gaur2]

Hi

Oracle is not Case Sensitive .

As i told XI Structure is Case Sensitive, due to it's internal XML Node are Case Sensitive.

Try one thing test any mapping in your system using test tab, Now Change Node Case in XML Format.

Test Again, It will produce error.

Suppose your test XML in this format is working fine

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

<ns0:mt_sender xmlns:ns0="urn:filetofile">

<name>DH</name>

<add>MUM</add>

</ns0:mt_sender>

Now change this XML format while testing(Dont change Data type structure)

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

<ns0:mt_sender xmlns:ns0="urn:filetofile">

<Name>DH</Name>

<add>MUM</add>

</ns0:mt_sender>

[Former Member]

Hi Chitti,

Can you give me more information on the same problem so that it will help me to understand better ?

--> sure it's not about XI being case sensitive, simply XML being case sensitive any message in XI will get converted into XML . and accordingly internally parsers will work on the tag names..

but in oracle at sql prompt it hardly matters.....at oracle level flag is as good as FLAG as a filed in table.

you can check this thing...!!!

That's why it is always suggested to follow naming convention with block Letters .

Regards,

[chitti_prakash]

Hi,

When i run the query with lowercase column names in the oracle database then it accepting properly.

Only when it is trying to send thorugh the JDBC receiver adapter it is complaining like that.

I know that ORACLE database is case sensitive. But why the JDBC receiver adapter i behaving like that.

Thanks,

[dharamveer_gaur2]

Hi

No Documentation i found regarding this, But once i also faced similar problem

where JDBC Response structure is in format <SenderStrucName>_response was working and

<SenderStrucName>_Response was not.

Genrally avoid this we should use Uppercase field format in XI JDBC Receiver Datatype Structure

[chitti_prakash]

Hi Dharmaveer,

Can you give me more information on the same problem so that it will help me to understand better ?

Thanks

Soorya

[dharamveer_gaur2]

Hi

XI is Case Sensitive. At Sender Side you are using SQL Query that's why it is working.At Receiver Side using Structure XI data type.

[chitti_prakash]

Hello ,

So as you mentioned that, XI is case sensitive and hence it is working in sender side( where we give the SQL query directly). but in receiver side we are converting that input message into XI message(which is case sensitive). Hence is is not working in the receiver side.

Can you please explain this clearly and more briefly so that i can write it to my customer ?

How can you say that XI is case sensitive?

Is there any document to prove that XI is case sensitive?

Thanks,.

[Former Member]

Hi Chitti,

I want to say one thing, i.e the data in the database is case sensitive not the column names in the Database.

You can use the uppercase or lowercase column names in the query. It will work fine for both the things.

Try to execute the same query in the Database, that is try to insert the data into Database directy by writing the insert query.

You can get the generated qurey from the above Dharmaveer's reply.

execute the same in Database.

Regards

Goli Sridhar

[chitti_prakash]

Hi Sirdhar,

I have done both ways (in the database and also using JDBC adapter).

If i give the query like,

INSERT INTO employee (flag, ID, NAME) VALUES ('NO', '234', 'ghj') on the oracle database then it is working fine. But when i try to configure the same thing using the receiver side JDBC adapter it is giving the error as mentioned above.

Thanks,

Soorya

[dharamveer_gaur2]

Hi

Your database have flag field ?

Check your Generated SQL query format at receiver runtime.

At runtime you can find the genereated sql statements by doing configuraitons in Receiver JDBC adapter.

In the JDBC Receiver adapter you have the Advanced Properties .

Over there enter the following

left column logSQLStatement

right column true

To see the query created ..

Login to adapter monitoring ..select the relevant jdbc adapter.

Now when any message is processed by the jdbc adapter in adapter monitoring at that time you will see a message link. When you click on that link a new window will open. In that window if you click on page down you will get to see the sql statement generated by the jdbc adapter.

[chitti_prakash]

Hi Dharmaveer,

I have cheked in the audit log,

Error Unable to execute statement for table or stored procedure. due to java.sql.SQLException: FATAL ERROR: Column 'flag' does not exist in table 'EMPLOYEE'

2008-10-24 13:18:20 Error MP: exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EMPLOYEE': java.sql.SQLException: FATAL ERROR: Column 'flag' does not exist in table 'EMPLOYEE'

Hence not even went to that step where we gather the SQL statement out of the input XML file.

Thanks,

Soorya

[Former Member]

Hi Chitti,

Here it is clearly saying that there is no flag column in the Employee.

Also look into the table which is having the flag column in that table.

Go to the database and execute the insert statement by using the same values.

Regards

Goli sRidhar

[chitti_prakash]

Hi sridhar,

I have done the same. But it worked fine. One columns has been inserted when tried to go with the above SQL query.

Thanks

Soorya