Solved

Problem passing query to MS SQL using DBI module

Posted on 2008-10-31
38
798 Views
Last Modified: 2012-05-05
I have the attached code using the DBI module doing a query to MS SQL.  When I pass it:
Global Sourcing - IT

it works fine. I get the following:  
Global Sourcing - IT's USD group number is 9244800

If I pass it something with astericks in it (which I have no control over):
Applications *** Internal Use Only ***

 I get the following, which replaces the astericks with files that are in the directory where the script is running:
Applications test.pl test2.pl wm_logwatch.pl working_hash_test.pl Internal Use Only test.pl test2.pl wm_logwatch.pl working_hash_test.pl not found.

Is there any way to make this work?

Thanks!
David

#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

$test = <@ARGV>;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;'; 

my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;

my $SQL = "select id, sym from ahd.ahd.zgrplist order by sym";

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute() or print "$DBI::errstr\n" and croak $!;
 

while (($id, $group) = $sth->fetchrow_array()) {

    chomp ($id, $group);

    ( index ( $test, $group ) >= 0 ) and print "$group" ."\'s USD group number is: $id\n" and exit 1;

}

$dbh->disconnect();

Open in new window

0
Comment
Question by:David Aldridge
  • 19
  • 15
  • 4
38 Comments
 
LVL 1

Author Comment

by:David Aldridge
ID: 22851597
btw, I've tried quotes, double quotes, and even replacing * with \* and nothing works so far.
0
 
LVL 13

Expert Comment

by:kawas
ID: 22851817
have you tried using a prepared statement? btw, how can you pass anything into your query when you are showing a simple select statement with no WHERE clause?
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22852193
Because it's not a long list and I am getting the entire contents of id and sym fields and putting them into an array.

I'm not sure what you mean by passing it a prepared statement.  Can you explain?

Thanks!
0
 
LVL 13

Expert Comment

by:kawas
ID: 22852247
something like the snippet below.  note foo = ? and $my_foo_value_here
my $SQL = 'select id, sym from ahd.ahd.zgrplist order by sym WHERE foo = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($my_foo_value_here) or print "$DBI::errstr\n" and croak $!;

Open in new window

0
 
LVL 13

Accepted Solution

by:
kawas earned 250 total points
ID: 22852254
Explanation

" The prepare call prepares a query to be executed by the database. The argument is any SQL at all. On high-end databases, prepare will send the SQL to the database server, which will compile it. If prepare is successful, it returns a statement handle object which represents the statement; otherwise it returns an undefined value and we abort the program. $dbh->errstr will return the reason for failure, which might be ``Syntax error in SQL''. It gets this reason from the actual database, if possible.

The ? in the SQL will be filled in later. Most databases can handle this. For some databases that don't understand the ?, the DBD module will emulate it for you and will pretend that the database understands how to fill values in later, even though it doesn't."

from http://www.perl.com/pub/a/1999/10/DBI.html
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22852623
I guess I'm just dense.  I can't figure out what's different in that and what I was doing the first time.  Is what I'm doing here not a prepared statement?  But you did raise a good point and instead of dumping everything to an array and searching it, I can do a specific query based on the $test which is passed to the script.  The script now looks like this and works as before, but still breaks if there is an asterick in $test.
#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

$test = <@ARGV>;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;'; 

my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;

my $SQL = "\'select sym from ahd.ahd.zgrplist order by sym WHERE id = $test\'";

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($SQL) or print "$DBI::errstr\n" and croak $!;
 

