PostgreSQL-Field is not listed when doing "select * from"

[ertank]

Hello,

Using SVN 3994 of Zeos library, Lazarus 1.8.0RC2 SVN 55228 on Raspberry Pi 3. Database system is PostgreSQL 9.4.12 installed using apt-get on same Raspberry system.

When converting my current code from SQLdb to Zeos, I got rather strange problem and I am not sure if this is a zeos bug or something else.

I have below table in my database with some records in it (inserted before using SQLdb):

Code: Select all

CREATE TABLE sayimbaslik
(
  autoinc serial NOT NULL,
  serino character varying(20),
  id uuid NOT NULL,
  ameliyathane character varying(20),
  hekim character varying(40),
  scrub character varying(40),
  sirkule character varying(40),
  ameliyatadi character varying(20),
  tarih date,
  hastano character varying(20),
  onayzamani timestamp without time zone,
  durum smallint,
  kayitzamani timestamp without time zone DEFAULT now(),
  yazdirildi smallint DEFAULT 0,
  sonyazdirma timestamp without time zone,
  CONSTRAINT sayimbaslik_pkey PRIMARY KEY (id)
)

I have TZQuery with following SQL set at design time:

Code: Select all

select * from sayimbaslik where id = :id

My code to open that TZQuery is as follows:

Code: Select all

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  Guid: TGuid;
begin
  CreateGUID(Guid);
  ZQuery1.Params[0].AsString := GuidToString(Guid);
  ZQuery1.Open();
end;

I get "Field not found 'id'" error if I try to do following after opening the TZQuery:

Code: Select all

procedure TForm1.SpeedButton2Click(Sender: TObject);
begin
  ShowMessage(ZQuery1.FieldByName('id').AsString);
end;

Indeed, there is no id field listed if I put a TDBGrid on the form and display records. Below is log output from ZSQLMonitor:

Code: Select all

2017-06-15 13:13:48 cat: Connect, proto: postgresql-9, msg: CONNECT TO "mydb" AS USER "postgres"
2017-06-15 13:13:48 cat: Execute, proto: postgresql-9, msg: select setting from pg_settings where name = 'bytea_output'
2017-06-15 13:13:48 cat: Execute, proto: postgresql-9, msg: SELECT version()
2017-06-15 13:13:48 cat: Execute, proto: postgresql-9, msg: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
2017-06-15 13:13:50 cat: Prepare, proto: postgresql-9, msg: Statement 1 : select * from sayimbaslik where id = ?
2017-06-15 13:13:50 cat: Bind prepared, proto: postgresql-9, msg: Statement 1 : '{2E964560-F194-4F1C-B96A-43B96452F9BC}',
2017-06-15 13:13:50 cat: Execute, proto: postgresql-9, msg: select pc.relname, pns.nspname, pa.attnum, pa.attname from pg_catalog.pg_class pc join pg_catalog.pg_namespace pns on pc.relnamespace = pns.oid join pg_catalog.pg_attribute pa on pa.attrelid = pc.oid where pc.oid = 17030 and pa.attnum > 0
2017-06-15 13:13:50 cat: Execute prepared, proto: postgresql-9, msg: Statement 1
2017-06-15 13:13:50 cat: Prepare, proto: postgresql-9, msg: Statement 2 : SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname = E'sayimbaslik' ORDER BY nspname,relname,attnum
2017-06-15 13:13:50 cat: Execute prepared, proto: postgresql-9, msg: Statement 2
2017-06-15 13:13:50 cat: Execute, proto: postgresql-9, msg: SELECT oid, typname, typbasetype,typtype FROM pg_type WHERE (typtype = 'b' and oid < 10000) OR typtype = 'p' OR typtype = 'e' OR typbasetype<>0 ORDER BY oid
2017-06-15 13:13:50 cat: Execute, proto: postgresql-9, msg: DEALLOCATE "2249064721";

However, quite strangely, I have no problem with below code and everything works as expected:

Code: Select all

procedure TForm1.SpeedButton1Click(Sender: TObject);
var
  Guid: TGuid;
begin
  CreateGUID(Guid);

  ZQuery1.SQL.Text := 'create table if not exists test2(id uuid not null primary key, acol integer)';
  ZQuery1.ExecSQL();
  ZQuery1.SQL.Text := 'insert into test2 values(:id, 3)';
  ZQuery1.Params[0].AsString := GuidToString(Guid);
  ZQuery1.ExecSQL();
  ZQuery1.SQL.Text := 'select * from test2 where id = :id';
  ZQuery1.Params[0].AsString := GuidToString(Guid);
  ZQuery1.Open();
  // I can see id field in dbgrid and records just fine
end;

I am stuck in the middle of my conversion process from SQLdb to Zeos.

Any help is appreciated.


EDIT: I confirm that I do not have id field when I do a "select * from" with Lazarus 1.8.0RC1 64bit, running on Windows 10 64bit.

[marsupilami]

Hello Ertan,

this is a bug - actually more than one bug - in Zeos. Fixing it will take some time. I will keep you informed here.

With best regards,

Jan

[ertank]

Hello Jan,

I already completed my switch to zeos library. My luck, I recognize this problem after converting to zeos finished for good.

Just asking, do you think you will have a work around by next Monday? I have no intention of pressure at all.

If you believe it takes more than a couple of days, I need to switch back to SQLdb as I put additional form designs and code in the project. I do not want to do this by all means.

Thanks & regards,

Ertan

[ertank]

Until this bug gets fixed, one may use something like below as a workaround.

Code: Select all

ZQuery.ParamChar := '#';
ZQuery.SQL.Text := 'select id::text, next_field, other_field, last_field from table where id = #id';

"id" is a uuid field in the table. We typecast it to text data type in above SQL. Typecast field will return a text data type in the result set for uuid field and Zeos process that text data types fine.

One should type all field names if he/she needs to use all of them in the application code. This is the culprit of this workaround.