tmbg has asked for the wisdom of the Perl Monks concerning the following question:

I'm using Tie::DBI to insert records into a MySQL database with a hash All is well, except inserting dates doesn't work properly 

use Tie::DBI;

my %user_table;
my $t = tie %user_table,'Tie::DBI', {
   db       => "mysql:music_server",
   user     => "root",
   password => "",
   table    => "users",
   key      => "nick",
   CLOBBER  => 1,
};
use POSIX qw(strftime);
my $now_string = strftime "%Y-%m-%d %H:%M:%S", localtime;

$user_table{$nick} = {
     hostmask    => $hostmask,
     level       => $level,
     credits     => $level,
     moderates   => $moderates,
     dateCreated => $now_string,
);

[download]

This works great, I get a brand new row inserted into my table, but no matter what I give to dateCreated, it always turns up NULL.

here is the description of the table 

mysql> describe users;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| nick        | varchar(15) |      | PRI |         |       |
| hostmask    | text        | YES  |     | NULL    |       |
| level       | int(11)     | YES  |     | 100     |       |
| credits     | int(11)     | YES  |     | 0       |       |
| moderates   | int(11)     | YES  |     | 3       |       |
| songsPlayed | int(11)     | YES  |     | 0       |       |
| dateCreated | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

[download]

Hope someone has some pointers!

Thanks!

Replies are listed 'Best First'.
Re: Tie::DBI question
by chromatic (Archbishop) on May 07, 2001 at 03:49 UTC
    I don't know how Tie::DBI handles quoting, but I'll assume it does. I don't see anything immediately wrong, so I would try an insert with the normal DBI module: 
    use POSIX qw(strftime);
my $now_string = strftime "%Y-%m-%d %H:%M:%S", localtime;

use DBI;
my $dbh = DBI->connect('DBI:mysql:foothills', 'root', '') or die;
my $sth = $dbh->prepare("INSERT INTO users values('test', 'test', 1, 1
+, 1, 1, ?)");
$sth->execute($now_string);
$dbh->disconnect();

    [download]
    You may want to make better fake values than I've used -- I just created a new table with a single datetime column.

    If that insert works, the problem's probably somewhere in Tie::DBI. Yuck.

    Update: The problem *is* in Tie::DBI, specifically within the _fields() method. It checks the allowed column names from the database, then issues the statement:

    my %fields = map { lc($_)=>1 } @{$sth->{NAME}};

    The lc there is the trouble.

    If you add WARN => 1 to the tie statement, you'll get a warning about an unknown field named 'dateCreated' in the initialization line.

    The solution is either to remove the lc() from Tie::DBI, or to use lowercase keys in the anonymous hash. Perhaps some databases don't support mixed case in field names, so Dr. Stein thought he would be safer than sorry.

[reply]
[d/l]
[select]
      It works fine with inserts from either the mysql shell or bits of DBI code like above, but it doesn't work at all from Tie::DBI no matter how I quote it. This is driving me insane. I know I can break down and do it using the standard DBI stuff, but I really want it to work with Tie::DBI.
[reply]
Re: Tie::DBI question
by tmbg (Novice) on May 07, 2001 at 07:37 UTC
    I (sorta) found the problem. Apparently, Tie::DBI can't handle ANY field that has a mixed case name. It wasn't the DATE that was tripping it up. dateCreated and songsPlayed wouldn't be set at all, whereas nick, hostmask, level, credits, and moderates would. changing them to songs_played and date_created fixed it.
[reply]
      From Tie::DBI: 
              # if we get here, we can fetch the names of the fields
        my %fields = map { lc($_)=>1 } @{$sth->{NAME}};

      [download]
      See that nice "lc"?

      I suspect that since field names are case insensitive on the DB side, but hashes are not, the author presumed that you'd always use lowercase. So I bet you didn't need to change your database... just your access code.

      -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]
      you could also set the CASESENSITIV flag to a non-zero number.
[reply]

Back to Seekers of Perl Wisdom