At 11:15 AM 15/03/2006, you wrote:

>hi--
>
>i've heard there is no such thinkg as a tBooleanField in FireBird....
>
>is that true?

Yes. In database terms, Firebird doesn't support a Boolean data type.

>how do i go about converting all my BDE/Paradox tBooleanFields?

Decide whether you want to use a SMALLINT or a CHAR to represent your
Boolean type.

In logic terms, a Boolean can be be true, false or unknown (unknown
is represented by NULL). If you want to enforce strict two-state
Boolean like Paradox (defaults to False and can only be True or
false) then you add DEFAULT and NOT NULL constraints to the field definition.

You can do it the hard way (field by by field) or you can create a
Boolean domain that you'll use for all of your Paradox
Booleans. Don't name the domain "boolean" because recent SQL
standards have defined specifications for a type named BOOLEAN (and
it will become a reserved word eventually and break your definitions).

CREATE DOMAIN D_BOOLEAN AS CHAR
DEFAULT 'F' NOT NULL
CHECK (
(VALUE IN ('Y', 'N'))
AND (VALUE = UPPER(VALUE)) )

or, if you prefer SMALLINT:

CREATE DOMAIN D_BOOLEAN AS SMALLINT
DEFAULT '0' NOT NULL
CHECK (
(VALUE IN (1,0)) )

Now, whenever you define a field with D_BOOLEAN as the type, it will
have these characteristics.

Then, in your conversion process, you will need to read the Paradox
Boolean and match it up with your D_Boolean true and false values.

>any other field types that don't exist?

Oh yes indeed! You don't say what mechanism you are doing for the
conversion but, before you start, you are going to have to decide
what to do with your numeric, integer and date fields.

Paradox's Number is a floating point type.

-- If it is being used for things you count (like money, yardages,
etc.) then convert it to a fixed point type (decimal or numeric,
there's no difference in Firebird. DECIMAL and NUMERIC have
precision (the whole length of the number, in bytes) and scale (the
number of decimal places you want to store). You have 18 positions
of precision available. Paradox's Money type should be converted to
Numeric(18,2), BCD type should be NUMERIC(18,4).

-- If it is being used for things you measure (like temperature) then
make it DOUBLE PRECISION (a floating point type)

-- FLOAT is at the low end of usefulness so you can pretty much ignore it.

Dates

Depends on Paradox version. If > pdx 4 then the types DATE, TIME and
TIMESTAMP correspond. Below pdx 4, there will be "challenges".

Alpha fields should be VARCHAR(n) of the appropriate length. For
fixed length Alphas (like ISBN numbers, barcodes, etc.) use CHAR(n).

Memo should be BLOB SUB_TYPE TEXT. Graphic and OLE types should be
BLOB SUB_TYPE BINARY. FormattedMemo depends on the content: if it
is readable as text, e.g. RTF or HTML, you'll want SUB_TYPE TEXT,
otherwise make it BINARY.

Autoincrement type isn't supported but Firebird has a superior way to
do auto-incrementing numbers. See
http://firebird.sourceforge.net/index.php?op=faq#q0011.dat
Define these columns as BigInt, define the generator and the triggers
and set the generator to the highest value of the existing data
BEFORE before you start the data conversion.

That's not the definitive How-To, by the way. As a minimum, read the
old IB 6 manuals (esp. DataDef.pdf and LangRef.pdf) before you launch
into this.

Helen