From: | David Gagnon <dgagnon(at)siunik(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with special character (ÿ) on postgresql 7.4... getting out of idea .. please help :-) |
Date: | 2005-03-14 21:32:38 |
Message-ID: | 423602F6.2000109@siunik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I really have a problem with a production environment (RH 9, Postgresql
7.4).
When executing a stored procedure on my computer (development
environment: 7.4 under cygwin. ) everything is oki
When I deploy on the production env the same stored procedure with the
same data (different OS and postgresql instance) the stored procedure
crash. I get this error:
java.io.IOException: Unable to get row: ERROR: cannot EXECUTE a null
querystring when executing the
I looked with pgadminIII and found that a charater used as a string
separator (i.e.: ÿ) is shown as � on the production database. It look
just oki in my dev env. (I included part of both stored procedure below).
Both stored procedure have been added to postgresql via JDBC. When I
update the stored procedure via Pgadmin III the stored procedure look oki.
Any Idea what can be the error. Is there any JDBC/Postgresql 7.4 version
that can cause the behavior. Do I have to set a flag somewhere?! Is
there a way I can work around this problem?
Thanks for your help .. it's really appreciated
/David
CREATE OR REPLACE FUNCTION uk_webos_parseitemprice(int4, _int4)
RETURNS int4 AS
'
DECLARE
commandId ALIAS FOR $1;
arrayProp ALIAS FOR $2;
rawData RECORD;
oneRow text[];
i INTEGER;
idValue VARCHAR;
typeValue VARCHAR;
....
OFFSET 1
LOOP
select into oneRow (string_to_array(rawData.VDDATA,\'ÿ\'));
action:=oneRow[1];
FOR i IN array_lower(oneRow, 1)..array_upper(oneRow, 1) LOOP
column:=oneRow[i];
IF (column = \'þ\')
THEN
....
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION uk_webos_parseitemprice(int4, _int4)
RETURNS int4 AS
'
DECLARE
....
-- RAISE NOTICE \'test \' ;
FOR rawData IN
SELECT VDNUM, VDVSSRC, VDVSNUM, VDKEY, VDDATA, ts
FROM VD
WHERE VDVSNUM = commandId
AND VDKEY = \'IL\'
AND VDVSSRC = 1
ORDER BY VDNUM
OFFSET 1
LOOP
select into oneRow (string_to_array(rawData.VDDATA,\'�\'));
action:=oneRow[1];
FOR i IN array_lower(oneRow, 1)..array_upper(oneRow, 1) LOOP
column:=oneRow[i];
IF (column = \'�\')
THEN
column:= null ;
END IF;
IF (i = arrayProp[1])
THEN
idValue:= column;
ELSIF (i = arrayProp[2])
THEN
typeValue:= column;
ELSIF (i = arrayProp[3])
THEN
itemIdValue:= column;
ELSIF (i = arrayProp[4])
THEN
resourceIdValue:= column;
ELSIF (i = arrayProp[5])
THEN
minimalQuantityValue:= column;
ELSIF (i = arrayProp[6])
THEN
unitPriceValue:= column;
END IF;
END LOOP;
IF ((action = \'UPDATE\') or (action = \'GUESS\'))
THEN
EXECUTE \'DELETE FROM IL WHERE ILNUM =\' || idValue;
END IF;
-- process the insert statement
insertStatement:= \'INSERT INTO IL ( ILNUM, ILTYPE, ILICNUM, ILRRNUM,
ILQTE, ILPRIX, ts ) VALUES ( \' || idValue ||\', \'|| typeValue ||\',\';
IF (itemIdValue is null)
THEN
insertStatement:= insertStatement || \' null,\';
ELSE
insertStatement:= insertStatement || quote_literal(itemIdValue)|| \',\';
END IF;
IF (resourceIdValue is null)
THEN
insertStatement:= insertStatement || \' null,\';
ELSE
insertStatement:= insertStatement || quote_literal(resourceIdValue)||
\',\';
END IF;
insertStatement:= insertStatement ||
minimalQuantityValue||\',\'||unitPriceValue||\',CURRENT_TIMESTAMP ) \';
-- RAISE NOTICE \'insertStatement %\', insertStatement ;
EXECUTE insertStatement;
END LOOP;
return -1;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
From | Date | Subject | |
---|---|---|---|
Next Message | Ragnar Hafstað | 2005-03-14 22:03:18 | Re: Problem with special character |
Previous Message | Michael Fuhr | 2005-03-14 21:26:36 | Re: plpython function problem workaround |