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

Esteemed Monks,

I wrote a Perl program wich uses PostgreSQL. This program was running well for months until yesterday, when my SysAdmin upgraded the DBD::Pg module for the current version (1.40).

Now, where I have 

my $sql = "
 LOCK TABLE tabreserves IN SHARE MODE;
 SELECT 
  tabreserves.date,
  tabreserves.hour_i,
  tabreserves.hour_e,
  tabreserves.idreserve,
  tabreserves.fk_idfield,
  tabfields.imped,
  tabfields.design

  FROM tabreserves
  LEFT JOIN tabfields ON
   tabfields.idfield = tabreserves.fk_idfield
  WHERE date = ? AND (hour_i < ? AND hour_e > ?)
   AND (fk_idfield = ? OR ? = ANY (imped))
  LIMIT 1
";

[download]
Raises 2 errors:

1st 

DBD::Pg::st execute failed: ERROR:  cannot insert multiple commands in
+to a prepared statement

[download]

If I delete the first line LOCK TABLE tabreserves IN SHARE MODE;, raises another error.

2nd 

DBD::Pg::st execute failed: ERROR:  column "hour_i" is of type integer
+ but expression is of type character varying
HINT:  You will need to rewrite or cast the expression.

[download]

Needless to say that I have _many_ statements wich uses the same structure - e.g. multiple commands in a prepared statement and not casting expressions, since that wasn't mandatory before.

When we (me and the sysadmin) saw the errors we thought: "well... time to upgrade to PostgreSQL 8!". We were using 7.4. We did the upgrade. But the errors persist.

What am I missing here? Do I really need to cast expressions? and why can't I use multiple statements now?

Thanks,
Miguel

Replies are listed 'Best First'.
Re: Need help with DBD::Pg
by injunjoel (Priest) on Feb 23, 2005 at 19:46 UTC
    Greetings all,
    From what I have read of the documentation here it looks as though you will need to use $sth->bind_param() prior to your $sth->execute() in order to correct your varchar vs. int problem.
    As for your table locking issue (e.g. your multiple commands issue), it appears as though you will have to split those out (I wish I could point to something in the documentation but I was unable to locate even a part of your error in the docs or the source).
    HTH

    -InjunJoel
    "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo
[reply]
[d/l]
[select]
      I tried that too: 
      use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);

my $sql = "...";
$sth->prepare($sql);

$sth->bind_param(1,$date, {pg_type => PG_DATE});
$sth->bind_param(etc.)...

$sth->execute();

      [download]
      Then, I get this error: Bareword "PG_DATE" not allowed while "strict subs"
[reply]
[d/l]
[select]
Re: Need help with DBD::Pg
by thor (Priest) on Feb 23, 2005 at 19:34 UTC
    Does the statement prepare fine? Do you have RaiseError turned on at the database handle level? Does setting the trace on the database handle give you anything meaningful?

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

[reply]

      > Does the statement prepare fine?
      As far as I can see, that is the problem. It does not prepare fine.

      >Do you have RaiseError turned on at the database handle level?
      Yes: $dbh->{RaiseError} = 1;

      >Does setting the trace on the database handle give you anything meaningful?
      Good question. I don't know. Never done that. And don't know how to do that.

      The strange thing, at least to me, is that it was working quite well before the upgrade.

[reply]
[d/l]
        As far as I can see, that is the problem. It does not prepare fine.
        I got the impression from your original post that it failing on the execute. If it is indeed the prepare, then nothing to see here
        >Does setting the trace on the database handle give you anything meaningful?
        Good question. I don't know. Never done that. And don't know how to do that.
        Take a look at the DBI perldoc for a heading called "Tracing". There's no way that I can do it justice here.

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

[reply]
Re: Need help with DBD::Pg
by eclark (Scribe) on Feb 23, 2005 at 21:44 UTC

    Take a look at the pg_server_prepare attribute in the DBD::Pg docs.

    That feature prevents you from running multiple statements in one prepare. Something that you shouldnt do anyway, since by nature prepared statements are to be reused

    On another note, why bother locking?

    update: What I mean is, you're just going to have to change your code. It was bad that DBD::Pg let you do this in the old version.

[reply]
      You're right.
      I tried with $dbh->{pg_server_prepare} = 1; but with no success too.

      For me, that was a Big Change.

      Answering your question, on another statement, I have: 

      $sql = "INSERT INTO tabreserves(
  date_reserve, hour_i, hour_e, date,
  fk_idclient, fk_idfield, fk_iduser,name_temp,
  value, vat_perc, vat_val,desc_perc, desc_val,
  total, nhours)
VALUES(NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?);
SELECT currval('public.tabreserves_idreserve_seq'::text)

$sth = $dbh->prepare($sql);
        
$sth->execute(
<values_goes_here>
);
$sth->bind_columns(\(my $new_id_reserve));
$sth->fetch();

      [download]
      This code was working before the upgrade. Now it's broken. It raises errors like the ones I posted before.
[reply]
[d/l]
[select]

        That code is incorrectly designed. Another process could call nextval() between the INSERT and SELECT. Sequences are not atomic in transactions.

        Sequences are to be used by calling nextval() first and then inserting that value in a second statement. The database guarantees that no clients will retrieve the same nextval.

[reply]

Back to Seekers of Perl Wisdom