PostgreSQL SUBSTRING Macro

[eduardomendes]

Hi,

when trying use SUBSTRING macro occurs error.

"No argument for format '%s'."
UniQuery.add('SELECT {fn SUBSTRING(FIELD,1,2)} FROM TABLE');
UniQuery.Open();

But if I execute query like this, the problem not occurs:

UniQuery.add('SELECT {fn SUBSTRING(FIELD, 1, 2)} FROM TABLE');
UniQuery.Open();


How can I fix this problem?

Thanks!

[AlexP]

hello,

This problem is due to the fact that the "1,2" symbols are perceived as a number 1.2 when parsing the string. To make your statement correct even without spaces, you should change the DecimalSeparator fractional part separator, i.e.

DecimalSeparator := '.';
UniQuery2.SQL.Add('SELECT {fn SUBSTRING(DNAME,1,2)} FROM DEPT');
UniQuery2.Open;

[eduardomendes]

Hi AlexP,

this problem not occours with SQL Server, only with PostgreSQL.

Why?

Thanks!

[AlexP]

hello,

We will fix the behaviour of the PostgreSQL provider for corerct work with such parameters in the next version.
For the time being, you can either change DecimalSeparator or surround numeric parameters with spaces.

[eduardomendes]

Thanks AlexP!