#266 PostgreSQL and problem with $ for functions

[maciej-izak]

Hi,

some time ago ZeosLib has no problem with SQL like:

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;


INSERT INTO servupgrade(nrop, query) values (261,'');

with current ZeosLib version, SQL like above can't be executed (but mentioned SQL is ok for example in pgAdmin). I guess this problem is introduced by some code in ZDbcPostgreSqlStatement.pas (line 1437), "$1" is badly interpreted in this context.

Large part of my code is broken.

[marsupilami79]

Hello Maciej,

do you do that using an TZSQLProcessor or using a TZQuery? Similar updates worked for me in the past using a TZSQLProcessor, setting the Terminator property to dtSetTerm:

set term ^ ;
CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT^

set term ; ^

INSERT INTO servupgrade(nrop, query) values (261,'');

Could you see if that works for you too?

Best regards,

Jan

[maciej-izak]

Hi Jan, thanks for suggestion, in my case is used TZQuery. The problem is significant because I can't edit all SQLs, also TZQuery is used in many places in code (big legacy project) and can't be replaced in simple way... The problem is somewhere in Zeos, mentioned SQL code works for many years for me and with many Zeos versions.

[maciej-izak]

For the ZeosLib the problematic part seems $BODY$ , for exaple :

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
  RETURNS integer AS
'
SELECT CASE WHEN trim($1) SIMILAR TO ''[0-9]+'' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

'
  LANGUAGE 'sql' IMMUTABLE STRICT;


INSERT INTO servupgrade(nrop, query) values (261,'');

works fine, also this is correct too:

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim(chartoconvert) SIMILAR TO '[0-9]+' 
        THEN CAST(trim(chartoconvert) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;


INSERT INTO servupgrade(nrop, query) values (261,'');

[marsupilami79]

Soo - I added a test that is similar to your bugreport on Zeos 7.2 (Rev. 4721) and Zeos 7.3 (Rev.4720).

The outcome is

• on Zeos 7.2 the test works a s expected.
• on Zeos 7.3 I get an error that PostgreSQL cannot insert multiple commands in one prepared statement. Which is understandable.

My environment for these Tests: Zeos 7.2 (Rev. 4721), Zeos 7.3 (Rev. 4720), Delphi XE10.2, PostgreSQL 9.6.

Which error message do you get? Do you use the latest revision for your version of Zeos? Which version of Zeos do you use?

I can understand that the $1 can be a problem because EgonHugeist added support for that kind of parameters to Zeos 7.3 during the last months, I think and maybe the $BODY$ quotation for strings is not (yet) fully understood by the PostgreSQL tokenizer.

[marsupilami79]

Side note: Please don't use trunk. trunk is not our current development branch. It will become the current development branch, once we finish up with Zeos 7.3.

[maciej-izak]

I am using trunk (my own clone repo synced with Zeos trunk : https://github.com/newpascal-ccr/zeos ).

I have the same error ( 42601 ) : "cannot insert multiple commands in one prepared statement"

[EgonHugeist]

Is your issue resolved now?
Please close if ok.

[EgonHugeist]

Hi maciej,

patches are done. I'm sorry for this inconsistency. I'd fixed another bug and this lead to next bug.
Patch is merged to trunk already.

Cheers, Michael

[maciej-izak]

Tested with my code, works fine now! Thanks!