DBX Error: Invalid Field Type ??

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



LVL 1
BalsheAsked:
Who is Participating?
 
vadim_tiConnect With a Mentor Commented:
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
 
BalsheAuthor Commented:
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
 
vadim_tiCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
vadim_tiCommented:
also you can try

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


0
 
BalsheAuthor Commented:
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
 
vadim_tiCommented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
BalsheAuthor Commented:
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
 
vadim_tiCommented:
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
 
BalsheAuthor Commented:

those bothe queries works fine
0
 
vadim_tiCommented:
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
 
BalsheAuthor Commented:
the first one works

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








0
 
vadim_tiCommented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
BalsheAuthor Commented:
this one works
0
 
vadim_tiCommented:
so you need to do in your query

qryGet_IN_calls_Records.ParamByName('File_ID').AsBCD :=FILE_ID;
0
 
BalsheAuthor Commented:
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
 
BalsheAuthor Commented:
i'm Still Having the same error with another query



0
 
vadim_tiCommented:
post your another query, and data types you use in it
0
 
BalsheAuthor Commented:
 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
 
BalsheAuthor Commented:
Field "ID" in Table  TESTING_PROCESSED_FILES  is of type "Number"

Process_File_ID :Integer;


0
 
vadim_tiCommented:
1)how id field is defined in database?

2)if query

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

is working?
0
 
BalsheAuthor Commented:
no
if i put a large number "401261"   it will give the same error


0
 
vadim_tiCommented:
try

Qry_Update_Status.ParamByName('File_ID').AsFmtBCD :=Process_File_ID;
0
 
vadim_tiCommented:
or

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

0
 
BalsheAuthor Commented:
they don't work
0
 
BalsheAuthor Commented:
There is no Datatype in the field definition of TSQLQUERY






0
 
vadim_tiCommented:
are you sure?

FieldByName method of TSQLQuery returns TField
TField has property DataType

at least in delphi7
0
 
vadim_tiCommented:
lets make

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

0
 
BalsheAuthor Commented:
sorry there is but it's of type TFieldType ,so how are you gonna convert it to string??? using INTtostr will not work
0
 
vadim_tiCommented:
ORD

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

0
 
BalsheAuthor Commented:
the result is
'TFMTBCDField'
0
 
vadim_tiCommented:
try please this string


Qry_Update_Status.ParamByName('File_ID').AsFMTBcd := StrToBcd('401261');
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
BalsheAuthor Commented:
still not working in all queries
0
 
vadim_tiCommented:
this query work?
send another one
0
 
Pierre CorneliusCommented:
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
 
vadim_tiCommented:
sure it is better
0
 
Pierre CorneliusCommented:
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
 
BalsheAuthor Commented:
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
 
vadim_tiCommented:
bad dbexpress oracle driver i think
0
 
BalsheAuthor Commented:
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
 
vadim_tiCommented:
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
 
BalsheAuthor Commented:
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
 
Pierre CorneliusCommented:
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
 
BalsheAuthor Commented:
yes PierreC it's correct too
0
 
BalsheAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.