Re: A cronjob for copying a table from Oracle

Please help, struggling since hours with this :-(

I've created the following table (columns here and in the proc
sorted alphabetically) to acquire data copied from Oracle:

# \d qtrack
Table "public.qtrack"
Column | Type | Modifiers
-------------+-----------------------------+---------------
appsversion | character varying(30) |
beta_prog | character varying(20) |
category | character varying(120) |
catinfo | character varying(120) |
details | character varying(50) |
devinfo | character varying(4000) |
emailid | character varying(16) |
email | character varying(320) |
formfactor | character varying(10) |
id | character varying(20) | not null
imei | character varying(25) |
name | character varying(20) |
osversion | character varying(30) |
pin | character varying(12) |
qdatetime | timestamp without time zone |
copied | timestamp without time zone | default now()
Indexes:
"qtrack_pkey" PRIMARY KEY, btree (id)

And for my "upsert" procedure I get the error:

SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too
long for type character varying(16)

CONTEXT: SQL statement "update qtrack set APPSVERSION = $1 ,
BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 ,
DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID =
$10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 ,
QDATETIME = $15 , COPIED = current_timestamp where ID = $10 "
PL/pgSQL function "qtrack_upsert" line 2 at SQL statement

My "upsert" procedure is:

create or replace function qtrack_upsert(
_APPSVERSION varchar,
_BETA_PROG varchar,
_CATEGORY varchar,
_CATINFO varchar,
_DETAILS varchar,
_DEVINFO varchar,
_EMAILID varchar,
_EMAIL varchar,
_FORMFACTOR varchar,
_ID varchar,
_IMEI varchar,
_NAME varchar,
_OSVERSION varchar,
_PIN varchar,
_QDATETIME timestamp
) returns void as $BODY$
begin
update qtrack set
APPSVERSION = _APPSVERSION,
BETA_PROG = _BETA_PROG,
CATEGORY = _CATEGORY,
CATINFO = _CATINFO,
DETAILS = _DETAILS,
DEVINFO = _DEVINFO,
EMAIL = _EMAIL,
EMAILID = _EMAILID,
FORMFACTOR = _FORMFACTOR,
ID = _ID,
IMEI = _IMEI,
NAME = _NAME,
OSVERSION = _OSVERSION,
PIN = _PIN,
QDATETIME = _QDATETIME,
COPIED = current_timestamp
where ID = _ID;

if not found then
insert into qtrack (
APPSVERSION,
BETA_PROG,
CATEGORY,
CATINFO,
DETAILS,
DEVINFO,
EMAIL,
EMAILID,
FORMFACTOR,
ID,
IMEI,
NAME,
OSVERSION,
PIN,
QDATETIME
) values (
_APPSVERSION,
_BETA_PROG,
_CATEGORY,
_CATINFO,
_DETAILS,
_DEVINFO,
_EMAIL,
_EMAILID,
_FORMFACTOR,
_ID,
_IMEI,
_NAME,
_OSVERSION,
_PIN,
_QDATETIME
);
end if;
end;
$BODY$ language plpgsql;

The weird thing is when I omit the 7th param
in my PHP code as shown below, then it works:

$sth = $pg->prepare(SQL_UPSERT);
while (($row = oci_fetch_array($stid,
OCI_NUM+OCI_RETURN_NULLS)) != false) {
$sth->execute(array(
$row[0],
$row[1],
$row[2],
$row[3],
$row[4],
$row[5],
null, #$row[6],
$row[7],
$row[8],
$row[9],
$row[10],
$row[11],
$row[12],
$row[13],
$row[14])
);
}

And I'm very confused why it says varying(16) in the error message.
It should say varying(4000) instead.

Isn't this a bug? The 6th overflows somehow and gets into 7th

Please save me, I want to go home for weekend
Alex