Subject Re: [IBO] "FieldType" or alike in IBO?
Author Helen Borrie
At 08:12 PM 4/08/2005 +0000, you wrote:
>maybe I just looked in the wrong places, but I can't seem to find
>something like the BDE "FieldType" property for TIB_Column (which if I
>understand correct corresponds with BDE's TField (?)).
>Maybe I don't need it anyway - I just have the wrong idea about how
>things are done best?
>I experienced that quite often in the 3 months I've been trying to
>understand FB + IBO.
>Basically I just want to take some field values that are returned by
>an IB_Cursor and use them to build a query statement:
>Crsr.SQL.text := 'select field1, field2 from table where condition'
>DSQLUpd.SQL.Add('update Tablex set Fieldy=' + crsr.
>fieldbyname('field1').asstring [...]
>OK as long as I have integer fields, but string fields must be quoted,
>date/time/timestamp fields need quotes and formatting and - in germany
>- the decimal comma in floats has to be replaced by a decimal point.
>I wanted to write a function SQLStrVal(Field: TIB_Column), but how do
>I find out, what data type "Field" has?
>I found some "Is<xxx>" properties, but can I be sure that they are
>mutually exclusive?

Yes - they are quick checks for just this kind of testing. But it's the
HARD way to do things.

Instead of building literal SQL strings, build parameterised strings. You
can use the IB_Column.Value method instead of an Is... method, with both
Fields[] and Params[] to pass variants. After the parameterised query has
been prepared, IBO will take care of determining type and passing search
values of the correct types.

And - avoid assigning your string to the SQL.Text property. The SQL
property is a TStrings. While it is *possible* to write to the Text
property, it is not meant to be used that way. It has a lot of potential
for errors when you are constructing strings on the fly. Use
MyStatement.SQL.Add(), always. It is quite OK to assign the entire
statement in a single string if you wish; although it is much more elegant
in you code to break it into pieces. Do this in the OnPrepareSQL
event. For example:

i: integer;
QueryFieldNames := TStringList.Create;
// ...etc....
with MyStatement do
SQL.Clear; // this clears the SQL property and invalidates the statement
for i := 0 to QueryFieldNames.Count -2 do
SQL.Add( QueryFieldNames[i] + ',');
SQL.Add(QueryFieldNames[i + 1] );
SQL.Add('FROM ' + YourTableNameVar);
for i := QueryFieldNames.Count - 1 do
// this is a really neat IBO trick!

At this point, the statement now has its SQL and its Params. All that's
needed now is params assignment code in the BeforeOpen event. Because of
the "free" nature of your query construction, you just have to be careful
here to test that the user has supplied search values for all of the
SQLWhereItems! That test should be performed before all of the
OnPrepareSQL stuff happens.

i: integer;
with MyStatement do
if not Prepared then Prepare; // this will run the OnPrepareSQL code
// and set up the Params[]
for i := 0 to Params.Count - 1 do
case i of
0: Params[i].Value := <whatever>;
1: Params[i].Value := <whatever>;
// ......... etc

Exactly how you get these values entirely depends on your interface; but
I hope this helps to get the idea.