Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Why does my query work on the command line, but fail in my browser?

( #7550=categorized question: print w/ replies, xml ) Need Help??
Contributed by chromatic on Apr 14, 2000 at 01:46 UTC
Q&A  > database programming


Description:

This is often a quoting issue. For example, your query may look like this:
INSERT member (last, first) VALUES(NULL, "chromatic");
and your script like this:
$last = "NULL"; $first = "chromatic"; $query = qq { INSERT member (last, first) VALUES ($last, $first) }; $rows = $dbh->do($query);
Printing your $query would reveal that $last and $first are not being quoted.

The solution is to use something like this:

$last = $dbh->quote("NULL"); $first = $dbh->quote("chromatic");
or this:
$query = qq { INSERT member (last, first) VALUES (?, ?) }; $rows = $dbh->do($query, undef, $last, $first);

Answer: Why does my query work on the command line, but fail in my browser?
contributed by tommyw

One possibility (seen all too often) is that your webserver invokes your perl script in a different environment from the one you're using.

For example, if you're using Oracle, making sure that ORACLE_HOME is set makes a difference

Have a look in your servers log files: with luck it'll pinpoint the problem.

Answer: Why does my query work on the command line, but fail in my browser?
contributed by cavac

The query

INSERT member (last, first) VALUES(NULL, "chromatic");
also uses double quotes. Most databases either don't accept that, or interpret it in some other way (like PostgreSQL, which uses it to indicate that column name or table name is case sensitive.

This is especially problematic when you just copy the whole string from whatever tool you use to test the queries into Perl.

Answer: Why does my query work on the command line, but fail in my browser?
contributed by CMonster

Whoopsie! Be wary of quoting NULLs. The program listing has $dbh->quote("NULL"); but that gives 'NULL', or the string NULL, rather than the empty value NULL. To get the latter, use $dbh->quote(undef); ...which works just fine if quoting a variable; you get a quoted string when it contains a value, and NULL when it's undef.

Answer: Why does my query work on the command line, but fail in my browser?
contributed by Anonymous Monk

set the ORACLE_HOME and ORACLE_SID in your perl script. like: $ENV{'ORACLE_HOME'}='/home/oracle'; $ENV{'ORACLE_SID'}='ora1';

Answer: My query works fine from the command line, but it fails in my script!
contributed by Jonathan

I find it easier to build up the query string in a series of sprintf statements where the single quotes can be easily embedded :)

Answer: Why does my query work on the command line, but fail in my browser?
contributed by ralphie

check to make sure that the userid the script runs under has been granted database access rights. when using apache, the id can be found in httpd.conf under the user/group settings.

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!
  • 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
  • Outside of code tags, you may need to use entities for some characters:
            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?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others cooling their heels in the Monastery: (4)
    As of 2014-09-20 23:08 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (163 votes), past polls