Database

Create a TTable-Component without a form...

Solution 1

Actually you don't even need to use any component. I pass in nil all the time:

FSession := TSession.Create(nil);
  FDatabase := TDatabase.Create(nil);
  FSession.SessionName := 'DBSession'
  FDatabase.Connected := False;
  FDatabase.AliasName := Database;
  FDatabase.DatabaseName := USER_DATABASE;
  FDatabase.SessionName  := FSession.SessionName;

  FUserTBL := TTable.Create(nil);
  FUserTBL.DatabaseName := FDatabase.DatabaseName;
  FUserTBL.SessionName := FSession.SessionName;
  FUserTBL.TableName := USERTBL;
  FUserTBL.IndexName := USERSpIndex;

  FUserSource := TDataSource.Create(nil);
  FUserSource.DataSet := FUserTBL;

Solution 2

This is some code I had laying around: It worked when I last used it in a larger app. I don't see anything special I set up in that little app that I neeeded to do differently. o try something like this. I did this some time ago, I think I decended to add events, but I don't recall...

unit Unit2;

interface
uses db, DBTables, dialogs;

type fake = class(Ttable)
  procedure fakeFilterRecord(DataSet: TDataSet; var Accept: Boolean);
end;

var
  MyTable : fake;

implementation

procedure fake.fakeFilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  showmessage('hi Bill');
end;

Initialization

  MyTable := fake.create(nil);
  With Mytable do begin
    DataBaseName := 'dbdemos';
    TableName := 'biolife';
    OnFilterRecord := MyTable.fakeFilterRecord;
    Filtered := true;
    active := true;
  end;

  {just prove I got some data...}
  showmessage(MyTable.fields[1].asstring);

