FAQ Help On-line Trustware License News E-Mail
Join IBO List List Archive What is IB Objects? Downloads
Events Tech Info Sheets IBO Community Links Home
IB Objects FAQ
Frequently Asked Questions




Oldest Active Transaction (OAT)



Data Types


IB Objects

User Interface
Data Transfer             Home

How can I move data from one database to another?
Use two TIB_Connection components, a TIB_Transaction and two TIB_Cursors with a TIB_DataPump. Put SELECT * FROM TABLE into the SQL property of each of the cursors, Prepare them and then call the Execute method of the datapump. Remember to call Commit or CommitRetaining at the end of it.

I am using the IB_Datapump component. How can I detect an error during the execution ? I tried the OnError event, but it wasn't triggered when a key violation occurred.

What is an efficient way to count the source and inserted rows in order to display the progress of the datapump for each table?

Trap errors in the OnError event of the IB_Statement that is receiving the pumped rows.

Take a look at the IBF_DataPump form/unit. It optionally enables a progress meter with some timings as well. This is the same form as is used in the IB_WISQL program.

Does the IBO datapump method cost a lot of memory? For example, if it pulls all data into memory first, pumping a million records will be expensive.
IB_WISQL's datapump can handle as many records as you want. It opens up a "pipe" and just pours it right through without piling anything up. It is the fastest way possible, as it goes right to the bare API calls. It even shares buffer space between input and output if it can. Thus, no moving data around between records.

It will automatically take care of casting datatypes or differing data lengths in the cases that DIRECT buffer mapping is not possible.

Anywhere from 1000 to 2000 inserts a second on a local connection and 300-1500 over a LAN remote connection would be typical.

Data Attributes             Home

I have a problem with the FieldsDisplayFormat property of IB_Connection in combination with TIBOTable: I want to use this property to define DisplayMasks for my fields at the domain level. An entry like TABLEPRICES.PRICE=#####0.000 doesn't work.
Domain-level settings go in the TIB_Connection component. To make the domain settings work, you need to set the FieldEntryTypes property to fetDomainName. Its default is fetSQLType.

I want display masking to work on my numeric columns so that the FieldEditMask is removed from the field during editing and replaced after the focus leaves the field.
You can't use an edit mask with numeric data entry. Just use the DisplayFormat and then it will show as you want but, when focused, it will remove the formatting and allow the user to edit just the numbers.

There are probably special controls you could get to handle numbers in a clever way. Try looking out on Torries or the Delphi Super Pages.

InterBase® generally orders upper and lower case differently. This can annoy users when they order a grid by clicking on the column headers. How can I order in grids to ignore case?
A common way to accomplish case-insensitive search keys is to maintain a separate proxy search column that is trigger-maintained to be the UPPER() equivalent of the real column. You just have to put your index on the proxy column so that IB will take advantage of it to optimize queries.

In IBO, use the NOCASE ColumnAttribute to mark this proxy search column to make IBO substitute it for the original one automatically when searching.

How can I display only the time-portion or only the date-portion from a date field?
Check the NODATE or NOTIME ColumnAttributes in the Dataset Editor.

I want to disable right trimming for all string fields for all the TIB_Cursors I have in my application. Is there a way to change it globally?
Do this on the IB_Connection component. Use the fetDomainName or fetSQLType setting for FieldEntryTypes and enter a string in the IB_Connection.FieldsTrimming property.

How can I set a default date for a field using the 'Default' attribute setting in the Property Editor?
Use the standard InterBase® date literals, NOW, TODAY, YESTERDAY or TOMORROW. IBO will also correctly handle LASTW (one week back from the current date), NEXTW (one week forward from the current date) or NULL.

How can I return a value of 0 or 1 (Checked and Unchecked) from a TIB_Checkbox?
This is handled at the dataset (or connection) level via the ColumnAttribute parameter "BOOLEAN". For the column in question, in the dataset's property editor, check BOOLEAN and use the edit boxes to enter the True and False values that your database column uses. In your example, this would be 0 for True and 1 for False.

How can I force a Boolean column in ib_grid to accept only two values at edit time: "checked" or "unchecked"? I want to get rid of the default "grayed" state that appears when a Boolean is null.
Set the REQUIRED flag in the ColumnAttributes property of the dataset and enter a value for the DEFAULT flag, too, if you like.

Datasets - Buffers             Home

I want to locate a record ONLY in buffer, without fetching all the records that haven't been fetched, e.g. I have a table with a numeric field called 'ID'. How can I search (ONLY in the buffer) for the record which has ID=10?

You use:

KeyFieldByName( 'ID' ).AsInteger := 10; 
if SeekKeyForBufferFields then 
  // Record was in the buffer.
This method will not go to the server looking for the answer. It's an internal method not necessarily intended for public usage but it will have the effect you want.

How can I make record-to-record comparisons within the same dataset?
The dataset surfaces two buffer pointers. Here are the lists of properties for each:
----- 1 -----
----- 2 -----

It is handy when you need to make record to record comparisons of two records in the same dataset. Use FieldByName() and BufferFieldByName() to access the two rows.

