how to pass a multidimensional array of String values to the database?
hi everyone,
i am using servlets where in a scenarion arised where i should be sending two dimension array to the database using stored procedures.
i have series oft wo fields effective date and code in the frontend. it so becomes that the next effdate becomes the expiry date of the previous one . we wanted to send the whole values at one go to the data base .could anyone can tell me how can i send the data to the data base using sql procedures. we have used plsql table type in the procedure
I think you can do this using a callable statement and a dynamicaly generated plsql block
Lets assume that your string array is a table of 3 columns and n number of rows and your PLSQL table is a table of records which has 3 VARCHAR fields field1 field2 and field3 each correspondin to a column of the string array
String str[][] /// this is your array;
StringBuffer sb = enw StringBuffer();
sb.append("DECLARE\n");
sb.append("my_table_ MY_TABLE_TYPE;\n");
sb.append("BEGIN\n");
for (int i=0; i<str.length; i++){
sb.append("my_table_("+ i +").field1=?;\n");
sb.append("my_table_("+ i +").field2=?;\n");
sb.append("my_table_("+ i +").field3=?;\n");
}
sb.append("my_procedure(my_table_);\n");
sb.append("END;\n");
CallableStatement cs = connection.prepareCall(sb.toString());
for (int i=0; i><str.length; i++){
cs.setString(i*3+1, str[i][0]);
cs.setString(i*3+2, str[i][1]);
cs.setString(i*3+3 str[i][2]);
}
cs.execute();
there my be a easy way if there is a java datastructure which is directly mapped to the PLSQL table>
LRMKa at 2007-7-13 9:57:33 >
