Re: A cronjob for copying a table from Oracle

On Friday 10 December 2010 8:51:19 am Alexander Farber wrote:
> 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

Looks like you got your EMAIL and EMAILID reversed. In your argument list
EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL.

>
> 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

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com