Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > update a mysql-db with date

Reply
Thread Tools

update a mysql-db with date

 
 
Captain Beefart
Guest
Posts: n/a
 
      07-29-2004
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.

 
Reply With Quote
 
 
 
 
Brian McCauley
Guest
Posts: n/a
 
      07-29-2004
Captain Beefart <(E-Mail Removed)> 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\\
 
Reply With Quote
 
 
 
 
Captain Beefart
Guest
Posts: n/a
 
      07-30-2004
Brian McCauley wrote:

> Captain Beefart <(E-Mail Removed)> 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

 
Reply With Quote
 
Brian McCauley
Guest
Posts: n/a
 
      07-30-2004
Captain Beefart <(E-Mail Removed)> writes:

> Brian McCauley wrote:
>
> > Captain Beefart <(E-Mail Removed)> 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\\
 
Reply With Quote
 
Matt Garrish
Guest
Posts: n/a
 
      07-30-2004

"Brian McCauley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Captain Beefart <(E-Mail Removed)> writes:
>
> > Brian McCauley wrote:
> >
> > > Captain Beefart <(E-Mail Removed)> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP General 11 11-08-2003 11:24 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C Programming 3 11-08-2003 09:07 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments