Cannot create Paradox tables with zero-size Blob fields.

Description

Reported by Hallvard Vassbotn

The BDE Paradox driver has a bug in the implementation of the DbiTranslateRecordStructure function, you cannot create a Paradox table with a Blob field of size 0 using the VCL TTable component.
The 16-bit version of BDE also has this same problem, but the TableType property of TTable must be manually set to ttParadox for it to appear. It seems to be another bug that causes GetDriverTypeName to return nil instead of 'PARADOX'. This makes DbiTranslateRecordStructure to simply copy the logical record onto the physical record instead of actually converting it.

Steps to reproduce the problem:
Create a new application. Drop a button and a memo on the mainform. Add this code to the Click event of the button:

procedure TForm1.Button1Click(Sender: TObject);
var
  Table: TTable;
begin
  Table := TTable.Create(Self);
  try
    with Table do
     begin
       DatabaseName := 'C:\';
       TableType := ttParadox;
       TableName := 'TestIt.DB';
       FieldDefs.Add('Blob', ftBlob, 0, false);
       CreateTable;
       Open;
       Memo1.Lines.Add(Format('Blob.Size = %d', [FieldByName('Blob').Size]));
     end;
  finally
    Table.Free;
  end;
end;

Now run the application and press the button. You will see in the memo that the size of the Blob field in the table that was created is 1, not 0 as we intended.

This can also be verified by using the Database Explorer and see that the size and physical size for the blob field is 1 and 11, respectively (not 0 and 10 as we intended).

Solution / workaround

For 16-bit BDE:

You can get around the bug by relying on another bug... Simply make sure TableType is ttDefault (the default). Then use '.DB' as the extension of the TableName property to signal that this should be a Paradox database. To solve the problem for all cases, follow the guidelines below.

For 32-bit BDE:

The only way I have found to fix this (other than calling the DBI-functions directly) is to modify the implementation of the TTable.CreateTable method found in SOURCE\VCL directory:
At line 1790, insert the following code (line 1338 for Delphi 1.0):

      Check(DbiTranslateRecordStructure(nil, iFldCount, FieldDescs,
        GetDriverTypeName(DriverTypeName), PSQLLName, pFLDDesc, False));

{$DEFINE Fix_Paradox_ZeroSizeBlob_Bug}{$IFDEF Fix_Paradox_ZeroSizeBlob_Bug}
{ Correct a bug in the Paradox driver. This enables us to have blob fields 
  that use zero extra bytes in the record structure. }
if StrComp(DriverTypeName, 'PARADOX') = 0 then
 for I := 0 to iFldCount-1 do
  with PFieldDescList(FieldDescs)^[I] do
   if (iFldType = fldBLOB) and (iUnits1 = 0) then
    with PFieldDescList(TableDesc.pFLDDesc)^[I] do
     begin
       { The Paradox driver has a bug in that it sets all Blob fields of 
         size 0 to size 1 }
       Dec(iLen, iUnits1);
       iUnits1 := 0;
     end;
{$ENDIF}

      iIdxCount := IndexDefs.Count;

The first and the last statements are taken from the original source so that you know where to insert the code.

This is a quick-and-dirty fix around a problem in the BDE Paradox driver, but it does work. The code first check to see if the we are in fact using the Paradox driver. Then it loops through all the logical fields in the FieldDescs array. If it finds a Blob field that has a logical size of 0, it changes the correspondig physical field in the TableDesc.pFLDDesc array so that the iLen and iUnits1 fields are set correctly.
If you do not want to modify the VCL source, you can create your own TTable component that implements this corrected CreateTable. Note that this would be a static replacement rather than a virtual override. This means that any code that calls the old TTable.CreateTable (like TBatchMove.Execute) would still call the old code.

Requesting the OldValue property of a Field throws an exception.

Description

By Quentin Sarafinchan; additional information by Arjen Broeze

There are two possible exceptions that can be thrown if you try to access the OldValue property of a field:
The first is caused when the DataSet's CachedUpdates property is set to False. If this is the case the OldValue property is not available and trying to access it throws the EDatabaseError exception with the message 'Not in cached update mode'.
The second is caused when you try to access the OldValue property when adding or inserting a new record. Since there is no old record, trying to access the old value of a field results in the EDBEngineError with the message 'At end of table'.

Solution / workaround

Enclose the code that utilizes the OldValue property of a field in a try-except block or check if the 'State' property of the DataSet is dsEdit before attempting to get the OldValue of a field.

It seems that there's a bug involving large fields, on Oracle databases. It gives an exception violation, but not when the user is the creator of the table.

Description

