FILE to JDBC

[naveen_chichili]

Hi All,

I have a file to JDBC scenario where i have used JDBC lookup to get the last count from database and i am incrementing the count when ever i run the interface.

for particular Field ex:XYZ i have made as a * primary key* in my table DB table(to avoid duplication).

when i run single file i am able to fetch the count from DB and pass the incremented to respective table in DB

Now the problem is:

if there are two files in source folder two files got picked up and trying to access DB count (LOOKUP) simultaneously

When i place multiple files in the source location multiple files are picking at a time and trying to fetch the count simultaneously from DB and failing in Receiver adapter i am unable to pass UNIQUE value to the field for which i have set as a PRIMARY KEY in such cases.

Would appreciate if any one suggest the solution

Thanks in advance,

Naveen

[Former Member · Accepted solution]

hi

you should use eoio mode. eoio is the messages going one by one.

so you don't care about the count of files.

Naoki

[Former Member · Accepted solution]

hi

or if you want more ristrictly to manage the ID.

you should use module proccessor.

you can create some codes to manage the ID.

Naoki

[naveen_chichili · Accepted solution]

Hi Naoki,

I tried with EOIO but it works sporadically actually it shoud work but its very strange that some times it works but not some times...

Suggest..

Regards,

naveen

[Former Member · Accepted solution]

hi

maybe the some message is holding in the que.

you should delete error message from que.

please check tr:smq1, smq2 or message monitor in rwb.

and Ill find some error message is there, you must delete them.

1. I always use EOIO for 10 or more projects, they are moving correctly.

Naoki

[anupam_ghosh2 · Accepted solution]

Hi Naveen,

As far as I understand your problem is that you have a primary field "xyz" which has to be incremented with each new row to be stored in the database table. You are reading this value first from database incrementing the value and again putting it back in database. The problem you are facing is due to the fact that multiple programs are reading the same value of "xyz" and after incrementing by 1 trying to put it back in table. Since all these primary key values are now equal you are receiving errors. This is as you know termed as problem of "Isolation " in database terms. I am quoting definition of isolation from Wikipedia

"Isolation refers to the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions (multiple transactions occurring at the same time). Each transaction must remain unaware of other concurrently executing transactions, except that one transaction may be forced to wait for the completion of another transaction that has modified data that the waiting transaction requires. If the isolation system does not exist, then the data could be put into an inconsistent state. This could happen, if one transaction is in the process of modifying data but has not yet completed, and then a second transaction reads and modifies that uncommitted data from the first transaction. If the first transaction fails and the second one succeeds, that violation of transactional isolation will cause data inconsistency. Due to performance and deadlocking concerns with multiple competing transactions, some modern databases allow dirty reads which is a way to bypass some of the restrictions of the isolation system. A dirty read means that a transaction is allowed to read, but not modify, the uncommitted data from another transaction. Another way to provide isolation for read transactions is via MVCC which gets around the blocking lock issues of reads blocking writes. The read is done on a prior version of data and not on the data that is being locked for modification thus providing the necessary isolation between transactions"

Within database there is an option to use sequence. Sequence can be created in oracle in this manner

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

for example

CREATE SEQUENCE supplier_seq

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

This sequence number may be inserted into table suppliers using the following command

INSERT INTO suppliers

(supplier_id, supplier_name)

VALUES

(supplier_seq.nextval, 'Kraft Foods');

Here you do not have to increment the primary key field and never you get same primary key value. Thus your problem will get resolved. If you are using DB other than oracle please search for appropriate command for generating a sequence.

Hope this helps.

regards

Anupam

[naveen_chichili · Accepted solution]

Hi Anupam,

Thanks for your reply,

The problem here is I am having Header, Detail and Item rows in my source text file

Ex:

Hu2026.

Du2026

Du2026

Iu2026

Iu2026

Iu2026

Hu2026.

Du2026

Du2026

Du2026.

Iu2026

Iu2026

Iu2026

u2026..

I am storing in three different tables in the database. If the XYZ:

XYZ field in first header is stored as u201D1u201D

