Solved

Take a result set and cast a column as BOOLEAN - the field MUST be Boolean

Posted on 2008-09-30
7
720 Views
Last Modified: 2013-12-09
I have a database table in Firebird which doesn't have a Boolean datatype, so the fiels is actually of type VARCHAR(5) and the values are all "True" and "False" (strings).
Unfortunately my application uses a 3rd party component which no longer works now that the fields aren't Boolean - I think it HAS TO MAP to Boolean fields.
I've already spent about 5 hours going through hundreds of lines of code trying to rewire it to accept the VARCHAR fields - WHEN I HAD AN IDEA - couldn't I have some kind of a temporary DataSet where I could pump the resultset so that the column is BOOLEAN?

The only way that *I* know how to do this would be to set up a table in ANOTHER database like MSaccess or BDE and pump the data there - do you know of a better way?
0
Comment
Question by:rfwoolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Assisted Solution

by:2266180
2266180 earned 125 total points
ID: 22611778
here are a few solutions:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21894525.html (obviously you modify the IIF condition accordingly :) )
or
select cast( <column> as bool/boolean) from table
or
select cast <column> as bool/boolean from table
convert function

or anything else that resembles this. I never worked with firebird so I can't say. but testing would show which works. but I think at least the first solution will work. according to google, the second solution should work too
0
 

Accepted Solution

by:
somosnones earned 250 total points
ID: 22612024
Add a calculated field to the dataset with boolean datatype.
Handle the OnCalcFields event of the dataset to assign the boolean value to the calculated field

procedure TForm1.DataSetCalcFields(DataSet: TDataSet);
begin
  DataSetBoolField.AsBoolean := SameText(DataSetVarcharField.AsString, 'true');
end;
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 22612029
typically boolean is really a smallint that only holds 2 values
0
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 125 total points
ID: 22612320
this could probably solve your problem:
http://www.devrace.com/en/fibplus/articles/473.php
http://www.delphi3000.com/articles/article_2403.asp

but you will need the FIB Components ...

this is a tool to help with the conversion
http://cc.codegear.com/Item.aspx?id=16691

or define the Boolean Domain:
http://www.firebirdfaq.org/faq12/

or switch to version 7 interbase which does support boolean data type.
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 22615118
Thanks so much for some very interesting solutions - somosnones the calculated field idea is a great one - I'm yet to try it - and rather disappointed that I didn't think of that myself. Actually I did list it as one of the things to try but never got round to it
-ciuly I have tried the CAST AS function and can't get it to work - I think it's only for the WHERE clause e.g. WHERE (CAST MyStringField AS DATE) = 01/10/2008
-Geert Gruwez - so glad I found out that FIB components can  do TFields that are decendants of TBooleanFields - that is a GREAT find - unfortunately the cost of the components will take a VERY SIGNIFICANT chunk from my renumeration - but I'm thinking about it.!!!
Thanks so much - I will get back to you as soon as I can try it...
0
 
LVL 13

Author Closing Comment

by:rfwoolf
ID: 31501816
Thanks for all the help!
0
 
LVL 13

Author Comment

by:rfwoolf
ID: 22645774
Thanks for all the help. In the end I went with a Calculated Field idea from somosnones. The FIB components would have been easier but they cost too much.
I finally did get my 3rd party components working but it has taken me a week!!! :D
 
0

Featured Post

Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

729 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