A thorough description is given in the original bug report by Olivier Steinberg.
I have no independent confirmation of this bug, so if anyone can confirm it (or debunk it), please do so!

Problems with Blobs and CachedUpdates

Description

By David J Schuldenfrei

First (minor) bug (not yet independently verified, reported for Delphi 2, RS):
The UpdateSQL won't work if any of the update fields are TBlobFields or TBytesFields (you get an error message Capability not supported). A workaround could be found using the OnUpdateRecord event. This is probably not a real bug but an annoyance.

Second bug:

This is a major bug, reproducible even at design time. If you have a "large" result set (65 records or more!) in a Dataset with BlobFields you will get "Invalid Blob Handle". Demo:

1. Open a new project.
2. On the main form drop a TTable, Table1
3. Set properties: DatabaseName: DBDemos; TableName: RESERVAT.DB; Cache Updates: True; (DBDemos is a demo Paradox DB, provided with Delphi).
4. Add a Datasource (DataSource1), Set its Dataset property to Table1
5. Add a TDBMemo, set itsDataSource to DataSource1 and its DataField to Pay_Notes
6. Open Table1 (Set Active = true)

DBMemo1 displays: (Memo too large)

If Table1.CachedUpdates = False then everything works as expected !!!
Has anybody got a fix, workaround ? Otherwise, I believe this is a major bug in Delphi / BDE.

Additional comment by Bob Pitatzides:

I was able to reproduce it. There must be a bug. When I ran the program I had an exception "This is not a Blob field". When I set CachedUpdates to False, everything worked fine.

Solution / workaround

Trivial workaround: don't use CachedUpdates in combination with Blob fields

If you're using Sybase SQL Anywhere 5.x and TStoredProc on Delphi 2, you may have trouble.

Description

By Jean-François Claeys; confirmed (D3) by dlewis2@worldnet.att.net

If you're using Sybase SQL Anywhere 5.x and TStoredProc in Delphi 2, you will find yourself in bad hands. Don't try to access parameters with the Object inspector, the types are always false and as soon as you run your app, Delphi will tell you the parameter list is incorrect. It seems Delphi doesn't recognize accurately the parameters definition that Sybase is returning it.

Solution / workaround

The solution is quite simple: Do everything at run-time:

procedure TDatamodule1.OnCreate
begin
  with StroredProd1, Params
  do begin
    Close;
    Clear;
    CreateParam(ftInteger,'First',ptInput);
    CreateParam(ftString,'Second',ptInputOutput);
            {NEVER USE ptOuput , always ptInputOutput for results}
  end;
end;

And be sure to have at least one parameter on your stored procedure, otherwise...

Locate (method of TTable and TQuery) in combination with loPartialKey doesn't always work

Description

By Bob Pitatzidis

Try the following example code (on a simple table with two fields: Firstname and lastName):

procedure TForm1.Button1Click(Sender: TObject);
var
  myvariant: variant;
begin
  myvariant := vararraycreate([0,1], varvariant);  {create 2 fields}
  myvariant[0] := 'pete';
  myvariant[1] := 'fish';
  if query1.locate('Firstname;LastName',
                   {'LastName;Firstname',}
                   myvariant,
                   [loCaseInsensitive, loPartialKey])
  then showmessage('Found')
  else showmessage('NOT found');
end;

It seems that the partial key does not work in the following cases:

1. Put the fields in order other than that defined in TQuery. (Like in the example code between brackets)
2. Cut characters from the beginning of the search string. ie search for 'ish' instead of 'fish'

Long query on Sybase crashes with time-out error.

Description

Reported by Adrienne Beiles and Brett ...

Any SQL which runs for more than sixty seconds on Sybase, crashes with a lock time out error. There are only three timing settings in the BDE which can be set. They are...

• On Sybase Driver: Connect Timeout :-default 60, our setting 600
• Timeout:- default 500
• On Alias: Max Query Time :-default 3600 secs

The queries we have tried to do actually run in the native Sybase WISQL program, they can take up to 10 minutes. The SQL's are being run through Database Desktop and Delphi with an SQL component with the same reults.
It appears from the documentation we have that these options are the appropriate ones to effect the timing changes we require however altering the default values appears to have no effect.

On 3 Feb 99 a comment to this was received:
We encountered similar problems using Delphi 4.02. What we did was put a TDatabase component on a form, select SYBASE as driver and put the default parameters in there. The TIMEOUT parameter is not put in the parameter overrides. Putting it there does nothing.
The TIMEOUT parameter can be found in the BDE administrator: Configuration, Native, SYBASE.

