What are some common causes of syntax errors in queries?

[rodry]

I am not sure how I should explain this error since it could be one of many things. But, in short, DBI tells me that there is a syntax error in the query, even though when I print the query to the browser and then copy-paste it into the terminal to execute it, it works.

What are some obvious things to look for when getting these kinds of errors?

[chromatic]

If you're not using the RaiseError attribute, you can check $sth->err to see if there was an error in the query. (I don't see where you specifically do any quoting of the values stored $username and $password, so that's still a possibility in my mind. btrott has a good tutorial on placeholders here.)

The other thing worth checking is: print keys %$pointer; just to see if having Login capitalized in the $user2 assignment is a problem (as it's all lowercase in the SELECT statement).

According to the Perl DBI book, some database drivers convert field names to all uppercase or all lowercase. You can coerce DBI into fixing this for you with the following modification: $pointer = $sth->fetchrow_hashref('NAME_lc'); That will make the hash keys all lowercase. 'NAME_uc' makes them all uppercase. 'NAME' is the default, doing no mangling at all.

[btrott]

Are you checking for errors? The best way to do this is to set RaiseError to 1:

# do a $dbh = DBI->connect(...)
$dbh->{'RaiseError'} = 1;

Then you'll see any errors that your database engine is giving you.

Also, try dumping out the hashref that you're getting back:

use Data::Dumper;
while ($pointer = $sth->fetchrow_hashref) {
    print Dumper $pointer;

That'll show you what fields you're getting in the hash ref that you get back. You might also think about using bind_columns, since it's faster. More details in Tricks with DBI.

[comatose]

Another reason you could get an uninitialized value warning is if the field is NULL. Before using database fields, you should make sure they have a value to avoid the message.

print $value if (defined $value);

[rodry]

It may be a problem with the way DBI handles the query. I swear the thing works fine until I start adding more fields to the loop that store the query's results

Anyways, I stayed up all night trying to figure it out. I even re-wrote the entire script. It worked fine until, as I mentioned, started adding more fields to that loop.

my $username = param("username");
my $password = param("password");
my $user2;
my $pass2;
my $name;
my $client;

&Create_DB_Connection;

$SQL = "SELECT name, clientid, login, password FROM Client WHERE login
+='$username' AND password='$password'";

&Do_SQL;

while ($pointer = $sth->fetchrow_hashref)
{
    $user2 = $pointer->{'Login'};
    $pass2 = $pointer->{'Password'};
    $name = $pointer->{'name'};
    $client = $pointer->{'ClientID'};
}

If I try to print out, say, $user2, it will give me a USE OF UNITIALIZED VARIABLE and it won't display the result. Therefore, I suspect the query is not executing for some odd reason. Also checked the case sensitivity of the tables heads. Please help.

[Anonymous Monk]

i get similar error using "my" keyword to declare variable.

one way to solve it is to assign a default value right away when you declare variables with my.

ie:

$user2 ="user2";
$pass2 ="*****";

or the other way to get around is to use the following:

no warnings;

hope this helps.