I can not create a field of type ENUM in Delphi7

[Pyh]

Hi all!

Delphi 7, Zeos 6.6.4-stable, PostgreeSQL 8.4

Thanks for your project, he has very much helped me!
At transition with MySQL on PostgreSQL has faced such problem:

Code: Select all

CREATE TYPE TTestType AS ENUM ('z', 'x', 'c');
CREATE TABLE TestTable (
    ID integer PRIMARY KEY,
    t TTestType
);
INSERT INTO TestTable VALUES ('1', 'z'), ('2', 'x'), ('3', 'c'), ('4', 'c');

From PGAdmin III

Code: Select all

SELECT * FROM TestTable;
id | t
------
1 | z
2 | x
3 | c
4 | c

In Delphi using ZQuery this SELECT create only a field "id" and the field "t" is absent. Tell please what to do?

Thanks.

[trupka]

Zeos 6.6.x don't work with enum types. Maybe you can work around the problem with typecasting eg.

Code: Select all

select id, cast(t as char) as t from TestTable

[Pyh]

Though the real problem is much more difficult than an example (((
While has solved a problem another by - has created two tables:
BaseEnumTypes and BaseEnumValues.

With impatience I wait for support of types ENUM - since this type very convenient in use.

Thanks!

[seawolf]

Open file src\dbc\ZdbcPostgresql.pas and change

function TZPostgreSQLConnection.GetTypeNameByOid(Id: Oid): string;
var
I, Index: Integer;
QueryHandle: PZPostgreSQLResult;
SQL: PAnsiChar;
TypeCode, BaseTypeCode: Integer;
TypeName: string;
LastVersion,isEnum: 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,typtype FROM pg_type'
+ ' WHERE (typtype = ''b'' and oid < 10000) OR typtype = ''p'' OR typtype = ''e'' OR typbasetype<>0 ORDER BY oid';

QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
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);
isEnum := LowerCase(StrPas(FPlainDriver.GetValue(QueryHandle, I, 3))) = 'e';
if isEnum then
TypeName := 'enum'
else
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
else
Result := '';
end;

Open file src\dbc\ZDbcPostgreSqlMetadata.pas and change

function TZPostgreSQLDatabaseMetadata.UncachedGetColumns(const Catalog: string;
const SchemaPattern: string; const TableNamePattern: string;
const ColumnNamePattern: string): IZResultSet;
....

if (PgType = 'bpchar') or (PgType = 'varchar') or (PgType = 'enum') then
begin
if AttTypMod <> -1 then
Result.UpdateInt(7, AttTypMod - 4)
else Result.UpdateInt(7, 0);
end
else if (PgType = 'numeric') or (PgType = 'decimal') then
begin
...

Open file src\dbc\ZDbcPostgreSqlUtils.pas and change

function PostgreSQLToSQLType(Connection: IZPostgreSQLConnection;
TypeName: string): TZSQLType;
begin
TypeName := LowerCase(TypeName);
if (TypeName = 'interval') or (TypeName = 'char')
or (TypeName = 'varchar') or (TypeName = 'bit') or (TypeName = 'varbit') then
Result := stString
else if TypeName = 'text' then
Result := stAsciiStream
else if TypeName = 'oid' then
begin
if Connection.IsOidAsBlob() then
Result := stBinaryStream
else
Result := stInteger;
end
else if TypeName = 'name' then
Result := stString
else if TypeName = 'enum' then
Result := stString
else if TypeName = 'cidr' then
Result := stString
else if TypeName = 'inet' then
Result := stString
else if TypeName = 'macaddr' then
Result := stString
else if TypeName = 'int2' then
Result := stShort
else if TypeName = 'int4' then
Result := stInteger
else if TypeName = 'int8' then
Result := stLong
else if TypeName = 'float4' then
Result := stFloat
else if (TypeName = 'float8') or (TypeName = 'decimal')
or (TypeName = 'numeric') then
Result := stDouble
else if TypeName = 'money' then
Result := stDouble
else if TypeName = 'bool' then
Result := stBoolean
else if TypeName = 'date' then
Result := stDate
else if TypeName = 'time' then
Result := stTime
else if (TypeName = 'datetime') or (TypeName = 'timestamp')
or (TypeName = 'timestamptz') or (TypeName = 'abstime') then
Result := stTimestamp
else if TypeName = 'regproc' then
Result := stString
else if TypeName = 'bytea' then
begin
if Connection.IsOidAsBlob then
Result := stBytes
else
Result := stBinaryStream;
end
else if TypeName = 'bpchar' then
Result := stString
else if (TypeName = 'int2vector') or (TypeName = 'oidvector')
or (TypeName = '_aclitem') then
Result := stAsciiStream
else if (TypeName <> '') and (TypeName[1] = '_') then // ARRAY TYPES
Result := stAsciiStream
else
Result := stUnknown;

if Connection.GetCharactersetCode = csUTF8 then
case Result of
stString: Result := stUnicodeString;
stAsciiStream: Result := stUnicodeStream;
end;
end;

In order to test I added the following procedure in ZTestDbcPostgreSql.pas

procedure TZTestDbcPostgreSQLCase.TestEnumValues;
var
Statement: IZStatement;
ResultSet: IZResultSet;
begin
Statement := Connection.CreateStatement;
CheckNotNull(Statement);
Statement.SetResultSetType(rtScrollInsensitive);
Statement.SetResultSetConcurrency(rcUpdatable);

// Select case
ResultSet := Statement.ExecuteQuery('SELECT * FROM extension where ext_id = 1');
CheckNotNull(ResultSet);
ResultSet.First;
Check(ResultSet.GetInt(1) = 1);
CheckEquals('Car', ResultSet.GetString(2));
ResultSet.Close;
Statement.Close;

// Update case
ResultSet := Statement.ExecuteQuery('UPDATE extension set ext_enum = ''House'' where ext_id = 1');
ResultSet.Close;

ResultSet := Statement.ExecuteQuery('SELECT * FROM extension where ext_id = 1');
CheckNotNull(ResultSet);
ResultSet.First;
Check(ResultSet.GetInt(1) = 1);
CheckEquals('House', ResultSet.GetString(2));
ResultSet.Close;
Statement.Close;

// Insert case
ResultSet := Statement.ExecuteQuery('DELETE FROM extension where ext_id = 1');
ResultSet.Close;

ResultSet := Statement.ExecuteQuery('INSERT INTO extension VALUES(1,''Car'')');
ResultSet.Close;

ResultSet := Statement.ExecuteQuery('SELECT * FROM extension where ext_id = 1');
CheckNotNull(ResultSet);
ResultSet.First;
Check(ResultSet.GetInt(1) = 1);
CheckEquals('Car', ResultSet.GetString(2));
ResultSet.Close;
Statement.Close;

end;

Attach you find the sql code in order to populate the example table

[mdaems]

SVN rev 769 (Testing branch)

Thanks for the patch.

Some comments:
- If somebody wants this feature ported to the 6.6-patches branch : please patch, test and send me the exact changes (files or SVN diff)
- seawolf, can you avoid execution of the tests only when the server version is right? (I'm testing with 83.4, so didn't have any trouble)

Mark

[seawolf]

At the moment I think is not necessary because if no enum fields are found nothing is done.

Anyway the problem is to implement, on the metadata, function added with Postgres 8.3, necessary if someone wants to know all the field are contained in an enum. So in this case it necessary to check which Postgres version is installed