CGI & DataBase Lists

[ShrtCut]

I have this cgi script that a friend of mine created (he has since passed on) and I am having a little problem getting it to work right now, I sure would appreciate some help with it.

What it does is draw member names from a specified section of my IPB database and creates a list to be displayed on part of my webpage,,,When I link the script on the webpage I get this error in that section of the page,,,,,,

Software error:
Can't call method "prepare" on an undefined value at get_bandits.cgi line 21.

That part of my script, including line 21, reads,,,,,,

# New style connect.
$dbh = DBI->connect("DBI:mysql:BlahBlah:localhost","BlahBlah","BlahBlah");
$sth = $dbh->prepare(qq(SELECT name FROM ibf_members WHERE mgroup=25 order by name)) or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;

Just starting to learn more about cgi's and I dont know exactly what is wrong with the 'prepare' function,,,Could somebody point me in the right direction please?

THANKS in advance :)

[KevinADC]

I assume you have the correct arguments in the connect() function where you have BlahBlah.

$dbh = DBI->connect("DBI:mysql:BlahBlah:localhost","BlahBlah","BlahBlah");

try this and see what you get:

$dbh = DBI->connect("DBI:mysql:BlahBlah:localhost","BlahBlah","BlahBlah") or die "Can't open DB: $!";

it seems that your connect() function is failing so $dbh is not defined and the prepare() function returns the error.

[ShrtCut]

Thanks for the tip Kevin, Now I get this error,,,,,,

Can't open DB: at get_bandits.cgi line 19.

As you assumed I have something wrong with the db info :( Here is the connection string that I get from my DB control panel, both perl and php,,,,,,

Perl

$dbh = DBI->connect("DBI:mysql:shrtcut_ibf:localhost","shrtcut_oldguy","<PASSWORD HERE>"); 

PHP

$dbh=mysql_connect ("localhost", "shrtcut_oldguy", "<PASSWORD HERE>") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("shrtcut_ibf"); 

I dont see that I have anything wrong with the DB info, but maybe a more seasoned eye can spot it (I hope) :)

[KevinADC]

I'm a bit weak in this area, but try you connection string like this:

$dbh = DBI->connect('DBI:mysql:database=shrtcut_ibf:host=localhost','shrtcut_oldguy','<PASSWORD HERE>') or die "Can't connect to DB: $!";

you may want to read through the DBI module documentation, which is quite lengthy:

[search.cpan.org...]

and the mysql driver documentation:

[search.cpan.org...]

[ShrtCut]

OK, I inserted the code you provided, and it looks like it is connecting to the db, however, now it says,,,,,,

Can't call method "prepare" on unblessed reference at get_bandits.cgi line 19.

What is "unblessed"?

This makes me believe something in the "prepare" command isnt quite right, wouldnt you think?

Here is the complete cgi file just in case :)

#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use DBI;
print "Content-type:text/html\n\n";
print "<table style=\"color:#00FF00\" cellspacing=\"5\" cellpadding=\"1\"><tr>";
#print "<font color=\"#00FF00\" size=\"1\">";
$count = 0;
$width = "25%";
# New style connect.
$$dbh = DBI->connect("DBI:mysql:shrtcut_ibf:localhost","shrtcut_oldguy","<PASSWORD HERE>") or die "Can't connect to DB: $!";
$sth = $dbh->prepare(qq(SELECT name FROM ibf_members WHERE mgroup=25 order by name)) or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;
while ($names = $sth->fetchrow_array) {
foreach $i ($names) {
print "<td width=\"$width\">$i</td>";
$count = $count+1;
if (($count % 4) == 0) {
print "</tr>\n";
}
}
}
$dbh->disconnect;
print "</tr></table>";
sub dienice {
my($msg) = @_;
print "<h2>Error</h2>\n";
print $msg;
exit;
}

Does the "#New style connect" need to be there?

[KevinADC]

is this a typo?

$$dbh = DBI->connect

[ShrtCut]

No sir, that is the way it is in the file,,,Should there be only 1 $?

[KevinADC]

looks to me like there should only be one $. Remove the second $ and retry the script.

[ShrtCut]

Didnt change anything :(

Any other ideas? Do you know of a pre-made cgi script that would do the same thing I am trying to do?

[rocknbil]

I'll take a stab. :D

You have to take a look at what qq means: qq is a perl method of storing a "quoted" string so you don't have to toothpick it to death with escapes: \\\ that is, a way of passing a string without worrying about escaping characters. It can include newlines and entire blocks of text.

The way it works is the first character after qq is the delimiter; it will slurp up everything up till it finds the ending delimiter (string terminator) which must be the exact same character. The character can be anything NOT used in the string. Examples:

$result = qq¦This is my string and I / can/
use "anything" in it except the vertical pipe.
¦;

$result = qq/
In this string the slash is my delimiter, so I can't use those unless I escape it like so \/ which is a bit counter to the point.
/;

$result = qq^ This is a better use of qq if I need to use slashes, but carats would need to be escaped now.^;

So. Look at your select statement:

qq(SELECT name FROM ibf_members WHERE mgroup=25 order by name)

But guess what: because the initial character of qq is "(", the ending ")" is not the end of your select, it's after the word dienice:

qq(SELECT name FROM ibf_members WHERE mgroup=25 order by name)) or dienice(

Which is a pretty mangled select statement. :-)

Armed with this knowledge, please give this a try. Store your select in a variable, just like so:

$select = qq/SELECT name FROM ibf_members WHERE mgroup=25 order by name;/;
$sth = $dbh->prepare("$select") or dienice("Can't select from table: ",$dbh->errmsg);
$sth->execute;

Storing it in a variable like that will help you spot troubles like these and keep the syntax correct.

There is the possibility that if "(" is used with qq then ")" will terminate the string (in which case my post is a total waste of space.) But I don't think so, at least not in my experience.

[KevinADC]

rocknbil,

I don't think that is correct. Parenthesis and other paired brackets, <> {} [] can be used as qq delimiters even though they aren't strictly matching like // ¦¦ ~~ and so on. I think this example will show that to be true:

mysub(qq(this is my text));

sub mysub {
print shift;
}

[rocknbil]

Well then, like I said, wasted space, sorry. It was the only thing I saw out of line.

[KevinADC]

not wasted space, you learned something new and maybe others have too. Brackets are a speacial case when used as delimiters for a number of things, such as regexp's:

my $str = 'this and that and this and that';
$str =~ s(this)(that)g;
print $str;

it's a sort of odd exception, like i before e except after c in the English or la mano in Spanish.

[wruppert]

I created my own little shrtcut_ibf database with an ibf_members table, added a few records, and the program worked fine.

The only change I made was the $$dbh into $dbh. If that change does not fix the problem then there is something wrong with the database.

The line "# New style connect" is a comment, has no effect on the program.