Beefy Boxes and Bandwidth Generously Provided by pair Networks DiBona
The stupid question is the question not asked
 
PerlMonks  

Search breaks based on search string

by Kiko (Scribe)
on Sep 01, 2005 at 16:29 UTC ( #488414=perlquestion: print w/ replies, xml ) Need Help??
Kiko has asked for the wisdom of the Perl Monks concerning the following question:

Hello Fellow Monks, It’s been a while since I’ve program, but a good friend of mine asked me to put together a small database to track blackberry inventory. I agreed to do it and with the best language of course. The only problem, I’m a little rusty and will need your expert advice.

It’s an Access DB, the script works fine with any search string except with status=DEPLOYED or emp_user_name=*, every other search string will return no more than 50 resulst, the ones that are failing will return around 400 results. I’m sure I’m going the wrong way about querying and displaying the records.

Any advice or help that you can provide is always appreciated!
# vars for query my ($sql_bb_activity_code,$sql_bb_model,$sql_bb_pin,$sql_bb_phone,$sql +_bb_imei_esn_dec,$sql_bb_status,$sql_bb_region_code)=@_; if ($emp_user_name eq "*") { ($statement)="SELECT bb_id, emp_user_name, bb_activity_code, bb_mo +del, bb_pin, bb_phone, bb_imei_esn_dec, bb_status, bb_region_code FRO +M blackberry ORDER BY emp_user_name"; } elsif (($emp_user_name or $bb_activity_code or $bb_model or $bb_pin or + $bb_phone or $bb_imei_esn_dec or $bb_status or $bb_region_code) and +($emp_user_name ne "*")) { if ($bb_activity_code ne "") { $sql_bb_activity_code =" AND bb_activity_code LIKE '%$bb_activ +ity_code%'"; } if ($bb_model ne "") { $sql_bb_model =" AND bb_model='$bb_model'"; } if ($bb_pin ne "") { $sql_bb_pin =" AND bb_pin LIKE '%$bb_pin%'"; } if ($bb_phone ne "") { $sql_bb_phone =" AND bb_phone LIKE '%$bb_phone%'"; } if ($bb_imei_esn_dec ne "") { $sql_bb_imei_esn_dec =" AND bb_imei_esn_dec LIKE '%$bb_imei_es +n_dec%'"; } if ($bb_status ne "") { $sql_bb_status =" AND bb_status='$bb_status'"; } if ($bb_region_code ne "") { $sql_bb_region_code = " AND bb_region_code='$bb_region_code'"; } ($statement)="SELECT bb_id, emp_user_name, bb_activity_code, bb_mo +del, bb_pin, bb_phone, bb_imei_esn_dec, bb_status, bb_region_code FRO +M blackberry WHERE emp_user_name LIKE '%$emp_user_name%' $sql_bb_acti +vity_code $sql_bb_model $sql_bb_pin $sql_bb_phone $sql_bb_imei_esn_de +c $sql_bb_status $sql_bb_region_code ORDER BY emp_user_name"; } else { exit; } ################################################## # Connect to the database and send sql statement # ################################################## my ($db)=new Win32::ODBC("DSN=cbsc;UID=;PWD=;"); if ($db) { $connection_status= "Connection Established!"; } else { $connection_status= "Unable to connect to database!"; } $db->Sql("$statement"); push @error,$db->Error(); # Print out any errors if (@error) { foreach (@error) { print "<BR>$_"; } } ################# # PRINT RESULTS # ################# while ($db->FetchRow()) { my (%dbrow) = $db->DataHash(); $count++; push @grab_results, " <TR VALIGN=_TOP BGCOLOR=\"EEEEEE\"> <TD><FONT SIZE=2><A href=\"javascript:popw +in('view_bb.pl?bb_id=$dbrow{'bb_id'}', '$dbrow{'bb_id'}')\">$dbrow{'b +b_id'}</A>&nbsp\;</FONT></TD> <TD><FONT SIZE=2>$dbrow{'emp_user_name'}&n +bsp\;</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_activity_code' +}&nbsp\;</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_model'}&nbsp\; +</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_pin'}&nbsp\;</ +FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_phone'}&nbsp\; +</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_imei_esn_dec'} +&nbsp\;</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_status'}&nbsp\ +;</FONT></TD> <TD><FONT SIZE=2>$dbrow{'bb_region_code'}& +nbsp\;</FONT></TD> </TR> "; } print<<HTML_HERE; <b>Total = $count</b> <TABLE BORDER="0" CELLSPACING="1" CELLPADDING="1" WIDTH="711"> <TR VALIGN="_TOP" BGCOLOR="#CCCCCC"> <TD><FONT SIZE="2">BB ID</FONT></TD> <TD><FONT SIZE="2">EMP&nbsp;Username</FONT></TD> <TD><FONT SIZE="2">Activity&nbsp;Code</FONT></TD> <TD><FONT SIZE="2">Model</FONT></TD> <TD><FONT SIZE="2">PIN</FONT></TD> <TD><FONT SIZE="2">Phone</FONT></TD> <TD><FONT SIZE="2">IMEI/ESN(dec)</FONT></TD> <TD><FONT SIZE="2">Status</FONT></TD> <TD><FONT SIZE="2">Region</FONT></TD> </TR> HTML_HERE foreach (@grab_results) { print "$_"; } print "</TABLE>"; # Print out any errors if (@error) { foreach (@error) { print "<BR>$_"; } } # Print out the sql query print "<BR><font color=FFFFFF>SQL: $statement </font>"; ### Close ODBC connection $db->Close();

Edit g0n - added readmore tags

Comment on Search breaks based on search string
Download Code
Re: Search breaks based on search string
by g0n (Priest) on Sep 01, 2005 at 17:20 UTC
    That's a fairly large chunk of code. I can't see any obvious bugs, but not having your database it's tricky to debug.

    I notice you print out the sql statement at the end of your HTML. At risk of stating the obvious, have you run the failing sql statements directly against the database, to see if you get the same results?

    --------------------------------------------------------------

    g0n, backpropagated monk

      Thanks for your reply!

      I print out the statement in white for debugging purposes.

      I ran this query against the DB and it came back fine, but in the script it hangs:
      SELECT bb_id, emp_user_name, bb_activity_code, bb_model, bb_pin, bb_ph +one, bb_imei_esn_dec, bb_status, bb_region_code FROM blackberry ORDER + BY emp_user_name
      This script returned no values in Access, but once i removed the emp_user_name LIKE '%%' part, it ran fine:
      SELECT bb_id, emp_user_name, bb_activity_code, bb_model, bb_pin, bb_ph +one, bb_imei_esn_dec, bb_status, bb_region_code FROM blackberry WHERE + emp_user_name LIKE '%%' AND bb_status='DEPLOYED' ORDER BY emp_user_n +ame
      I'm going to make a few modifications to see if i can fix it now that i have more to work with.

      Thanks for the advice!
Re: Search breaks based on search string
by Tanktalus (Canon) on Sep 01, 2005 at 20:20 UTC

    Please, oh please, do not put your variables right into your SQL statement. That's just asking for long-term problems. Nevermind nasty injection bugs.

    my @where; my @binds; if ($emp_user_name ne "*") { if ($bb_activity_code) { push @where, 'bb_activity_code LIKE ?'; push @binds, "%$bb_activity_code%"; } if ($bb_model) { push @where, 'bb_model = ?'; push @binds, $bb_model } if ($bb_pin) { push @where, 'bb_pin LIKE ?'; push @binds, "%$bb_pin%"; } if ($bb_phone) { push @where, 'bb_phone LIKE ?'; push @binds, "%$bb_phone%"; } if ($bb_imei_esn_dec) { push @where, 'bb_imei_esn_dec LIKE ?'; push @binds, "%$bb_imei_esn_dec%"; } if ($bb_status) { push @where, 'bb_status = ?'; push @binds, $bb_status } if ($bb_region_code) { push @where, 'bb_region_code = ?'; push @binds, $bb_region_code } if (@where) { push @where 'emp_user_name LIKE ?'; push @binds, "%$emp_user_name%"; } } my $statement = "SELECT bb_id, emp_user_name, bb_activity_code, bb_mod +el, bb_pin, bb_phone, bb_imei_esn_dec, bb_status, bb_region_code FROM + blackberry"; $statement .= ' WHERE ' . join(' AND ', @where) if @where; $statement .= ' ORDER BY emp_user_name';

    Of course, you then have to bind @binds to your query, which I assume is possible with Win32::ODBC, but I'll leave that to you to look at. Anyway, this should be much simpler than what you had - and easier to add to or otherwise modify. In fact, I'd go another step and put all those variables into a hash, and then you could loop through them to generate the SQL statement. A bit more abstraction which could make runtime a bit slower, but make modifications (of the code) much faster.

Re: Search breaks based on search string
by graff (Chancellor) on Sep 01, 2005 at 20:40 UTC
    First off, while you showed a lot of code, you may have left out some relevant things, like: "What does 'clean_sql()' do, exactly?" and "What is in @_ when this thing runs?"

    I actually expect that this line is a mistake, or at least the assignment to @_ is unnecessary:

    my ($sql_bb_activity_code,...,$sql_bb_region_code)=@_;
    Apart from that, some judicious use of arrays and hashes would make the code a lot shorter, more readable, and probably easier to maintain. Here's one way (which doesn't go as far as it could in terms of organizing things into data structures, but it goes a long way in the right direction), to set up the sql statement:
    use strict; # I assume your app already has this somewhere ##################### # DECLARE VARIABLES # ##################### my $count = 0; # Count the number of rows my @grab_results = (); # Array containing results my $statement; # sql statement my @error = (); # Array to store any DB errors my $connection_status; # Tells us whether we connected to a DB or not ################## # CGI PARAMETERS # ################## my %bb; my @fldnames = qw(emp_user_name bb_activity_code bb_model bb_pin bb_phone bb_imei_esn_doc bb_status bb_region_c +ode); my @fldops = qw(LIKE LIKE = LIKE LIKE LIKE = =); $bb{$_} = clean_sql(param($_)) for ( @fldnames ); ########################## # Generate SQL statement # ########################## my $whereclause = ''; if ( $bb{emp_user_name} ne '*') { my @conditions = (); for my $i ( 0 .. $#fldnames ) { my $fname = $fldnames[$i]; if ( $bb{$fname} ne '' ) { push @conditions, "$fname $fldops[$i] " . (( $fldops[$i] eq '=' ) ? $bb{$fname} : "\%$bb{$fname +}\%" ); } } exit if ( @conditions == 0 ); $whereclause = 'WHERE ' . join( ' AND ', @conditions ); } $statement = 'SELECT ' . join( ',', 'bb_id', @fldnames ) . " FROM blackberry $whereclause ORDER BY emp_user_name"; ################################################## # Connect to the database and send sql statement # ################################################## # ...
    That much, with your later stuff tacked on, passes "perl -cw", though it has not been tested in any way beyond that.

    As for your later observations about how the script hangs with the sql statement that has no "WHERE" clause, you might need to make up an ad-hoc perl script that you can run from the command line to connect to the database and just run that one query and print output to the screen, to see what happens.

      Thanks for your reply!
      The @_ is unnecessary, and clean_sql() replaces SQL and ODBC characters with ASCI code, i.e, $sql=~s/\'/\' \& chr\(39\) \& \'/g;

      Ok, so i'm using your code and i am getting the same results. when i search for status='DEPLOYED' or emp_user_name='*', it hangs and times out. I'll keep playing around with it tonight, i'll let you guys know what i find. BTW, running the SQL statement agains Access works just fine. I might have to switch to MYSQL : ). Thanks for all the replies... -Kiko
Re: Search breaks based on search string
by Roger (Parson) on Sep 02, 2005 at 01:10 UTC
    The goal of coding is to minimize the work efford, why reinvent the wheel when there are modules like Data::Table and Data::Tabular out there (on CPAN) that does all the hard work for you?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2014-04-19 03:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (477 votes), past polls