The database component gets a parameter override called MAX QUERY TIME.
This parameter is the max. query time WITHIN the timeout limit. So if the TIMEOUT is set to 500, and in the TDatabase component has a parameter override MAX QUERY TIME set to 3600, then the TIMEOUT limit in the BDE administartor configuration is used. Putting thr TIMEOUT parameter in the component's parameter overrides does not help: the BDE configuration TIMEOUT parameter value is used.

So the solution or workaround is:
Set the TIMEOUT to the maximum query time used for all your TDatabase components using Sybase, and then in the component iself limit the maximum query time for this component only using the override MAX QUERY TIME.

I hope somebody can use this info. This solution worked for us.

In Queries on Oracle tables, Delphi 2.0 always rounds the FloatField values

Description

The bug appears when Delphi 2.0 is used with RDBMS Oracle71. (and maybe woth other Oracle versions) To access Oracle, SQL Link is used. In Oracle7 I have a table, for example EMP(name varchar2(20), salary number(10,2))

In Delphi 2.0 I have a Query with SQL text = SELECT * FROM EMP, and query live=true. When I use a DBGrid to modify a record in Query, Delphi 2.0 always rounds the floatfield salary to an integer value. For example I write the number 100,45 to the salary field, after Post I get the number 101.
This bug does not appear when I use table instead of query.
First reported by Le Huy, and independently also reported by mitch ....

If an error occurs while a dataset is reading records from a database, it is not reported.

Description

Reported by Roger Hernandez; confirmed by Mark Sigerson

This problem is caused by the TDataset.GetNextRecords function, which has the following code:

function TDataSet.GetNextRecords: Integer;
begin
  Result := 0;
  try
    while (FRecordCount < FBufferCount) and GetNextRecord do Inc(Result);
  except
  end;
end;

The try/except block has a blank handler. This will trap ALL exceptions, giving NO indication to the user that an exception has occured.
I ran into this problem when using a stored procedure which returns a result set. When certain conditions are met, the stored procedure on the server raises an error. The client does not see any error message whatsoever! DbiError gets called, but the empty try/except block swallows the generated exception! This problem is also present in several other places in DB.PAS.

Mark Sigerson adds:
We are using Delphi 3.02 (3.0 Build 5.83 patched up to 3.02) on NT4 stations. Our application uses TQuery components to perform complex SQL queries on an Oracle 7 database.

We encounter the problem when a TQuery is opened using a SELECT statement which has a particular type of syntax error in its WHERE clause, that is, when the WHERE clause specifies a number value for a string field or a string value for a number field, e.g.

...
WHERE StringField > 6
...

or

...
WHERE NumberField = 'robert'
...

The query is executed and provokes the ORACLE SQL error "ORA-10722: invalid number error". An EDBEngineError is then created in the procedure DbiError in DBTables.pas and then caught and destroyed in TDataSet.GetNextRecords in DB.pas as described in the bug report. The exception-handling code around the opening of the TQuery in our application thus never gets the chance to execute, and the application is unaware of the error.

I can't see any way around this except to remove the blank try...except handler.

Using a TTable with a filter (OnFilterRecord), in combination with CachedUpdate mode, can cause loss of data.

Description

BDE 4.0, 4.01, 4.51, Paradox

By Marc Sprit; checked by Steve Ludmon

I have a TTable with filter (code on OnFilterRecord event), in CachedUpdates mode. When I want to edit the current record, using the Edit method of TTable component, BDE lost the contents of the current record !
Data are no longer valid and we can see a 'NAN' expression in TDBGrid numeric columns (because this numeric contains now invalid characters). This example show when the bug can be easily reproduced (MyTable use Biolife.db in DBDemos example database).

unit BugBDE40;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBTables, DB, StdCtrls, Grids, DBGrids;

type
  TfBugBDE40 = class(TForm)
    MyTable: TTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    MyTableSpeciesNo: TFloatField;
    MyTableCategory: TStringField;
    MyTableCommon_Name: TStringField;
    MyTableSpeciesName: TStringField;
    MyTableLengthcm: TFloatField;
    MyTableLength_In: TFloatField;
    MyTableNotes: TMemoField;
    MyTableGraphic: TGraphicField;
    procedure MyTableFilterRecord(DataSet: TDataSet; var Accept: Boolean);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
  public
  end;

var
  fBugBDE40: TfBugBDE40;

implementation

{$R *.DFM}

procedure TfBugBDE40.MyTableFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Accept := MyTableLengthCm.AsInteger >= 100;
  // or the condition (without persistent fields)
  //Accept := MyTable['Length (cm)'] >= 100;
end;

procedure TfBugBDE40.Button1Click(Sender: TObject);
begin
  // To enter in edit mode
  MyTable.Edit;
end;

procedure TfBugBDE40.FormCreate(Sender: TObject);
begin
  MyTable.Open;