XYZ field in first detail should be storedu201C1u201D and ABC in first detail should be storedu201C1u201D<as it is first detail in first header>

XYZ field in second detail should be storedu201C1u201D and ABC in second detail should be storedu201C2u201D<as it is second detail in first header>

XYZ field in first Item should be storedu201C1u201D and ABC in first item should be storedu201C3u201D<as it is first item in first header>

XYZ field in second Item should be storedu201C1u201D and ABC in second item should be storedu201C4u201D<as it is second item in first header>

XYZ field in third should be storedu201C1u201D and ABC in third item should be storedu201C5u201D<as it is third item in first header>

u2026

Record 2:

XYZ field in second header is stored as u201D2u201D

XYZ field in first detail should be storedu201C2u201D and ABC in first detail should be storedu201C1u201D<as it is first detail in second header>

XYZ field in second detail should be storedu201C2u201D and ABC in second detail should be storedu201C2u201D<as it is second detail in second header>

XYZ field in third detail should be storedu201C2u201D and ABC in second detail should be storedu201C3u201D<as it is third detail in second header>

XYZ field in first Item should be storedu201C2u201D and ABC in first item should be storedu201C4u201D<as it is first item in second header>

XYZ field in second Item should be storedu201C2u201D and ABC in second item should be storedu201C5u201D<as it is second item in second header>

XYZ field in third should be storedu201C2u201D and ABC in third item should be storedu201C6u201D<as it is third item in second header>

u2026u2026u2026u2026.so onu2026u2026..

In this case sequence which you suggested will not worku2026I tried generating sequence for the first <header level> able to generate the logic at sub levels. That is the reason I have used lookup to fetch the count in header level and used a piece of code to generate count at child levelsu2026.

how can I rectify my problem if I place multiple files in my source folder

Please suggest..

Regards,

Naveen.

[anupam_ghosh2 · Accepted solution]

Hi Naveen,

The solution to this problem lies in database, I do not think you can solve it by reading data,incrementing the values and put it back again, since you are not aware how many processes is reading the values at same time. The number of processes depends on number of files PI server is reading, and also within same XML message there are multiple headers.

Could you please help me out with following information

1. What is the database you are using?Is it oracle?

2. From your description I make out that, if we can solve this for one table it can be solved for other tables also since you are replicating the information in other tables also. Is this assumption correct?

3. For "xyz" header field only one sequence will work fine. We need to find ways for putting the values of sub-level values in table. Is this assumption correct?

4. The values of each sublevel are increasing from 1,2,3.... every time. Thus it requires another sequence. Please confirm.

5. If possible could you please provide structure of the tables with at least one row for one xml message so that I can analyze the type of sql statement/triggers required.

Hi Naveen,

Did you finally resolve the issue. If yes, could you please kindly share the solution with other forum members.

We would definately like to know how to resolve such errors.

regards

Anupam

Edited by: anupamsap on Jun 29, 2011 12:37 PM

[naveen_chichili · Accepted solution]

Hi Anupam,

I am trying with different options would surely let you know if i find the solution..

Regards,

Naveen.

[Former Member]

Hai!

U hAve mentioned that

>>>>>>f there are two files in source folder two files got picked up and trying to access DB count (LOOKUP) simultaneously

To solve thsi In your Sender File Adapter Configuration In the SOURCE Tab u can see a Field Connection mode u give input as

PER FILE TRANSFER. So only one file will transfer at a time.

Try this.Hope it will help u.

Regard's

Preethi.

[naveen_chichili]

Dear All,

Finally i solved my self by using JDBC lookup (fetching the sequence number).

Regards,

Naveen.

[rajasekhar_reddy14]

Hi,

i feel it is bad design to perform JDBC look up to retrive value from Table to increase the count.

what you can do here is Enter time stamp value in Primary Key,time stamp always different , so you no need to perfrom once more time to get count value.

if i understood your req wrongly,ignore my suggestion.

Regards,

Raj

[naveen_chichili]

Hi Raj,

Thanks for your quick reply,

Actually my requirement was to set Invoice ID as a primary key... so i feel including time stamp will not be possible.

Please suggest.

Regards,

Naveen.