A cronjob for copying a table from Oracle

Alexander Farber, 10.12.2010 12:02:

I'm preparing a PHP-script to be run as a nightly cronjob
and will first find the latest qdatetime stored in my local
PostgreSQL database and then just "select" in remote Oracle,
"insert" into the local PostgreSQL database in a loop.

But I wonder if there is maybe a cleverer way to do this?

And I'm not sure how to copy the Oracle's strange DATE
column best into PostgreSQL, without losing precision?

Oracle's DATE includes a time part as well.

So simply use a timestamp in PostgreSQL and everything should be fine.

Regards
Thomas

On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

And I'm not sure how to copy the Oracle's strange DATE
column best into PostgreSQL, without losing precision?

Oracle's DATE includes a time part as well.

So simply use a timestamp in PostgreSQL and everything should be fine.

Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp?

(I realize that this more an Oracle question, sorry)

What format string should I take for Oracle's to_date() function,
I don't see a format string to get epoch seconds there

Regards
Alex

Alexander Farber, 10.12.2010 12:53:

On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer<spam_eater@gmx.net> wrote:

And I'm not sure how to copy the Oracle's strange DATE
column best into PostgreSQL, without losing precision?

Oracle's DATE includes a time part as well.

So simply use a timestamp in PostgreSQL and everything should be fine.

Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp?

(I realize that this more an Oracle question, sorry)

What format string should I take for Oracle's to_date() function,
I don't see a format string to get epoch seconds there

I have no idea what you are doing in PHP, but why don't you simply generate a valid date/time literal for Postgres using the to_char() function?

Something like

SELECT 'TIMESTAMP '''||to_char(QDATETIME, 'YYYY-MM-DD HH24:MI:SS')||''''
FROM qtrack;

That literal can directly be used in an INSERT statement for PostgreSQL

Regards
Thomas

Hey Thomas, Alexander

2010/12/10 Thomas Kellerer <spam_eater@gmx.net>

Alexander Farber, 10.12.2010 12:53:

On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer<spam_eater@gmx.net>

wrote:

And I'm not sure how to copy the Oracle's strange DATE

column best into PostgreSQL, without losing precision?

Oracle's DATE includes a time part as well.

So simply use a timestamp in PostgreSQL and everything should be fine.

Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp?

(I realize that this more an Oracle question, sorry)

What format string should I take for Oracle's to_date() function,
I don't see a format string to get epoch seconds there

I have no idea what you are doing in PHP, but why don't you simply generate
a valid date/time literal for Postgres using the to_char() function?

Something like

SELECT 'TIMESTAMP '''||to_char(QDATETIME, 'YYYY-MM-DD HH24:MI:SS')||''''
FROM qtrack;

That literal can directly be used in an INSERT statement for PostgreSQL

He asked exactly that.

Regards
Thomas

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

--
// Dmitriy.

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

Hey Alexander,

Can you post the SQL with call of the function (SQL_UPSERT)
I guess ?

2010/12/10 Alexander Farber <alexander.farber@gmail.com>

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

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

--
// Dmitriy.

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

Hey Adrian,

2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>

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.

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

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

--
// Dmitriy.

On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:

Hey Adrian,

2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>

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@gmail.com

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@gmail.com>

On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:

Hey Adrian,

2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>

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@gmail.com

--
// Dmitriy.

On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote:

Huh! Yes, indeed ! But how is it possible ?! I see
EMAIL = _EMAIL,
EMAILID = _EMAILID,

rather than

EMAIL = $7,
EMAILID = $8,

in the function definition...

My guess the reversal is taking place in the PHP code. The table
definition and the argument list to the Pg function have one order for
emailid,email and the update and insert statements have another;
email,emailid. I would guess that the PHP is building the row variables
using the SQL statement order and than passing that to the Pg function
which has a different order.

--
Adrian Klaver
adrian.klaver@gmail.com

2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>

On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote:

Huh! Yes, indeed ! But how is it possible ?! I see
EMAIL = _EMAIL,
EMAILID = _EMAILID,

rather than

EMAIL = $7,
EMAILID = $8,

in the function definition...

My guess the reversal is taking place in the PHP code. The table definition
and the argument list to the Pg function have one order for emailid,email
and the update and insert statements have another; email,emailid. I would
guess that the PHP is building the row variables using the SQL statement
order and than passing that to the Pg function which has a different order.

Yeah, thats why I've asked the OP to post SQL with call of the function (in
PHP).

--
Adrian Klaver
adrian.klaver@gmail.com

--
// Dmitriy.

On Fri, Dec 10, 2010 at 6:15 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On Friday 10 December 2010 8:51:19 am Alexander Farber wrote:

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.

That was it Adrian, thank you so much! It was reversed in my
Oracle's select and thus the PostgreSQL's upsert was failing.

I was looking too many times at that spot, so I stopped really reading it.

Dmitiry, $7 and $8 etc. is probably what plpgsql
substitutes for _EMAIL and _EMAILID internally

Regards
Alex