http://qs1969.pair.com?node_id=321814

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

Fellow monasterians, I have successfully used placeholders in previous projects, but running into a problem with this one. Getting the Can't call method 'prepare' on an undefined value at... error. In checking with the DBI docs on CPAN I see nothing unusual.

My guess is that the fields in my database are not matching up with the placeholders. My fields are named exactly as the scalars you see in the code, and the empty values you see at the beginning and end of the list in the 'execute' statement are for the auto-incremented id and a TIMESTAMP.

However, no matter how I adjust the number of ? marks or execute values, I get the error (in the code below their are 17 ?'s and 17 values). A recent node by grinder that mentions the placeholder for an auto incre. id is not necessary (however, I have found in other scripts this was necessary--weird). What about the TIMESTAMP? Does it need a placeholder? Anyway, here's the suspect code. Thanks! 
    
$sth = $dbh->prepare (q{
   INSERT INTO sponsor VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
);
$sth->execute ('',
   $name,            
   $address,        
   $city,         
   $state,         
   $zip,             
   $country,        
   $contact,     
   $title,         
   $phone,         
   $bestime,     
   $email,        
   $license,     
   $type,         
   $capacity,     
   $distributor,
   ''
   );

[download]
Update:
Something injunjoel said caused me to go back and discover my problem was coming before the prepare and execute statements. I learned a valuable lesson. But I also learned a lot about placeholders, and I'm indebted again to the monastery for wise replies. Problem solved and code improved. Thanks. Also, fixed references from injunjoe to injunjoel (I need new glasses).

—Brad
"A little yeast leavens the whole dough."

Replies are listed 'Best First'.
Re: Using placeholders in MySQL returning an error
by injunjoel (Priest) on Jan 16, 2004 at 16:43 UTC
    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
[reply]
[d/l]
[select]
      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]
      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]
Re: Using placeholders in MySQL returning an error
by Abigail-II (Bishop) on Jan 16, 2004 at 16:13 UTC
    Most likely, your prepare fails. But you aren't testing for it, let alone are you inspecting a possible error message.

    Abigail

[reply]
      I was testing with || die $DBI::errstr;but it wasn't turning up anything different than my  use strict; use warnings; use CGI::Carp qw(fatalsToBrowser); was. I'm not sure what the difference is, i.e., should I expect more detail from the former test? I apologize for not showing all my code in the first post.

      —Brad
      "A little yeast leavens the whole dough."
[reply]
[d/l]
[select]
Re: Using placeholders in MySQL returning an error
by hardburn (Abbot) on Jan 16, 2004 at 16:56 UTC

    . . . mentions the placeholder for an auto incre. id is not necessary (however, I have found in other scripts this was necessary--weird).

    The node in question was correct. Not only is setting an auto increment field unnecessary, it's undesirable without a really good reason. I suspect the other scripts you mentioned were using a database with a bad schema.

    $sth = $dbh->prepare (q{
   INSERT INTO sponsor VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
);

    [download]

    After you call prepare, add a or die "Couldn't prepare statement: " . $dbh->errstr;, or turn RaiseError on when you call DBI->connect (unless you already have). A similar note goes for the execute (which can fail if you have an incorrect number of parameters).

    You have a huge number of placeholders there. Instead of a static SQL statement, you can generate the SQL at runtime to ensure you have the correct number of placeholders for your data. This can be done by pulling all the parameters you set out of execute statement and into a hash. The keys of the hash are the name of the fields, and the values are the data you put in for that field. Example:

    my %sql_data = (
    name     => $name, 
    address  => $address, 
    # and so on
);
my $sql = q/INSERT INTO sponsor (/ . 
    join(',', keys %sql_data) . 
    q/) VALUES (/ . 
    join(',', ('?') x keys %sql_data) . 
    q/)/;
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
# values() is guarenteed to come out in the same order as keys()
$sth->execute(values %sql_data) or die $dbh->errstr;
$sth->finish();

    [download]

    ----
    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]

      On a note of maintinability/style.. it's better to use complete inserts i.e.
       INSERT INTO tablex (field1, field2, field3) VALUES ('x','y','z')
      over incomplete inserts i.e.
       INSERT INTO tablex('x','y','z')
      Performing the following:
       ALTER TABLE tablex ADD field4 varchar(20)
      breaks your incomplete insert, since you are not specifying directly which fields the values being inserted are mapped to, it expects your insert to include a value for field4.

      If you have an app that uses this table but will never make use of the new field: field4, then you have no need to alter that app. Leaving incomplete inserts lying around in a script is a recipe for disaster.

      p.s. I learned this the hard way.
      Grygonos
[reply]
[d/l]
[select]
      Amazing hardburn. I tried the hash and bingo. Gonna love this solution for those huge tables. You don't know how many times I counted those ?'s to make sure I had the right number. And they look kinda funny all strung out in the code. Anyway, elegant solution. Thanks.

      —Brad
      "A little yeast leavens the whole dough."
[reply]
Re: Using placeholders in MySQL returning an error
by Cine (Friar) on Jan 16, 2004 at 16:38 UTC
    Abigail-II already answered you should check the error values from you calls.
    But to answer your other questions.
    An autoincrement can be either NULL, 0 or left out all of which gives the desired value. If you give a specific value it will use that.
    TIMESTAMP is similar. (I may be wrong about the value '0', though)


    T I M T O W T D I
[reply]
[d/l]

Back to Seekers of Perl Wisdom