I can not create a field of type ENUM in Delphi7

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

Pyh
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 10.12.2009, 11:12
Contact:

I can not create a field of type ENUM in Delphi7

Postby Pyh » 10.12.2009, 11:18

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
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

Postby trupka » 12.12.2009, 00:29

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
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 10.12.2009, 11:12
Contact:

Postby Pyh » 12.12.2009, 20:40

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
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 12.01.2010, 22:42

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[I]
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
You do not have the required permissions to view the files attached to this post.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 19.01.2010, 00:09

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
Image

seawolf
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 19.01.2010, 10:32

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


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest