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

I am trying to insert form values into a MySQL databse.
Can anybody see what I am doing wrong?

I am not sure of the perl syntax yet!!!!!

BR Soren

#******************CODE*********************
#!/usr/local/bin/perl -wT

use strict;
use CGI;
use DBI;

my $cgi = new CGI;
my $fornavn = $cgi->param("fornavn");
my $efternavn = $cgi->param("efternavn");
my $cpr = $cgi->param("cpr");
my $adresse = $cgi->param("adresse");
my $zip = $cgi->param("zip");
my $city = $cgi->param("city");
my $tjenestested = $cgi->param("tjenestested");

my $dbh = DBI->connect("um","","","mysql") ||
    die "Could not connect: $DBI::errstr\n";

my $sth = $dbh->prepare(INSERT into people (fornavn, efternavn, cpr, a
+dresse, zip, city, tjenestested) values ($fornavn, $efternavn, $cpr, 
+$adresse, $zip, $city, $tjenestested));
$sth->execute();
$sth->finish();

$dbh->disconnect;

print "Done\n";

[download]

Edit Masem 2001-10-15 - CODE tags, removed excess BR tags

Replies are listed 'Best First'.
Re: dbi insert
by merlyn (Sage) on Oct 15, 2001 at 18:33 UTC
    Replace: 
    my $sth = $dbh->prepare(INSERT into people (fornavn, efternavn, cpr, a
+dresse, zip, city, tjenestested) values ($fornavn, $efternavn, $cpr, 
+$adresse, $zip, $city, $tjenestested)); 
$sth->execute(); 
$sth->finish();

    [download]
    with 
    my $sth = $dbh->do(q{
    INSERT INTO people (fornavn, efternavn, cpr, adresse, zip, city, t
+jenestested)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  }, undef,
  $fornavn, $efternavn, $cpr, $adresse, $zip, $city, $tjenestested);

    [download]

    -- Randal L. Schwartz, Perl hacker

[reply]
[d/l]
[select]
Re: dbi insert
by davorg (Chancellor) on Oct 15, 2001 at 18:47 UTC
    my $dbh = DBI->connect("um","","","mysql") ||
    die "Could not connect: $DBI::errstr\n";

    This should be:</> 

    my $dbh = DBI->connect("DBI:mysql:$host", $user, $pass) ||
    die "Could not connect: $DBI::errstr\n";

    [download]

    Your SQL statement needs to be a quoted string:

    my $sth = 
  $dbh->prepare("INSERT into people (fornavn, efternavn, 
                                     cpr, adresse, zip, 
                                     city, tjenestested) 
                 values ($fornavn, $efternavn, $cpr, 
                         $adresse, $zip, $city,   
                         $tjenestested"));

    [download]

    And I'm willing to bet that some of those column values should be quoted - but not knowing your schema, I can't tell which ones.

    Also, you should check the return code from all of your functions:

    my $sth = 
  $dbh->prepare("INSERT into people (fornavn, efternavn, 
                                     cpr, adresse, zip, 
                                     city, tjenestested) 
                 values ($fornavn, $efternavn, $cpr, 
                         $adresse, $zip, $city,   
                         $tjenestested"))
  || die $dbh->errstr;

$sth->execute || die $dbh->errstr;
$sth->finish  || die $dbh->errstr;

    [download]
    --
    <http://www.dave.org.uk>

    "The first rule of Perl club is you don't talk about Perl club."

[reply]
[d/l]
[select]
Re: dbi insert
by MZSanford (Curate) on Oct 15, 2001 at 18:33 UTC
    Just a few things i can mention, without actually knowing your error message (please include next time) ...

    1. your connect() has "um" where i would expect "dbi:mysql:SERVER", also, missing login and pass. Xomsthing line : DBI->connect("dbi:mysql:localhost",$user,$pass) || die "foo : $!\n";
    2. You need to quote your SQL in the prepare() statement.
    3. You may want to use $dbh->do() if you are doing inserts ... a bit easier to read.
    4. Lastly, i would suggest posting the actual problem you are having (error message, etc) so people can give more precise answers.
    The requirements change because they don't know what they want, or how much they own you.
[reply]
[d/l]
[select]
(jeffa) Re: dbi insert
by jeffa (Bishop) on Oct 16, 2001 at 01:58 UTC
    This question has been more than answered, but i wanted to point out that this code could be much more maintainable if you stored the names of your CGI param variables (which, if you notice, are most conveniently the same as your database field names) in an array: 
    my @fields = qw(fornavn efternavn cpr adresse zip city tjenestested);

my $cgi = new CGI;

# apply validation/error checking as needed
# in this case, if no param is found, undef is default
my @params = map { $cgi->param($_) || undef } @fields;

# connect to database . . .

my $sth = $dbh->prepare(
   'INSERT into people(' .
   join(',', @fields), ') values(' .
   join(',', map { '?' } @fields), ')'
) or die $dbh->errstr;

$sth->execute(@params) or die $dbh->errstr;

    [download]
    It looks a bit intimidating, but you only need to worry about changing ONE line (the first one in this example) if you add or subtract fields. Read up on map and join for more info. :)

    jeffa

[reply]
[d/l]
      I have tried most of what has been suggested, but I still cannot get it to work.
      It sends back NO error code, but it does not insert any data in my db

      ******************code*********************

      #!/usr/local/bin/perl -wT

      use strict;
      use CGI;
      use DBI;

      my $cgi = new CGI;
      my $fornavn = $cgi->param("fornavn");
      my $efternavn = $cgi->param("efternavn");
      my $cpr = $cgi->param("cpr");
      my $adresse = $cgi->param("adresse");
      my $zip = $cgi->param("zip");
      my $city = $cgi->param("city");
      my $tjenestested = $cgi->param("tjenestested");


      my $dbh = DBI->connect("DBI:mysql:um","root","") ||
      die "Could not connect: $DBI::errstr\n";

      my $sth = $dbh->prepare("INSERT into people (fornavn,
      efternavn, cpr, adresse, zip, city, tjenestested)
      values ($fornavn, $efternavn, $cpr, $adresse, $zip,
      $city, $tjenestested)");

      $sth->execute();
      $sth->finish();


      $dbh->disconnect;

      print "Content-type: text/html\n\n";
      print "<HTML>";
      print "<HEAD>";
      print '<META HTTP-EQUIV="Refresh" CONTENT="1; URL=um.htm">';
      print "</HEAD>";
      print '<BODY bgcolor="#333366">';
      print "</BODY>";
      print "</HTML>";

      ******************code*********************
[reply]
        Try changing the connect call to: 
        my $dbh=DBI->connect('DBI:mysql:um', 'root', '',
       {AutoCommit=>1, RaiseError=>1});

        [download]
        The RaiseError value means that any subsequent DBI error will cause your program to die with an error message. AutoCommit explicitly controls whether you require a commit call. mysql used not to support transactions, so committing was irrelevant. I don't know if this is still true.

[reply]
[d/l]
[select]
Re: dbi insert
by ralphie (Friar) on Oct 15, 2001 at 21:51 UTC
    mysql has a quirky syntax for the insert statement. while the previous posts are correct to point out that you should use placeholders, i think you'll have better luck with this syntax

    $dbs=$dbh->prepare("insert into people set (fornavn=?,efernavn=?...)"); and then
    execute the statement handle with the relevant scalars as merlyn suggests.

[reply]

Back to Seekers of Perl Wisdom