Solved

Problem passing query to MS SQL using DBI module

Posted on 2008-10-31
38
796 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
Comment Utility
btw, I've tried quotes, double quotes, and even replacing * with \* and nothing works so far.
0
 
LVL 13

Expert Comment

by:kawas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
if just an id, try $ARGV[0]
0
 
LVL 13

Expert Comment

by:kawas
Comment Utility
instead of <@ARGV>
0
 
LVL 1

Author Comment

by:David Aldridge
Comment Utility
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
Comment Utility
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
Comment Utility
I thought that might get it... same results though.
0
 
LVL 13

Expert Comment

by:kawas
Comment Utility
really? what errors are you getting now?
0
 
LVL 1

Author Comment

by:David Aldridge
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:David Aldridge
Comment Utility
Still gets the same results.
0
 
LVL 13

Expert Comment

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

Author Comment

by:David Aldridge
Comment Utility

#!/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
Comment Utility
how are you calling your script? can you show us that too?
0
 
LVL 1

Author Comment

by:David Aldridge
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

#!/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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

743 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

8 Experts available now in Live!

Get 1:1 Help Now