Solved

DBX Error: Invalid Field Type ??

Posted on 2004-09-20
47
1,570 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
ID: 12109438
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
ID: 12109684
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
ID: 12109740
also you can try

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


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Balshe
ID: 12109769
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
ID: 12109803
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
ID: 12109830
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
ID: 12109862
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
ID: 12109904
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
ID: 12109934

those bothe queries works fine
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12109966
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
ID: 12110193
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
ID: 12110229
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
ID: 12110251
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
ID: 12110268
this one works
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12110280
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
ID: 12110306
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
ID: 12110611
i'm Still Having the same error with another query



0
 
LVL 6

Expert Comment

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

Author Comment

by:Balshe
ID: 12110641
 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
ID: 12110648
Field "ID" in Table  TESTING_PROCESSED_FILES  is of type "Number"

Process_File_ID :Integer;


0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12110656
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
ID: 12110707
no
if i put a large number "401261"   it will give the same error


0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12110735
try

Qry_Update_Status.ParamByName('File_ID').AsFmtBCD :=Process_File_ID;
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12110743
or

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

0
 
LVL 1

Author Comment

by:Balshe
ID: 12111041
they don't work
0
 
LVL 6

Accepted Solution

by:
vadim_ti earned 500 total points
ID: 12111166
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
ID: 12111225
There is no Datatype in the field definition of TSQLQUERY






0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12111276
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
ID: 12111303
lets make

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

0
 
LVL 1

Author Comment

by:Balshe
ID: 12111318
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
ID: 12111329
ORD

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

0
 
LVL 1

Author Comment

by:Balshe
ID: 12111342
the result is
'TFMTBCDField'
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12111422
try please this string


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

Expert Comment

by:Ferruccio Accalai
ID: 12111449
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
ID: 12111516
still not working in all queries
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12111529
this query work?
send another one
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12111611
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
ID: 12111643
sure it is better
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12111647
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
ID: 12111763
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
ID: 12111808
bad dbexpress oracle driver i think
0
 
LVL 1

Author Comment

by:Balshe
ID: 12111881
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
ID: 12111970
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
ID: 12112260
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
ID: 12112329
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
ID: 12112414
yes PierreC it's correct too
0
 
LVL 1

Author Comment

by:Balshe
ID: 12119887
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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