Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

What are some common causes of syntax errors in queries?

by rodry (Beadle)
on Apr 17, 2000 at 12:36 UTC ( #7803=categorized question: print w/replies, xml ) Need Help??
Contributed by rodry on Apr 17, 2000 at 12:36 UTC
Q&A  > database programming


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?

Answer: What are some common causes of syntax errors in queries?
contributed by 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.

Answer: What are some common causes of syntax errors in queries?
contributed by 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.
Answer: What are some common causes of syntax errors in queries?
contributed by 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);
Answer: What are some common causes of syntax errors in queries?
contributed by 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.
Answer: What are some common causes of syntax errors in queries?
contributed by 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.

Please (register and) log in if you wish to add an answer

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others examining the Monastery: (3)
    As of 2018-12-12 07:24 GMT
    Find Nodes?
      Voting Booth?
      How many stories does it take before you've heard them all?

      Results (57 votes). Check out past polls.

      • (Sep 10, 2018 at 22:53 UTC) Welcome new users!