Solved

DBX Error: Invalid Field Type ??

Posted on 2004-09-20
47
1,559 Views
Last Modified: 2008-01-09
hi all


I have this Error when ever i try to open sqlquery
the error is :

DBX Error: Invalid Field Type

Database is Oracle
I'm using Delphi 6.0

Here is the code:



here is Database Decleration

...
...
...

dbIncalls:TSQLConnection;

...
...
...

Here i open tha database in another procedure


...
...
...



    dbIncalls                 := TSQLConnection.Create(nil);
    dbIncalls.DriverName      := 'Oracle';
    dbIncalls.GetDriverFunc   := 'getSQLDriverORACLE';
    dbIncalls.ConnectionName  := 'OracleConnection';
    dbIncalls.LibraryName     :=  'dbexpora.dll';
    dbIncalls.VendorLib       := 'oci.dll';
    dbIncalls.KeepConnection  := true;
    dbIncalls.Params.Add('DriverName=Oracle');
    dbIncalls.Params.Add('Database=SRV');
    dbIncalls.Params.Add('User_name=USER1000');
    dbIncalls.Params.Add('Password=PASS1000');
    dbIncalls.Params.Add( 'Blobsize=1');
    dbIncalls.Params.Add('LocaleCode=0000');
    dbIncalls.Params.Add( 'RowsetSize20' );
    dbIncalls.Params.Add( 'OS Authentification=False' );
    dbIncalls.Params.Add( 'Multiple Transaction=False' );
    dbIncalls.Params.Add('Trim Char=false' );
    dbIncalls.Params.Add( 'Oracle TransIsolation=ReadCommited');
    dbIncalls.LoadParamsOnConnect := false;
    dbIncalls.AutoClone := false;
    dbIncalls.LoginPrompt := false;
    dbIncalls.Connected := true;




...
...
...


Var
  qryGet_IN_calls_Records      :  TSQLQuery;
  Current_Calling_party_number :  String;
  InCalls_array_index          :  Integer;
  Start_index,End_index        :  Longint      ;
Begin
  TRY
     qryGet_IN_calls_Records:=TSQLQuery.Create(nil);
     qryGet_IN_calls_Records.SQLConnection :=dbIncalls;
     qryGet_IN_calls_Records.SQL.Clear;
     qryGet_IN_calls_Records.SQL.Add(' Select Calling_party_number,Call_begin_time,SUBSCRIBER_TYPE,BALANCE,');
     qryGet_IN_calls_Records.SQL.Add(' Call_begin_time,Calling_party_number from TESTING_IN_CALLS where File_ID=:File_ID');
     qryGet_IN_calls_Records.ParamByName('File_ID').AsInteger :=FILE_ID;
{ Here is the problem }
     qryGet_IN_calls_Records.Open;       // <------
{ DBX Error: Invalid Field Type }
     While not qryGet_IN_calls_Records.Eof Do
       Begin
               ...
               ...
               ...


       End;




Appreciate your help



0
Comment
Question by:Balshe
  • 21
  • 20
  • 3
  • +1
47 Comments
 
LVL 1

Author Comment

by:Balshe
Comment Utility
The Type of the Field Called "File_ID" IS

FILE_ID                                NUMBER(10)

i tried almost every thing i know

i'm asking for your help


0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
1) try the same query without "where" clause, to know if a problem with a parameter
2) if a problem is with a parameters try
qryGet_IN_calls_Records.ParamCheck := false;
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
also you can try

qryGet_IN_calls_Records.ParamByName('File_ID').AsFMTBCD := VarToBcd(FILE_ID);


0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
1) try the same query without "where" clause, to know if a problem with a parameter


i tried that ,didn't work
and also tried this :

 qryGet_IN_calls_Records.SQL.Add(' Select Calling_party_number,Call_begin_time,SUBSCRIBER_TYPE,BALANCE,');
  qryGet_IN_calls_Records.SQL.Add(' Call_begin_time,Calling_party_number from TESTING_IN_CALLS where File_ID=1'); //this works ,but not all the time

2) if a problem is with a parameters try
qryGet_IN_calls_Records.ParamCheck := false;

i have the same problem with other queries that have more than one parameter ,so this will not work with me ,because it will paybass other parameters

3) qryGet_IN_calls_Records.ParamByName('File_ID').AsFMTBCD := VarToBcd(FILE_ID);
returned the same error



any other ideas ?







0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
what you mean
//this works ,but not all the time


