Re: A cronjob for copying a table from Oracle - Mailing list pgsql-general
| From | Dmitriy Igrishin |
|---|---|
| Subject | Re: A cronjob for copying a table from Oracle |
| Date | |
| Msg-id | AANLkTinRhe=zdrzxVr6tK=QzFE22LTiTpBJR5uCoFPYW@mail.gmail.com Whole thread Raw |
| In response to | Re: A cronjob for copying a table from Oracle (Adrian Klaver <adrian.klaver@gmail.com>) |
| Responses |
Re: A cronjob for copying a table from Oracle
(Adrian Klaver <adrian.klaver@gmail.com>)
|
| List | pgsql-general |
Huh! Yes, indeed ! But how is it possible ?! I see
EMAIL = _EMAIL,
EMAILID = _EMAILID,
rather than
EMAIL = $7,
EMAILID = $8,
in the function definition...
--
// Dmitriy.
EMAIL = _EMAIL,
EMAILID = _EMAILID,
rather than
EMAIL = $7,
EMAILID = $8,
in the function definition...
2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>
I am just looking at the CONTEXT message above and it showing EMAIL beingOn Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:
> Hey Adrian,
>
> 2010/12/10 Adrian Klaver <adrian.klaver@gmail.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.
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;
--
// Dmitriy.
pgsql-general by date: