problem displaying data in grid in pgsql

[albourgz]

Unidac 6.4.16, c++ builder xe10 pro, vcl win32 app.

Code: Select all

QDict->SQL->Strings[0]="SELECT ID, DTON, WHAT, RC, AUTO, DUREE, substr(command, 1, {fn LOCATE('''',command) } -1) as pgm FROM thetable";
QDict->DataTypeMap->AddFieldNameRule("PGM", ftWideString);

command is character varying 256 (in oracle varchar2(256 char)).
DBMonitor: sql translation is correct.
This query is source of a TCrDBGrid.
Query from db monitor copied to pgadmin gives the good results.
Problem in postgres: only 20 first chars of substr(command, 1, {fn LOCATE('''',command) } -1) are displayed in the grid. (only in postgres, connected to oracle display is fine!!!!).

Any hint?

[azyk]

In the provided sample for PostgreSQL the substr construction does not return field length that is not set in the mapping rule either, therefore UniDAC sets string field length by default (20).

To solve the issue set exact value to the field length in Data Type Mapping rule. For example, the rule can look like this:

Code: Select all

QDict->DataTypeMap->AddFieldNameRule("PGM", ftWideString, 256);

[albourgz]

Why is string field length by default different for Oracle and for Postgres?

[azyk]

The default size(20) does not depend on the used DBMS, property Size default 20; is written in implementation of the standard TStringField class and its descendant TWideStringField.

During SQL query execution UniDac requests for recordset fields metadata from DBMS, including the size. Oracle for T-SQL substr function returns the size in metadata, therefore UniDAC sets the size to the PGM field which is returned by Oracle. PostgreSQL for T-SQL substr function in metadata does not return the size, therefore UniDAC does not set the size to the PGM field. As the size for TWideStringField is not set by any others, the size takes a value by default.