qryGet_IN_calls_Records.ParamByName('File_ID').AsString := '1';

qryGet_IN_calls_Records.ParamByName('File_ID').Value := 1;


0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
Comment Utility
I guess that you can't call a Parameter with the same name of an already existent field....
this > qryGet_IN_calls_Records.ParamByName('File_ID').AsInteger :=FILE_ID;
Your code is puzzling through Field  File_ID, param FILE_ID and value FILE_ID....

try renaming the parameter to ID_FILE

qryGet_IN_calls_Records.SQL.Add(' Select Calling_party_number,Call_begin_time,SUBSCRIBER_TYPE,BALANCE,');
     qryGet_IN_calls_Records.SQL.Add(' Call_begin_time,Calling_party_number from TESTING_IN_CALLS where File_ID=:ID_FILE');
     qryGet_IN_calls_Records.ParamByName('ID_FILE').AsInteger := FILE_ID;

F68 ;-)
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
Hi Ferruccio68

I tried that before, it didn't work --> returns the same error

------------
vadim_ti


I tried concatenating the sql string and on some queries it works, other queries it gives the same error (DBX Error: Invalid Field Type)
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
I think first of all you need to know where is a problem, SELECT clause, WHERE clause
or parameters using

you can try

const
q1='SELECT * from TESTING_IN_CALLS';
q2='SELECT * from TESTING_IN_CALLS WHERE File_ID=1';


you need to localize what make a problem
try to get working query without parameters  (working all the time)
after it will be possible to find right for parameter passing
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility

those bothe queries works fine
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
const
q2='SELECT * from TESTING_IN_CALLS WHERE File_ID=%d';
q3='SELECT * from TESTING_IN_CALLS WHERE File_ID=:p1';

not fine solution but must work:


qryGet_IN_calls_Records.SQL.Clear;
qryGet_IN_calls_Records.SQL.Add(format(q2, [1]);
qryGet_IN_calls_Records.SQL.Open;

==============================
try please also
qryGet_IN_calls_Records.SQL.Clear;
qryGet_IN_calls_Records.SQL.Add(q3);
qryGet_IN_calls_Records.Params[0].Value := 1;
qryGet_IN_calls_Records.SQL.Open;
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
the first one works

the second one didn't work giving the same error------------>DBX Error: Invalid Field Type








0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
Last try and i give up


const
q3='SELECT * from TESTING_IN_CALLS WHERE File_ID=:p1';


qryGet_IN_calls_Records.SQL.Clear;
qryGet_IN_calls_Records.SQL.Add(q3);
qryGet_IN_calls_Records.ParamByName('p1').AsBCD := 1;
qryGet_IN_calls_Records.SQL.Open;

0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
Comment Utility
just from head: couldn't be that FILE_ID var is a Int64 type var? I mean: It could be that you're passing a value to the parameter that is out of Integer range....
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
this one works
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
so you need to do in your query

qryGet_IN_calls_Records.ParamByName('File_ID').AsBCD :=FILE_ID;
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
yes i know just let me fix all queries

and i'll let you know

thanks


========
Ferruccio68

File_ID is integer ,the values that i have  are  something like this
(401259 , 401260, 401261 ...etc)
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
i'm Still Having the same error with another query



0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
post your another query, and data types you use in it
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
 Qry_Update_Status:=TSQLQuery.Create(nil);
  Qry_Update_Status.SQLConnection:=dbIncalls;
  Qry_Update_Status.SQL.Add('update TESTING_PROCESSED_FILES set status=1 where id=:FILE_ID');
  Qry_Update_Status.ParamByName('File_ID').AsBCD :=Process_File_ID;
  Qry_Update_Status.ExecSQL(false);
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
Field "ID" in Table  TESTING_PROCESSED_FILES  is of type "Number"

Process_File_ID :Integer;


0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
1)how id field is defined in database?

2)if query

'update TESTING_PROCESSED_FILES set status=1 where id=2'

is working?
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
no
if i put a large number "401261"   it will give the same error


0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
try

Qry_Update_Status.ParamByName('File_ID').AsFmtBCD :=Process_File_ID;
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
or

Qry_Update_Status.ParamByName('File_ID').AsFloat :=Process_File_ID;

0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
they don't work
0
 
LVL 6

Accepted Solution

by:
vadim_ti earned 500 total points
Comment Utility
last try:

Qry_Update_Status.ParamByName('File_ID').AsFmtBCD :=DoubleToBcd(Process_File_ID);

or

