PERL : SQL array bind issue while grouping

Shell Programming and Scripting
1

Hi,

I have an array of strings. Each string has 4 comma separated values. I am binding this array to a SQL where I am required to do an INSERT after grouping.

The binding is done as :

$insertADWSth->bind_param_array(1,A_CONSTANT_STRING);
$insertADWSth->bind_param_array(2,B_CONSTANT_STRING); 
$insertADWSth->bind_param_array(3,\@lineRecArray);
 
$insertADWSth->execute_array({ArrayTupleStatus=>\my @tuple_status})

..and the SQL is :

INSERT INTO ABCD
(A,B,C,D,E,F,G)
(SELECT 
LINE_ARR.A, 
LINE_ARR.B, 
REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,2),
XYZ.D ,
XYZ.E , 
REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,3),
SUM(TO_NUMBER(REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,4))) 
FROM 
(SELECT ? A, ? B, ? LINE_REC FROM DUAL) LINE_ARR,
XYZ
WHERE XYZ.X = REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,1)
GROUP BY 
LINE_ARR.A, 
LINE_ARR.B, 
REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,2),
XYZ.D ,
XYZ.E , 
REGEXP_SUBSTR( LINE_ARR.LINE_REC,'[^,]+',1,3))

This gets executed successfully. But the issue is all records are inserted without being grouped. If I replace the arrays with a table having the same data then the grouping goes successful.

Does this mean when an array is bound to the SQL, the SQL is executed once for each element of the array ? and thats why it won't group ?

If yes, then is there a way to achieve such a thing ? where we have the data in an array and we need to group and sum it ?

Thanks
Dhritman

2

I don't think this is a perl question, really, but a database one.

The Database specific driver (DBD module) will use the database-specific bind operator, if it is supported. I'm quite sure it is in this case.

Have you tested this SQL code by using interpolation instead of binding? ie, $A where you have your first ? and $B for your second, etc.