Skip site navigation (1) Skip section navigation (2)

Error after upgrade

From: "Paul Houselander" <housey(at)sme-ecom(dot)co(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Error after upgrade
Date: 2008-01-28 11:37:03
Message-ID: FIEEKDEHFMGPNODJNFGFEEHHCEAA.housey@sme-ecom.co.uk (view raw or flat)
Thread:
Lists: pgsql-general
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



Responses

pgsql-general by date

Next:From: Magnus HaganderDate: 2008-01-28 11:45:16
Subject: Re: Is news.postgresql.org down?
Previous:From: mljvDate: 2008-01-28 11:19:11
Subject: Re: Very long execution time of "select nextval('..');"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group