update a mysql-db with date

Discussion in 'Perl Misc' started by Captain Beefart, Jul 29, 2004.

  1. Hi,
    I have created a database in mysql that have a field in dtetime format.
    In perl I have the string "20040728165500" to update this field in the db.
    I'm using the module mysql.

    Part of the code:
    my $sql_statement="INSERT INTO cme (date, name) VALUES ($datetime, $name)" ;
    print "$sql_statement\n";
    my $sth = $dbh->query($sql_statement);

    The output:
    INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)
    Can't call method "prepare" on an undefined value
    at /usr/lib/perl5/vendor_perl/5.8.2/i686-linux/Mysql.pm line 169.

    Anyone that can tell me how to do it?
    I've been searching for more than a week now. :(
     
    Captain Beefart, Jul 29, 2004
    #1
    1. Advertising

  2. Captain Beefart <> writes:

    > my $sql_statement="INSERT INTO cme (date, name) VALUES ($datetime, $name)" ;
    > print "$sql_statement\n";
    > my $sth = $dbh->query($sql_statement);
    >
    > The output:
    > INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)


    That doesn't look like valid SQL to me. Not, of course, that this has
    anything to do with Perl of course.

    You should quote the strings in you SQL or better still use placeholders.

    > Can't call method "prepare" on an undefined value
    > at /usr/lib/perl5/vendor_perl/5.8.2/i686-linux/Mysql.pm line 169.


    Please post a minimal but complete script that reproduces the
    symptoms.

    This and much other valuable advice can be found in the posting
    guidelines.

    --
    \\ ( )
    . _\\__[oo
    .__/ \\ /\@
    . l___\\
    # ll l\\
    ###LL LL\\
     
    Brian McCauley, Jul 29, 2004
    #2
    1. Advertising

  3. Brian McCauley wrote:

    > Captain Beefart <> writes:
    >
    >> my $sql_statement="INSERT INTO cme (date, name) VALUES ($datetime,
    >> $name)" ; print "$sql_statement\n";
    >> my $sth = $dbh->query($sql_statement);
    >>
    >> The output:
    >> INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)

    >
    > That doesn't look like valid SQL to me. Not, of course, that this has
    > anything to do with Perl of course.
    >
    > You should quote the strings in you SQL or better still use placeholders.
    >
    >> Can't call method "prepare" on an undefined value
    >> at /usr/lib/perl5/vendor_perl/5.8.2/i686-linux/Mysql.pm line 169.

    >
    > Please post a minimal but complete script that reproduces the
    > symptoms.
    >
    > This and much other valuable advice can be found in the posting
    > guidelines.
    >

    Sorry, I found out my mistake by myself, The problem occured already when I
    tried to open the database. The opening command was in error.
    But it didn't give me any message that this command failed. And as I was
    unsure about the dateformat from the beginning and as the error gave me
    some indication through the prepare method and that there where some
    undefined value, I only could think about this date. Stupid of course
    but...
    It working now and by the way, you thought it didn't look like valid SQL,
    hmm, I think it is and it's working. What was it that you didn't like?

    Thank you anyway.
    /CB
     
    Captain Beefart, Jul 30, 2004
    #3
  4. Captain Beefart <> writes:

    > Brian McCauley wrote:
    >
    > > Captain Beefart <> writes:


    > >> INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)

    > >
    > > That doesn't look like valid SQL to me.


    > It working now and by the way, you thought it didn't look like valid SQL,


    SQL doesn't have bareword strings so you need quotes around Beefart.
    You probably need them around 20040729134000 too.

    > hmm, I think it is and it's working. What was it that you didn't like?


    This may mean mysql does have bareword strings. Using bareword
    strings, except interactively is a bad idea. That's why we always
    recommend that you switch them off in Perl. The same goes for SQL.
    It may be handy to have bareword strings in a command line tool but
    you shouldn't be using them in programatically generated SQL.

    --
    \\ ( )
    . _\\__[oo
    .__/ \\ /\@
    . l___\\
    # ll l\\
    ###LL LL\\
     
    Brian McCauley, Jul 30, 2004
    #4
  5. Captain Beefart

    Matt Garrish Guest

    "Brian McCauley" <> wrote in message
    news:...
    > Captain Beefart <> writes:
    >
    > > Brian McCauley wrote:
    > >
    > > > Captain Beefart <> writes:

    >
    > > >> INSERT INTO cme (date, name) VALUES (20040729134000, Beefart)
    > > >
    > > > That doesn't look like valid SQL to me.

    >
    > > It working now and by the way, you thought it didn't look like valid

    SQL,
    >
    > SQL doesn't have bareword strings so you need quotes around Beefart.
    > You probably need them around 20040729134000 too.
    >
    > > hmm, I think it is and it's working. What was it that you didn't like?

    >
    > This may mean mysql does have bareword strings. Using bareword
    > strings, except interactively is a bad idea. That's why we always
    > recommend that you switch them off in Perl. The same goes for SQL.


    MySQL does not allow bareword strings either on the command line or via DBI
    (the string would be treated as the name of a column). If the date field is
    a bigint (for example, since I didn't see it specified anywhere what types
    he's using) the date he's entering wouldn't cause an error, but the unquoted
    string should kill the insert statement. My guess, however, since he wasn't
    checking whether the open succeeded is that he's not checking whether the
    insert succeeds.

    The query() call in his code would also suggest that he's not using the DBI
    module, but more likely the Mysql module to access the database, and I'm not
    sure if it supports placeholders (one more reason to switch). To the OP,
    this doesn't make it any less important to properly quote your variables. At
    the very least you should be using the built in quote method. Even if your
    code is only run by you, it will be more portable should you decide to
    change databases at some future point. And if the variables come from an
    untrusted source, it will stop someone from running malicious code on your
    database.

    Matt
     
    Matt Garrish, Jul 31, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    631
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,326
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    548
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    744
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    419
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page