in reply to Using placeholders in MySQL returning an error

Greetings all,
Although your Can't call method 'prepare' on an undefined value at... Error indicates to me that it is a problem with your $dbh not being initialized (perhaps post more code so we can see), I would also like to mention that you can alter your INSERT statement to see if it is in fact your SQL.
Rather than:
INSERT INTO sponsor VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[download]
You could also do:
INSERT INTO sponsor(name, address, city, state, zip, country, contact,
+ title, phone, bestime, email, license, type, capacity, distributor) 
+VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[download]
This way you can map in the values you want to insert and you don't have to worry about AUTO_INCREMENT or TIMESTAMP fields since anything not specified in the field names list will get the default value from the databases column definitions. As well you can also do (update: at least in MySQL although its not standard SQL):
INSERT INTO sponsor SET name=?, address=?, city=?, state=?, zip=?, country=?, contact=?, title=?, phone=?, bestime=?, email=?, license=?, type=?, capacity=?, distributor=? Which again allows you to specify which columns you want to insert and columns not specified get default values.
Just some other approaches to your insert.
However back to your error I would check that your $dbh is being successfully initialized. 
my $dbh = DBI->connect('DBI:yourdb_driver:your_dbname','user','passwor
+d') || die $DBI::errstr;

[download]
Should do it.
side note: I have also seen people set AUTO_INCREMENT columns to 'NULL' to get values back... just a thought.
Hope that helps

Replies are listed 'Best First'.
Re: Re: Using placeholders in MySQL returning an error
by mpeppler (Vicar) on Jan 16, 2004 at 17:01 UTC
    I would avoid the  insert ... set foo = ?, ... syntax, as it is completely non-standard SQL.

    Michael

[reply]
[d/l]
      Michael, thanks, but I would love to hear more. Code samples? Thanks.

      —Brad
      "A little yeast leavens the whole dough."
[reply]
        Well - injunjoel mentioned the possibility of using the INSERT into foo SET bar = ?, ... syntax to execute an insert with placeholders. I just wanted to point out that using INSERT ... SET ... is not part of the SQL standard (AFAIK). It may be tempting to use because it is of course very similar to UPDATE ... SET ... WHERE, but your code will not be portable to other database engines if you decide to use it.

        As others have pointed out, it is always good to be explicit when writing SQL statements. This means writing 

        INSERT into the_table(foo, bar, baz) values(...)

        [download]
        instead of 
        INSERT into the_table values(...)

        [download]
        and 
        SELECT foo, bar, baz FROM the_table WHERE ...

        [download]
        instead of 
        SELECT * FROM the_table WHERE ...

        [download]
        It requires a little more typing, but it clarifies things, and will make errors more obvious.

        Michael

[reply]
[d/l]
[select]

        Don't see what code samples could be provided besides what is already above. It's just as mrpeppler said: INSERT INTO foo SET . . . works on MySQL, but may not work anywhere else (it doesn't on PostgreSQL, for instance). Which is unfortunate, IMHO, because the SET syntax makes it easier to figure out which parameters are being set to what in a large statement.

        ----
        I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
        -- Schemer

        : () { :|:& };:

        Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
Re: Re: Using placeholders in MySQL returning an error
by bradcathey (Prior) on Jan 16, 2004 at 18:07 UTC
    Error indicates to me that it is a problem with your $dbh not being initialized
    Thanks injunjoel! You were right. It had nothing to do with my prepare statement. And I was testing with  || die $DBI::errstr; but it wasn't turning up anything different than my use warnings; use CGI::Carp qw(fatalsToBrowser); was. I feel badly that it had nothing to do with the prepare but did I ever learn a lot about placeholders from replying monks. Thanks again for the nudge injunjoel and to all monks.

    Update: don't know how my reply to injunjoel ended up here. But apologies.

    —Brad
    "A little yeast leavens the whole dough."
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom