http://www.perlmonks.org?node_id=11159892


in reply to Re^2: Perl and Mysql Queries
in thread Perl and Mysql Queries

It's always a good idea (and good defensive programming) to check the results of things as you go. So, I'd write 

my $dbh = DBI->connect('dbi:mysql:jobs_db', "Poster',
'Poster Password');
defined $dbh or die "Unable to connect to DB: " . $dbh->errstr;

my $sth =$dbh->prepare("INSERT INTO jobs (JobID,EmployerID,
EmployerName,Title,City,StateProvince,Description,Function,
Sector,Country,Posted) Values(?,?,?,?,?,?,?,?,?,?,?)");
defined $sth or die "Unable to prepare insert: " . $dbh->errstr;

$sth->execute($JobID,$EmployerID,$EmployerName,$Title,$City,
$StateProvince,$Description,$Function,$Sector,$Country,$Posted)
or die "Failed to execute insert: " . $dbh->errstr;

[download]
The die statements will leave something informative in your log file, and will give you information about what's failing. And I'm also going to encourage you to add 
use strict;
use warnings;

[download]
at the top of all of your scripts. Generally, your scripts should compile cleanly, and should not put any warnings in the error log. If there are warnings, fix them.

Alex / talexb / Toronto

Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Replies are listed 'Best First'.
Re^4: Perl and Mysql Queries
by Milti (Beadle) on Jun 11, 2024 at 12:27 UTC

    Now gettin these errors: Tue Jun 11 05:09:26 2024 error client 73.21.226.229 DBI connect('jobs_db','poster',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at E:\\Pinnacle\\DiversityLink\\cgi-bin\\job_post_test.pl line 36., referer: http://diversitylink.com/ Tue Jun 11 05:09:26 2024 error client 73.21.226.229 Can't call method "prepare" on an undefined value at E:\\Pinnacle\\DiversityLink\\cgi-bin\\job_post_test.pl line 37., referer: http://diversitylink.com/

    Historically the DBI connect statement worked fine, at least with Mysql 5.7. I haven't a clue as to what the 'Can't call method "prepare' error means by 'undefined value'.

[reply]
      I haven't a clue as to what the 'Can't call method "prepare' error means by 'undefined value'.

      It's telling you that you have done effectively this:

      $ perl -E '$x->prepare("foo");'
Can't call method "prepare" on an undefined value at -e line 1.

      [download]

      Your connection statement failed and therefore you don't have a defined database handle. So when you try to call the prepare method you are doing it on something which is undefined as $x is in the snippet above.

      All of this is because the connection failed. Fix the first problem first, as always.

      Which version of DBD::mysql are you running?

      🦛

[reply]
[d/l]
[select]
[reply]
        Can't call method "prepare" on an undefined value at E:\\Pinnacle\\DiversityLink\\cgi-bin\\job_post_test.pl line 37

      You have to admit, that's a pretty clear explanation of what's gone wrong. To translate that from geek-speak to English, what it's saying is that Perl can't call the prepare method on an undefined value. And this goes back to the post I did yesterday that encouraged you to program defensively -- always check what the return status is on calls like this, in order to avoid failures like the one you're seeing.

      Imagine you're doing a simple task, like frying some eggs for breakfast. You need to get the eggs out of the fridge, find the pan, turn the ring on, wait for it to warm up, crack open the eggs, season to taste, and serve. Some of these steps depend on the previous steps. If you skip the step where you turn on the ring, the eggs aren't going to cook. If you're out of eggs, nothing much is going to happen -- the pan's warm, you have your seasoning ready, but no breakfast.

      The DBI documentation is pretty clear (and I've been using it for 25 years); you have to 1. Connect to the database; if that doesn't work, nothing else matters. 2. Prepare the statement handle; you may have a DB connection, but if your prepare fails, you can't do anything. 3. Execute the query or command, and check the return status; it may have failed (wrong number of bind variables, for example). Sometimes, 4. You also do a fetch using the statement handle. Afterwards, you clean up after yourself and 5. Finish with the statement handle and Disconnect from the database.

      I would add that for a test script, it's fine to put the credentials into the script, but for anything you're considering having anybody else use, I strongly advise you put the credentials in a separate file that is not put into version control. Credentials should never be put into version control.

      Alex / talexb / Toronto

      Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

[reply]

        Curiously, the day after posting this, I went to make my breakfast of eggs, bacon and a toasted english muffin .. and realized I was out of both eggs and english muffins. That was weird. Then I thought of my post and laughed. I'm an odd duck, I guess.

        Alex / talexb / Toronto

        Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

[reply]
      it means your DBI->connect still didn't work and so your $dbh ended up undefined, which makes $dbh->prepare meaningless, because you're ending up trying to call the "prepare" method of some undefined object instead of the "prepare" method of a database handle.
[reply]
[d/l]
[select]
       failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client

      I would first double check that you are able to connect to that database with the given user and password with another method. For example, with the command-line "mysql" client. If that doesn't work you may need to fix the credentials. Also upgrade DBI and DBD::mysql if they are old.

[reply]

In Section Seekers of Perl Wisdom