end;

procedure TfBugBDE40.FormDestroy(Sender: TObject);
begin
  MyTable.Close;
end;

end.

Borland US let me know that this problem is registered and will be fixed in the Delphi 3.01 release with BDE 4.5 (available -Autumn 97-).
Update: On 9 January 1998 we (the DeBug team) received a message from Olivier Dewit that the bug has NOT been fixed. It is still present in Delphi 3.02; BDE 4.51.

Comment from checker:
I duplicated the bug quite easily and was unable to fix the problem. It appears that there is indeed a bug in the BDE! The error occurs when you have BOTH cached updates AND a filter set up at the same time. I tried a lot of different ways of turning things on and off but kept on coming back to the same situation - caching and filtering at the same time just don't gel! It would seem that the BDE keeps the filtered records in memory as well as the cache information for updating changes. However when both are used at the same time they get a little mixed and we lose the data. Not a very nice situation at all...

Solution / workaround

By Steve Ludmon

I was able to find a work around which allows both filters and cached updates.

First I feel a little maintenance on the data table is required. It is not good practice to name fields (or variables) with anything other than alphanumerics and the underscore character. The filtered field in question is called "Length (cm)". Change it to "Length_cm" (without quotes) using the database desktop. This simplifies the SQL string coming up.

Next - replace the table component with a query component. Also remove the MyTableFilterRecord code as it is no longer needed. Set the database property of the query to DBDEMOS as before. Change the SQL string of the query to:

select * from biofile where Length_cm > 100

This query now duplicates the table with the filter. CachedUpdates can now be turned on for the query successfully.

I tried this and it works faultlessly.

The BDE Paradox Hun 852 DC language driver in 16 bit BDE works incorrectly.

Description

If you insert the words "bögre" and "bot" into an indexed Paradox table, the word "bögre" will precede then word "bot", however in the Hungarian language the letter o differs from the letter ö, and precedes it.

With the letters u and ü the situation is the same, and the language driver fails with them.
Reported by Skumát László

Using secondary indexes, when data containing international characters is entered, data may be corrupted

Description

Reported by Svein Olav Mytting; checked by Hallvard Vassbotn

BDE 2.52 - 4.0

I presented the following message with the Delphi newsgroups last December. It describes a bug in 16-bit BDE (2.52). It was confirmed from 3-4 others, including one that claimed the same problem to exist in 32-bit BDE.
I then filed a bug report with Borland, but got no response. I also e-mailed several persons at Borland, also with no response. The bug is still not fixed, at least not for 16-bit use, and I am struggling with it on a regular basis.
Here follows the original message. Some new comments follow in the end...

December 1996
Is there any chance that you use Paradox files with one or more secondary indexes? And, do you think some users may input one or more international characters, beyond ASCII 127, into your tables? (like E with accents, German umlauts, Scandinavian characters, etc...) In that case, this applies to YOU... Read on for more details, as well as an exampe I'd like you to test.

I know it is a rather serious accuse, and everybody is invited to prove me wrong, but I believe I have isolated a bug in BDE that can make your data go corrupt any day. They may actually be corrupt already. Basically, if you have one or more secondary indexes on fields that contain international characters, major table operations like regenerating indexes, or packing the table, can corrupt the table. Funny enough, you will not notice this, until you try to delete records...

I have checked this with 16-bit BDE 2.52 (the last one) only, and I do not know if the problem applies to other versions. I guess it doesn't. (See second part, RPS) First, I assumed the problem was in my language driver, but then I tested with standard ASCII driver as well as the Norwegian Pdox Ansi Nordan40 driver, and they behaved identically with respect to the problem. Maybe you will not see the problem if you never fumbled with language drivers. Please state your BDE software version, and what sort of language driver you use (see BDECFG, front page and system page), if you respond to this post. If you don't get any problems, you could try to change to another language driver, and test again, if you are interested.

