Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

PERL : SQL array bind issue while grouping

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting PERL : SQL array bind issue while grouping
# 1  
Old 01-24-2011
PERL : SQL array bind issue while grouping
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 :

Code:
$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 :

Code:
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

Moderator's Comments:
Mod Comment Please use code tags for listings
Last edited by pludi; 01-24-2011 at 08:30 AM..
sinpeak
# 2  
Old 01-24-2011
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Grouping array elements - possible?

I have a script which takes backup of some configuration files on my server. It does that by using an array which contains the complete path to the files to backup. It copys the files to a pre defined dir. Each "program" has it's own folder, ex. apache.conf is being copied to /predefined... (7 Replies)
Discussion started by: dnn
7 Replies

2. Shell Programming and Scripting

awk and perl grouping.

Hello folks. After awk, i have decided to start to learn perl, and i need some help. I have following output : 1 a 1 b 2 k 2 f 3 s 3 p Now with awk i get desired output by issuing : awk ' { a = a FS $2 } END { for ( i in a) print i,a }' input 1 a b 2 k f 3 s p Can... (1 Reply)
Discussion started by: Peasant
1 Replies

3. Shell Programming and Scripting

Pass perl array to SQL oracle

Hello, Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg : #!/usr/bin/perl @v_array = (1,2,4,5,6,8); $db_userid = 'ni71/ni711'; $bufTPO = qx{ sqlplus -s << EOF $db_userid set verify off set feedback off set... (1 Reply)
Discussion started by: arunshankar.c
1 Replies

4. Programming

SQL Grouping SUM

Hello people, I'm scratching my head to find a solution to this query. I have a simple SQL table: data | tot ================== 111201/0000 | 3 111201/0001 | 5 111201/0002 | 7 111201/0003 | 2 ..... 111201/0059 | 1 111201/0100 | 5 111201/0101 | 8 111201/0102 | 6... (7 Replies)
Discussion started by: Lord Spectre
7 Replies

5. UNIX and Linux Applications

SQL database call into Multidimensional Array using Perl Script

#!/usr/local/bin/perl use DBI; use File::Copy; use Time::Local; use Data::Dumper; -Comments Describing what I'm doing-------------- -I'm pulling information from a database that has an ID and Name. They are separated by a space and I'm trying to load them into a multidimensional array so as... (3 Replies)
Discussion started by: eazyeddie22
3 Replies

6. Solaris

BIND DNS Server issue on Solaris 10

Hi all, I have some sort of problem with BIND DNS server my environment as follows. bash-3.00# cat /etc/release Solaris 10 6/06 s10s_u2wos_09a SPARC Copyright 2006 Sun Microsystems, Inc. All Rights Reserved. Use is subject to... (3 Replies)
Discussion started by: h@foorsa.biz
3 Replies

7. Shell Programming and Scripting

PERL : Bind 2D array to SQL

Hi, I am in the need of doing a bulk insert via : SQL - INSERT INTO <table> (SELECT..) OR PLSQL Block - FORALL i IN 1 .. count INSERT INTO <table>(arrayname(i)) I have a 2D array in my perl code which has the rows to be bulk inserted. Is there a way to bind the 2D array to the SQL... (4 Replies)
Discussion started by: sinpeak
4 Replies

8. Solaris

rpc bind issue

Hi all, I am using Solaris 10 OS on X86 platform. Long back i tried to configure DHCP server on my system but because of rpc-bind, total went in vein.Even now i am unable to access even USB also. When i tried to look for "volfs" service it was offline because one of its dependant... (0 Replies)
Discussion started by: vvpotugunta
0 Replies
Login or Register to Ask a Question