Finalization
{important!  MyTable has no parent - this will leak memory if you don't free it...}
MyTable.free;

end.

Database structure version tracking

Thanks for the ideas, posted here and via email. I think I found a better solution.

Apparently the BDE keeps a structure version number, at least for Paradox files. (I don't know about dBase or others.) Whenever you change the structure (in Database Desktop, for instance) the BDE increments the version number. The following unit provides a function that returns the structure database version:

(*****************************************************************************
* DbUtils.pas
*
* Database Utilities
*
* 09/20/96 WTR - created
*****************************************************************************)
unit Dbutils;

(****************************************************************************)
(****************************************************************************)
  interface
(****************************************************************************)
(****************************************************************************)

uses
  DbTables;

function DbGetVersion(table: TTable): LongInt;


(****************************************************************************)
(****************************************************************************)
  implementation
(****************************************************************************)
(****************************************************************************)

uses
  Db, DbiProcs, DbiTypes, {DbiErrs,}
  SysUtils;


{---------------------------------------------------------------------------}
(*
 * Purpose:    determine the version number of the specified table
 * Parameters: table (I) - table of interest
 * Returns:    version number
 * Exceptions: EDatabaseError
 *)
function DbGetVersion(table: TTable): LongInt;
var
  hCursor  : hDBICur;
  tableDesc: TBLFullDesc;
  cName    : array[0..255] of Char;
begin
  { make c-string copy table name }
  StrPCopy(cName, table.TableName);

  { ask BDE to create info record for specified table }
  Check(DbiOpenTableList(table.DBHandle, True, False, cName, hCursor));

  { get info record into structure }
  Check(DbiGetNextRecord(hCursor, dbiNOLOCK, @tableDesc, nil));

  { get version field from extended portion }
  Result := tableDesc.tblExt.iRestrVersion;

  Check(DbiCloseCursor(hCursor));
end;

end.

Lookup value on input

Dirk Couck wrote:

> 

How can I lookup a value in a table or query while the user is typing in . The cursor (and 
display) should be closer to the exact value as the user puts in  more characters.

> 

I did something like this in delphi 1. It might not be the best solution but it works.

I Keep a TTable open for the value to be searched for. The index must, of course, be on the field that's used in the edit box. In the editbox's change event I use the editbox's value in a FindNearest call on the TTable. Whatever I get as a result is placed back in the editbox's text.

This is only the general way of it. In fact I enable a 1/3 second timer in the change event and do the search (and disable the timer) in the timer's timer event. This allows the user to type rapidely without having to do the search for every character typed.

You may also want to do some special processing for the backspace key or force the selection to the added part of the string.

Instead of returning the results to the edit box (which would overwrite the end-user's entry) display the results in something else - eg a listbox. You could display several near matches this way eg:

procedure Edit1OnChange( ...);
var i:integer;
begin
if not updating then exit; {set updating elsewhere - eg a timer}
updating:= false;
Table1.FindNearest([Edit1.text]);
ListBox1.clear;
i:= 0;
while (i < 5) and (not (table1.eof)) do
        begin
        listbox.items.add(Table1.fields[0].asString);
        inc(i);
        table1.next;
        end;
listbox1.itemindex:= 0;
end;

DbiCopyTable example

Here is an example of a routine that I use for copying and deleting tables. It uses DB, DBTables, DbiProcs,DbiErrs, and DbiTypes. You simply provide the directory to copy from, the source table name, the directory to copy to, and the destination table name, and the BDE will copy the entire table, indexes and all to the new file. The delete function takes the path to delete from and the name of the table to delete, the BDE takes care of deleting all associated files (indexes, etc.). These procedures have been pulled off a form of mine, and I've edited them to remove some dependencies that existed with that form. They should now be completely stand-alone. (If they compile, that is! :) Use in good health, and enjoy!

procedure TConvertForm.CopyTable(FromDir, SrcTblName, ToDir, DestTblName: String);
var
  DBHandle: HDBIDB;
  ResultCode: DBIResult;
  Src, Dest, Err: Array[0..255] of Char;
  SrcTbl, DestTbl: TTable;
begin
  SrcTbl := TTable.Create(Application);
  DestTbl := TTable.Create(Application);
  try
    SrcTbl.DatabaseName := FromDir;
    SrcTbl.TableName := SrcTblName;
    SrcTbl.Open;
    DBHandle := SrcTbl.DBHandle;
    SrcTbl.Close;
    ResultCode := DbiCopyTable(DBHandle,false,
      StrPCopy(Src,FromDir + '\' + SrcTblName),nil,
      StrPCopy(Dest,ToDir + '\' + DestTblName));
    if (ResultCode <> DBIERR_NONE) then
    begin
      DbiGetErrorString(ResultCode,Err);
      raise EDatabaseError.Create('While copying ' +
        FromDir + '\' + SrcTblName + ' to ' +
        ToDir + '\' +   DestTblName + ', the '
        + ' database engine   generated the error '''
        + StrPas(Err) + '''');
    end;
  finally
    SrcTbl.Free;
    DestTbl.Free;
  end;
end;

procedure TConvertForm.DeleteTable(Dir, TblName: String);
var
  DBHandle: HDBIDB;
  ResultCode: DBIResult;
  tbl, Err: Array[0..255] of Char;
  SrcTbl, DestTbl: TTable;
  SrcTbl := TTable.Create(Application);
  try
    SrcTbl.DatabaseName := Dir;
    SrcTbl.TableName := TblName;
    SrcTbl.Open;
    DBHandle := SrcTbl.DBHandle;
    SrcTbl.Close;
    ResultCode := DbiDeleteTable(DBHandle,
      StrPCopy(Tbl,Dir +   '\' + TblName),nil);
    if (ResultCode <> DBIERR_NONE) then
    begin
      DbiGetErrorString(ResultCode,Err);
      raise EDatabaseError.Create('While deleting ' +
        Dir +   '\' + TblName + ', the database ' +
        'engine generated the error '''
        + StrPas(Err) + '''');
    end;
  finally
    SrcTbl.Free;
  end;
end;

Problem with "Getting Started" Guide P 42

In article <a67cc$12c21.16d@news.vortex.is>, From siggir@vortex.is 

(Sigurdur Reynisson), the following was written:

> I just installed Delphi 2.0 and it went just fine, no comment's or remarks came from the 
installation process.

> 

> I'm going through the "getting started" guide for Delphi 2.0.  On page 42, "Adding a 
display grid" for the DBGrid, having  set the DataSource to 
GDSDataModule.CustomerSource, I get  no data in the DBGrid. At the bottom of page 
42 it simply states  "Immediately, the data is displayed in the DBGrid." but that  never 
happens.

> Also when I run the application I get no data on any  form/datasource in it.

> 

> In the "Database Explorer" from the Delphi menu, I get all  the tables and their data 
with no problem.

The table needs to be open. Change the Active property of the Ttable to True.

Delphi table scrolling: PeekMessage() tricks

I have had a play with PeekMessage(). The function works, but the catch is as follows.

I am scrolling a linked table. The "seek" in the link slows the scolling down. So I only want to "seek" and locate the record pointer in the other table when the user has STOPPED scrolling. While the user is scolling, I don't want seeks. So I thought, if there are Messages pending, with PeekMessage(), then don't seek, else do. Sounds simple, but the trick is this.

I set the seek filter to WM_MOUSEFIRST/LAST. The user presses the mouse down continuously on the scroll bar of a DBGrid. This results in PeekMessage() returning false, which is not what we want. Only continous and very fast Mouse up/mouse down/mouse up/mouse down action would return true.

If I use 0 and 0 in the filter to catch ANY message, the result always returns true. The reason, I think, is that any mouse click in my display has some consequence, i.e. a PAINT message, so the PeekMessage returns true at all times, so does not help.

What would be great would be if there was a OnMouseUp() handler which works when the user releases the mouse on the DBGrid scroll bar. Sadly, OnMouseUp() works only inside the DBGrid, not on its scrolls bars. OnMouseUp() with TForm's KeyPreview:=true does not respond at all, I have found.

One more thing I tested was polling with GetKeyState() for the Mouse buttons. I thought while the mouse is DOWN do not update the "seek", otherwise do. Unfortunately, by the time the mouse is UP, the scrolling has finished. This is the way the Windows scroll bars seem to work. So the seek never happens and the table record pointer is not updated.

These issues all concern the scroll bars, everything else can be handled with OnKeyUp() and OnMouseUp() elsewhere.

OnCalcFields Hint

The Oncalc event is called VERY often, and can be unnecessary and time consuming

lets say you have a calculated field, and that need calclating according to some field that the user edits (i.e)

MyCalcField.AsInteger := Table1Field1.AsInteger + 10;

Now, should you decide to go and sequentially go through every record in the table, that is a lot of on calc events occuring! They are also possibly unnecessary if you are not doing processing involving the MyCalcField.

I would suggest turning this event off before processing, and the turning it back on after processing i.e. :

Procedure TForm1.BigProcessingFunction;
begin
  Table1.OnCalcFields := nil;
  
 <Do massive processing here!>

  Table1.OnCalcFields := Table1OnCalcFields;
 
end;

of course the fields are not calculated during the processing time, which can be a bummer, but if you need the result of a specific field, you can calculate that during the massive processing!

This method of avoiding unnecessary code can be used all over the place and make a significant difference to the speed of an App.

BDE alias info

> 

> Does anyone know how to interrogate the IDAPI configuration to get the directory 
mapping for an alias?

> 

Take a look at the GetAliasParams Method of TSession.

The returned stringlist will contain the path.

I use the following function:

uses  DbiProcs, DBiTypes;

function GetDataBaseDir(const Alias : string): String;
(* Will return the directory of the database given the alias
  (without trailing backslash) *)
var
  sp  : PChar;
  Res : pDBDesc;
begin
  try
    New(Res);
    sp := StrAlloc(length(Alias)+1);
    StrPCopy(sp,Alias);
    if DbiGetDatabaseDesc(sp,Res) =  0
    then Result := StrPas(Res^.szPhyName)
    else Result := '';
  finally
    StrDispose(sp);
    Dispose(Res);
  end;
end;

First access to any table is very slow

From: "Jim O'Flaherty" <jimo@sequel.com>

NOTE: Be aware that if you change your table definition, you will need to delete the file in this Cache dir that represents it. You can find the filename in the SCache.INI file located in the same directory by viewing it in your favorite text editor.

db file at run-time

uses DB, DBTables, StdCtrls;

procedure TForm1.Button1Click(Sender: TObject);
var
  tSource, TDest: TTable;
begin
  TSource := TTable.create(self);
  with TSource do begin
    DatabaseName := 'dbdemos';
    TableName := 'customer.db';
    open;
  end;
  TDest := TTable.create(self);
  with TDest do begin
    DatabaseName := 'dbdemos';
    TableName := 'MyNewTbl.db';
    FieldDefs.Assign(TSource.FieldDefs);
    IndexDefs.Assign(TSource.IndexDefs);
    CreateTable;
  end;
  TSource.close;
end;