Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^4: SQL query using dynamic array

by cocl04 (Sexton)
on Nov 18, 2008 at 20:33 UTC ( [id://724377]=note: print w/replies, xml ) Need Help??


in reply to Re^3: SQL query using dynamic array
in thread SQL query using dynamic array

What is the following code supposed to do?

foreach $i(0..$#{$rows}) { foreach $j (0..$#{$rows->[$i]} ) { $rows->[$i][$j]; } }

Answer: Each time the code runs I select distinct(id) from table. When I fetch the id's and put them into an array, they are all placed into array index '0'. I want to pass individual id's to the sql statement so I add indexes by the above logic where I iterate through by incrementing $i++.

Have you tried replacing the query results with hardcoded data? Does the program work then as you want it to?

while ($i<=2) { my $placeholder = ":p_$j"; print "$placeholder\n"; my $rd = "$rows->[$i][0]"; print "$rd\n";

Answer: Yes. I can set the rd to equal a specific value and it returns the value result. If the array = ('A01','B88','C88',....'nth'), I want to only select * from table where id = 'A01'; pass 'A01' to email, then 'B88'....then 'C88'....'nth'.

The while loop iterates through the array and changes the index value. The value changes as $i increments. The problem is the sql code only returns data for the first array index. All of the print statements change. All of the emails and spreadsheet data is changed. but only the first array index is populated in the emailed spreadsheets.

Replies are listed 'Best First'.
Re^5: SQL query using dynamic array
by Corion (Patriarch) on Nov 18, 2008 at 20:42 UTC
    What is the following code supposed to do?
    foreach $i(0..$#{$rows}) { foreach $j (0..$#{$rows->[$i]} ) { $rows->[$i][$j]; } }
    Answer: Each time the code runs I select distinct(id) from table. When I fetch the id's and put them into an array, they are all placed into array index '0'. I want to pass individual id's to the sql statement so I add indexes by the above logic where I iterate through by incrementing $i++.

    I don't see how your answer relates to the code snippet I posted. Please show a data structure for $rows and what changes you expect this code to make to that data structure.

    I did not mean setting $rd to a single specific value, but setting $rows to a premade dataset instead of something returned from an SQL query.

    Why are you sure that the SQL code "only returns data for the first array index."? What steps did you take to find out whether you get all data or just one row of data? Have you used Data::Dumper?

    You show a long block of code, but I have the feeling that you don't see the structure of your code, as I can't make out in your description what parts of the code you are talking about.

      my $i = 0; # Connect to Oracle database. #When you fetch the data for the below sql statment it is loaded into +array index '0'. There is no way to index it. array = ['0','1','2',. +..'nth']. # Set up Query for ID my $stmt_1 = "select alignment from rd_list"; # Prepare Query my $query = $connection->prepare($stmt_1); # Execute Query $query->execute() or die $connection->errstr; # Create Array_ref for array. my $rows = $query->fetchall_arrayref; # Load data in array. foreach $i(0..$#{$rows}) { foreach $j (0..$#{$rows->[$i]} ) { $rows->[$i][$j]; } } my $num = $query->rows; print "\nThis process returns $num of ID's.\n\n"; while ($i<=2) { my $rd = "$rows->[$i][0]"; print "$rd\n"; $i++; }

      In terms of a premade dataset. I know the exact count of $row. I just print the count. I have not created array via hard coding a premade list of $row. I can try that. I know that $row has the correct values because I checked them against the list in my database.

      Each time I run the process, it sends an email to my inbox with three reports. I have it set to process while $i < = 2. So, the first email with id = '01' is the only worksheet with data from the sql statement. The other two have all the headers, etc.. but no data from the sql. It is as if the sql prepare holds on to the first value. I know the value changes, because it prints each time and email it each time.

      I also know that the first report from array index '0' is correct because I validate the selected data from my data in Oracle.

        Your comment does not match your code:

        # Load data in array. foreach $i(0..$#{$rows}) { foreach $j (0..$#{$rows->[$i]} ) { $rows->[$i][$j]; } }

        The code does nothing. It also doesn't make sense. You still haven't answered what you think this code should do.

        Please read perldsc, perlreftut and use Data::Dumper to print out your data structure, so you can see what your data structure is and how to use it.

        You keep talking about "email", but "email" seems to be largely unrelated to the problem you have here. If you think "It is as if the sql prepare holds on to the first value.", what steps did you try to validate or reject that idea? Dumping out your data structure is very helpful in these situations. If you "know the value changes, because it prints each time and email it each time", then the place where you are printing data must be different from the place where you try to use the data in Excel. Investigate these situations and find where your data deviates from your assumptions.

        I'm sorry that I can't be of much more assistance - debugging a program is a process that you need to largely do yourself.

        A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://724377]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2024-04-26 07:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found