Thread: Error after upgrade

Hi

Im in the process of moving an application that is currently using Postgres
7.4.8 to a new system running Postgres 8.1.9

Ive managed to use pg_dump to get all the data migrated across, however I
have an update script that causes an error on the new system but works on
the older one, I just cant seem to get to the bottom of it and was hoping
someone could point me the right way!

The script is pretty basic and uses Perl DBI to just update a record, an
extract is below

$domid = '288' ;
$sdate = '2008-01-24';
$uinctr = '1';
$uinsize = '1000';
$uinspam = '0';
$uinvir = '0';
$uoutctr = '1';
$uoutsize = '100';
$uoutspam = '0';
$uoutvir = '0';
$uname = 'paul';
$stattype = "W";
$statmode = "U";

$uweeklysqlu = "UPDATE stats_? SET
statdate=?,inctr=inctr+?,insize=insize+?,inspam=inspam+?,invir=invir+?,outct
r=outctr+?,outsize=outsize+?,outspam=outspam+?,outvir=outvir+? WHERE
account=? AND stattype=? AND statmode=? AND extract(week from
statdate)=extract(week from date ?) and extract(year from
statdate)=extract(year from date ?)";

$uweeklysqlu=$dbv->prepare($uweeklysqlu);

$uweeklysqlu->execute($domid,$sdate,$uinctr,$uinsize,$uinspam,$uinvir,$uoutc
tr,$uoutsize,$uoutspam,$uoutvir,$uname,$stattype,$statmode,$sdate,$sdate);

The above gets the error

"DBD::Pg::st execute failed: ERROR:  syntax error at or near "$14" at
character 277"

when run against Postgres 8.1.9 but works against 7.4.8, ive tried the
script but substituting the variables in the prepare instead of execute and
it works i.e.

$uweeklysqlu1 = "UPDATE stats_$domid SET
statdate='$sdate',inctr=inctr+'$uinctr',insize=insize+'$uinsize',inspam=insp
am+'$uinspam',invir=invir+'$uinvir',outctr=outctr+'$uoutctr',outsize=outsize
+'$uoutsize',outspam=outspam+'$uoutspam',outvir=outvir+'$uoutvir' WHERE
account='$uname' AND stattype='$stattype' AND statmode='$statmode' AND
extract(week from statdate)=extract(week from date '$sdate') and
extract(year from statdate)=extract(year from date '$sdate')";

$uweeklysqlu=$dbv->prepare($uweeklysqlu);

$uweeklysqlu->execute;

I think its something very simple but I just cant seem to solve it?

Any ideals

Kind Regards

Paul

"Paul Houselander" <housey@sme-ecom.co.uk> writes:
> $uweeklysqlu = "UPDATE stats_? SET
> statdate=?,inctr=inctr+?,insize=insize+?,inspam=inspam+?,invir=invir+?,outct
> r=outctr+?,outsize=outsize+?,outspam=outspam+?,outvir=outvir+? WHERE
> account=? AND stattype=? AND statmode=? AND extract(week from
> statdate)=extract(week from date ?) and extract(year from
> statdate)=extract(year from date ?)";

"date ?" is incorrect.  You got away with it before because you were
using an older driver that substituted a literal constant into the query
before sending it (ie, the query wasn't really prepared at all).  The
right way is "?::date", or more verbosely "CAST(? AS date)".  See the
discussions of casting and assigning types to constants in sections
4.1.2.5 and 4.2.8 of the manual.

I doubt that "stats_?" as a table name is going to work either.

            regards, tom lane