It also makes it easy to perform non visual operations on the data behind the scenes of data-bound visual controls, e.g. when you need to paint cells in an IB_Grid, just use the BufferRowNum and BufferFields[] to access the data in the buffer in order to paint the cells. You don't have to worry about disabling controls and restoring the current record pointer, etc.

What do the Callback features of datasets do?
When an IBO dataset goes into a situation where multiple records could potentially be fetched, flags are set that cause events to be generated between fetches, according to the settings on the CallbackXXX properties. It is designed to provide control over the behavior of the processing query and make it possible for the user to break out if necessary.

Handler code goes on the dataset's OnCallback event. For example, you could use it to keep a progress meter updated, allow a click on a cancel button to make the query discontinue fetching, etc.

Refer to the help for TIB_Dataset for additional information.

Datasets - SQL             Home

I need a way to simulate the TField.OnSetText and TField.OnGetText events on the TDataSet. For example, I would like to enter and display in Gallons, but store in Litres and switch between them "on the fly".
Use a calculated field to store the alternative column and then switch between the two columns. See the IBOSource app that shows the Size column in Kb instead of bytes.

What are differences between SQLWhereItems, SQLWhereLow, SQLWhereMed, SQLWhereHigh ?
Just use SQLWhereItems. All others are for internal use only, to ensure that input parameters will be in a certain order. For designing your applications, InterBase® doesn't care where information is in the where clause.

What are differences between the values of RefreshAction? What is the fastest way to refresh data?
raOpen is fastest because it just opens the dataset and doesn't try to restore the record pointer, which potentially causes additional fetches to be made.
raRowNum will reopen the dataset and go to the RowNum that it was on prior to the refresh.
raDataPos uses the Bookmark to make it go to the same record at which it was positioned before the refresh.

On my master-detail form, when I try to add a row in the master by clicking + on the update bar while I'm still editing the detail, I get one or more exceptions: " is a required field". What causes this?
If you try and post a detail record while the master is still in Insert state, IBO attempts to do a PostRetaining on the master dataset so that the referential integrity of the master-detail relationship will be satisfied.

In client/server a row is not actually sent to the server until the insert on the client is posted. You need to resolve detail record changes prior to making such a change on the master record. So, if you try and post a detail record and its master record is still unposted, you will get a referential integrity violation and the post of the detail will fail.

Your problem might be due simply to the master dataset requiring that all of its detail datasets be posted before it will allow itself to scroll, change states, etc.

Using key Links or MasterLinks or MasterSource with params seem to result in the same sort of master-detail relationship. What's the advantage of one over the other?
They are not the same.
The MasterSource property links to the datasource that is connected to the master dataset in a master-detail relationship. You should select this from the drop-down list on that property editor. Use the MasterLinks property to link the foreign key of the detail dataset to the primary key of the master, e.g.
DetailTable.MasterKeyID = MasterTable.MasterKeyID

KeyLinks does not form master-detail relationships. Its normal use is to identify the column (or columns) that make one row in the dataset unique. In the case of a lookup relationship, KeyLinks has a special format to link the unique column of the lookup query back to the lookup key in the KeySource table, e.g.


I have a trigger which should use Gen_ID(genname, 100) to step the generator 100 places with every firing. I've set the GeneratorLinks property on my TIBOQuery to point to my generator but it is stepping by 1 instead of 100. How do I make it step properly?
If you want the value in your application before posting time, use the GeneratorValue function or Gen_ID function of the internal dataset instead and call it in the IBOQuery's BeforeInsert event. i.e.
IBOQuery1.ParamByName('IDField').AsInteger := GeneratorValue(MyGenerator,100);
IBOQuery1.ParamByName('IDField').AsInteger := Gen_ID(MyGenerator,100);

If you simply want to have the trigger fire at posting time and you don't need to get the generated value back into your application at that point, access the internal field's IB_Column property (via FieldByName) ** at creation time ** and set REQUIRED to false. This will allow NULL to pass through the CheckRequiredFields test and your generator will fire when the row posts. e.g.

IBOQuery1.FieldByName('IDField').Required := False;

I can't understand what the TIB_Dataset.KeyDescLinks property is for.
KeyDescLinks is part of the relationship between a lookup dataset and the KeySource dataset that is linking to it in order to get back a description value for a lookup key.
KeyDescLinks tells which dependent description columns being used by the KeySource dataset need to be updated.

So, if the KeySource dataset has DEPTNAME and DEPTID and the KeyLinks relationship is based on DEPTID, you can make an entry in the lookup dataset instructing that the DEPTNAME column being used by the KeySource dataset should receive a new value if DEPTID is changed.

The Locate() method for TIB_Query seems to work well. Is there a way to do a "LocateNext" ?
You will find that this works rather nicely: instead of using the Locate() method, you can leave the Filtered property False and set a filter based on the value you are locating. Then, use the FindFirst, FindNext, FindLast, etc. methods to navigate through the records isolated by the locate (filter) criteria.

If an SQL statement changes completely at runtime according to input from the user, is it sufficient to change the statement, call InvalidateSQL, then Refresh? Or is there a need to close the query, change the SQL statement and reopen the query?
There is a much better way.
Use the OnPrepareSQL event, SQLWhereItems property, InvalidateSQL and Refresh methods and you have a quick and efficient way to alter your query's WHERE criteria.

