"Unknown Type: 14" error accessing bigint field

[Cigydd]

Hi all,

I encounter the following error using ZeosLib (v. 6.6.4 and 6.6.5).

In my code:

Code: Select all

 if DMCis.QHl.FieldByName('CF').Value<>Null then

the Value returned is shown in Delphi as "Unknown Type: 14".
An error is raised saying "Invalid variant type conversion".

The undelying data field (PostgreSQL) has data type bigint.

I consider this a bug.

I've read somewhere that the variant type 14 corresponds to the VT_DECIMAL type. I've found it in the ZVariant unit but by debugging I noticed that the code there isn't involved anyhow.

What should I do about this? I need to work with the bigint number as variant under Zeos too. BDE worked fine in this case but I like Zeos too much to leave it

[seawolf]

Try adding, on ZDbcPostgresqlutils.pas,

function PostgreSQLToSQLType(Connection: IZPostgreSQLConnection;
TypeOid: Integer): TZSQLType; overload;
begin
case TypeOid of
1186,18,1043: Result := stString; { interval/char/varchar }
25: Result := stAsciiStream; { text }
26: { oid }
begin
if Connection.IsOidAsBlob() then
Result := stBinaryStream
else
Result := stInteger;
end;
19: Result := stString; { name }
21: Result := stShort; { int2 }
23: Result := stInteger; { int4 }
20,14: Result := stLong; { int8 }
700: Result := stFloat; { float4 }
701,1700: Result := stDouble; { float8/numeric. no 'decimal' any more }
790: Result := stFloat; { money }
16: Result := stBoolean; { bool }
1082: Result := stDate; { date }
1083: Result := stTime; { time }
1114,1184,702: Result := stTimestamp; { timestamp,timestamptz/abstime. no 'datetime' any more}
24: Result := stString; { regproc }
17: { bytea }
begin
if Connection.IsOidAsBlob then
Result := stBytes
else
Result := stBinaryStream;
end;
1042: Result := stString; { bpchar }
22,30: Result := stAsciiStream; { int2vector/oidvector. no '_aclitem' }
651, 1000..1028: Result := stAsciiStream;
else
Result := stUnknown;
end;
end;

and

function PostgreSQLToSQLType(Connection: IZPostgreSQLConnection;
TypeName: string): TZSQLType;
begin
TypeName := LowerCase(TypeName);
if (TypeName = 'interval') or (TypeName = 'char')
or (TypeName = 'varchar') 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 = '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') or (TypeName = 'bigint') 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;

[Cigydd]

Hi seawolf,

thank you very much for your efforts but it didn't work for me.

Do I need to recompile Zeos after the changes?
I added your recommendations and recompiled the Zdbc and ZComponent packages but still no change.

Maybe another code point is still causing variant type 14 to appear as that bigint field's Value. Debugging doesn't give me any clue.

[seawolf]

Can you provide the sql declaration of that table or, at least, of that field?

[Cigydd]

Code: Select all

…
cf bigint,
…

The table is declared in Czech, the declaration is very long and I doubt it's of any use.

[seawolf]

I've done some tests with Delphi 2009 and an svn snapshost but I encountered no problems. So I need more infos:

- Have you tried the lastest svn version? Do you encounter the same problem?
- Which Delphi version are you currently using?
- Which component is QHl?

[Cigydd]

Thanks again.

- I'm using Zeos 6.6.5 stable. Will test on SVN if needed.
- I'm using Delphi 5 and it may be the catch (it doesn't support Variant type vtInt64; VT_DECIMAL (=14) used in ZVariant unit works but it creates an unusable Variant ("Unknown type 14")).
- QHl is TZQuery

[Cigydd]

One, partial, solution.

When I don't need the value as a Variant and want only to test it against Null, I can use the field's IsNull property. This method doesn't generate an error.