• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

Funky Invalid Field Type error when using TQuery descendant w/SQL2005

This query was caught by profiler from the Open method of a Delphi TQuery
descendant. TQuery->TwwQuery->TSWQuery (Our home-rolled descendant)

We were setting the FQuery.SQL.Text to
'SELECT Store_key, Store_ID, Store_Name from SYCFSTORE'
and issued an FQuery.Open and keep getting a Invalid Field Type error on
the SQL 2005 database.

select   c.name,
  t.type,
  c.length,
  c.status,
  t.name,
  c.prec,
  c.scale
from syscolumns c,
     systypes t
where c.id = object_id ('dbo.SYCFSTORE') and
c.usertype = t.usertype order by colid ASC


In SQL 2000, this is the result of the above query:
name                           type length status name                     prec   scale
------------------------------ ---- ------ ------ ------------------------ ------ -----------
STORE_KEY                      56   4      0      int                      10     0
STORE_ID                       39   10     16     varchar                  10     NULL
STORE_NAME                     39   35     16     varchar                  35     NULL
GUID                           63   16     8      bigint                   0      NULL
GUID                           47   16     8      nchar                    0      NULL
GUID                           35   16     8      ntext                    0      NULL
GUID                           39   16     8      nvarchar                 0      NULL
GUID                           39   16     8      sql_variant              0      NULL
GUID                           37   16     8      uniqueidentifier         0      NULL
ADDRESS_KEY                    56   4      8      int                      10     0
PHONE_KEY                      56   4      8      int                      10     0
IN_STORE_AREA                  56   4      8      int                      10     0
POS_KEY                        56   4      8      int                      10     0
STORE_SYSTEM_TYPE              47   5      16     UDT_TYPE                 5      NULL
STORE_VERSION_NUM              63   5      8      numeric                  5      2
START_BUSINESS_DAY             58   4      8      smalldatetime            16     0
START_CALENDAR_DAY             58   4      8      smalldatetime            16     0
CURRENT_BUSINESS_DAY           58   4      8      smalldatetime            16     0
NEXT_BUSINESS_DAY              58   4      8      smalldatetime            16     0
GL_DEPT                        47   6      56     char                     6      NULL
FIRST_UNCLOSED_DAY             58   4      8      smalldatetime            16     0
NEXT_EXPORT_NUM                56   4      0      int                      10     0
INITIALIZATION_EXPORT_FLAG     47   1      16     UDT_BOOLEAN_DFLT_FALSE   1      NULL
NEXT_HQ_EXPORT_NUM             56   4      0      int                      10     0


This is the result from running the query in SQL 2005:
name                           type length status name                      prec   scale
----------------------------------- ------ ------ ------------------------- ------ -----------
STORE_KEY                      56   4      0      int                       10     0
STORE_ID                       39   10     16     varchar                   10     NULL
STORE_NAME                     39   35     16     varchar                   35     NULL
GUID                           37   16     8      uniqueidentifier          0      NULL
GUID                           39   16     8      sql_variant               0      NULL
GUID                           35   16     8      ntext                     0      NULL
GUID                           63   16     8      bigint                    0      NULL
GUID                           39   16     8      nvarchar                  0      NULL
GUID                           47   16     8      nchar                     0      NULL
GUID                           0    16     8      xml                       0      NULL
ADDRESS_KEY                    56   4      8      int                       10     0
PHONE_KEY                      56   4      8      int                       10     0
IN_STORE_AREA                  56   4      8      int                       10     0
POS_KEY                        56   4      8      int                       10     0
STORE_SYSTEM_TYPE              47   5      16     UDT_TYPE                  5      NULL
STORE_VERSION_NUM              63   5      8      numeric                   5      2
START_BUSINESS_DAY             58   4      8      smalldatetime             16     0
START_CALENDAR_DAY             58   4      8      smalldatetime             16     0
CURRENT_BUSINESS_DAY           58   4      8      smalldatetime             16     0
NEXT_BUSINESS_DAY              58   4      8      smalldatetime             16     0
GL_DEPT                        47   6      56     char                      6      NULL
FIRST_UNCLOSED_DAY             58   4      8      smalldatetime             16     0
NEXT_EXPORT_NUM                56   4      0      int                       10     0
INITIALIZATION_EXPORT_FLAG     47   1      16     UDT_BOOLEAN_DFLT_FALSE    1      NULL
NEXT_HQ_EXPORT_NUM             56   4      0      int                       10     0

