Re: A cronjob for copying a table from Oracle - Mailing list pgsql-general

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.

>
> 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;
>
> The weird thing is when I omit the 7th param
> in my PHP code as shown below, then it works:
>
>        $sth = $pg->prepare(SQL_UPSERT);
>        while (($row = oci_fetch_array($stid,
> OCI_NUM+OCI_RETURN_NULLS)) != false) {
>                $sth->execute(array(
>                 $row[0],
>                 $row[1],
>                 $row[2],
>                 $row[3],
>                 $row[4],
>                 $row[5],
>                 null, #$row[6],
>                 $row[7],
>                 $row[8],
>                 $row[9],
>                 $row[10],
>                 $row[11],
>                 $row[12],
>                 $row[13],
>                 $row[14])
>                 );
>       }
>
> And I'm very confused why it says varying(16)  in the error message.
> It should say varying(4000) instead.
>
> Isn't this a bug? The 6th overflows somehow and gets into 7th
>
> Please save me, I want to go home for weekend
> Alex



--
Adrian Klaver
adrian.klaver@gmail.com