How to send 1000 records per each time through JDBC adapter

[Former Member]

Hi all,

In my JDBC to File scenario, SQL Server database having 10,000 records. I want to split these records into 1000 records and process 1000 records each time. How we can do in JDBC adapter. Is there any options in JDBC adapter. Please give me reply very thanks to all of u.

[Former Member]

Hi Sree !

Just execute a SELECT query that recovers a TOP 1000 (order by some criteria) from those records that haven't been read before (with a flag or mark field). And execute an UPDATE query (also from jdbc sender adapter) that flags the read records as read (also same top 1000 with same order criteria, to ensure you are flagging the same records you read before).

Regards,

Matias.

ps: please award points if helpful.

[Former Member]

You can write this query in JDBC adapter

SELECT column FROM table

WHERE ROWNUM <= 1000 and readflag = 'N';

and in Update SQL statement you can write

Update table_name set readflag = 'Y' where readflag = 'N'

so that it will not read records which are already being read.

for more on this refer

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

[Former Member]

Update SQL statement should be

Update student set readflag = 'Y' where rownum <=2 and readflag = 'N'

and your job would be done

Rest all is same as my previous post

[Former Member]

Hi all,

Thanks for your repsone. I am very happy.

If it sender Jdbc we can write the query. If it is Receiver Jdbc adapter and I want to send the record as 1000 at a time.

You may give this advise that is you can use RecordsetPerMessage option at sender side. But here I want to read all the records and send 1000 records only.

please give me valuable answer.

thanks in advance...

[Former Member]

Hi Sree !

As far as I know, the RecordsetPerMessage option if not available in jdbc sender adapter.

Did you test to write the suggested select/update query for the jdbc sender adapter? it should select 1000 records or less that were not previously flagged as read.

Regards,

Matias.

[Former Member]

Hi sree,

u can use below Query to access fixed range:

Select * from <Table Name> where ID BETWEEN 1 To 1000

Here ID is a field in table which is stored in order.

But i dont know how can we make it dynamic in JDBC adapter to process 1000 records each time.

Thanks,

Maheshwari

[Former Member]

You can manage it in SQL server itself by using sql queries with proper where clause

if you want exact solution,pl. ur your query in detail,with fields,table,condition

thx,