Solved

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

Posted on 2010-08-17
8
1,507 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:fprovin
  • 3
  • 3
  • 2
8 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 33459765
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
 

Author Comment

by:fprovin
ID: 33459832
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
 
LVL 25

Expert Comment

by:epasquier
ID: 33459951
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
 

Author Comment

by:fprovin
ID: 33460121
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Accepted Solution

by:
epasquier earned 500 total points
ID: 33462295
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
 

Author Closing Comment

by:fprovin
ID: 33462546
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
 
LVL 25

Expert Comment

by:epasquier
ID: 33462724
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 33472815
there has been a product for a while...
getting better at universal data acces

http://www.devart.com/unidac/
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 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

19 Experts available now in Live!

Get 1:1 Help Now