Qry_Update_Status.ParamByName('File_ID').AsFmtBCD :=VarToBcd(Process_File_ID);
============================
one more thing:
could you please make this:

const
q1='select  ID from  TESTING_PROCESSED_FILES';



     qryGet_IN_calls_Records:=TSQLQuery.Create(nil);
     qryGet_IN_calls_Records.SQLConnection :=dbIncalls;
     qryGet_IN_calls_Records.SQL.Clear;
     qryGet_IN_calls_Records.SQL.Add(q1);
     qryGet_IN_calls_Records.Open;

ShowMessage(IntToStr(ord(qryGet_IN_calls_Records.fieldbyname('ID').DataType)));

and send me please what number you will get

0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
There is no Datatype in the field definition of TSQLQUERY






0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
are you sure?

FieldByName method of TSQLQuery returns TField
TField has property DataType

at least in delphi7
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
lets make

ShowMessage(qryGet_IN_calls_Records.fieldbyname('ID').ClassName);

0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
sorry there is but it's of type TFieldType ,so how are you gonna convert it to string??? using INTtostr will not work
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
ORD

ShowMessage(IntToStr(ORD(qryGet_IN_calls_Records.fieldbyname('ID').DataType)));

0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
the result is
'TFMTBCDField'
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
try please this string


Qry_Update_Status.ParamByName('File_ID').AsFMTBcd := StrToBcd('401261');
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
Comment Utility
In oracle all numbers above 8 must be handled as floatfield

so a NUMBER(7) can be handled as TIntegerField, but in your case NUMBER(10) you have to handle it as TFloatField

0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
still not working in all queries
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
this query work?
send another one
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
Comment Utility
I was just wondering about this:

> ShowMessage(IntToStr(ORD(qryGet_IN_calls_Records.fieldbyname('ID').DataType)));

Is this not better?

> ShowMessage(GetEnumName(TypeInfo(TFieldType), qryGet_IN_calls_Records.fieldbyname('ID').DataType));
 {You will need TypInfo in uses clause)
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
sure it is better
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
Comment Utility
AFTER:
=====

TRY
     qryGet_IN_calls_Records:=TSQLQuery.Create(nil);
     qryGet_IN_calls_Records.SQLConnection :=dbIncalls;
     qryGet_IN_calls_Records.SQL.Clear;
     qryGet_IN_calls_Records.SQL.Add(' Select Calling_party_number,Call_begin_time,SUBSCRIBER_TYPE,BALANCE,');
     qryGet_IN_calls_Records.SQL.Add(' Call_begin_time,Calling_party_number from TESTING_IN_CALLS where File_ID=:File_ID');
     qryGet_IN_calls_Records.ParamByName('File_ID').AsInteger :=FILE_ID;


ADD THIS:
=======
     qryGet_IN_calls_Records.ParamByName('File_ID').ParamType:= ptInput;
     qryGet_IN_calls_Records.ParamByName('File_ID').DataType:= ftInteger;
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
thanks  PierreC
i tried this before posting the question
but i find  vadim_ti way intresting in some how
if i can find the class of the field i can change it

but the obly problem is this

this thread used to be "BDE" and now when i'm trying "DBEXPRESS" it became more slower than before
why is that?
0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
bad dbexpress oracle driver i think
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
so how can i solve that?
the problem is that each thread works this way:
--opens it's own connection
--begin trans
--process records and files
--if faild at any point then rollback
    else
         commit
--give feedback to main thread
--terminate

so i need dbexpress because i can make multible transactions

0
 
LVL 6

Expert Comment

by:vadim_ti
Comment Utility
1)how much threads you run simultaneously?
2)how long thread processing?
3)what about queries? is it ok now?

you can try ADO model with OLEDB provider for Oracle
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
about queries you gave me the answer or you can say approach and it's good

now while i'm testing it's only 1 or 2
but on life it's gonna be 10 to 40





0
 
LVL 14

Expert Comment

by:Pierre Cornelius
Comment Utility
Based on Ferrucio's comment and my previous post:

What about

>qryGet_IN_calls_Records.ParamByName('File_ID').DataType:= ftFloat; (or maybe even a ftLargeInt)
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
yes PierreC it's correct too
0
 
LVL 1

Author Comment

by:Balshe
Comment Utility
hi  all


Thanks to you all Guys you’ve all helped me in a way
But special thanks to vadim_ti  

vadim_ti  could you go to this URL:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21138881.html

and post a comment

thanks

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now