cancel
Showing results for 
Search instead for 
Did you mean: 

Inserting Multiple Rows into Database Table using JDBC Adapter - Efficiency

Former Member
0 Kudos

I need to insert multiple rows into a database table using the JDBC adapter (receiver).

I understand the traditional way of repeating the statement multiple times, each having its <access> element. However, I am just wondering whether this might be performance-inefficient, as it might insert records one by one.

Is there a way to ensure that the records are inserted into the table as a block, rather than record-by-record?

Accepted Solutions (0)

Answers (2)

Answers (2)

justin_santhanam
Active Contributor
0 Kudos

Kanwaljit,

Is it ok , if you insert into the table like below structure,

Insert into tablename(EMP_NAME,EMP_ID) VALUES('J','1000'),('P',1001),('S','1002'),('G','1003')

Best regards,

raj.

bhavesh_kantilal
Active Contributor
0 Kudos

Kanwaljit,

Instead of having multiple STATEMENT tags, you can have a single STATEMENT tag with multiple ACCESS tags. This would ensure that all the insertions happen in a single transaction.

Regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh/Kanwaljit,

If we have multiple ACCESS tags then what happens is that the connection to the database is made only once. But the data is inserted row by row.

Why i am saying this?

If we add the following in JDBC Adapter..logSQLStatement = true. Then incase of multiple inserts we can see that there are multiple

<i>Insert into tablename(EMP_NAME,EMP_ID) VALUES('J','1000')

Insert into tablename(EMP_NAME,EMP_ID) VALUES('J','2000')</i>

Doesnt this mean that rows are inserted one by one?

Correct me if i am wrong.

This does not mean that the transaction is not guaranted. Either all the rows will be inserted or rolled back.

Regards,

Sumit

Former Member
0 Kudos

Hello Sumit,

Thanks a lot for the reply.

But I still have one doubt, how can I exactly see the SQL Query for The Adapter.

"logSQLStatement = true"

Could you kindly elaborate on that. Where do I need to add this and where do I see the log after that?

Thanks and Best Regards,

Kanwaljit

Former Member
0 Kudos

Hi Kanwaljit,

In the JDBC Receiver adapter you have the Advanced Properties . Cick on the check box and then you will get a table in the downside.

Over there enter the following

<b>left column</b> logSQLStatement

<b>right column</b> 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.

Regards,

SUmit

Former Member
0 Kudos

Hi Kanwaljit,

Did you find a solution to this problem? Even I am now facing a similar issue. I tried using both

- multiple <table_names> inside single <statement>

- multiple <key> inside a single <table_name>

The problem is that I can see mutiple <keys> generated as required (in the RWB>Messsage Moni> Message Content); but only the very first detail record is getting inserted into the db table....

Can you suggest something?

P.S: I tried enabling "batch mode"(advanced adapter properties) and adding entry logSQLStatement =true.

Thank you

Regards,

Smitha