Why is it pulling over the GUID field with an xml type and is there a way to prevent
this from happening? Not sure where this query is actually being run in our descendant.
even debugging, we can't seem to catch it.
0
Eddie Shipman
Asked:
Eddie Shipman
  • 7
  • 2
1 Solution
 
Tomas Helgi JohannssonCommented:
   Hi!
Do you have any fields listed in the FieldList of the Query component ?
If so delete all field from that list and re-add all fields to the list.
That could be the issue that the Query component is using old field defs
after you have altered the table field defs on the database.

Regards,
  Tomas Helgi
0
 
Eddie ShipmanAll-around developerAuthor Commented:
This is a dynamic query where we are adding fields like this:

    with TIntegerField.Create(Owner) do
    begin
      // SQL Fieldtype is int
      FieldName := 'STORE_KEY';
      Name := Self.Name + FieldName;
      Origin := FQuery.DatabaseName + '.SYCFSTORE.' + FieldName;
      DataSet := FQuery;
    end;
    with TStringField.Create(Owner) do
    begin
      // SQL Fieldtype is varchar(10)
      FieldName := 'STORE_ID';
      FixedChar := True;
      Size := 3;
      Name := Self.Name + FieldName;
      Origin := FQuery.DatabaseName + '.SYCFSTORE.' + FieldName;
      DataSet := FQuery;
    end;
    with TStringField.Create(Owner) do
    begin
      // SQL Fieldtype is varchar(35)
      FieldName := 'STORE_NAME';
      FixedChar := True;
      Size := 35;
      Name := Self.Name + FieldName;
      Origin := FQuery.DatabaseName + '.SYCFSTORE.' + FieldName;
      DataSet := FQuery;
    end;

Then, when we issue the Open on the next line, we get the problem.
This control has worked flawlessly for yrs on SQL2000 and now having
to support SQL2005, we are running into a few inconsistencies such
as this.

Any idea how to remove the problem?
0
 
Eddie ShipmanAll-around developerAuthor Commented:
BTW, we have GUID as SQL Fieldtype uniqueidentifier in our table.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Tomas Helgi JohannssonCommented:
Hmmm,
The only difference I can see on the lists above is that SQL2005 has this line
GUID                           0    16     8      xml                       0      NULL
                                                          -----
Does your app support/know how to handle XML field type ?

Regards,
  Tomas Helgi
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Also, I cannot find where the above query is being run. I believe it is run in DbiGetFieldDescs which is
in the BDE unit and since they didn't include the source to BDE, I can't modify the query to fix the
problem.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
I don't think it has anything to do with my app, It seems to be the control having the problem.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Traced it down to this statement:
{Line 8911 in DBTables.pas (procedure TQuery.GetStatementHandle(SQLText: PChar))}
  while not CheckOpen(DbiQPrepare(FStmtHandle, SQLText)) do

Apparently the DbiQPrepare returns the Status 10000 which corresponds to the DbiError
for Invalid Field Type.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
I have tried adding the GUID field to the fielddefs implicitly like the store_key, store_id, and store_name fields
and also totally removing the addition of the persistant fields and I still get the error on a SQL2005 DB.
Since it looks like it is coming from a BDE procedure, I do not think I'm going to be able to fix the SQL
that this thing is using.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Solution:
Modify GUID field FieldType from uniqueidentifier to VarChar(36). More mods need to be made to make
applcaition work correctly but we sort of solved it this way.
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now