Problem with special character (ÿ) on postgresql 7.4... getting out of idea .. please help :-) - Mailing list 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;