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