[GENERAL] Problem with special character =?UTF-8?B?KMO/KSBvbiBwb3N0Z3Jlc3Fs?= =?UTF-8?B?IDcuNC4uLiBnZXR0aW5nIG91dCBvZiBpZGVhIC4uIHBsZWFzZSBoZWxwIDotKQ==?=

[David Gagnon]

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;

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend