JDBC sender adapter ==> advanced questions

[Former Member]

Hi Expert,

basically I want to query a database VIEW to pull data, can you please answer my questions:

1) Can I specify variable in the select statement like select * from myView where createTime<today and flag="true"? today is a variable for today's date. Or some other variable we can define in PI?

2) Since generally view is not updatable, so what I should enter in the update statement? Let's say the view is based on three tables, one table named TableA has the field flag. So I guess I can update the flag directly in the TableA. However how can I make sure the the records created between the select and the update are not marked as false? I know if select and update is made to the same table, PI would make sure the update will be done on the sames records resulted from the select. However in my case, select is done on view while update is done on table. How can I be sure the flag is changes only for he processed records?

3) In my scenario, I need to initially load 100mb data from JDBC adapter, would it be problem with this hug amount of data in the JDCB CC?

Thanks

[Former Member · Accepted solution]

Hi Jayson,

1) Can I specify variable in the select statement like select * from myView where createTime<today and flag="true"? today is a variable for today's date. Or some other variable we can define in PI?

A) Yes, it works but it should be createTime<TODAY().

2) Since generally view is not updatable, so what I should enter in the update statement? Let's say the view is based on three tables, one table named TableA has the field flag. So I guess I can update the flag directly in the TableA. However how can I make sure the the records created between the select and the update are not marked as false? I know if select and update is made to the same table, PI would make sure the update will be done on the sames records resulted from the select. However in my case, select is done on view while update is done on table. How can I be sure the flag is changes only for he processed records?

A) UPDATE TableA set flag="flase" where createTime<TODAY().

This shd work.

3) In my scenario, I need to initially load 100mb data from JDBC adapter, would it be problem with this hug amount of data in the JDCB CC?

NO, 100mb is not a big deal for PI.

Regards,

Karna...

[Former Member · Accepted solution]

Hi Karna,

thanks for your reply.

for question 1, I guess you are saying that we can use database variable like today(), but is it possible to use PI variable? (Somehow define a variable in PI and set the value in mapping, for instance, then use it in the select statement)

for question 2, I was asking it for general case where createTime clause is not applicable.

for question 3, depending on hardware. At least I had problem with 10MB message with file adapter, but with file adapter we can splitt the huge message into many smaller messages, thus no issue there. I really hope JDBC adapter can handle huge message smoothly. Have you had this kind of experience?

Thanks

[Former Member · Accepted solution]

HI Jayson.

for question 1

A) i think it is not possible to use a PI variable. if you maintain the Variable in mapping, how will it be triggered, because mapping will be triggered after you get the data from database.

for question 2,

A) if you are using two different tables then i am not sure how it is updated, but if the case is like using View for select and Update on table then use the same condition then it shd work fine.

for question 3,

Depends on your server capability. if you want to make small messages then you can use select statement with number of records. like

SELECT TOP 10* FROM tb1.

Our JDBC CC picked 10 thousand records at a time and processed all of them in 5 min which is more than 100 mb.

Regards,

karna...

Edited by: Chirumamilla Sukarna on Mar 2, 2009 10:10 PM

[Former Member]

Sorry wrong post