Here is a small example that I invite you to test:
1) With the Database Desktop, create a table with one field only. Let the field be alphanumeric, one character wide. Make this the key field. Create a secondary index on this one-character field. Let the index be non-case sensitive (that's the default). You may name the table, field, and index whatever you want.
2) Now we will add some data to our table. Throw up a Delphi form with a TDBGrid, or use the Database Desktop. Simply insert international characters in the table. Make for instance three records with variations on E with accent. (é, è, and ê). Or Norwegian ÆØÅ, Swedish ÄÖÅ, German öüä, or whatever you like. One record should be enough, theoretically. If you couldn't read all these weird chars, you get the point anyway, I hope.
3) So, we have some records of characters beyond Ascii 127. Now close the table, and do a major table operation on it. For our example, go to Database Desktop, choose Restructure, check "Pack Table", and Save. This will make the BDE pack the table.
4) So far it looks good. But now try to open the table like earlier, and try to delete all the records. Viola! Delete does not work, and you (at least I) get the message "Corrupt index".
The table is not only corrupt - it is more or less corrupt beyond repair. Try some TUtility app (I use the Pack.zip from DSP) - it will lie and report the table to be completely good! A Table Repair is not the solution either, it is part of the problem, I believe. That's because the "major table operation" mentioned in step 3, could have been replaced with several other operations like DbiRegenIndexes, TUtility table pack, or actually even TUtility Table Repair. Adding another secondary index on a field containing international chars, does the same trick. The table seems to be good at first, but turns out to be bad as soon as you try to delete records containing indexed international chars.

The bottom line is, I believe, that any building or rebuilding of secondary indexes goes haywire with internationel characters.

If this problem did turn up in your place, imagine the danger for real business situations. Maybe you should make a copy of all the tables you use in your current projects, and try to delete records that contain indexed internatoinal characters. This in order to see if any of your tables are already corrupted.

The fields involved may of course be wider than 1 character. The international chars don't even have to be in the start of the words. Actually, those fields don't even have to be the first ones to compose the secondary index. (If they are, you will see that sorting goes bad, even before you try to delete records.) All of this happens at least on MY machine. How am I supposed to live with that? Have I just sitted too long in front of my Delphi windows? Do I see ghosts in broad daylight? Fata Morgana on my own desk? After struggling with this for weeks, should I start growing tomatoes instead? Do I need serious help? Hey, why do I need such a white shirt with extra long arms? Tw...Heeeeeeee-.#%
(end of original message)

Second part
Not much has been learnt since I posted the message and filed the bug report. A danish developer reported recently that he was regularly struggling with the same. It seems the problem can be avoided if you do not use any non-US language drivers. Further, most reports seem to be from Scandinavia, so it may be that the problem does not occur with french, or german drivers, etc. More testing is desirable.

I now also believe that major table operations like reindexing are NOT necessary to produce the bug, but it will accelerate the problem. Neither is deletion of records necessary. Changes in a table forcing indexes to re-order some records containing indexed international characters will often be enough to corrupt the table.

In general, international characters together with international language drivers screw up. I have found the best solution to be to skip language drivers altogether and accept US sorting of all characters. I believe this problem is a serious one for international 16-bit developers, of which there should be significant numbers. But since Borland is not interested in responding to the problem, much less solving it, I do not know what to do more. For the moment I am considering leaving the Paradox format for the benefit of some SQL server.

Comment from checker:
I can confirm that I see the same bug running BDE 4.0 on NT 4.0 sp3.
I did the test in Database Desktop 7.0 but you would see the same problem with any app that uses BDE.

Access violation in IDSQL32.DLL

Description

Reported by Wm. Scott Miller

If you get an access violation concerning IDSQL32.DLL you have more than likely set a DefaultExpression for a field in a DataSet.
Verified in Dbase, Paradox, MS SQL 6.5.
Platform: Appears to be a BDE problem. Verified in 4.51.
Borland has been notified and is currently researching this bug.

Solution / workaround

Set a CustomConstraint for the DataSet that contains the field for which you wish to set a DefaultExpression. This will solve the Access Violation.

When the CachedUpdates property is set to true and the Filtered property it set to true too, when you put the dataset into edit mode (i.e. Table1.edit) the contens of some fields are changed and some are deleted

Description

Reported by Ivan Zernovac; checked by Reinier Sterkenburg

This will not happen if you use the Filter property, it happens only if you use the OnFilterRecord event handler. The problem does not occur on every table (I dont know why). Maybe this should be reported to other Delphi users.
Ivan has provided a demo program which demonstrates the problem.

Comment from checker:
The demo program does show the described buggy behaviour. But I haven't checked whether this behaviour is reproducible with other tables or what the cause may be.
Can anyone please look into this?

BDE error number 9987 - Invalid File Name

Description

Reported by Paul Cooper; checked by Jack Russell

I've recently installed Delphi 2.0. However I can't get any of the database demo example programs to work.
The BDE reports an error number 9987 - Invalid File Name

All the database files are present and intact, and can be read using the database desktop though.

Solution / workaround

The fault lies with WIN 95's OSR2 release and the new FAT32 disk access. Users with FAT32 need to download a new copy of IDAPI32.DLL from the Borland ftp site. The new .DLL is capable of reading the new FAT system, thereby enabling the Borland DBE to function correctly.

The file to look for is FAT32.ZIP; can be found through
ftp://ftp.inprise.com/pub/devsupport/patches/index.txt

