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

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

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