Solved

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

Posted on 2010-08-17
8
1,540 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 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