Search Postgresql Archives

Re: A cronjob for copying a table from Oracle

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux