How can i use Field type booelan for mysql table with Delphi

Hi,

I use mysql, my sql odbc and Delphi (BDE components because i need to keep the same application for mysql table or paradox table or access table).
With access for instance my field may have boolean type in Delphi and my code is based on this.
But with mysql, the type is tinyint in mysql and arrive in smallint in Delphi DBE component

How may i convert this tinyint in boolean in a systematic way to avoid adapt and change code ?
Thanks
fprovinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
boolean doesn't always exist in databases

common practise is to use a number type
0 = false,
anything else is <> 0 is true

if you would go interbase there isn't boolean either
not unless you declare it yerself anyway

so best approach : a number for your boolean
IsChecked := FieldByName('X').AsInteger <> 0;
0
fprovinAuthor Commented:
Hello,

i undestand that boolean does'nt always in many databases.
My pb is that my application code is made with odbc access and i need to use the same application with mysql odbc.
==> with access i have boolean type so i have mnay field like that (TBooleanField)

object Table1ValeurBooleen: TBooleanField
  FieldName = 'ValeurBooleen'
  Required = True
end

how can i use your number type without change the TBooleanField previsously created (if i create the same field directly based on mysql odbc it took smallint field but this will change completely my code (many tests, many fields, ...)

thanks
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
Geert is correct, depending on the DB type you simply cannot have TBooleanField.

But TField has a AsBoolean virtual function that can (or cannot) be implemented in other fields. So it might work even with TSmallIntField. Worth checking.

If not, what you should do is creating a function that will manage all kind of fields and return a boolean value :

function FieldAsBoolean(F:TField):boolean;
begin
 if F is TBooleanField Then Result:=TBooleanField (F).AsBoolean;
 if F is TIntegerField Then Result:=TIntegerField (F).AsInteger>0;
end;
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

fprovinAuthor Commented:
Hi,

With TSmallIntField it unfortunatly doesnt work
Your function seems very interesting. Just a question :
where do i call this function (in wTable1ValeurBooleen: TBooleanField) ?

Thanks
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
in all your code, instead of having tests like :

if Table1ValeurBooleen.AsBoolean Then ...

you would have :

if FieldAsBoolean(Table1ValeurBooleen) Then ...

That can be a bit of work if you have many but once done, you can change the type of DB and therefore have whatever TField descendant to represent boolean, as long as you have a way to convert their value into booleans.
Note that " if F is TIntegerField ... " will give a solution with all TIntegerField descendants like TAutoIncField, TSmallintField and TWordField.

You will also probably need a procedure to set the value as well :
procedure SetFieldAsBoolean(F:TField;v:boolean);
begin
 if F is TBooleanField Then TBooleanField(F).AsBoolean:=v;
 if F is TIntegerField Then TIntegerField(F).AsInteger:=Integer(v);
end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fprovinAuthor Commented:
Hi,

Thank you very much : this solution will effectively work with all my code (many changes but it will work with any db like you say).

For the declared object it's still a problem : in component TTable i need to explicitely create the Field and of course his type ==> for odbc Access it's asboolean but if i use this table and his field with mysql i have a exception (incompatibility of type). I have one solution : to create explicitely the field in running after connect to the db but it's means to do this with all tables (100) and fields. Do you have another solution ?

Thanks
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
If you mean that with the same TTable object you will need to change the DB connection at run time, and this connection can either be Access or MySQL, then no there is no easy solution.
There are 2 not-so-easy :
a) as you said, once you changed the connection you recreate the fields but that is not very effective and lots of work.
b) you create datamodules, one for each kind of connection, and put all you need to connect and access tables, fields for ONE DB type only on each.
All your datamodules can be descendants of the same DM ancestor, which will expose the tables and fields as properties. Your descendants DM will only have to route those properties to the actual specific tables.
That is some work to do at the beginning, but fortunately that can be done quickly with cut/paste and then regenerate all the fields once the connection established to the correct DB for each module.

Then, to use these, you have to know which DM is to be used. maybe add another DM to manage all the redirection aspects when you switch to another connection, and DataSources pointing to the correct DataSets.

If you have trouble following me on this, create a new question
0
Geert GOracle dbaCommented:
there has been a product for a while...
getting better at universal data acces

http://www.devart.com/unidac/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.