FieldByName on SmallInt Column returns wrong value

ralle1 · Post by **ralle1** » Tue 03 May 2011 07:18

I’ve got the following problem:

I work with Database Firebird 2.1, a Table with a column with type SMALLINT.
The selected Record has a value of -14000 in the SMALLINT column.

The select with TIBCQUERY works fine, if I read out the value with IBCQuery1.FieldByName('FIELD1').AsInteger it returns -14000.

Now I define persistent Fields in the Fieldeditor. The special is that the SMALLINT Column FIELD1 is defined in Delphi as a TIntegerField. If I now read out the value with IBCQuery1.FieldByName('FIELD1').AsInteger it returns a wrong Value 51536.

Devart IBDAC 3.10.0.16 for Delphi 2007 for Win32

Note: Because there are several databases with different definitions, means INTEGER or SMALLINT in the Column FIELD1. So it's just not possible to change the source code to TsmallIntField.

Is there a way to solve this problem?

Greetings

Dimon · Post by **Dimon** » Wed 04 May 2011 11:11

Unfortunately, the only way to avoid this problem is to map smallint columns to TSmallintField. You can look at the TIntegerField.GetValue method in the DB unit to understand the reason of this problem.

ralle1 · Post by **ralle1** » Mon 09 May 2011 08:42

Thanks for the note. I hope I find any elegant solution to solve my problem. Thank you for the help.

ralle1 · Post by **ralle1** » Mon 09 May 2011 14:32

I have an idea to solve the problem - maybe you can give me a little tip:

I want to change the FieldType in the BeforeOpen event. For this I have overridden the method TSmallintField.SetFieldType. This works. But now I have to check what fieldkind (smallint or integer) the field in the firebird database is. I could identify with the system tables, but for that I must know the name of the table which is selected. Do you have any idea how I can find out this?

PS: I know this is not a “Devart problem” – but perhaps you have got a good idea!!

Dimon · Post by **Dimon** » Tue 10 May 2011 08:07

To solve the problem, you can use the AfterExecute event handler instead of BeforeOpen and use the GetFieldDesc method there, like this:

Code: Select all

  if IBCQuery.GetFieldDesc(Fieldname).DataType = dtSmallint then
    ...
  else
  if IBCQuery.GetFieldDesc(Fieldname).DataType = dtInt32 then
    ...

ralle1 · Post by **ralle1** » Tue 10 May 2011 09:31

Thank you Dimon, that works fantastic.

I hope I do not nerve - but another small question please:

If I want to change the datatype of a column in the Firebird database the command “ALTER TABLE TESTTABLE ALTER COLUMN TESTCOL TYPE INTEGER” doesn’t work, because it’s part of an index segment.

Do you know a way to solve this by updating the system tables ?

Many thanks for your help.

AndreyZ · Post by **AndreyZ** » Thu 12 May 2011 07:51

Please take a look at this article: http://www.firebirdfaq.org/faq74

ralle1 · Post by **ralle1** » Fri 13 May 2011 12:48

Thank you for the information.