Zeos Problem with PostgreSQL

[cpalx]

Hello

I use zeos 7.1.3 (before version i cant install)
Lazarus 1.2.4
Windows and Linux

I have a new big problem,
i have querys ans stored procedures returns varchar and use functions like initcap, in before versions (7.0) all was ok but now i have the problem :

The problem:
i execute the query, for example:

select cast( initcap(name) as varchar ) as Name From People

the query execute, but when i display in DBgrid, it shows (Memo)

that problem only occur in 7.1.* (7.2alpha) versions

how can i solved that problem?

[EgonHugeist]

Hello Christian,

Thought you did broke with Zeos?-> did read your two latest threads in the LCL forum.
Welcome back! Not lucky with the SQLDB performance
Is this the bug you was talking about?

So this is NOT a bug it is a wanted behavior!:
Varchar(no specified length) Should be used as Memo. We had to many bugreports for string trunkations(until 7.0 we used 255 chars by default)

Two solutions:
1. Fall back too old behavior by:
Add 'Undefined_Varchar_AsString_Length=255' to the TZConnection.Properties
2. Simply give the Varchar cast a length like "cast(x as Varchar(Your expected length)).."

Hope you're lucky now...

[cpalx]

not lucky at all,

i tested lie you tell me, The query works as i spected, but not the stored procedures.

Look this issue:

CREATE TABLE DEMO(
demoid serial,
demotext varchar(50),
primary key(demoid)

);

insert into demo(demoid,demotext) values(1,'My first text'),(2,'My second text');


CREATE OR REPLACE FUNCTION f_demotext(id integer)
RETURNS TABLE(thetext VARCHAR(50)) AS
$BODY$
DECLARE

BEGIN
RETURN query EXECUTE( 'Select cast(initcap(demotext) as varchar(50) ) as thetext from DEMO where demoid=' || $1 );
END

$BODY$
LANGUAGE plpgsql

select * from f_demotext(1)
if we display it in a dbgrid still showing "(memo)"

Lazarus 1.2.4
Zeos 7.2 (alfa)

[cpalx]

i use PostgreSQL, 9.1 and 9.3

thanks for reading me.

[cpalx]

Using 'Undefined_Varchar_AsString_Length=255' works fine, but my question is why dont works with a Store procedure using VARCHAR(50) (without 'Undefined_Varchar_AsString_Length=255)

[EgonHugeist]

Have no idea yet. I'll add your testcase(btw thank you for that) this weekend and check what's going wrong with the TZPosgresSQLCallable stmt.
But i have NO time before comming weekend.. I'll notify you if i found a patch.

[cpalx]

Thanks a lot

[EgonHugeist]

Hi,

did debug me in. Zeos is not able to determine the field-lengths because the PostgreSQL function pgfsize returns -1(no limit) as field-length while the fieldtype (OID 1043-> varchar) is correct. IMPO is it not an issue with your cast funtion, NOPE, the more i think it is an issue with the StoredProcedure you are using.

I do not see a solution from our side. Switching back to old behavior makes trouble if you would expect more than 255 characters as field-length.. Nope i'm against that.

All i can offer would be a little patch to use the suggested 'Undefined_Varchar_AsString_Length=your_length' option for the TZAbstractDataSet-descendants too. So you don't need a global option. What do you think?