Re: Multiple command usage in DBD-Pg v 1.49

[Max Pyziur <pyz(at)brama(dot)com>]

Much thanks. That got me through the placeholder problem to bring up the 
next one. I now get a "cannot insert multiple commands into a prepared 
statement" error message.

The complete statement looks like this:
$PRICERPT1_SQL =   qq(SELECT pr_date, SUM(volume) AS tot_volume, 
SUM(open_interest) AS tot_oi
INTO TEMP oi_vol_tmp
FROM csi_prices
WHERE symbol LIKE ?
AND pr_date BETWEEN ?::date - ?::int AND ?::date
GROUP BY 1;

SELECT aa.pr_date, EXTRACT(dow FROM aa.pr_date) AS dow, EXTRACT(week FROM 
aa.pr_date) AS week,
EXTRACT(month FROM aa.pr_date) AS month, EXTRACT(year FROM aa.pr_date) AS 
year,
EXTRACT(doy FROM aa.pr_date) AS doy, EXTRACT(day FROM aa.pr_date) AS day,
aa.hi_price, aa.lo_price, aa.price, aa.volume, aa.open_interest, 
bb.tot_volume, bb.tot_oi
FROM prices aa, oi_vol_tmp bb
WHERE aa.pr_date = bb.pr_date
AND symbol = ?
AND aa.pr_date BETWEEN ?::date - ?::int AND ?::date
ORDER BY 1);

And it gets executed like this:
$dbh1 = DBI->connect($DSN_MAIN, $DBUSER, '', { AutoCommit => 1});
$dbh1->trace(0);

$PRICERPT1 = $dbh1->prepare($PRICERPT1_SQL); $PRICERPT1->execute($COM2, 
$ENDDATE, $INTERVAL, $ENDDATE, $COM, $ENDDATE, $INTERVAL, $ENDDATE);

while (@PRICERPT1 = $PRICERPT1->fetchrow_array) {

[...]

Is there any way to execute this in DBD-Pg 1.49? When I initially 
inserted:
$dbh->{pg_server_prepare} = 0;

the query worked as originally intended.

Thanks again.

Max Pyziur
pyz(at)brama(dot)com



On Wed, 16 Jan 2008, Tom Lane wrote:

> Max Pyziur <pyz(at)brama(dot)com> writes:
> > FROM prices aa, oi_vol_tmp bb
> > WHERE aa.pr_date = bb.pr_date
> > AND symbol = ?
> > AND aa.pr_date BETWEEN date ? - integer ? AND ?
>
> > I've tried using:
> > AND aa.pr_date BETWEEN date ?::date - integer ?::int AND ?::date
> > with no success.
>
> It should just be
>
> ... AND aa.pr_date BETWEEN ?::date - ?::int AND ?::date
>
> The type-name-at-the-front syntax is redundant with the cast, and
> is only allowed for simple literal constants anyway.  It worked for
> you before because DBD-PG was replacing the ? marks with literal
> strings, but it wasn't ever really "the right thing".  See
>
> http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
>
> 			regards, tom lane