Setting the TTable's Active property to true in the Object Inspector may cause "Table is busy" error mesage when calling the EmptyTable method.

Description

Reported by Ivan Zernovac

To reproduce:

1. Backup the Custoly.db table.
2. Drop a TTable component on the form, set the database property to DBDEMOS and the table property to Custoly.db
3. Set the Active property to true.
4. Drop a button on the form and attach the following handler: with table1 do begin close; Exclusive:=true; EmptyTable; // <<<<< 4. place breakpoint here Exclusive:=false; open; end;
5. Set a breakpoint on the line with the EmptyTable statement

After calling the EmptyTable method, a "Table is busy" error mesage occurs.

Solution / workaround

Open the table using its Open method, instead of setting its Active property to true in the Object Inspector.

Packaged procedures in Oracle 8.04 using REF CURSORS no longer function. (Delphi 4; BDE v. 5)

Description

Reported by Tom Scott

This bug has been reported to Inprise and confirmed as a bug. It is #21770.

You cannot use a REF CURSOR in a package with anything other than an input/output Cursor parameter.
Depending on which value you have for the DLL32 variable in the BDE, you will get the following results:
Using SQLORA8.DLL, you get ORA-01036 - Illegal variable name/number.
Using SQLORA32.DLL, you get ORA-01001 - Invalid Cursor.
The net result is that packaged procedures using REF CURSORS that worked perfectly well under Delphi 3 and BDE 4 no longer function.

This is a severe restriction in using Oracle with Delphi, since packages are an intregral part of C/S development with Oracle, and REF CURSORS are the primary way to return cursor-datasets to Delphi from within a procedure.

It looks like the BDE leaks memory resources each time it's started and stopped.

Description

Reported by Maurizio Ferreira; checked by Reinier Sterkenburg

If you have a program that opens and closes teh BDE several times you can lock your system (I had a program that opened and closed the BDE at each arrival of data and it locked nt4 sp3).
The bug can be reproduced using a form with a Timer and the following code as event handler:

procedure TForm1.TimerTimer(Sender: TObject);
begin
  if not Loaded
  then Check (dbiinit (nil))
  else Check (dbiexit);
  Loaded := not Loaded
end;

procedure TForm1.check (cc:dbiresult);
begin
  if cc <> dbierr_none
  then raise exception.create (format ('dbi error %d',[cc]));
end;

(use unit BDE; declare Loaded as a Boolean and initialize it to False)

After activating the program follow memory usage with any monitor.
I tried this with Delphi 4 pro, patch #2, and BDE 5.01, but I'm quite sure the bug exists with previous versions of the BDE
I've seen the bug both on win98 and Nt4 sp3

The data packet of a TClientDataSet accumulates unusable data when calling SetOptionalParam more than once with the same parameter name.

Description

Reported by Wayne Sherman

This extra data is saved with the packet and cannot be removed(?). Thus, the packet can grow in size over time when the dataset itself has not been modified. Also, data stored with additional calls to SetOptionalParam is inaccessable and hence lost.

To Duplicate:

• Start a new application
• Drop a TClientDataSet on Form1.
• Drop a TButton on the form.
• Use object inspector to assign a file name to the FileName property of ClientDataSet1 as follows: 'Test.cds'
• Double click DataSet to bring up Fields Editor
• Right click in fields editor and choose New Field
• Add a string field named 'TestField' of size 20. (The field is not important, but we need at least one to work with the dataset)
• Close Fields Editor.
• In the OnCreate event of the form add the following code:

  procedure TForm1.FormCreate(Sender: TObject);
  begin
    try
      ClientDataSet1.Open;
    except
      ClientDataSet1.CreateDataSet;
    end;
    ClientDataSet1.LogChanges := false;
  end;

