Re: A cronjob for copying a table from Oracle

Huh! Yes, indeed ! But how is it possible ?! I see
EMAIL = _EMAIL,
EMAILID = _EMAILID,

rather than

EMAIL = $7,
EMAILID = $8,

in the function definition...

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

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

--
// Dmitriy.