See the PriceList sample application for an example of this.

I have a couple of Stored Procedures that are used a lot in my ISAPI application. If I set Prepared to true, does that prepare SP on creation of the web data module and unprepare it automatically or do I have to call UnPrepare?
IBO will unprepare for you when the connection closes if you don't do it explicitly.

SQLWhereItems doesn't seem to work at the right time to affect the criteria for a search.
Use the OnPrepareSQL event to add items to the SQLWhereItems and it will be parsed into the SQL that is sent to the server. Because it is cleared out at the beginning of each cycle, you should only modify the SQLWhereItems property during the OnPrepareSQL phase.

Monitor state in the controls (or whatever storage means you are using) and, when it changes, call InvalidateSQL. This flags the dataset to indicate that it needs to do a reprepare. Next time the statement is executed, IBO will automatically do the OnPrepareSQL phase again.

Using the filtering option of TIB_Query, I tried a filter NAME LIKE 'h%' but it returned the error message 'This is an unknown SQL statement.' I tried it with NAME = 'h%' - now the SQL was right but it returned the wrong values from the table.
The filter property uses the established syntax and behavior of TTable and TQuery. See the FilterOptions and the help in the Delphi VCL help file on how to use this and IBO will be just the same.

Tell it to match foPartial and use an asterisk for the wildcard, instead of the % symbol. IBO takes care of mapping it into the proper SQL format for you. (You can verify it by looking in the SQL trace monitor.)

I want to insert a datetime value into a table using the DSQL component. InterBase® doesn't like the datetime formats I use (FormatDateTime('mm/dd/yy' + ' hh:nn:ss', datetime). Is there a way to build a datetime as a string that InterBase® will accept?
Include IB_Parse in the uses clause and call the Get_IBDateLiteral() function:
query.sqlwhereitems.add('FieldDate=' +get_IBDateLiteral(ADatetime));
It returns the date literal string already single-quoted for you.

How can I read the results from a Stored Procedure back into my application?
If the SP has a FOR SELECT...DO... SUSPEND construction, for returning a multi-row data set, use a TIB_Query or TIB_Cursor. Use a TIB_StoredProc for a procedure that has to be EXECUTEd.

To pass input parameters use:

   ib_query1.ParamByName('xxx').asXXX := ;
   ib_storedproc1.ParamByName('xxx').asXXX := ; 
In both cases, read the fields returned by the fields array:

   aVariable := ib_query1.FieldByName('xxx').asXXX;
  aVariable := ib_storedproc1.FieldByName('xxx').asXXX;
NOTE: You can still use TIB_StoredProc if you have a SUSPEND statement in the body of your stored procedure. You just need to set the StoredProcForSelect property to true.

A workaround for a bug in IB that causes some ugly behavior on remote connections is to put a SUSPEND statement in all your stored procedures and use the FOR...SELECT option to process them.

Is there any way to get a query plan without executing the query?
Prepare the IB_DSQL, IB_Cursor or IB_Query component and then read the StatementPlan property.

How can I check out whether a statement or stored procedure is OK without executing or compiling it on the server and possibly getting an error back?
The tool IB_WISQL allows all statements to be Prepared and shows the plan without having to execute them. This is especially handy when working with DDL statements such as triggers and stored procedures. It allows them to be compiled (Prepared), thus checking for syntax errors, etc., and does not make you have to take the risky step of executing them.

My table has a trigger that sets the primary key to a unique value by invoking a generator. When I try to post a new row, I get an error message back saying "Value [xxxx] is required". Why doesn't the trigger fire?
Delphi "knows" about constraints on tables (like the PRIMARY KEY and NOT NULL). It flags a NOT NULL column as "Required" and throws the error if your app tries to post an inserted row with a NULL value in a Required column.

Delphi doesn't "know" about triggers. If a NOT NULL column is among the fields in your dataset, you must either supply a value or, to make the trigger fire, prevent Delphi from trying to validate it.

You can use the dataset's GeneratorLinks property to have it fetch the next value from the generator before posting. This is simply a string of the form

Make sure your trigger checks for NULL before invoking the generator, e.g.


Another solution is to set the column's REQUIRED attribute to False on the ColumnAttributes tab of the dataset. Thus, Delphi will permit the transaction to start without the NOT NULL constraint being satisfied, permitting the trigger to fire.

For NOT NULL columns that are not generators, you can insert a default value or a place-holder by adding some code in the OnBeforePost event, e.g.

FieldByName('LastUpdate').AsDateTime := Now;

I have several tables and one history-table. Each table has an AFTER INSERT, AFTER UPDATE and AFTER DELETE trigger to insert a row in the history table.

If I change a table interactively, one new row is inserted in the history table, as expectd. But when I make a change in a table with my application, with PessimisticLocking True, two new rows appear in the history table. Why?

You have verified that PessimisticLocking is doing its job! PessimisticLocking works by doing a "dummy update" and thus, by touching the row it prevents other transactions from editing that row. It thus acts as a lock. Of course, it doesn't change any values, but your trigger doesn't know that and fires off a row to your history table. Then, when you do the actual actual update, you get another history row!