• In the OnClick event of Button1 add the following code:

  procedure TForm1.Button1Click(Sender: TObject);
  var
    StringIn, StringOut: string;
  begin
    StringIn := TimeToStr(Time);
    ClientDataSet1.SetOptionalParam('TestData', StringIn, false);
    StringOut := ClientDataSet1.GetOptionalParam('Testdata');
    ShowMessage(Format('Param written in as: %s%s Param read out as: %s',
      [StringIn, #13, StringOut]));
  end;

• Run the project.
  Ignore the exception on the first run (the file does not exist yet, we catch the exception and create a new dataset).
  Button1 adds a parameter to the dataset (the current time), reads it back, and shows us a message about what it did.
• Run the program a couple of times, each time hitting Button1 several times and observe the results.

Observed Problems:

1. The parameter we read back is always the value of the first call to SetOptionalParam. Additional calls to SetOptionalParam add data to the packet, but this data is inaccessible. It just accumulates and takes up space in the packet(forever-?).
2. When exiting, the packet gets saved to a file. Since this "extra" data gets saved, on successive runs, the packet size will continue to grow as these SetOptionalParam entries accumulate. I don't think there is any way to clear this data from the packet once its in there.

It is clearer what is happening when you look at the file with a file editor (like HexEdit). You can see how the multiple copies of the optional parameter are accumulating in the data packet.

Why is this a bug:
Suggested/Expected Behavior:
1) When calling SetOptionalParam, if the parameter exists, it should be replaced by the current value. Space in the packet from the old value should be reclaimed so the packet won't grow unnecessarily over time. Thus, when GetOptionalParam is called, you get the last value that was set.
2) Provide a method of deleting optional parameters from the data packet. (Could be as simple as passing a null or empty variant to SetOptionalParam).
What is the behaviour you get? (See above)
How is this different and is this really wrong?
It is a problem for simple local databases that use TClientdataset. I was hoping to store global database info in the datapacket so it would be saved automatically when the dataset was streamed. With its current behavior, the packet size and thus the file size will continue to increase whenever optional parameters are stored using SetOptionalParam. Also, OptionalParam values cannot be removed from the packet as far as I can see. Also these values cannot be written with new data since these cannot be retrieved.
What's the cause (if known)?
Seems to be a problem in dbclient.dll. I did not find the source code that does the work for TClientDataSet.SetOptionalParam.

When using TQuery to query a Paradox table (in a peer-to-peer networking situation with a Win95 system), I get an "access violation" message from the NT 4.0 operating system.

Description

Reported by Justo Mayora

Delphi version: 3.00
Developer OS version: Windows 95 4.00.950a with service pack 1.
User OS version: NT 4.0 Service pack 3
BDE version: 5.0

I am enclosing a test program. I developed and compiled this program in a computer with Windows 95. I run this program in Windows 95 and it works fine. Run it in NT 4.0 and it sends you the "access violation" message.

I am developing a program for multiuser access on a Windows network. For testing purposes I am using two computers: one with Windows 95 and the other with Windows NT 4.0. I followed the instruction about configuring the BDE for peer-to-peer networking.

I set the netdir directory to a directory on the NT workstation and set the Local Share to true on both computers.

Using the BDE admin program, configure an alias "Test". Set the path parameter to c:\temp. Store the table "Vendor" and its index file in this directory.
Put the TestProject.exe file wherever you want.

I don't know if this is a Delphi's bug or mine. Please review.
Thanks,
Justo Mayora
Republic of Panama

TSession.NetFileDir is limited to a length of 31 characters which is rather short.

Description

Reported by Gregg R. Zegarelli; checked by Dennis Waterman

This is an interesting one, going from D3 to D4. In my code I set a string of about 50 chars long representing a directory. Then use this var as NetFileDir. Then when connect to a database it chops the NetFileDir to about 30 chars. Thus, the net file dir is trying to save in a non-existent directory, thereby giving an error on load. This worked fine in V3. This kind of issue blows the entire operability in the project (and the upgrade).
Can someone confirm the bug?

Dennis Waterman followed up to this:
I've searched in the source code and in the function TSession.SetNetFileDir (unit dbtables) the "NetFileDir" is set using a temporary buffer. In Delphi 4 this buffer is of type DBINAME = array[0..31] of char. In Delphi 2 however this buffer is an array[0..255] of char. So that is why the NetFileDir is chopped after 31 characters. I've send a bugreport about it to Borland

Error calling an Oracle stored function which returns boolean using TStoredProc

Description

Reported by Paul Scott

Note: this bug has not been checked by the Debug Team. If anyone can confirm (or deny) the existence of this bug, please let us know!
Situation:
Create an Oracle stored function or packaged stored function using which returns a boolean. As boolean is not a Ansi SQL reserved type it cannot be called from a query. But a TStoredProc should be able call this function because it can return a param called "RESULT" which can be of type boolean.

If any attempt is made to execute or prepare this or any function which returns boolean, then Delphi causes an Oracle exception to be raised something alone the lines of "This is not a procedure or is invalid", yet other stored functions without boolean return types can be called using TStoredProc.

Validity Tests:

1. The same stored function can be called from Oracle SQL+, Oracle SQL Worksheet OK, so Oracle knows the syntax of function is valid and can call this function properly.
2. Setting TStoredProc in Delphi to TRUE at design time also raises same exception.
3. Calling other Stored functions which don't return booleans work OK in Delphi.

Possible causes:
I suspect the Delphi/BDE is using a query/query cursor to call the stored proc which is why boolean is invalid, because boolean is not a Ansi SQL reserved type allowed in query cursors. This suggests that the Delphi/BDE implementation of TStoredProc/stored procedures is incorrect as it should be independent of queries.

Solution / workaround

Make the Oracle function return type char 'Y' / 'N' and then convert to boolean. This workaround works but is a hack as it should be unecessary if TStoredProc was implemented correctly

Rounding to two decimals in an Oracle (7) database goes wrong.

Description

Reported by Fredrik Larsson; confirmed by Glen Thomson

Fredrik Larsson wrote:
I have a table where one column is of type number and the length is 5+2 (2 decimals). Then I have an application that connects to this table. No problem there. BUT here is something interesting when entering values

Entering	Results	Should result in
0,99	0,99	0,99
0,999	0,1	1
0,9999	0,1	1
0,99999	0,1	1
0,999999	1	1

This can be reproduced everywhere... I have tried DBGrids, DBEdit, Database Explorer etc.

Configuration:
Delphi 3.02; BDE 5 (with SQL-links); Oracle 7.3
BDE-Configuration:
DLL32 --> SQLORA32.DLL
VENDOR INIT --> ORA73.DLL

Glen Thompson wrote (in DDJ-thread):
A colleague reported the following confidence-shattering bug in the BDE updating a table in Oracle 7.
A table in Oracle has a column defined as NUMBER(8,2).
Update this column with a value of 0.090 and the value changes to 0.09. (all OK so far)
Update this column with a value of 0.095 and the value changes to 0.01.
Update this column with a value of 0.099 and the value changes to 0.01.
YES!!! The value going into Oracle is 0.01, NOT 0.10 as expected.

The fault seems to be with the BDE, as the UPDATE command in Oracle SQL*PLUS gives the correct results.
It seems that 3 or 4 decimal place examples all work OK.

Solution / workaround

By Michael Starke:
I had similar problems with the 0.999 values (Delphi 1, 2, 3 and various BDE versions) and connected with Inprise:
We were able to solve the problem in 32bit by changing the BDE setup:
ENABLE FLOAT = TRUE
ENABLE INTEGER = TRUE
Both were FALSE to be backward compatible to 16bit.

(old workaround:)
Workaround is to use the Format function in Delphi to round to the correct number prior to hitting using DB post !!

If you have a packaged or stand-alone procedure with more than 10 parameters, and you use the TStoredProc params editor, Delphi will crash (on Windows 95) or disappear (on NT). This concerns Oracle 8.04 databases.

Description

Reported by Tom Scott

To reproduce:

1. Create a procedure with 11 parameters.
2. Compile in Oracle.
3. On a form, add a TDatabase and a TStoredProc component.
4. Set the params on the TDatabase component so that you can set Connected = true.
5. Link the TStoredProc component to the TDatabase.
6. Put in your procedure name in the StoredProcName property.
7. Double-click on the Params property.
8. Crash!

Solution / workaround

Query the USER_ARGUMENTS view in Oracle for your package and/or procedure, and create the parameters dynamically. Or write a component, as I did, that replaces the TStoredProc component and does this for you. If you use a lot of packages, you may want to do this anyway, since TStoredProc doesn't understand cursor parameters automatically, nor does it translate NUMBER parameters with no scale into integers.

Secondary index becomes corrupt after a Key Violation error.

Description

Reported by Rune Moberg; checked by Reinier Sterkenburg

I first encountered this with Delphi 1 and BDE ver 2.5.2 (the latest one that added Pdox7 support).
If you have a table with one primary index and in addition one secondary index (I had it on a 20 character long Alphanumeric field) which is defined as being unique and maintained (in DBD) you might run into "Index out of date" problems if the secondary index isn't the one that's active when posting a record.
To reproduce the bug:

1. using a TTable, select the primary index as the active index
2. put enough components on a form (a couple of dbedits and a dbnavigator will do fine)
3. add a record that violates the restraint that the secondary index normally dictates (i.e. a value that already exist in the table; key violation exception raised)
4. if you go back to design mode and select the secondary index, the exception "index out of date" will be raised
5. if you retry step 1-3 again, no Key Violation exception will be raised (because the secondary index is now utterly corrupt and ignored until you specifically request it)

The bug has also been duplicated with Delphi 3.01, 3.02 and 4.0 (and the BDE shipping with 3.01 and the BDE update 4.51).

A quick testrun with DBD32 indicates that DBD32 doesn't corrupt the index (but did throw a key violation which is only proper) so Borland have at some point had the necessary knowledge on how to deal with unique secondary indeces... :-)

Solution / workaround

Not a real solution: Stay away from unique secondary indices! (? what about primary indices which aren't autocounter?!?)