CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
problem with mySQL script.

 



Maebius
Novice

Jun 28, 2001, 9:28 AM

Post #1 of 4 (495 views)
problem with mySQL script. Can't Post

I am just learning how to spell SQL, and am attempting to get the basics down. In this quest, I have written a very simple script by using some examples I found. Not quite cut&paste since I am trying to understand the reasoning behind each line...

My script is below, but it does not seem to be working correctly. I get no errors, and no output either! (see the " print "Script complete"; " command at the end)

Here is the necessary info, as far as I can tell. If something is missing let me know...
I used a few different types of fields, just to get the practice and see how each is handled...

Database name: 'maebius'
Table name: 'test'
contents of table:
testID - a tinyint, primary key.
testName - a varChar(20)
testNumber - a tinyint (5)
testSet - a set with the contents ('red','yellow','green','blue')

I have tried commenting out indivifual sections of the script, and it seems to hang up around the "$sth->execute(test)..." area, or the line above...
If these are commented out, the script runs and I get the output "Script Complete". When there is no output, everything seems to run fine since the browser does not continually "load", or anything else.


Code
#!/usr/bin/perl  

use DBI;
print "Content-type: text/html\n\n";

my $DSN = "DBI:mysql:maebius";
my $user = "username";
my $pw = "password";

$dbh = DBI->connect($DSN,$user,$pw) or print "Cannot connect: $DBI::errstr\n";

my $query = " select testID,testName,testNumber,testSet from test where testName = * ";

my $sth = $dbh->prepare($query) or print "$DBI::errstr\n";
$sth->execute(test) or print "$DBI::errstr\n";

#while ( my($testID,$testName,$testNumber,$testSet) = $sth->fetchrow_array())
#{
#print " $testID _ $testName _ $testNumber _ $testSet \n";
#}

print "Script complete.";


-Maebius
Living life -umop apisdn-
maebius@everthorn.net


Rivotti
User

Jun 28, 2001, 11:46 AM

Post #2 of 4 (489 views)
Re: problem with mySQL script. [In reply to] Can't Post

Hi Maebius,

check if this works:

my $test = "" # string for testName;
my $query = " select testID,testName,testNumber,testSet from test where testName = ? ";
my $sth = $dbh->prepare($query) or print "$DBI::errstr\n";
$sth->execute($test) or nt "$DBI::errstr\n";

check http://www.mysql.com/documentation/index.html for documentation on Mysql

Rivotti







Maebius
Novice

Jun 28, 2001, 12:48 PM

Post #3 of 4 (486 views)
Re: problem with mySQL script. [In reply to] Can't Post

Thanks for the hints!

I saw a few typos in your example, but it works almost fine after fixing them. (Namely the semi-colon in the my $test="" line was after teh comment, not before, so it was parsing the comment too.) :)

The only thing I now notice with the script below is that it does not return any entries. If I edit the line my $test = ""; to have some known entry between the quotes (such as $test = "Maebius"; ) then it does return that row of the table with the ID, Name, number, etc.

But at least you have set me on the correct path! I had started to run through the documentation, and decided to hack away before I finished. I'm still not sure what your fix did, but at least it works now!

(my actual, productive, useful script-in-planning will already have the name passed to it, so this version will work great for my intended purpose!)


Code
#!/usr/bin/perl  

use DBI;
print "Content-type: text/html\n\n";

my $DSN = "DBI:mysql:maebius";
my $user = "maebius";
my $pw = "mypassword";

$dbh = DBI->connect($DSN,$user,$pw) or print "Cannot connect: $DBI::errstr\n";

my $test = ""; # string for testName
my $query = " select testID,testName,testNumber,testSet from test where testName = ? ";

my $sth = $dbh->prepare($query) or print "$DBI::errstr\n";
$sth->execute($test) or print "$DBI::errstr\n";

while ( my($ID,$Name,$Number,$Set) = $sth->fetchrow_array())
{
print " $ID _ $Name _ $Number _ $Set \n";
print "Cycled through the While Loop!";
}

print "Script complete.";

-Maebius
Living life -umop apisdn-
maebius@everthorn.net


Rivotti
User

Jun 28, 2001, 3:26 PM

Post #4 of 4 (481 views)
Re: problem with mySQL script. [In reply to] Can't Post

Maebius...

Sorry if I didn't explain what was the problem. Let's see...
In your script you where using:

-my $query = " select testID,testName,testNumber,testSet from test where testName = * ";
the * was the first problem. The correct plaholder is ? as you can see in my example;

-$sth->execute(test) or print "$DBI::errstr\n";
this was the second error. I saw that you wanted to put the value of a variable there. You forgot to use $ before teste. In my example I created a var $test before but you could do the same thing without the var:
$sth->execute("Maebius") or print "$DBI::errstr\n";
this will replace ? with Maebius.

a hope this was usefull. Check this link http://search.cpan.org/doc/TIMB/DBI-1.18/DBI.pm. It's the Perl/DBI documentation. Read all because it's a valuable source.

regards
Rivotti





 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives