Re^2: Arrays and Stored Procedures

Hi, could you explain a little further about temporary table approach. I do not know if this will be possible as i do not have access to the schema containing the data, only the procedures (security and confidentiality etc). If you could give a little detail, i will see what the DBA can implement. Thanks.

Comment on Re^2: Arrays and Stored Procedures

Replies are listed 'Best First'.
Re^3: Arrays and Stored Procedures by mje (Curate) on Nov 21, 2008 at 08:43 UTC
I'm assuming that since you have 4 parameters all arrays that each index represents a group of values i.e., array_of_ids[0] goes with array_of_param_names[0] etc. create a global temporary table: `CREATE GLOBAL TEMPORARY TABLE mygtbl ( id type, param_name type, param_type type, value type ) on commit preserve rows;` [download] Your perl code changes to "insert into mygtbl values(?,?,?,?)", and you can use execute_array to insert the values. You then either change the procedure to read the values from mygtbl or you write a wrapper procedure which reads the values from mygtbl into arrays and pass them to your original procedure.	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: Arrays and Stored Procedures
by mje (Curate) on Nov 21, 2008 at 08:43 UTC

I'm assuming that since you have 4 parameters all arrays that each index represents a group of values i.e., array_of_ids[0] goes with array_of_param_names[0] etc.

create a global temporary table:

CREATE GLOBAL TEMPORARY TABLE mygtbl (
  id type,
  param_name type,
  param_type type,
  value type
) on commit preserve rows;
[download]

Your perl code changes to "insert into mygtbl values(?,?,?,?)", and you can use execute_array to insert the values. You then either change the procedure to read the values from mygtbl or you write a wrapper procedure which reads the values from mygtbl into arrays and pass them to your original procedure.

[reply]
[d/l]