Problem: I use domain on Postgres 8.1 with latest ZeosLib

[krluigo]

Greetings all,

I use Postgres 8.1 with latest (13/10/2005) ZeosLib 6.5.1.-alpha.

I have a problem: use domain (example: CREATE DOMAIN "Str100" AS varchar(100) NULL;), and i do not know to use fields - which this type created - in the Zeos (not initialized as Field in ZDataSet).

Lui

[aperger]

Szia/Hello,

You can download the CVS version with WinCVS, this version contain some bugfix and support 8.1 not only 8.x., so... this version is better! Looks me :-)

Attila

[andrea.lai]

Excuse me
how can download the CVS release ?
I have downloaded last CVS ( 13/10/2005 ) and with PostgreSQL I have much error ...

[aperger]

Excuse me
how can download the CVS release ?
I have downloaded last CVS ( 13/10/2005 ) and with PostgreSQL I have much error ...

Click here "ZeOS CVS + WinCVS"

[krluigo]

Szia/Hello,

You can download the CVS version with WinCVS, this version contain some bugfix and support 8.1 not only 8.x., so... this version is better! Looks me :-)

Attila

Hi,

I try yet (from :pserver:anonymous@cvs.sourceforge.net:/cvsroot/zeoslib), but unfortunately still not working.

Lui

[krluigo]

I think this type oid > 10000 (my cause value is 16681), and this is the problem...., or i don't know

see the code (initialize only oid < 10000!):

Code: Select all

function TZPostgreSQLConnection.GetTypeNameByOid(Id: Oid): string;
var
  I: Integer;
  QueryHandle: PZPostgreSQLResult;
  SQL: PChar;
  TypeCode: Integer;
  TypeName: string;
begin
  if Closed then Open;

  { Fill the list with existed types }
  if not Assigned(FTypeList) then
  begin
    SQL := 'SELECT oid, typname FROM pg_type WHERE oid < 10000';

    QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
    CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL);
    DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);

    FTypeList := TStringList.Create;
    for I := 0 to FPlainDriver.GetRowCount(QueryHandle)-1 do
    begin
      TypeCode := StrToIntDef(StrPas(
        FPlainDriver.GetValue(QueryHandle, I, 0)), 0);
      TypeName := StrPas(FPlainDriver.GetValue(QueryHandle, I, 1));

      FTypeList.AddObject(TypeName, TObject(TypeCode));
    end;
    FPlainDriver.Clear(QueryHandle);
  end;

  I := FTypeList.IndexOfObject(TObject(Id));
  if I >= 0 then
    Result := FTypeList[I]
  else Result := '';
end;

[aperger]

Szia / Hello

I don't know / Sajnos nem tudom....

[krluigo]

I create above domain:

Code: Select all

CREATE DOMAIN "public"."Str100" AS varchar(100) NULL;

I create table with 2 fields (one field type is domain):

Code: Select all

CREATE TABLE "public"."Test"
(
  "field1" "public"."Str100", 
  "field2" INTEGER
) WITH OIDS;

I create ZeosLib ZConnection, etc..., ZTable with Test table, and "Add Fields" in Fields Editor: see the field1 & field2 (TStringField, TInteger)

I reorder the fields (field2 before field1):

Code: Select all

CREATE TABLE "public"."Test"
(
  "field2" INTEGER,
  "field1" "public"."Str100"
) WITH OIDS;

Again "Add Fields": now see only the field2 (TInteger)

I try multiple this thing more fields, which type is domain, in start: see only first domain type field & more not doamain type fields....

What is the problem?

[krluigo]

How work TZAbstractRODataset.InternalInitFieldDefs?!?!???

Code: Select all

procedure TZAbstractRODataset.InternalInitFieldDefs;
var
  I, J, Size: Integer;
  AutoInit: Boolean;
  FieldType: TFieldType;
  ResultSet: IZResultSet;
  FieldName: string;
  FName: string;
begin
  FieldDefs.Clear;
  ResultSet := Self.ResultSet;
  AutoInit := ResultSet = nil;

  try
    { Opens an internal result set if query is closed. }
    if AutoInit then
    begin
      CheckSQLQuery;
      CheckConnected;
      ResultSet := CreateResultSet(FSQL.Statements[0].SQL, 0);
    end;
    if not Assigned(ResultSet) then
      raise Exception.Create(SCanNotOpenResultSet);

    { Reads metadata from resultset. }
    with ResultSet.GetMetadata do
    begin
      for I := 1 to GetColumnCount do
      begin
        GetColumnType(I);
        FieldType := ConvertDbcToDatasetType(GetColumnType(I));

        if FieldType in [ftString, ftWidestring, ftBytes] then
          Size := GetPrecision(I)
        else Size := 0;

        J := 0;
        FieldName := GetColumnLabel(I);
        FName := FieldName;
        while FieldDefs.IndexOf(FName) >= 0 do
        begin
          Inc(J);
          FName := Format('%s_%d', [FieldName, J]);
        end;

        with TFieldDef.Create(FieldDefs, FName, FieldType,
          Size, False, I) do
        begin
          {$IFNDEF FPC}

ResultSet.GetMetadata.GetColumnType(xx): stInteger & stString is correct, before not call this......
after call field type is: stInteger, stUnknown

