Re: A cronjob for copying a table from Oracle

Hey Adrian,

2010/12/10 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>

> 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.
>
Yes, but he refers arguments by name, rather than number. UPDATE statement
seems to be correct in the function definition.

>
> >
> > 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.