Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

SQL queries using dynamic array

by Anonymous Monk
on Aug 03, 2005 at 23:47 UTC ( #480665=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Good day perl monks, I am wanting to use an SQL query that will use an array as part of the WHERE clause. The original query looks something like this
# // Connect to Microsoft SQL 2000 Database my $DSN = 'driver={SQL Server};Server=DIGGER;database=Mailbird;uid=dig +ger;pwd=psswrd;'; my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n"; # Prepare the SQL statement my $sth = $dbh->prepare( <<SQL ); SELECT c_email, c_first, c_last FROM tblClient, tblGroup, tblRegion WHERE tblGroup.g_name = 'motel' AND tblRegion.r_name='Otago' SQL # Execute the SQL statement $sth->execute;
Where the query states 'Otago' I am wanting to use an array containing a list of values (eg Otago, Auckland, Northland etc). There for the query would be something like
AND tblRegion.r_name='Otago' OR tblRegion.r_name='Auckland' OR tblRegi +on.r_name='Northland ...
I have looked at joining this array into one variable, with little success. How should I approach this?
Forever greatfull

Comment on SQL queries using dynamic array
Select or Download Code
Re: SQL queries using dynamic array
by Enlil (Parson) on Aug 03, 2005 at 23:59 UTC
    Here is one way (granted it is not tested):
    my @values = qw/Otago Auckland Nortland/; #Quote Values to be safe my $values = join("," ,map { $dbh->quote($_) } @values ); my $statement = " SELECT c_email, c_first, c_last FROM tblClient, tblGroup, tblRegion WHERE tblGroup.g_name = 'motel' AND tblRegion.r_name IN ( $values )"; my $sth = $dbh->prepare($statement); $sth->execute();

    HTH

    -enlil

      For those who like bind variables:

      my $placeholders = join ( ',', ('?') x @values ); my $statement = <<"EOF"; SELECT c_email, c_first, c_last FROM tblClient, tblGroup, tblRegion WHERE tblGroup.g_name = ? AND tblRegion.r_name IN ( $placeholders ) EOF my $sth = $dbh->prepare($statement); $sth->execute('motel', @values);
Re: SQL queries using dynamic array
by pg (Canon) on Aug 04, 2005 at 01:32 UTC

    As 1st reply pointed out, IN is the way to go, just remember that a SQL statement can contain at most 4000 chars. So you need some logic there to control the length, and break into multiple statements if needed.

      just remember that a SQL statement can contain at most 4000 chars.

      Which database has that limitation?

      I've googled a bit, and I think that you have that statement mixed up with '(some char column type(s)) can be at most 4000 characters', which would mean that each bind variable could be at most that length.

        The limitation for Microsoft SQL 2000 is 4000 characters. that's unicode, and we are talking about 8000 octets.

        I could not remember the exact length limitation for Oracle, but my pression is that it is 3000+ characters. (The Oracle I use is a bit old, version 8.1.7)

Re: SQL queries using dynamic array
by graff (Chancellor) on Aug 04, 2005 at 01:53 UTC
    Previous replies are both useful. Here's a way to simplify and shorten everything by using placeholders within the "IN (...)" portion of the query:
    # assume that $dbh is ready... my @places = qw/Otago Auckland Northland Boolaboola/; my $query = <<ENDSQL; SELECT c_mail, c_first, c_last FROM tblClient, tblGroup, tblRegion WHERE tblGroup.g_name = 'motel' AND tblRegion.r_name IN ENDSQL my $sth = $dbh->prepare( $query . ' (' . join(',',map{'?'} @places) . +')' ); $sth->execute( @places );
    This way, you don't need to worry about quoting the place names, and it might even give you more headroom in terms of overall statement length.

    The @places array dictates how many placeholders are added for the "IN (...)" clause, as well as providing the placeholder values for the execute call, so if you change the number of elements in the array, the sql stuff will follow through without further ado.

      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 cory.clay@rentacenter.com or cocl04@yahoo.com...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++; }
        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.

Re: SQL queries using dynamic array
by Anonymous Monk on Aug 04, 2005 at 02:59 UTC
    Exactly what I wanted. Thank you for your generosity. I really enjoy this element of programming.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://480665]
Approved by sk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2014-07-12 01:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (238 votes), past polls