Re: A cronjob for copying a table from Oracle

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

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.klaver@xxxxxxxxx>

On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:
> Hey Adrian,
>
> 2010/12/10 Adrian Klaver <adrian.klaver@xxxxxxxxx>
>
> > 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.klaver@xxxxxxxxx

-- 
// Dmitriy.