($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n" and exit 1;
 

$dbh->disconnect();

Open in new window

0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22852637
As a matter of fact, I don't need "order by" either, so that line looks like this:

my $SQL = "\'select sym from ahd.ahd.zgrplist WHERE id = $test\'";
0
 
LVL 13

Expert Comment

by:kawas
ID: 22852649
change:
my $SQL = "\'select sym from ahd.ahd.zgrplist order by sym WHERE id = $test\'";
to
my $SQL = 'select sym from ahd.ahd.zgrplist order by sym WHERE id = ?';

and
$sth->execute($SQL) or print "$DBI::errstr\n" and croak $!;
to
$sth->execute($test) or print "$DBI::errstr\n" and croak $!;

i am assuming that $test is a literal?
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22852777
Yes, $test is a literal.  I changed it to this and it does the same thing.
#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

$test = <@ARGV>;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';

my $SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;
 

($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n" and exit 1;
 

$dbh->disconnect();

Open in new window

0
 
LVL 13

Expert Comment

by:kawas
ID: 22852874
are you trying to read in files or a string of text representing the id you would like to query for?
0
 
LVL 13

Expert Comment

by:kawas
ID: 22852881
if just an id, try $ARGV[0]
0
 
LVL 13

Expert Comment

by:kawas
ID: 22852885
instead of <@ARGV>
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22852906
A string is passed to the script like so and these work:

test.pl WEBMETHODS ARCHITECTURE
test.pl SAP-BPI MAINTENANCE

This one breaks it:

test.pl Applications *** Internal Use Only ***
0
 
LVL 13

Expert Comment

by:kawas
ID: 22852930
okay.

2 things: get rid of the <@ARGV> statement and replace with $ARGV[0], and make sure that the strings that you pass into your script are enclosed in quotes if there is whitespace in the name.
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22853134
I thought that might get it... same results though.
0
 
LVL 13

Expert Comment

by:kawas
ID: 22853181
really? what errors are you getting now?
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22853284
Exactly the same.  If it doesn't have astericks, it gives me the right number from the database for the string passed to it.  With the astericks, it seems to pass the names of the files in the directory to the database as the query and it returns "not found".  Here is the exact error when I pass it "Applications *** Internal Use Only ***".

Applications Perl-1.pl test.pl test2.pl test3.pl wm_logwatch.pl working_hash_test.pl Internal Use Only Perl-1.pl test.pl test2.pl test3.pl wm_logwatch.pl working_hash_test.pl not found.

0
 
LVL 13

Expert Comment

by:kawas
ID: 22857626
I am not having the same problems. Could you post the script as you have it?
0
 
LVL 39

Assisted Solution

by:Adam314
Adam314 earned 250 total points
ID: 22863963
When you say this:
    <@ARGV>
This means concatenate the arguments passed to the script, and do filename globbing on the results.  See here:
    http://perldoc.perl.org/functions/glob.html

You want to say this for your line 7:
    my $test = shift @ARGV;

Then maybe add something like this to line 8:
    die "Usage: $0 <search_term>\n" unless $test;
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:David Aldridge
ID: 22867454
Still gets the same results.
0
 
LVL 13

Expert Comment

by:kawas
ID: 22867472
can you post your script, because you shouldnt be getting the same results.
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22868819

#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

#$test = $ARGV[0];

my $test = shift @ARGV;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';

my $SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;
 

($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n" and exit 1;
 

$dbh->disconnect();

Open in new window

0
 
LVL 13

Expert Comment

by:kawas
ID: 22868849
how are you calling your script? can you show us that too?
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22868907
I'm just running it from inside of Komodo and adding the following as "Script Arguments":

'Applications *** Internal Use Only ***'

I've tried it with quotes, with double quotes, with nothing, and with the astericks escaped.
0
 
LVL 13

Expert Comment

by:kawas
ID: 22868918
try running the script from the command line. maybe komodo is doing some extra things for you
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22869913
Here's what I get from the command prompt.  Much more interesting information, but I still don't have a clue what to do about it.  :-)

C:\Test\logwatch>test3.pl Applications *** Internal Use Only ***
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast sp
ecification (SQL-22018) at C:\Test\logwatch\test3.pl line 20.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
 at C:\Test\logwatch\test3.pl line 20
0
 
LVL 39

Expert Comment

by:Adam314
ID: 22869928
I'm guessing Komodo is doing the filename globbing.  Try running from the command line.

Or, if you want the script to ask the user to enter the id:
  Instead of:
      my $test = shift @ARGV;
  Try:
      print "Enter ID: ";
      my $test = <STDIN>;
      chomp($test);

0
 
LVL 39

Expert Comment

by:Adam314
ID: 22869950
The code you posted doesn't have a line 20.  Also, with spaces in the name, you'll need to put them in double-quotes in windows command prompt:
    test3.pl "Applications *** Internal Use Only ***"
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870061
Here's line 20.
$sth->execute($test) or print "$DBI::errstr\n" and croak $!;

There reason that it went longer is I tried adding a regex to parse $text and remove the astericks and do a "LIKE" query by take "Applications%" and that didn't work.  I just removed that and here is the code I ran from the command prompt.  I tried it with the double quotes and got the same results.  Here are the results of running the attached code with the double quotes:

C:\Test\logwatch>test3.pl "Applications *** Internal Use Only ***"
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast sp
ecification (SQL-22018) at C:\Test\logwatch\test3.pl line 16.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
Bad file descriptor at C:\Test\logwatch\test3.pl line 16

#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

#$test = $ARGV[0];

my $test = shift @ARGV;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';

$SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;
 

($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n" and exit 1;
 

$dbh->disconnect();

Open in new window

0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870309
Sorry, I didn't do a very good job of cleaning that mess up before I posted it.  I apologize. Here's the code that I am running right now and here's the error message:

C:\Test\logwatch>test3.pl "Applications *** Internal Use Only ***"
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast sp
ecification (SQL-22018) at C:\Test\logwatch\test3.pl line 14.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
Bad file descriptor at C:\Test\logwatch\test3.pl line 14
#!/perl -w
 

use DBI;

use DBD::ODBC;

use Carp;
 

#$test = $ARGV[0];

my $test = shift @ARGV;
 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';$SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;
 

($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n" and exit 1;
 

$dbh->disconnect();

Open in new window

0
 
LVL 13

Expert Comment

by:kawas
ID: 22870356
I think that your original question has been answered. the new problem is now based on your query. i havent used microsoft sql server, so i dont know what is wrong with your query...

not sure if this is possible, but you might want to try running the sql server client and trying your select statement with the asterisks in it. Might be easier to debug the statement that way.
0
 
LVL 39

Expert Comment

by:Adam314
ID: 22870401
Are you sure that is the exact code you are using?  Your $dbh isn't defined anywhere.

Does MS SQL have a query browser - some program where you can enter SQL statements, and view the results?  (I know MySQL has one, but I'm not familiar with MS).

If so, what do you get if you enter this:
    select sym from ahd.ahd.zgrplist WHERE id = 'Applications *** Internal Use Only ***';
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870439
Here's the code and me running the code from the command line.  I'll pull up the client and see what I can come up with:

C:\Test\logwatch>type test3.pl
#!/perl -w

use DBI;
use DBD::ODBC;
use Carp;

#$test = $ARGV[0];
my $test = shift @ARGV;

my $DSN = 'driver={SQL Server};Server=S02ASQLAHDPRD;database=ahd;uid=esm_rpt;pwd=Neptune01;';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;
$SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';
my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;
$sth->execute($test) or print "$DBI::errstr\n" and croak $!;

($id) = $sth->fetchrow_array();
print "$test" ."\'s USD group number is: $id\n" and exit 1;

$dbh->disconnect();
C:\Test\logwatch>test3.pl "Applications *** Internal Use Only ***"
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast sp
ecification (SQL-22018) at C:\Test\logwatch\test3.pl line 14.
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
Bad file descriptor at C:\Test\logwatch\test3.pl line 14

C:\Test\logwatch>
0
 
LVL 13

Expert Comment

by:kawas
ID: 22870455

#!/perl -w

 

use DBI;

use DBD::ODBC;

use Carp;

 

my $test = shift @ARGV;

 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';

my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;

my $SQL = 'select sym from ahd.ahd.zgrplist WHERE id = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;

 

my ($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n";
 

$dbh->disconnect();

Open in new window

0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870609
When I run this in the client, it runs fine and pulls the number.  I did notice a mistake in my sql though as I was looking for the wrong field. I had sym and id reversed in my query.  I've added the corrected code but get the same results.  Here is the successful SQL statement I ran in the client:

SELECT     id
FROM         ahd.ahd..zgrplist
WHERE     (sym = 'Applications *** Internal Use Only ***')



#!/perl -w

 

use DBI;

use DBD::ODBC;

use Carp;

 

my $test = shift @ARGV;

 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;'; 

my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;

my $SQL = 'select id from ahd.ahd.zgrplist WHERE sym = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;

 

my ($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n";

 

$dbh->disconnect();

Open in new window

0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870717
I'm getting close!  I am actually getting the number back now, but can you tell me how to get rid of the this other error message?  It looks like it's just my complete lack of understanding how to use DBI.

C:\Test\logwatch>test3.pl "Applications *** Internal Use Only ***"
Applications *** Internal Use Only ***'s USD group number is: 35580992
DBI::db=HASH(0x1a5c23c)->disconnect invalidates 1 active statement handle (either destroy statement
handles or call finish on them before disconnecting) at C:\Test\logwatch\test3.pl line 18.
0
 
LVL 1

Author Closing Comment

by:David Aldridge
ID: 31512106
I really do appreciate all of the help.  If I could award more points to you I would.  This was really important.
0
 
LVL 1

Author Comment

by:David Aldridge
ID: 22870814
I have attached the working code for MY future reference.  Thanks for all of the help!

#!/perl -w

 

use DBI;

use DBD::ODBC;

use Carp;

 

my $test = shift @ARGV;

 

my $DSN = 'driver={SQL Server};Server=SQLSERVER01;database=ahd;uid=reports;pwd=reports;';

my $dbh = DBI->connect("dbi:ODBC:$DSN") or print "$DBI::errstr\n" and croak $!;

my $SQL = 'select id from ahd.ahd.zgrplist WHERE sym = ?';

my $sth = $dbh->prepare($SQL) or print "$DBI::errstr\n" and croak $!;

$sth->execute($test) or print "$DBI::errstr\n" and croak $!;
 

my ($id) = $sth->fetchrow_array();

print "$test" ."\'s USD group number is: $id\n";

$sth->finish(); 

$dbh->disconnect();

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now