Обсуждение: Perl DBI question

Hi,

I'm trying to setup some scripts that will allow me to use Perl DBI to
INSERT into my table.  However, I have not had any success at all.  I'm
using perl CGI as well so I've granted ALL permissions on my table to
the apache user and I still can't INSERT.  I can, however, UPDATE and
SELECT on the table, just not INSERT.

Here is a piece of my code:

#--Establish the DB connection
#--Assign the DB name
$dbName = 'checkbook';

#--Connect to the Pg DB using DBI
my $dbh = DBI->connect("dbi:Pg:dbname=$dbName");

$sth = $dbh->do("INSERT INTO transactions
(date,description,amount,confirmation,nameid,typeid) VALUES
('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)")
;

Please HELP...........

Thanks,
Mike

On Tue, Jun 17, 2003 at 16:51:33 -0500,
  "Kovalcik, Mike A [ITS]" <mkoval01@sprintspectrum.com> wrote:
>
> $sth = $dbh->do("INSERT INTO transactions
> (date,description,amount,confirmation,nameid,typeid) VALUES
> ('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)")
> ;

Most likely you want single quotes around $nameid and $typeid.

> I'm trying to setup some scripts that will allow me to use Perl DBI to
> INSERT into my table.  However, I have not had any success at all.  I'm
> using perl CGI as well so I've granted ALL permissions on my table to
> the apache user and I still can't INSERT.  I can, however, UPDATE and
> SELECT on the table, just not INSERT.
>
> Here is a piece of my code:
>
> #--Establish the DB connection
> #--Assign the DB name
> $dbName = 'checkbook';
>
> #--Connect to the Pg DB using DBI
> my $dbh = DBI->connect("dbi:Pg:dbname=$dbName");
>
> $sth = $dbh->do("INSERT INTO transactions
> (date,description,amount,confirmation,nameid,typeid) VALUES
> ('$datePaid','$description','$amount','$confirmation',$nameid,$typeid)"
> )
> ;

What kind of error message are you getting?

With just a quick glance, I would say check your quoting.  i.e., is
$amount supposed to be quoted?

You can do parameter binding on $dbh->do statements also, like this:

$dbh->do("INSERT INTO transactions
(date,description,amount,confirmation,nameid,typeid) VALUES
(?,?,?,?,?,?)", undef, ($datePaid, $description, $amount,
$confirmation, $nameid, $typeid));

That takes care of all your quoting so you don't have to worry about it.

Also, you probably want to post this to the INTERFACES list.

--Jeremy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



You should not be using the "do" method, but the prepare/execute model
instead. Using placeholders can not only be more efficient but allows
quoting to happen properly. Here is a rewrite:

#--Establish the DB connection
#--Assign the DB name
my $dbName = 'checkbook';
my $dbuser = "joe";
my $dbpass = "sixpak";

my $dbh = DBI->connect("dbi:Pg:dbname=$dbName", $dbuser, $dbpass, {AutoCommit=>1, RaiseError=>1})
  or die "Could not connect to the database: $DBI::errstr\n";

my $SQL = "
INSERT INTO transactions (date,description,amount,confirmation,nameid,typeid)
VALUES                   (?   ,?          ,?     ,?           ,?     ,?     )";

my $sth = $dbh->prepare($SQL);

my $count = $sth->execute($datePaid,$description,$amount, $confirmation,$nameid,$typeid);

print "Insert count: $count\n";


It is hard to tell why your inserts are going wrong without more information, but
the RaiseError should at least help catch some obvious errors.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200306181337

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+8KUHvJuQZxSWSsgRAhWoAJ9/aw9AaCMa5vGvEpvujEEBm4iBXQCggSSw
jPaGcdMt9Qq9XeJqttvdX48=
=x1nu
-----END PGP SIGNATURE-----

setting RaiseError to true  is the way to go in DBI.
it catches most of the error and prints in apache error log.

if RaiseError = 1 one does not have to do the explicit
die after the connect even i think

regds
mallah,

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> You should not be using the "do" method, but the prepare/execute model  instead. Using
> placeholders can not only be more efficient but allows  quoting to happen properly. Here is a
> rewrite:
>
> #--Establish the DB connection
> #--Assign the DB name
> my $dbName = 'checkbook';
> my $dbuser = "joe";
> my $dbpass = "sixpak";
>
> my $dbh = DBI->connect("dbi:Pg:dbname=$dbName", $dbuser, $dbpass, {AutoCommit=>1,
> RaiseError=>1})
>  or die "Could not connect to the database: $DBI::errstr\n";
>
> my $SQL = "
> INSERT INTO transactions (date,description,amount,confirmation,nameid,typeid)  VALUES
>         (?   ,?          ,?     ,?           ,?     ,?     )";
>
> my $sth = $dbh->prepare($SQL);
>
> my $count = $sth->execute($datePaid,$description,$amount, $confirmation,$nameid,$typeid);
>
> print "Insert count: $count\n";
>
>
> It is hard to tell why your inserts are going wrong without more information, but  the
> RaiseError should at least help catch some obvious errors.
>
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200306181337
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE+8KUHvJuQZxSWSsgRAhWoAJ9/aw9AaCMa5vGvEpvujEEBm4iBXQCggSSw
> jPaGcdMt9Qq9XeJqttvdX48=
> =x1nu
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to
> increase your free space map settings



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/