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.

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.

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.

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.

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.

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.

See the PriceList sample application for an example of this.

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.

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.)

To pass input parameters use:

   ib_query1.ParamByName('xxx').asXXX := ;
   ib_storedproc1.ParamByName('xxx').asXXX := ; 

   aVariable := ib_query1.FieldByName('xxx').asXXX;
  aVariable := ib_storedproc1.FieldByName('xxx').asXXX;

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.

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

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.

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

...
var 
MyDataset : TIB_DataSet; 
begin 
... 
  MyDataset := TIB_DataSet.Create (Application); 
  with MyDataset do begin
  IB_Connection := myConnection; 
  IB_Transaction := myTransaction; 
  SQL.add(sqlstring); 
  prepare; 
  open; 
  ... 
  end;
end; 

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.

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.

Accessing the AsString property also loads a blob automatically.

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.

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.

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.

CREATE TABLE ARRAYS( 
ID INTEGER NOT NULL, 
CHARARRAY CHAR(10) [5], 
INTEGERARRAY INTEGER [5, 2], 
CONSTRAINT ARRAYS_PK PRIMARY KEY ( ID ));

qrArrays.Open; 
qrArrays.Insert; 
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; 
  end; 
qrArrays['INTEGERARRAY'] := tmpVar; 
qrArrays.Post;

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:

Refer to the IB manuals to see how to work with character sets in the CREATE TABLE statement.

Just be sure that your currency amounts can live within a few million dollars. DECIMAL( 9, 2 ) isn't a whole lot of precision.

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.

    SELECT TABLEA.FIELD1, TABLEB.FIELDX
    FROM TABLEA
    JOIN TABLEB
    ON TABLEA.IDFIELD = TABLEB.IDFIELD

    SELECT TABLEA.FIELD1, TABLEB.FIELDX
    FROM TABLEA, TABLEB
    WHERE TABLEA.IDFIELD = TABLEB.IDFIELD

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.

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.

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?

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.

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.

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

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.

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

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

            Home