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 | AANLkTimpZ=k_jnh77qgEV61rqxcsf1XWW05gREsOXE_K@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 |
Hey Adrian,
--
// Dmitriy.
2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>
Looks like you got your EMAIL and EMAILID reversed. In your argument listOn 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
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.
seems to be correct in the function definition.
Adrian Klaver
>
> 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@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
pgsql-general by date: