Zeoslib Portal

Posted: **10.01.2013, 13:36**

Delphi 10, zeoslib 6.6.6 (also tried 7.0.3), libpq.dll 7.1 and 9.2.2, postgresql 8.3.8

when I do "select field1, field2, field3 from table1" no erros but I can access only Query.FieldByName('field1') or Query.Fields[0], other names gives "no field with name", all others indexes return "List index out of bounds". For this only field query gives the valid data.

This happens with one database, query to another database on the same server return field set as expected.
Any ideas how to understand what's wrong?

UPD
table without bytea
select field1, field2, field3 from table1 gives 3 fields
select * from table1 gives 3 fields

table WITH bytea
select field1, field2, field3 from table2 gives 1 field
select * from table2 gives 20 fields (table2 has 27 fields)
select table2.field1, table2.field2, table3.field3 from table2 join table3 gives 1 field
select table2.*, table3.field3 from table2 join table3 gives 20 fields
select table3.field3, table2.* from table2 join table3 gives 21 fields
select table3.field3, table3.field4, table2.* from table2 join table3 anyway gives 21 fields

have created a view with select table3.field3, table3.field4, table2.* from table2 join table3
select * from view1 - 21 fields
select field3, field4, field1 from view1 - 1 field

That's very fun!

Posted: **12.01.2013, 20:55**

borispr1,

can somebody confirm this issue? IMHO is this a select issue for PostgreSQL not for Zeos. Zeos only processes your requests and the results, which are returned from the Server.

Could this option change the beahvior:
TZConnection.Properties.Values['oidasblob'] = True;

AFIAK the bytea-fields where loaded as ftBytes.

Posted: **13.01.2013, 15:22**

EgonHugeist wrote:borispr1,
IMHO is this a select issue for PostgreSQL not for Zeos. Zeos only processes your requests and the results, which are returned from the Server.

phpPgAdmin works fine, so php lib gives query results as expected

Could this option change the beahvior:
TZConnection.Properties.Values['oidasblob'] = True;

Added
TZConnection.Properties.Values['oidasblob'] := 'True';
True as text, because I cannot assign True value to string property
But this line changed nothing.

Posted: **13.01.2013, 16:22**

borispr1,

is it possible you've allready assigned the fields to your formular?

I can't believe we're talking about a Zeos issue. There must be something terribly wrong on your side. You're the first who is reporting such strange things.

Or attach an example which makes it possible to reproduce this issue..

Posted: **15.01.2013, 14:56**

EgonHugeist wrote:borispr1,
is it possible you've allready assigned the fields to your formular?

This is a program I used to get fields count for different queries

Code: Select all

unit Unit1;

type
  TForm1 = class(TForm)
    ZConnection1: TZConnection;
    Button1: TButton;
    ZQuery1: TZQuery;
    Edit1: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
  public
  end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ZConnection1.Properties.Values['oidasblob'] := 'True';
  ZConnection1.Connected := true;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZQuery1.SQL.Text := Edit1.Text;
  ZQuery1.Open;
  ShowMessage( IntToStr(ZQuery1.Fields.Count) );
  ZQuery1.Close;
end;

That's all!

Posted: **15.01.2013, 23:05**

borispr1,

you making me curious!

I'm sure this problem is NOT a Zeos issue. Are theire some grant permissions or something like that? I've no clue what's going wrong on your side ):.

Did you check another new catalog or schema? A loads of people using Zeos+PostgreSQL but this is the first time i see something like that.

Posted: **20.01.2013, 10:07**

Got some time for experiments and installed postgresdac trial
same computer, same Delphi, same Postgres, same database

program code

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
begin
  ZConnection1.Properties.Values['oidasblob'] := 'True';
  ZConnection1.Connected := true;
  PSQLDatabase1.Connected := true;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZQuery1.SQL.Text := Edit1.Text;
  PSQLQuery1.SQL.Text := Edit1.Text;
  ZQuery1.Open;
  PSQLQuery1.Open;
  Label1.Caption := 'ZEOS: ' + IntToStr(ZQuery1.Fields.Count);
  Label2.Caption := 'PGDAC: ' + IntToStr(PSQLQuery1.Fields.Count);
  ZQuery1.Close;
  PSQLQuery1.Close;
end;

ZeosDBO gives ONE field, PostgresDAC gives THREE (screen shot is in attachment)

Posted: **20.01.2013, 19:29**

borispr,

can you please give me the table create-script to reproduce your issue? What is type mvarchar()???? You can send a pm if you doubt.

Posted: **25.01.2013, 08:41**

EgonHugeist wrote:borispr,
What is type mvarchar()????

Code: Select all

CREATE TYPE mvarchar;
CREATE FUNCTION mvarchar_in(cstring) RETURNS mvarchar AS '$libdir/mchar', 'mvarchar_in' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION mvarchar_out(mvarchar) RETURNS cstring AS '$libdir/mchar', 'mvarchar_out' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION mvarchar_recv(internal) RETURNS mvarchar AS '$libdir/mchar', 'mvarchar_recv' LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION mvarchar_send(mvarchar) RETURNS bytea AS '$libdir/mchar', 'mvarchar_send' LANGUAGE c IMMUTABLE STRICT;
CREATE TYPE mvarchar (
    INTERNALLENGTH = variable,
    INPUT = mvarchar_in,
    OUTPUT = mvarchar_out,
    RECEIVE = mvarchar_recv,
    SEND = mvarchar_send,
    TYPMOD_IN = mchartypmod_in,
    TYPMOD_OUT = mchartypmod_out,
    ALIGNMENT = int4,
    STORAGE = extended
);

in pg libdir is mchar.so file

in example i used view which join 4 tables but the behaviour is the same with any table from this view
here is the declaration of one of them

Code: Select all

CREATE TABLE _reference27 (
    _idrref bytea NOT NULL,
    _version integer NOT NULL,
    _marked boolean NOT NULL,
    _ismetadata boolean NOT NULL,
    _parentidrref bytea NOT NULL,
    _folder boolean NOT NULL,
    _code mchar(8) NOT NULL,
    _description mvarchar(50) NOT NULL,
    _fld371 mvarchar,
    _fld372rref bytea NOT NULL,
    _fld373rref bytea NOT NULL,
    _fld374rref bytea,
    _fld375rref bytea,
    _fld377 mvarchar(10),
    _fld378 mvarchar(4),
    _fld379 mvarchar(12),
    _fld380 mvarchar(9),
    _fld382rref bytea,
    _fld383rref bytea,
    _fld384 mvarchar NOT NULL,
    _fld385 boolean,
    _fld4019rref bytea,
    _fld4020 timestamp without time zone,
    _fld4021rref bytea,
    _fld4022rref bytea,
    _fld4023 timestamp without time zone,
    _fld4024 mvarchar(5),
    _fld4025rref bytea,
    _fld4026 timestamp without time zone NOT NULL
);

P.S. Might I was wrong about bytea dependence. Now I think it's all about mvarchar type. I do access it as Field.AsString and in ZeosDBO then need to do UTF8Decode. PostgresDAC already gives it decoded to windows charset.

Posted: **25.01.2013, 22:21**

borispr,

uh now i understand we're talking about UDT's. Hmpf i'm sorry, currently i can't see an implementation for user defined types, so Zeos returns allways stUnknow as columntype and these where not handle in the component-layer (the reason for your missing fields). But nothing prevents us to implement this remaining part. But i'm not a postgressql hero, i must admit. On the other hand i know very much about Zeos. All i can do is to invite you to help me. I can guid you through all related functions. What do you think?

Edit:

I do access it as Field.AsString and in ZeosDBO then need to do UTF8Decode. PostgresDAC already gives it decoded to windows charset.

That's not true. If you use Zeos7 then this behavior can change if you set it like this:

Code: Select all

TZConnection.ClientCodepage := 'utf8';
TZConnection.ControlsCodepage := cGet_ACP; //if you're a Ansi-Delphi user (<=D2007)
TZConnection.AutoEncodeStrings := True;

Posted: **26.01.2013, 08:58**

EgonHugeist wrote:borispr,
All i can do is to invite you to help me. I can guid you through all related functions. What do you think?

Let's try it

Posted: **27.01.2013, 12:16**

borispr1,

Let's try it

That's what i wanna hear! I wrote a little turorial: http://zeos.firmos.at/viewtopic.php?p=16873#16873

Please follow these instuctions first.

Write me a PM with your EMail if you doubt

The next step would be to determine the missing types here:

Unit ZDbcPostgreSQLUtils.pas

Used by Database-MetaInformations:

Code: Select all

function PostgreSQLToSQLType%u28Connection%u3a IZPostgreSQLConnection;
  TypeName%u3a string%u29%u3a TZSQLType; overload;

and
Used by Resultset-MetaInformations:

Code: Select all

function PostgreSQLToSQLType%u28Connection%u3a IZPostgreSQLConnection;
  TypeOid%u3a Integer%u29%u3a TZSQLType; overload;

This one will be called from procedure TZPostgreSQLResultSet.Open; to determine the field-types. I guess here we've got the leak! Also should we think about which type should be assumed. I propose to use the stString for UDT's?

But first start playing with the tests or check the functions i want to point you. Nice would be if you completely follow my little tutorial. Add a create script to our database\create_postgresql_bugreport.sql file and be sure the type is dropped in database\drop_postgresql_bugreport.sql. Add a testcase to \test\bugreport\ZTestBugCompPostgreSql.pas

So we both have a good base to start!

May i ask you if my suggestions are to complex? Than we'll go "your" way..

Zeoslib Portal

Delphi 10 TZQuery returns only one(first) field

Delphi 10 TZQuery returns only one(first) field