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

How do I include dots (.) in values to be sent to the MySQL DBI driver? For instance, the following snippet doesn't work...

$sth->prepare('INSERT INTO tablename SET value=?');

$sth->execute('hello.world');

[download]

It's nothing to with connecting, disconnecting, error handling and stuff, I'm more than happy with those, it's beacuse, in SQL, 'hello.world' means 'the value of the hello column in the world table'.

If I quote it, like $sth->execute("'hello.world'"); I end up with the quoted value (including quotes) in the database.

Replies are listed 'Best First'.
Re: DBI dots
by chromatic (Archbishop) on Oct 23, 2000 at 20:02 UTC
    It works for me: 
    my $sth = $dbh->prepare('INSERT INTO scratch SET text = ?');
$sth->execute('hello.world');
$sth->finish();

    [download]
    The result is that my scratch table had a new row with 'hello.world' in it.

    Otherwise, you might want to examine $DBI::errstr or set the RaiseError flags in the DBI constructor.

[reply]
[d/l]
Re: DBI dots
by agoth (Chaplain) on Oct 23, 2000 at 20:19 UTC
    Like chromatic illustrated,
    Prepare returns you a statement handle from your database handle, so it should be: 
    my $sth = $dbh->prepare;
$sth->execute;

    [download]

    not just sth->prepare, sth->execute which will never work.

[reply]
[d/l]
      Yeah, that was a mistake on my part when making this little example. It's correct in the real script. Like I said, I'm comfortable with DBI.

      So I actually concocted a little example to run, and it works. However, the original still isn't working, I'm trying to work out why not now.

[reply]
Re: DBI dots
by jptxs (Curate) on Oct 23, 2000 at 20:29 UTC

    update: hehehe I absent-mindedly fixed the dbh/sth error while reformatting this code and didn't even notice it : ) I also just dropped in the $sth = to make it reflect what it would really be like...

    Should work. try running the code with some error checks to sniff out the real problem like: 

    $sth = $dbh->prepare('INSERT INTO tablename SET value=?')
or die ("Couldn't prepare statement: ", $dbh->errstr);

$sth->execute('hello.world')
or die ("Couldn't execute statement: ", $sth->errstr);

    [download]

    "sometimes when you make a request for the head you don't
    want the big, fat body...don't you go snickering."
                                             -- Nathan Torkington UoP2K a.k.a gnat

[reply]
[d/l]
[select]
      I find setting RaiseError should be nearly mandatory for DBI. Either during connect: 
      $dhb = DBI->connect($dbi, $user, $pass, { RaiseError => 1 });

      [download]
      Or after the fact with the magic tied hash interface: 
      $dbh->{RaiseError} = 1;

      [download]
      Then you get an automatic die for each error return from DBI. Very cool.

      -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]
[select]

        well...

        I do a bunch of stuff that is not really needed to succeed interms of what success is for the whole program. I don't know if I want every whine from the database to kill me off - especially if it's something as trivial as a disconnect or something like that.

        "sometimes when you make a request for the head you don't
        want the big, fat body...don't you go snickering."
                                                 -- Nathan Torkington UoP2K a.k.a gnat

[reply]

Back to Seekers of Perl Wisdom