Solved

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

Posted on 2010-08-17
8
1,523 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 37

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 37

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

22 Experts available now in Live!

Get 1:1 Help Now