InterBase® - Data Types
I add fields into IBOQuery and, the moment I set it active, I am getting errors like 'Expecting float but the value is Integer'. If I delete the field and insert it again, I still get the error. The column is NUMERIC(9,2) in InterBase® but it seems as if the field type is not imported properly.
You are encountering a bug in the VCL. By default the VCL uses a TIntegerField to handle this type of column but you lose the digits of precision. IB uses an integer internally to store this column's value but, in the database, it is scaled so that all digits of precision are preserved.
By default, IBO uses a TFloatField and then maps the value to the scaled integer.
IBO is set up so that it will emulate the BDE behavior if that is how you have persistent fields defined. However, you will want to drop them and re-add them so that they are redefined the appropriate type.
Can IBO handle InterBase® ARRAY types?
Easily. Here is a table containing two ARRAY columns:
CREATE TABLE ARRAYS(
ID INTEGER NOT NULL,
CHARARRAY CHAR(10) ,
INTEGERARRAY INTEGER [5, 2],
CONSTRAINT ARRAYS_PK PRIMARY KEY ( ID ));
This is some code you can use to populate the ARRAY columns:
qrArrays['CHARARRAY'] := VarArrayOf(['One','Two','Three','Four','Five']);
tmpVar := VarArrayCreate( [ 1, 5, 1, 2 ], varVariant );
for ii := 1 to 5 do
for jj := 1 to 2 do begin
tmpVar[ ii, jj ] := ii * jj;
qrArrays['INTEGERARRAY'] := tmpVar;
What date separators does IB use? I am in the UK, hence we have a date format dd/mm/yyyy. Normal param type stuff fails - it seems to demand US format.
What is the best way of presenting a date variable to a query?
Up to v.5.5, IB allows only three possible formats for date literals:
mm/dd/yyyy (and variation mm/dd/yy)
dd.mm.yyyy (and variation dd.mm.yy)
dd-MMM-yyyy (and variation dd-MMM-yy)
v. 5.6 and v. 6 allow also the ISO date format yyyymmdd.
The following method in IB_Parse.pas will give you an IB safe string for a TDateTime that can be placed directly in the SQL statement:
function get_IBDateLiteral( ADateTime: TDateTime ): string;
It returns a string but you should use it as a date literal. If you are using a parameter, make sure you use the AsDateTime property of the field, not the AsString.
I need to retrieve hex data from InterBase® fields quickly to build a data stream for a UDP packet, i.e. $06, $06, $06, $00000001. This would be a common string for my app in both size and content. Character set OCTETS looks as if it would be the right storage type. How would I implement it with IBO?
Just declare the column type as character set OCTETS in the CREATE TABLE statement. That particular column will then not undergo any sort of transliteration and you will always get back what you put in.
Refer to the IB manuals to see how to work with character sets in the CREATE
I have about 150 tables in my database with many columns of type double precision to use with currency values. When I show this column in grid and do a calculation on it to sum or subtract values, I always see anomalies on the fraction part. I'd appreciate some advice on the following:
1. Should I use Numeric(9,2) to all currency columns?
2. If I do, will it solve my rounding problem?
3. Is there a easy way to make this conversion to many tables?
You could extract the database to a DDL script and then make all the changes for the currency columns. Make a domain that isolates all your currency columns. Then, generate this new database and use the IB_DataPump functionality in IBO to move all your data from the old database into the new database.
Just be sure that your currency amounts can live within a few million dollars. DECIMAL( 9, 2 ) isn't a whole lot of precision.
I was having a problem showing Blob Images based on the sample code. The Clear method of TIB_ColumnBlob doesn't set it to nil but returns the 'Invalid BlobNode' error message. I solved it in my procedure to clear the Blob Image by setting TIB_ColumnBlob explicitly to nil. Is this correct?
Don't set the pointer to the blob column to nil - NIL is not what you want here... it simply blows away the object that IBO provides to work with the contents of the blob column.
Use the Clear method to clear the contents of the blob column and if it is nullable it will set it to null. Look at your metadata to see whether a NOT NULL constraint is interfering with Clear. In that case, you might need to hold its place with an empty string.
InterBase® - SQL
When I execute the query
select * from table1 where table1.field1 <> 'N'
the result set excludes any records where field1 is null. Why is this?
Null and Not Null are states (not values) and so cannot be logically evaluated with arithmetic operators. You need to ask for null values explicitly, i.e.
select * from table1 where ((table1.field1 <> 'N') or (table1.field1 is null))
I want to log all transactions for a table together with the IB UserName of the person making the transaction. Is it possible to know who is running the current transaction?
Yes, use the system variable USER which is available in both triggers and stored procedures and also in DSQL.
What's the difference between "explicit" and "implicit" joins? Should I favor one over the other for IBO?
The distinction applies only to full inner joins. The explicit syntax is
SELECT TABLEA.FIELD1, TABLEB.FIELDX
ON TABLEA.IDFIELD = TABLEB.IDFIELD
The implicit syntax is
SELECT TABLEA.FIELD1, TABLEB.FIELDX
FROM TABLEA, TABLEB
WHERE TABLEA.IDFIELD = TABLEB.IDFIELD
Use the explicit JOIN syntax if you are using a TIB_Query and either one if you are
using TIB_Cursor. The explicit syntax is a good habit to get into, makes your SQL easier to understand and makes IB and IBO much happier.
In IBO you make extra trouble for yourself by putting JOIN criteria in the WHERE clause. If you want to do this, you have to put it in the JoinLinks property and allow IBO to parse it into the WHERE clause for you. IBO needs to know the difference between regular WHERE clause items and join criteria.
System - Users
TIB_Connection and TIB_Session each has an AlterUser procedure. The one in TIB_Session seems more comprehensive. Which one should I use?
Use the one in TIB_Connection if the properties in the IB_Connection match the admin credentials required in the method call. If they are different, use the TIB_Session method and send all of the values.
I want to call AlterUser to give some of the users of my system the ablity to add and change IB Users through the client. How can I do this?
In order to do this you will need to somehow embed the SYSDBA password into the EXE or allow your users to know it. This is undesirable.
Consider making a special table in your database to hold the user names and allow your users to insert, edit and delete to that table. Have a separate program running on the server that responds to an event and updates the security database indirectly. This keeps the security for user maintenance on the server.