cancel
Showing results for 
Search instead for 
Did you mean: 

issue with Insert using JDBC adapter

Former Member
0 Kudos

Hi PI Experts,

I have issue using INSERT statement with JDNC Adapter channel.

The Target system has a schema for any SQL Satement.

even when it is select, insert or update it requires to add scema before Table.

for example Select * from xyz.TABLE;

or insert into xyz.TABLE (A, B, C) values (1, 2, 3);

so in both above the cased i need to add xyz.

i can do it while declaring the table as a Data element

but the schema has . (dot) in it like xyz. which PI is throwing error when creating a field with dot in data type.

Can any one has any idea how we can overcome this or how to add a string with dot before the table name.

Please let me know if you are not clear with what i have mentioned.

Thanks,

Kiran.

Accepted Solutions (1)

Accepted Solutions (1)

justin_santhanam
Active Contributor
0 Kudos

Hi Kiran,

There are two ways of defining the table name. Lets consider the below structure:


<StatementName2>
<table>realDbTableName</table>
<dbTableName action=u201DINSERTu201D>
<access>
<col1>val1</col1>
<col2>val2</col2>
</access>
<access>
<col1>val11</col1>
</access>
</dbTableName>
</StatementName2>

When you try to create table name in the dbTableName it won't allow as per your requirement. So take a constant and in the constant have xyz.table. Now map that constant to Table tag.

In your xml , if you have the table tag, then JDBC adapter will give precedence to that tag and will think that's the table name. in the dbtablename tag, you can name anything you want.

In your xml, if you don't have the table name tag, then dbtablename tag will by default considered as table name.

Does it make sense?

raj.

stefan_grube
Active Contributor
0 Kudos

> <table>realDbTableName</table>

> <dbTableName action=u201DINSERTu201D>

table must be an element to node dbTableName in this example, like this:

<dbTableName action=u201DINSERTu201D>
<table>realDbTableName</table>

Answers (5)

Answers (5)

Former Member
0 Kudos

easiest way is to communicate with your DB administrator, just a small setting will save your all these efforts.

former_member241146
Active Participant
0 Kudos

Hi Kiran.

The "xyz." name is a alias for your table. It mean that your table object was created in the "xyz." account. You can add this object to DB User used in the Communication Channel Sender or you can create this table type GLOBAL.

Maybe this error in the PI can be because PI Database user doesn't have authorization to create a new object within "xyz." account

Try to talk with your database team.

I hope it helps you.

Regards.

Bruno

Former Member
0 Kudos

I did not understand this correctly.

can you explain me more detailed.

i am unable to create a Field in data type which has . in it.

so i cannot declare Element of node as xyz.TABLE.

Thanks,

Kiran.

stefan_grube
Active Contributor
0 Kudos

you do not need to create a node with a dot in your data type.

You can use any name for the node, the real table name is added to the element "table"

This is clearly described in online help, in JDBC adapter, receiver channel.

Former Member
0 Kudos

Hi Raj,

Thanks for the suggestion, but i have done it already, i have assigned a constant and assigned it in the mapping.

but run time it is taking actual field name declared.

Thanks,

Kiran.

Former Member
0 Kudos

Hi Kiran,

For if you want to Insert the data in target database using JDBC communication channel, you can follow the standard JDBC Statement structures(INSERT,SELECT,INSERT,UPDATE...Etc)

If Database is Sender side, No need to follow the below structure statements

Please find the below link:

http://help.sap.com/saphelp_nw04/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm

and there is lot of threads regarding this type errors.please check

Regards,

Sateesh