Pass multiple rows or array to DB2 Store procedure from java

Question

We need to update DB2 database with following type of data with store procedure from java application.

ManId ManaFirstName ManLastName CubicleId Unit EmpId EmpFirstName EmpLastName
2345  Steeven        Rodrigue   12345RT HR     2456     John        Graham
                                               45464    Peter       Black

Here, the columns related Emp (Emp Id , Emp First Name and Emp Last Name) is actually array, it can any number of employees from my front application for one manager. We need to pass these values in Store Procedure and process in SP. However, I am not able to find any array type datatype in db2.

I know I can take following two approaches :- 1. Delimited value :- Have varchar column and append all the values with help of delimiter and split in SP.

                     2456,John,Graham|45464,Peter,Black

2. Have separate SP and call in batch.

However, I am looking for approach where I can pass them in single go and some more structured datatype. Does DB2 have datatype like array to support this or any way to create custom datatype.

I am using Spring JDBCTemplate at front end to call SP (I am flexible to change that) and DB2 as database.\

P.S. :- Open queries is not option for me , need to call SP only. This SP is going to be called from java directly, so if have to use custom datatype, only scope is to define it in store procedure which is being called

mustaccio · Accepted Answer · 2014-12-15 13:08:09Z

1

Since the data types of Emp Id , Emp First Name and Emp Last Name are probably different, you should use a DB2 ROW type to contain them, not ARRAY. In Java that would be represented by java.sql.Struct. You can also pass an ARRAY of ROW types to the stored procedure. Check the manual for details and examples.

answered Dec 15, 2014 at 13:08

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panther Over a year ago

Thanks for input , It seems ok to me. i will try this tommarrow and will accept answer.

Collectives™ on Stack Overflow

Pass multiple rows or array to DB2 Store procedure from java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related