Change your trigger so that it will verify whether anything was actually changed and only write the history record when a change actually happens.

With RequestLive=False, with or without the clause 'for update' in queries, some of my queries are still editable. Should I be worried?
No. RequestLive just indicates that you want IBO to determine the UPDATE, INSERT and DELETE statements for you.

Providing custom SQL for the EditSQL, InsertSQL or DeleteSQL property is like setting "RequestLive" to true for that operation.

If I use a SELECT TIB_Query and define insert and update SQL, can I set parameters and invoke the insert or update part under program control ?
Setting RequestLive to true tells IBO to try and figure out the DML statements. You can define custom DML by plugging the statements into the EditSQL, InsertSQL and DeleteSQL properties.

IBO takes those statements and binds their parameters directly to the record buffers. If you plug in your own values you risk overwriting valid data.

If you have things that you need to do outside of the normal, Edit ... Post... Insert ... Post... activities, it would be better to have your own TIB_DSQL component off to the side and make the special activity happen there.

If a query is read only, would DeleteSQL still work and do processing on the server?
If you set the query's ReadOnly property or its PreventDeleting property to True, IBO will not allow that dataset to go into dssDelete mode and therefore you will need to delete it another way, e.g. using a separate DSQL statement.

If you want the dataset to be allowed only to delete a row, set PreventInserting and PreventEditing to True and leave PreventDeleting and ReadOnly false.

You do not need to set RequestLive to true if you supply the DeleteSQL property. RequestLive is used to tell IBO that you want it to automatically figure out the DML statements to do the edits, inserts and deletes.

If my EditSQL has 2 parameters, e.g.
I don't see parameters in the params on the list. Do I have to create them before calling UPDATE?
When applying parameters to the EditSQL and the other SQL properties, IBO uses the existing columns of the dataset as the parameters. Just make sure that you use only proper field names as parameters and IBO will do all the plumbing for you.

IBO only lets columns that matter be modified. It even takes care of making read-only any columns not included as parameters in those statements.

I use an outer join in a TIB_Query and have a SP in UpdateSQL, InsertSQL and DeleteSQL. Is it a problem if I omit JoinLinks from my TIB_Query?
If you are using an outer join then use the FROM clause and don't worry about using the JoinLinks property. The JoinLinks property is for the "implicit inner join" syntax of SQL-89, when you want JOIN criteria included in the WHERE clause of the statement when sent to the server.

How do I set a column to a null value?
Call the Clear method or set IsNull to true, e.g.
FieldByName('aCol').IsNull := True;

I am using IBOQuery to populate a grid. Right now, I have a separate IB_Query to delete a row. I would like to select the row in the grid and delete it via the DeleteSQL statement without need of this other query. Are these SQL properties meant to enable that?
DeleteSQL, EditSQL and so on were designed to give the developer a way to provide custom instructions for handling what happens on the server when a row is deleted from, updated in, etc. a dataset. For example, you may want to call a stored procedure, or something else along those lines, when the dataset's Delete (or Update) method is called.

Is a parameter in DeleteSQL, etc. going to be automatically assigned?
Yes. Also, you can use the :OLD_ prefix to get the value of the key columns to perform a "searched" delete or update.

What is the syntax for the JoinLinks property of TIB_Query?
Use JoinLinks when the query is an SQL-89-style "implicit" join on multiple tables, with JOIN criteria mixed up with WHERE criteria. JoinLinks identify which ones are the JOIN criteria. These get parsed into your WHERE clause as appropriate when the statement is prepared.

In relation to TIB_Statement events, exactly when does an After Insert trigger fire?
When CachedUpdates is false, the AFTER INSERT trigger fires upon the post of the insert.

I have about 10 million rows and want to filter them. The result could be also 10 million rows. How can I tell IBO to stop fetching at a position of 1000 rows and if the user scrolls the view down next 1000 will be transferred to the list?
Use the MaxRows property.

With MaxRows I can define how many records I want to transfer over the network as a resultset, but I don't want to open a new query to get another set. Is there any other way to control the flow of rows being fetched?
Set CallbackInc to something other than -1 (5 is good) to allow the user to control how much they want to wait for fetches. It gives a dialog that allows the user to cancel and restart fetching.

How do I enable ordering of calculated fields, so that user can click the title of a calculated field and order the rows in the grid according to the values in the calculated field column?
It is not possible.

A workaround is to duplicate your formula for deriving the calculated value into a computed column in the table. Maintain the column using a trigger and use that column in the OrderingItems. You don't have to include it in the select list.

The benefit of doing this is that you can put an index on the maintained calculated column and take advantage of query optimization. On the client you gain on-the-spot accuracy of your calculated field and avoid having to synchronize with the server to get the most up-to-date calculation.

Datasets - Synchronizing             Home

My table T1 has AFTER triggers to update another table, T2. To display the effects on both tables in my form, I use the AfterUpdate, AfterInsert or AfterDelete event of the T1 dataset to force a Refresh on the T2 dataset. It is quite complicated because there are many triggers and stored procedures involved.

