[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-31
11
Medium Priority
?
642 Views
Last Modified: 2011-10-03
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
Comment
Question by:EddieShipman
  • 7
  • 2
10 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 19607912
   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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19608745
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19608768
BTW, we have GUID as SQL Fieldtype uniqueidentifier in our table.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 19608994
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19609155
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19609163
I don't think it has anything to do with my app, It seems to be the control having the problem.
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 19610759
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19610824
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 19627935
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
 
LVL 1

Accepted Solution

by:
Vee_Mod earned 0 total points
ID: 19677651
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question