Re: A cronjob for copying a table from Oracle

On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:
> 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.

I am just looking at the CONTEXT message above and it showing EMAIL being
assigned the $7 variable, which according to his argument list is _EMAILID.
EMAIL and EMAILID are the only two fields where the variable number does not
match the variable/argument numbers and are in fact reversed. So something is
happening there and would explain the problem because you that would mean you
are trying to stuff a 320 char field into a 16 char slot :)

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

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