I thought the property BufferSynchroFlags was meant to help maintain correct synchronization between the client buffer and the server. The flags don't work for my situation. If I enable them for both tables, the trigger-generated values aren't brought to the server. Why is this?

BufferSynchroFlags are designed to synchronize only those records being edited and inserted.

If you want your datasets to synchronize with changes made in triggers on the server to other tables and rows, you need to use the DMLCaching features. See the D4Apps\Survey sample application for an example of how this works.

Datasets - Components             Home

With a TIB_Cursor, is First better than Open for positioning the cursor on the first record?

First closes the cursor (if open) then opens it and fetches the first row. Calling open may just open the cursor and leave you at BOF, i.e. before the first row.

Is it possible to include the RefreshOnParamChange property in TIBOQuery component?
If you want, you can set it at run-time by accessing the TIBOQuery or TIBOTable.InternalDataset reference, i.e. TIBOTable.InternalDataset.RefreshOnParamChange := true;

Transactions - Concurrency             Home

I want to block other users from editing a record until the current user has finished editing it. How can I do this?

In InterBase®, all you need to do is produce an update against the record that you want to lock, before the user is allowed to produce their updates. If another user is already editing that record, IB will return a deadlock as soon as another user attempts to edit the record.

With the BDE you must use SHARED NOAUTOCOMMIT because the default AUTOCOMMIT would defeat your lock. You would also have to handle the calls to StartTransaction, Commit and Rollback in your application.

With IB Objects, you can accomplish all of this if you set PessimisticLocking to True on your dataset component. IBO handles the rest for you, even if you are using a transaction with AutoCommit set to true.

I have some records displayed in a Grid. The user selects a record to Modify (dssEdit Mode). I want to grab the latest record from the database, so that I am sure they are modifying the most recent record. How can I tell IB_Query to refetch a record "NOW"?
If you are expecting to see changes committed by another user then you will need to use an isolation of tiCommitted or start a new transaction as a part of the tiConcurrency based dataset refresh.

Be sure BufferSynchroFlags has the BeforeEdit to guarantee that a fetch from the server is performed just before going into dssEdit state.

TIP: Drop a TIB_UtilityBar on your form and get comfortable with using this tool. Once you are used to it you will wonder how you developed client/server applications without it.

With my app, I have several TIB_Queries in readonly mode for selecting data. They are attached to Trans1. I have several TIB_DSQL queries used for inserting, updating and deleting data, all attached to Trans2. Both are routed through a single IB_Connection.

I issue a refresh to the readonly queries after data is updated/inserted/deleted. I have serverAutoCommit and AutoCommit on. Isolation is ReadCommited.

Occasionally I get record deadlock (lots of index updates and triggers going on) even though the same record can't/won't be updated by different users at the same time.

If you are using ServerAutoCommit and isolation of tiCommitted you should not see a deadlock anywhere...Part of the problem could be that your DSQL statements are running in tiConcurrency isolation instead of tiCommitted.

I have a TIB_Connection, a TIB_Transaction with tiCommitted isolation and a TwwIBOQuery. Here is the scenario: User 1 changes the row and posts it. User 2 edits and posts the same row. No problem. User 1 had already committed when User 2 first saw the row.

User 1 and User 2 both edit the record at the same time. User 1 posts first. User 2 posts second. Still no conflicts! But this time, User 1 and User 2 both saw the same row. When User 2 posts, User 1's changes are gone. Why was User 2 allowed to post?

Because you are using "Read Committed" isolation the server does not detect a conflict. If you use tiConcurrency, then it would be detected.

You might consider using BufferSynchroFlags of bsfBeforeEdit and possibly setting PessimisticLocking=True.

You can also supply an EditSQL that makes use of the WHERE clause to base the update on the old values and generate an exception if the record on the server is no longer as it was when fetched.

The best way to avoid this type of conflict going unrecognized is to use the StartTransaction and Commit methods, even when AutoCommit is set to true.

Transactions - Multi-database             Home

I have a transaction that must update two databases simultaneously. How, if possible, is this to be done using IB Objects?

Strictly "simultaneously" is not possible with a single API call. The API should only have one active thread in it at once. If you are talking about a multi-database update within a single transaction, yes, it is very simple.

Drop down two TIB_Connection components and point them to your databases. Then, drop down one TIB_Transaction and set the IB_Connection1 and IB_Connection2 properties to the two connection components. This will allow you to have a single transaction span two separate connections. They can even be on different servers.

The TIB_Statement class has separate IB_Connection and IB_Transaction properties. You would use two statements or datasets and hook one to one connection and one to the other connection and hook both of their IB_Transaction properties to the TIB_Transaction component.

Use as many statements or datasets as you like, do whatever you need to do in the transaction in both databases and either commit or rollback. The IB API and IBO will handle all of the TPC action that will give you rock-solid cross-database operations under transaction control.

Transactions - Oldest Active Transaction             Home

On my ASTA application server I use a single transaction that is set to AutoCommit and Read Commited. When the server executes SQL (insert/edit etc), I observe on the IB_Monitor that the Transaction Handle stays the same. I would expect the number to change after each statement. Is my assumption correct ?

