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

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?
LVL 13
rfwoolfAsked:
Who is Participating?
 
somosnonesConnect With a Mentor Commented:
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
 
2266180Connect With a Mentor Commented:
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
 
Nick UpsonPrincipal Operations EngineerCommented:
typically boolean is really a smallint that only holds 2 values
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Geert GConnect With a Mentor Oracle dbaCommented:
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
 
rfwoolfAuthor Commented:
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
 
rfwoolfAuthor Commented:
Thanks for all the help!
0
 
rfwoolfAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.