Date: Wed, 16 Jan 2008 12:59:27 -0500 (EST)
From: Max Pyziur <pyz@brama.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: Multiple command usage in DBD-Pg v 1.49
In-Reply-To: <25258.1200503831@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.60.0801161253460.4054@brama.com>
References: <Pine.LNX.4.60.0801161114120.12755@brama.com>
	<25258.1200503831@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed


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@brama.com


On Wed, 16 Jan 2008, Tom Lane wrote:

> Max Pyziur <pyz@brama.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
>