It depends on how you configure your transaction. If you are using a TIB_Transaction component and you don't want a transaction left open, you should set its Isolation to tiCommitted and call the CheckOAT method after executing a statement. CheckOAT will leave the transaction open if there are any changes pending but it will end the transaction if it can.

If you are taking explicit control of the transaction, it makes more sense to call Commit or Rollback. Each transaction keeps track of how many open cursors there are. If there are no open cursors (datasets that haven't fetched to EOF yet), the transaction will end.

If you are using a TIB_Cursor or TIB_Query, IBO automatically ends the physical transaction for you when the dataset completes fetching all records of the internal cursors.

What is the best way to keep the Oldest Active Transaction moving forward?
An application that uses data aware controls on the main form and also uses a main datamodule is common enough but, if it is likely to sit open all day, you want to prevent a user from holding up a transaction.

One technique is to call the explicit transaction's Commit method in the AfterSearch event of the main dataset, to cycle the transaction each time the user goes to search for a record. Make it return to search mode when they are done and place the form in a state that it isn't holding a transaction open. It will also prevent them leaving information on the screen that shouldn't be there and makes it very easy for them to go to work on a new lookup or whatever they want to do next.

If I set AutoCommit=True and let IBO handle transactions, to avoid having to call StartTransaction, Commit, CommitRetaining, etc., does this prevent the Oldest Active Transaction (OAT) from advancing?
If you left the dataset to the default (internal) setting then, when the dataset is closed, refreshed, or when EOF is fetched, the OAT will be moved on.

Transactions - General             Home

I have a large MDI-app in which I use TIBOXXX components. Any MDI child might be called many times. Each MDI child uses separate TIBOQueries and TIBOTables which reference a TIB_Connection on a DataModule.

I use only implicit transactions but I want to know if an explicit TIB_Transaction component on the DataModule (for all the various Queries and Tables) could be a better solution? Or is it better to use one TIB_Transaction component for each MDI child?

Whenever there is the possibility of multiple instances of a form, make sure you place the data access components on the form instead of in a datamodule. This way each form has its own isolated access to the data.

If each form represents a single unit of work then it may also be good idea to have a transaction component on each form as well.

It is not always a good idea to have a TIB_Transaction for each form. Multiple forms may have activities that need to be in a single transaction. This is where you can make use of the TIB_TransactionSource component. It allows you to code the form so that it can be used in a given transaction context just by assigning the form's TransactionSource component. In the AfterAssignment event of the IB_TransactionSource, assign its IB_Transaction property value to all the other statements and datasets on the form.

You can see this being done in most of the forms in the IB_WISQL application.

Without cached updates, is it safe to assume that, if a transaction is InTransaction, its Started property is true as well?
It is a safe assumption only when you are using explicit transaction control; otherwise you should test both.

Is there a way to update and refresh all of the datasets inside one transaction?
Call the TIB_Transaction.Refresh() method. It commits or rolls back, depending on the Boolean value you pass in, and refreshes each of its datasets according to the RefreshAction property of the dataset.

When I try close my app I get a error: 'Failed to Cancel Datasets'. What could cause this? Where can I search in my code to fix this error?
This is from the transaction code in IBA_Transaction.IMP. It means that, when closing a transaction (rolling back in your case), it is cancelling all datasets. For some reason, you have one or more datasets configured in such a way that it was unable to cancel them all.

Look at any code you might have in the BeforeCancel event or other events of that nature. If you are preventing a dataset being cancelled, you might check the code for a TransactionState of tsRollbackPending and then let it proceed to cancel, rather than prevent the cancellation.

Why would an implicit transaction have different defaults from an explicit transaction? If I put a transaction component on my form, I expect it to default to the same values as any implicit transaction. If there are different values, that means that the implicit transaction is not the same thing at all.
An implicit transaction is for when you want to ignore transactions in your application altogether. You just want to drop down some datasets with a connection and not worry about anything. The default transaction behavior is intended to make them as transparent as possible. Everything that posts should also commit immediately; you want to see data posted from other users and other datasets immediately. Thus, AutoCommit := true and Isolation := tiCommitted.

If you decide to take transaction control explicitly by dropping down a transaction component, then the InterBase® transaction default settings apply. It's that simple.

If you want to be sure that you have fully taken control of transactions and safeguard against any of the explicit settings slipping to nil (which could lead to serious problems) then drop a TIB_SessionProps component onto your main form or datamodule and set the property AllowDefaultTransaction to false. This will cause IBO to raise an exception instead of creating an internal transaction for statements and datasets that do not have an explicit transaction defined.

If Post is a full update to the database, does it mean there is no Commit required? Is Post always required if there is an explicit transaction?
Post is the process where IBO takes the changes made for the dataset and executes the INSERT, UPDATE or DELETE statements on the server. Inside a transaction context there could be multiple statements that either stand together or get removed. This is where Commit comes in. Posted changes can be committed to the server or they can be rolled back.

If you have AutoCommit set to true and you haven't called StartTransaction then IBO will automatically call isc_commit_retaining() for you as soon as Post is successful. In this situation, all changes that post are immediately committed.

Post is always required, whether there is an implicit or explicit transaction, to get the changes from the dataset buffers which are to be sent to the server.

If there is only one transaction component on the data module, will the IB_Transaction on components use that transaction?
No, means that it will create its own internal transaction. You must deliberately set a component's IB_Transaction property to your transaction component, by selecting it from the drop-down list. This is preferable to letting IBO create its own transaction, especially in an ISAPI module.

Also, placing transaction components on a data module is not recommended for ISAPI.

Components - TIB_Dataset             Home

When I have to create a TIB_Dataset (cursor or query) at runtime, what properties do I have to take care of?

IB_Connection, IB_Transaction, SQL. With an IB_Query it may also be beneficial to properly define the KeyLinks or KeyLinksAutoDefine properties, too.

MyDataset : TIB_DataSet; 
  MyDataset := TIB_DataSet.Create (Application); 
  with MyDataset do begin
  IB_Connection := myConnection; 
  IB_Transaction := myTransaction; 

Components - TIB_ConnectionSource             Home

How one might one use an IB_ConnectionSource? I use visual form inheritance. This might be a good component to use for this.
It allows you to design the form so that the queries get their connection from the connection source. So, when hooking a form into an app you can make a single assignment to the form's connection source and have it hooked into the rest of the form automatically.

It is used in all the IBO form base classes. Take a look at the IBF_Base form and then at some of the descendants like IBF_Browse or IBF_Query.

Controls - TIB_LookupCombo             Home

How can I clear the 'previous typing' from a TIBLookupCombo? The problem is that when a user types into a TIBLookupCombo (to perform an incremental search), the control 'remembers' the characters that were typed in before.
A call to procedure ClearIncSearchString will clear out the current incremental search criteria used in conjunction with the key-by-key behavior when calling the IncSearchKey() method.

Comboboxes generally can be used to either select from a list or to enter new values into a field. However, I can't get the TIB_LookupCombo to do this. I can only select from the list and search incrementally. I'm looking for a way to write to the field if the entered value is not found in the list. Is that possible?
You can remove the TIB_LookupCombo control and replace it with the TIB_ComboBox control. But you lose the benefit of having the user's new lookup value placed in the lookup table (or you have the benefit of not requiring it, if that is what you want).

You can write a handler for the OnNotInList event, to do an insert on the lookup dataset and proceed to allow the user to edit it further in a dialog , or just post it and make it transparent to them. You might also want to prompt them or something. It's up to you.

Controls - TIB_CtrlGrid             Home

What are the properties PartialCols and PartialRows in the IB_CtrlGrid used for ?
What is the difference between PanelHeightFixed true and false ?
PartialCols indicates whether or not to paint the panes that are only partially visible. PanelHeightFixed keeps the panels the same height instead of stretching them.

Controls - TIB_Grid             Home

How can get the active column from an IB_grid?
SelectedField is a property that tells you which column of the dataset is currently active in the Grid. Be cautious because it can be nil at times.

When a memo is displayed in a grid, the cell displays only (Memo) until the user clicks in the cell. It then displays a truncated view of the memo.
Is it possible to have the grid display the truncated view of the memos in all the cells when the grid is first drawn?
Yes, you just have to cause the BLOB to get loaded somehow. There is a Load method at the TIB_ColumnBlob class level.

Accessing the AsString property also loads a blob automatically.

I want to use a calendar in the IB_grid so, when I click on a date field in the ib_grid, my calendar should appear. How?
Use the EditLinks property to make an edit button appear and then you can tie into the OnEditButtonClick event and invoke whatever calendar or dialog you want.

Controls - TIB_Edit             Home

Where is the EditMask property in the TIB_Edit control ?

In IBO, data attributes are set at dataset level. Set the FieldsEditMask is on the TIB_Query. You can also set these attributes at the Domain level, on your TIB_Connection component. See help for more information.

Controls - TIB_CheckBox             Home

When I set IB_Grid.ReadOnly:=true, the Boolean values disappear. Is it a bug ?

Different glyphs are used for read-only checkboxes and for editable ones. When the grid is read-only, you will see a check mark when the Boolean value is true and there will be nothing to see if it is false, i.e. they disappear. This is intended behavior.

InterBase® - Platform             Home

On NT, is there any reason to run IB as an application rather than as a service?

By running it as an application you have to log on and leave the computer logged on all day to the same account. This becomes a security issue if you are depending on just passwords, etc to keep people where they belong.

Running IB Server as a service allows it to run regardless of who is logged in. That makes sense when you are serving many remote clients who could care less about what is going on with the server's applications.

Does IB benefit from Multiprocessors? and would it be better to run as a service or an application?
IB doesn't benefit from multi-processors on Win32 very well, if at all, in Superserver versions up to v. 6.0. Your system may do well if other things are going on and you dedicate IB to a single processor, using IB_Affinity (on www.ibobjects.com) to task your InterBase® to one CPU.

If you have a UNIX or Linux machine that still runs with the Classic architecture of InterBase®, SMP will benefit you because each user connection is a separate process and will scale across your processors.

Run it as a service if possible, to avoid having to have a user logged on in order to make the server available, to improve security and to save using up console resources unnecessarily.

InterBase® - Data Types             Home

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:

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 TABLE statement.

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             Home

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

The implicit syntax is

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             Home

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.

System - Connection             Home

My ISAPI DLL webbroker app, using IB Objects, works fine on my win 98 machine under PWS 4. It also works fine using an exe version running under webapp. But when I load it onto our NT webserver I get:

ISC ERROR CODE:335544375 ISC ERROR MESSAGE: unavailable database
Permissions, etc. seem ok.
Use TCP/IP protocol, with localhost as your server, if you need to.

Using IB_Wisql, how do you connect to a remote database that is connected over TCP/IP? and what about NetBEUI?
It follows the same format that the DatabaseName does:
TCP: <server name>:\<GDB filename with path on server>
NetBEUI: \\<server name>\<GDB filename with path on server>

This is also true when it comes to supplying a remote connection to your database parameters or in a BDE alias. In IB Objects you get the best of both worlds. You can supply the whole connect string in the DatabaseName property or you can use the Server, Path and Protocol property which are all coordinated together via parsing mechanisms.

Is there a way to detect a lost connection and try to reconnect automatically, without user action?

Hook into the OnError event and look for the ERRCODE that denotes a lost connection. Then, you can take whatever action you deem necessary to deal with the problem. If the connection is lost you need to do a disconnect and then connect again.

Perhaps you could cycle through the transactions and do an explicit Rollback for all of them. Use the TransactionCount and Transactions[] property of the TIB_Connection.

System - Security             Home

How I can find out from client-side where the security database (isc4.gdb) is on the NT server ?

There had better not be an answer for this question! You should NOT be able to do this if your security is set up properly.

If you really need to allow a client to decipher where the isc4.gdb is located then it is necessary to set up some sort of explicit programming on the server. You might consider placing it in a special table in your database. The alternative is brute force. Assume a path and try for a connection. If it fails, try another path. try and try again until you get a connection. Intercept the error codes because you will want to be able to tell if the file cannot be located.

IB Objects - IB_WISQL             Home

When I try to compile a stored proc using the DSQL tool of IB_WISQL, I get the error "Unsupported column type: 0". However, when I use InterBase®'s WISQL32, the procedure compiles no problem!

The problem is that you have left the Params checkbox checked. The checkbox is there is so that you can test DSQL statements that use input parameters. Can't do this in WISQL32 can you!

The catch is, the ":" in the stored proc is interpreted as an input parameter instead of as a parameter internal to the stored procedure. Telling IB_WISQL not to check for input parameters causes the internal parameter to be sent to the server in the appropriate way.

The rule of thumb is to ALWAYS look in the SQL trace monitor when there is an error preparing a statement...

IB Objects - Software             Home

The IBO sources include several files with the extensions .IMP, .INT and .PBL. What do the extensions mean?

The sources make heavy usage of INCLUDE directives.-
IMP is an included implementation file.
INT is an included interface file.
PBL is an included interface file that contains lists of properties/events to be published.
INC is a general include section of code.

Breaking things out into separate files helps with version control immensely and keeps the patch files smaller and makes it easier to navigate through the code. Locate the cursor on the file names you see in the code and hit CTRL+ENTER and Delphi automatically opens or goes to the file.

Can I upgrade to a higher version of InterBase® without having to change anything to my IBO application?
You can change your server and your IBO applications should continue to work just fine. If you change a v. 6.x database from Dialect 1 to Dialect 3 and change any data types, there may be corresponding changes needed to accommodate those. Watch out for double-quoted object names - in v. 6.x they are case-sensitive.

IB Objects - Compatibility             Home

I can't get InfoPower components to recognize TIB_Query.

InfoPower was not designed for native IBO datasets.

Use the TIBOQuery and TIBOTable components which are TDataset-based. For some older versions of InfoPower you need to use TwwIBQuery and TwwIBTable.

General - User Interface             Home

In some applications I notice a "flickering cursor" while an IB_Query is executing. It seems the BusyCursor is switching on and off very fast. If I move the cursor out of the application area to another place, such as the taskbar, the query is much faster! In fact, if I set the UseCursor property to False, it is faster still.

Use the BeginBusy() and EndBusy method calls in a try ... finally block to get total control over the screen cursor and prevent flickering.

General - Delphi             Home

To get consistency in the look of my applications, I would like to use the glyphs from the IBO bars controls on speed buttons I use elsewhere in my programs. Are these .bmp glyphs available someplace?

Open up the *.res file in the Delphi Image Editor program from the Tools menu. Then, open up the resource file and access the bitmaps. You can select the image, then copy and paste into MS Paint or some other graphics program.

Tip: the graphics utility Hypersnap (http://www.hyperionics.com) is ideal for manipulating glyphs.

Why am I getting 'Runtime error 216 at [address]' when I run my project?
The order of module creation is important. This error is quite common when you create a form first and a datamodule after. This causes the modules to be created in the wrong order and, when the form gets created first, it has controls trying to link to a datamodule that has not yet been created. Move the datamodule (or a form module containing needed data access objects) to the top of the creation order in the DPR.