Code: Select all

          Required := IsNullable(I) = ntNoNulls;
          {$ENDIF}
          if IsReadOnly(I) then
            Attributes := Attributes + [faReadonly];
          Precision := GetPrecision(I);
          DisplayName := FName;
        end;
      end;
    end;

  finally
    { Closes localy opened resultset. }
    if AutoInit then
    begin
      if ResultSet <> nil then
      begin
        ResultSet.Close;
        ResultSet := nil;
      end;
      if Statement <> nil then
      begin
        Statement.Close;
        Statement := nil;
      end;
    end;
  end;
end;

The Call Stack:

TZPostgreSQLDatabaseMetadata.GetColumns('','','teszt','')
TZAbstractResultSetMetadata.GetTableColumns($BC3A58)
TZAbstractResultSetMetadata.ReplaceStarColumns(TZSelectSchema($BC3800) as IZSelectSchema)
TZAbstractResultSetMetadata.LoadColumns
TZAbstractResultSetMetadata.IsNullable(1)
TZAbstractRODataset.InternalInitFieldDefs
TZAbstractRODataset.InternalOpen
Project1

[krluigo]

I use the 2005/09/26 CVS version by TZPostgreSQLConnection.GetTypeNameByOid() in ZDbcPostgreSql.pas & this working...

Code: Select all

function TZPostgreSQLConnection.GetTypeNameByOid(Id: Oid): string;
var
  I, Index: Integer;
  QueryHandle: PZPostgreSQLResult;
  SQL: PChar;
  TypeCode, BaseTypeCode: Integer;
  TypeName: string;
  LastVersion: boolean;
begin
  if Closed then Open;

  if (GetServerMajorVersion < 7 ) or
    ((GetServerMajorVersion = 7) and (GetServerMinorVersion < 3)) then
    LastVersion := True
  else
    LastVersion := False;

  { Fill the list with existed types }
  if not Assigned(FTypeList) then
  begin
    if LastVersion then
      SQL := 'SELECT oid, typname FROM pg_type WHERE oid<10000'
    else
      SQL := 'SELECT oid, typname, typbasetype FROM pg_type'
        + ' WHERE oid<10000 OR typbasetype<>0 ORDER BY oid';

    QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
    CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL);
    DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);

    FTypeList := TStringList.Create;
    for I := 0 to FPlainDriver.GetRowCount(QueryHandle)-1 do
    begin
      TypeCode := StrToIntDef(StrPas(
        FPlainDriver.GetValue(QueryHandle, I, 0)), 0);
      TypeName := StrPas(FPlainDriver.GetValue(QueryHandle, I, 1));

      if LastVersion then
        BaseTypeCode := 0
      else
        BaseTypeCode := StrToIntDef(StrPas(
          FPlainDriver.GetValue(QueryHandle, I, 2)), 0);

      if BaseTypeCode <> 0 then
      begin
        Index := FTypeList.IndexOfObject(TObject(BaseTypeCode));
        if Index >= 0 then
          TypeName := FTypeList[Index]
        else TypeName := '';
      end;
      FTypeList.AddObject(TypeName, TObject(TypeCode));
    end;
    FPlainDriver.Clear(QueryHandle);
  end;

  I := FTypeList.IndexOfObject(TObject(Id));
  if I >= 0 then
    Result := FTypeList[I]
  else Result := '';
end;

@@!#!!###@#!#@!@#@!# suxxx......

[fernando_garetto]

I modified this procedure and now fields associated to domains are available.

function TZPostgreSQLConnection.GetTypeNameByOid(Id: Oid): string;
var
I: Integer;
QueryHandle: PZPostgreSQLResult;
SQL: PChar;
TypeCode: Integer;
TypeName: string;
begin
if Closed then Open;

{ Fill the list with existed types }
if not Assigned(FTypeList) then
begin
SQL := 'SELECT oid, typname FROM pg_type WHERE OID < 10000';

QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL);
DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);

FTypeList := TStringList.Create;
for I := 0 to FPlainDriver.GetRowCount(QueryHandle)-1 do
begin
TypeCode := StrToIntDef(StrPas(
FPlainDriver.GetValue(QueryHandle, I, 0)), 0);
TypeName := StrPas(FPlainDriver.GetValue(QueryHandle, I, 1));

FTypeList.AddObject(TypeName, TObject(TypeCode));
end;
FPlainDriver.Clear(QueryHandle);
end;

I := FTypeList.IndexOfObject(TObject(Id));
if I >= 0 then
Result := FTypeList
else
begin
SQL := PChar('SELECT oid, typname FROM pg_type WHERE OID = ' + IntToStr(id));
QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);

SQL := PChar( 'select udt_name as DataType from information_schema.domains ' +
'where domain_name = '+ '''' + StrPas(FPlainDriver.GetValue(QueryHandle, 0, 1)) + '''');
QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);

TypeName := StrPas(FPlainDriver.GetValue(QueryHandle, 0, 0));
Result := TypeName;
end;
end;

[mdaems]

Hi Fernando,

What Zeoslib version did you change? Your version is quite different from the current beta release, so I need some more information before we can add this to the next release as a bugfix.
What Postgres versions have been tested? What was the problem? ...

Mark