Page 1 of 1

Problem: I use domain on Postgres 8.1 with latest ZeosLib

Posted: 29.11.2005, 23:17
by 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

Posted: 30.11.2005, 10:41
by 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

Posted: 30.11.2005, 11:25
by 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 ...

Posted: 30.11.2005, 11:54
by aperger
andrea.lai wrote: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"

Posted: 30.11.2005, 15:06
by krluigo
aperger wrote: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

Posted: 30.11.2005, 15:33
by 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;

Posted: 30.11.2005, 15:40
by aperger
Szia / Hello

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

Posted: 02.12.2005, 13:23
by 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?

Posted: 06.12.2005, 11:56
by 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

Posted: 07.12.2005, 16:26
by 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......

Posted: 22.12.2006, 16:13
by 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;

Posted: 23.12.2006, 15:00
by 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