Re^2: SQL queries using dynamic array

[Anonymous Monk]

Hi There,
I am working on a query that will dynamiclly use an array.  I want to 
+put the process into a while loop and pass one value at a time to the
+ where clause.  I have tried this a couple of ways with placeholders.
+  For some reason the process only returns the first index of the arr
+ay.  As the while loop iterates through the index, I print out the cu
+rrent array index and it works.  I even pass the array value to the e
+mail / attachment and it works.  It seems like the prepare statement,
+ value, and placeholder get saved once.  When the test reports are se
+nt out...only the first report as data from the first array index.  I
+s there a way to change this....Can I use the below process and put i
+t into a while loop?  Does Perl allow this?  Thanks in advance....  I
+ am at home...and I forgot my login...my user name is cocl04...or ema
+il me at [e-mail] or [e-mail]...Please help.
+..I have been working on this for day...and I came across this postin
+g tonight...Thanks...

i.e.....

my $i = 0;

while (loop...)}
# assume that $dbh is ready...

my $value = $places[$i];

my $query = <<ENDSQL;
SELECT c_mail, c_first, c_last
FROM tblClient, tblGroup, tblRegion
WHERE tblGroup.g_name = 'motel'
AND tblRegion.r_name =
ENDSQL

my $sth = $dbh->prepare( $query . ' (' . join(',',map{'?'} $value) . 
+')' );

$sth->execute( $value );
$sth->finish;
$dbh->disconnect;
$i++;
}

[graff]

Various bits of advice in no particular order:
• If you forgot your login password, there's a link on the Monastery home page (The Monastery Gates) for getting a password reminder via email -- it's easy. Use that.

• Don't put <code> tags around your entire post -- only use them around actual code and data. Everything else uses normal HTML tags (<P>, <UL>, etc). It really helps to make your posts as legible as possible; use a few iterations of "preview" if you have to, because it's worthwhile.

• You said:

  I want to put the process into a while loop and pass one value at a time to the where clause.

  Why do you want to use a while loop? Why not foreach or map? If you have the list of things already in an array, it makes more sense to use foreach or map.

• You said:

  As the while loop iterates through the index, I print out the current array index and it works.

  If that's true, then you did not post the code that works. The code you posted has syntax errors and does not include any print statements. Post code that you have actually tried (and that we can try also).

• In the code you posted, you are re-creating the entire SQL statement, and executing the statement, on every iteration of the loop. This contradicts what you seem to want to do, and totally defeats the purpose of the code you copied from my earlier post.

Try to express yourself more clearly. Try to think more clearly. What information does your script start with? What information do you want it to produce as output? How do you come up with that extra information? It looks to me like you are having equal amounts of trouble with Perl syntax and with your overall conception of the task.

UPDATE: I just noticed that you have posted the exact same body of messy nonsense as replies to several other nodes in this thread. DON'T DO THAT. Apart from the fact that those others will be even more puzzled than I was by your reply (because you were copying from my posted code, not from theirs), it's very likely they would have seen your single reply to me, because many monks use the Monastery services "Newest Nodes" and/or "Recent Threads" to keep up with ongoing dialogs.

[Anonymous Monk]

I appreciate your response.  I have my password set to my email addres
+s at work.  I also left my laptop at work.  So therefore, I can't get
+ to it.  Secondly, I already have a process that works great and the 
+syntax is correct.  I will post it on Monday.  I apologize.  Here is 
+what I am doing...I have a process that pulls data from oracle, forma
+ts an excel worksheet, and auto emails reports to the field by id gro
+up (District Manager, Regional Manager, VP, etc…).  Currently, all of
+ the email addresses are hard coded and they are sent by group.  The 
+business owner wants the reports to be tailored to individuals and se
+nt to that specific individual.  
So, I have a test process that queries the database and loads a list o
+f distinct id's into an array.  The email addresses are id@...com.  S
+o that part is easy.  By using a loop, I can successfully pass the id
+ to the Perl code and update the correct email heading and changes th
+e title of the file to the correct id and emails it to the correct ad
+dress.  I want to have the select * from table where id = id.  ...Whe
+re the id is specific to the array index.  I don’t want to use an IN 
+‘id’ for all id’s that match.  Currently, the test sql process select
+s the first array index for the id, but no data returns for the next 
+ids.  So, only id 1 (array index '0') has data.  The rest of the emai
+ls return no data.  I just need help dynamically selecting info for a
+ specific id.  The current process works, but I need it to be dynamic
+.  I hope this helps you understand.  I will send the code.  Will you
+ help me?  I have worked on this all week.  I just happen to do a sea
+rch at home and found this posting.  I know the prepare statement sho
+uld probably should not be done in a while loop or a loop for that ma
+tter.  But I am not sure how to approach the loop.  I basically took 
+the current process that works and tried to wrap it into a loop and p
+ass the new id from the distinct list to the sql statement and email.
+  The email process works, but the prepare sql statement only returns
+ the first index.  I thought that the $value could be used with a pla
+ceholder and updated each time the array index incremented.  But is l
+ooks like that is a bad idea.  Please help!!!!

i.e.
@id = ('A01','B55')


Thank you for your advice.

[graff]

Please help!!!!

Sorry, but it looks like the kind of help you need is not technical in nature. You completely ignored my advice about formatting your posts, you are not posting any code that makes any sort of sense, and your rambling description is also nonsensical.

The impression I get is that you have no idea what you are trying to do, which would explain why you are unable to describe it clearly. And since you are not paying any attention to the advice you've been given, there's not much I can offer that would help you.

The current process works, but I need it to be dynamic.

Well, you haven't really shown us anything that works yet, have you? So no, this doesn't help us understand. You might just need something as simple as "placeholders" in the sql statement, but I'm not sure, because nothing you've presented so far makes any sense.