Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Perl and Mysql Queries

by Milti (Beadle)
on Jun 10, 2024 at 17:13 UTC ( [id://11159883]=perlquestion: print w/replies, xml ) Need Help??

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

I am using Mysql 8.4 and am trying to use a form to provide info to a Perl cgi to insert data into a Mysql database table. Previous I used Mysql 5.7 and was successful with these statements:

use DBI; print "Content-type: text/html\n\n"; use POSIX qw(strftime); $shortdate = strftime "%B %d, %Y", localtime; $Posted=$shortdate; $JobID=param('JobID'); $EmployerID=param('EmployerID'); $EmployerName=param('EmployerName'); $Title=param('Title'); $City=param('City'); $StateProvince=param('StateProvince'); $Description=param('Description'); $Function=param('Function'); $Sector=param('Sector'); $Country=param('Country'); my $dbh = DBI->connect('dbi:mysql:jobs_db','poster','posterpasswd'); my $sth =$dbh->prepare("INSERT INTO jobs (JobID,EmployerID,EmployerNam +e,Title,City,StateProvince,Description,Function,Sector,Country,Posted +) Values(?,?,?,?,?,?,?,?,?,?,?)"); $sth ->execute($JobID,$EmployerID,$EmployerName,$Title,$City,$StatePro +vince,$Description,$Function,$Sector,$Country,$Posted);
Everything worked! Now it doesn't work. I have gotten an error message noting syntax error near my $sth=$dbh->prepare and another one indicating the use of place holders has been deprecated and cannot be used. I'm at a lost for ideas. Can anyone provide the currently acceptable way to use Perl statements to insert the data into the table? I would truly appreciate any help offered.

Replies are listed 'Best First'.
Re: Perl and Mysql Queries
by Corion (Patriarch) on Jun 10, 2024 at 17:18 UTC

    Can you please post the complete script, or ideally, a pared-down version that still compiles but also reproduces the error?

    It seems your script uses CGI, but at least that line is missing.

      It might also help if you post the complete and exact error messages that you are seeing.

      EDIT: BTW, at least in your post, your $Sector variable in your $sth->execute statement has a non-ascii character in it.

Re: Perl and Mysql Queries
by talexb (Chancellor) on Jun 10, 2024 at 18:37 UTC
      I have gotten an error message noting syntax error near my $sth=$dbh->prepare and another one indicating the use of place holders has been deprecated and cannot be used.

    OK -- sorry to hear that things have gotten worse. In order to better help you, we'll need a) the complete script that's failing (assuming it's not 400 lines long), and b) the error(s) that you got. Ideally, the lines on the errors will line up with the source code.

    You haven't mentioned if the data made it to the database OK (but it sounds like it did). I'm also wondering if you tried to insert the same row twice, causing the database to reject the second insert because it made for a duplicate primary key.

    Alex / talexb / Toronto

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

      Here are the error messages:

      [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Premature en +d of script headers: job_post_test.pl, referer: http://diversitylink. +com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Bareword fou +nd where operator expected at E:\\Pinnacle\\DiversityLink\\cgi-bin\\j +ob_post_test.pl line 38, near "my$sth =$dbh->prepare("INSERT", refere +r: http://diversitylink.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] (Might be +a runaway multi-line "" string starting on line 37), referer: http:// +diversitylink.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Semicolon se +ems to be missing at E:\\Pinnacle\\DiversityLink\\cgi-bin\\job_post_t +est.pl line 38., referer: http://diversitylink.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Use of ?PATT +ERN? without explicit operator is deprecated at E:\\Pinnacle\\Diversi +tyLink\\cgi-bin\\job_post_test.pl line 39., referer: http://diversity +link.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] syntax error + at E:\\Pinnacle\\DiversityLink\\cgi-bin\\job_post_test.pl line 38, n +ear "my$sth =$dbh->prepare("INSERT INTO ", referer: http://diversityl +ink.com/ [Mon Jun 10 12:18:31 2024] [error] [client 73.21.226.229] Search patte +rn not terminated or ternary operator parsed as search pattern at E:\ +\Pinnacle\\DiversityLink\\cgi-bin\\job_post_test.pl line 39., referer +: http://diversitylink.com/

      Here is the entire code:

      #!C:/Perl/bin/perl -w ##post_jobs.pl use CGI qw(:standard); use DBI; print "Content-type: text/html\n\n"; ###use POSIX qw(strftime); ####$shortdate = strftime "%B %d, %Y", localtime; $JobID=param('JobID'); $EmployerID=param('EmployerID'); $EmployerName=param('EmployerName'); $Title=param('Title'); $Logo=param('Logo'); $City=param('City'); $StateProvince=param('StateProvince'); $Description=param('Description'); $Function=param('Function'); $Sector=param('Sector'); $Country=param('Country'); $Posted=param('Posted'); if ($JobID eq ""||$EmployerID eq ""||$EmployerName eq ""||$Title eq "" +||$Logo eq ""||$City eq ""||$StateProvince eq ""||$Description eq ""| +| $Function eq ""||$Sector eq ""||$Country eq ""||$Posted eq ""||$Logo e +q "") { print "The FORM is INCOMPLETE"; } else { my$dbh = DBI->connect('dbi:mysql:jobs_db', "Poster','Poster Password') +; my$sth =$dbh->prepare("INSERT INTO jobs (JobID,EmployerID,EmployerName +,Title,City,StateProvince,Description,Function,Sector,Country,Posted) Values(?,?,?,?,?,?,?,?,?,?,?)"); $sth ->execute($JobID,$EmployerID,$EmployerName,$Title,$City,$StatePro +vince,$Description,$Function,$Sector,$Country,$Posted); $sth ->finish(); $dbh->disconnect (); } #!/usr/local/bin/perl print "Your Ad Was Posted.\n"; exit;
        At line 37 where you connect you have "Poster'. Perhaps that is just a typo in your post? If I run that code I get the same error as you posted which is caused by that incorrect quoting.
        Bareword found where operator expected at /tmp/b.pl line 38, near "my$ +sth =$dbh->prepare("INSERT" (Might be a runaway multi-line "" string starting on line 37) Semicolon seems to be missing at /tmp/b.pl line 38. String found where operator expected at /tmp/b.pl line 39, near "print + "" (Missing semicolon on previous line?)

        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;
        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;
        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.

        Might be a runaway multi-line "" string starting on line 37

        might have been a clue!

        Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond
Re: Perl and Mysql Queries
by Milti (Beadle) on Jun 12, 2024 at 14:36 UTC

    Problem solved! Thanks for all the suggestions but I gave up and removed Mysql 8! Went back to 5.1 and everything works just as intended! So much for upgrades!!!

      If it helps, I've been using MariaDB recently.
      It's just about a drop-in replacement for MySQL and I haven't had any such issues with the latest version.
        just about a drop-in replacement for MySQL

        Since they forked, MySQL and MariaDB have diverged quite a bit...of course, they both support the core SQL language specification but that still leaves lots of room for differences.

        We use MySQL Workbench for creating visual ERDs and building the DDL for the databases. There isn't an alternative for MariaDB that I'm aware of. An immediate problem with using this for MariaDB is that the MySQL tool adds the VISIBLE keyword to indexes. MySQL uses VISIBLE and INVISIBLE whereas MariaDB has visible indexes (with no keyword) and IGNORED indexes.

        Plus, our main production database mostly uses the Aria table engine, which isn't available in MySQL. It also uses temporal tables which have a vastly different implementation between MySQL and MariaDB.

        In short, MariaDB is far from drop-in replacement for MySQL for anything beyond the simplest use case.

      If you decide to give it another try at some point, add mysql_ssl=1 to your data source like so:
      DBI->connect('dbi:mysql:mysql_ssl=1;database=jobs_db','Poster','Poster +passwd')

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11159883]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2024-07-17 23:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.