Diese Seite mit anderen teilen ...

Informationen zum Thema:
WinDev Forum
Beiträge im Thema:
Erster Beitrag:
vor 7 Monaten
Letzter Beitrag:
vor 1 Monat
Beteiligte Autoren:
Steven Sitas, GuenterP, Fabrice Harari, Ericus, Art Bonds, Al, kingdr8, Allard

How Bomb Proof is HFSQL C/S ?

Startbeitrag von Steven Sitas am 18.01.2018 11:06

The last couple of years we have moved many of our users from applications developed with:
Clarion (6x) and Btrieve to WinDev (20 or 21) apps and HFSQL C/S.
Here is what we have found:
1. Performance and Program versatility -> Winner is HFSQL C/S
2. "Bomb Proof" -> Winner is Btrieve (C/S or Workgroup)

Under certain conditions, various HFSQL C/S files can GET corrupted.
We have found that the files that are usually corrupted are the files inside the BDD\__TRS directory of the C/S Engine.
These are the files supporting Transactions and when they get corrupted everything is a mess.

When does this happen?
Usually when the server has a power failure or something like that ...
Of course, we understand that a power failure is not something that should NORMALLY happen in an installation, but it will happen at some time - especially in small installations.

What helps?
Turning off the Hard disk cache - but NOT always.

Anything out there, as bomb-proof as Btrieve, that plays well with WINDEV?

Steven Sitas


Hi Steven, in the past, for at least 20 years Btrieve has been my *only* database system. Still, about hundred of our customers are still using Btrieve (16 bit - "DOS" applications). I wish, we could find a FREE 32+64 bit .dll which does Btrieve 5.5, 6.0 or 7.0 access. Of course, I know, there's Pervasive. But they don't provide anything for free.

Btrieve is more or less bullet proof, however, rarely a Btrieve file survives a power fail while within a write operation. Transactions work just fine, there's no locking in in an unfinished transaction.

Next. Btrieve can work with variable length files. So, you can pack a full invoice with header and body data and whatever else you wish into a single record. Yes, that hurts the "relational" principle, but SPEED is unbeatable then. And of course, Btrieve does logging, you can directly rebuild a full, but lost database from the log files. There's so much more ..

Still weeping ... sneef!

von GuenterP - am 18.01.2018 12:45
Hi Guenter,
I can throw a hard disk with Btrieve/Pervasive files in the ocean, I can hit it with a hammer, I can do thousands of power failures and the data is still there AND recoverable !!!

Never lost any Btrieve file because of ANY type of corruption ...
That's my experience with 32 bit Brieve - I have NO experience with the old 16 bit Dos version.

Steven Sitas

von Steven Sitas - am 18.01.2018 13:00
Btrieve claimed to be fastest flat-file db in the world under Novell 4.11 from my memory but it was a painful experience to write in C at that time and it's same as CTREE.

Does it exist with Novell network any more, any idea?



von kingdr8 - am 18.01.2018 15:01


Hi friends, and now ... ta-taa!

Btrieve 6.15 is back, made compatible with Windows 10 and has been named Btrieve 12 now


von GuenterP - am 18.01.2018 15:31


Hi friends,

I spoke to the local distributor of Actian products right now. Asked them to give me the price of a limited distribution license for Btrieve 12, they just didn't know but will call me back as soon as they know.

What they told me. Actian successfully hid the availability of their new product over the last year and seemingly are going to discontinue it now because of the low demand!

Next, I'll try to integrate WINDEV and Btrieve 12 within an application and will have a look into synchronization of the WD-analysis and the database.

von GuenterP - am 18.01.2018 16:07


Hi Guenter,
If you have any ideas on how to integrate Btrieve/Pervasive (without ODBC) I would be interested ..

Now about the price, here is some info from the press release:
- New Licensing - Btrieve 12 is licensed with a Limited Distribution License (LDL). A
Btrieve 12 LDL allows the holder to create and deploy licenses for 1,000 users.
- Introductory Pricing - The introductory price for a Btrieve 12 Limited Distribution
License from Actian is $2,995.

Steven Sitas

von Steven Sitas - am 18.01.2018 16:46



von Art Bonds - am 18.01.2018 16:52


Hi Steven,

this price (US$ 3.000,- ) is ridiculously high, sorry, no way to further talk about the thing if the price is that high. Are they on a suicide mission? Mission "Kamikaze"? The feat is: re-develop a product, put an unbelievable price tag on it and be angry ... OK, it's only US$ 3,- per seat and per life, that's a reasonable price, but they should scale it down and sell 100 seats at a time - not 1000.

I just downloaded Btrieve 12 and the SDK. Afaik, after unzipping there should be some DLLs to interface with the application, I'll have a look. And I'll wait until tomorrow to hear the price from the local distributor.

von GuenterP - am 18.01.2018 17:03
To carry on the bullet proofing of HFSQL...

And of course, Btrieve does logging, you can directly rebuild a full, but lost database from the log files.

Can we do this with HFSQL? Log all transactions via trigger. If a corruption occurs, nuke from above, restore by running the logs.

Or use something like Fabrice's replication. All 'clients' store data locally and replicate to server. If the server corrupts, you nuke from above, restore the latest backup, then have the 'clients' replicate transactions up to the server, where the server picks and chooses which transactions it wants to restore based on if the records exist or not. Or as an alternative, if we can store the original replications from the clients in a separate subdirectory that is safely away from the server, restore from backup and then access that subdirectory and restore from the original replications.


von Art Bonds - am 18.01.2018 17:09
Hi Art,
the files, that we have seen corrupted, are ALWAYS files used by the Client Server Engine to control transactions. They are *.TRX or *.TRS files AND never the actual *.FIC files (or Indexes).
They seem to have the same structure as the FIC files - but I am not sure ...

I am wondering if PCSoft is trying to speed things up with transactions and is doing something special with these files. Maybe using some kind of INTERNAL cache?

Steven Sitas

von Steven Sitas - am 18.01.2018 17:35
Hi Art,

replication between two remote servers would do the trick in HFSQL, elegantly too. However, for big datasets and heavy loads, I'd not recommend that way to go. Performance.

Logging to local machines is no good idea, it's an enormous work when rebuilding the database. Which records have been added, which ones were modified and which ones were deleted - at which time? There's no way to rebuild a database in no time from a bunch of local log files.

Only way: the database server has to do the logging, no one else! That's the way it's done by Btrieve. When recovering a damaged Btrieve file, the recovery program (BUTIL) produces a log file (same format but without the Save/Modify/Delete info). After that one creates the datafile and feeds the log file into it, using BUTIL.exe again. One can put the whole procedure into a batch file REBUILD.BAT and if a file error appears, customers are notified and run the command REBUILD %Filename and are up again in no time.

The problem with HFSQL re-indexing is that the data files essentially stay the same. I have yet to write a routine that does it like BUTIL does. Extract all data first and insert it into empty files.

von GuenterP - am 18.01.2018 17:41


Hi Art, thanks a lot, but I hope / suspect that the Austrian distributorship wakes Actian up from their trip and initiates a new pricing scheme. I'm ready to pay 300,- for 100 seats but not 3000,- for 1000 seats. Maybe, if I need another 100 seats, no problem.

von GuenterP - am 18.01.2018 17:45
Hello All

If it is just the transactions that are the problem then wouldn't it be easier to stop using transactions or code your own transaction process ?

I have a large application that has the full range of accounting procedures and I have never needed transactions as I designed the databases specifically NOT to need them. In my case, each record has all the information it needs to be stand alone and I don't maintain any running totals. This approach obviously wouldn't scale to handle a large enterprise but we do have large companies running the software with no problems.
A "transaction" for me is just a single record.
.e.g. a creditor invoice record has sufficient foreign keys to allow it to link to the creditor, job, general ledger, asset etc.
A debtor invoice has a header record with foreign keys for its various connections and the invoice line records are in a separate linked file.
If the system crashes, the user just enters the missing data. If I want to display a creditor balance, I add up ALL the relevant transactions. Yes, this means that the display of data is not instant as it would be if I maintained running totals, but they are always correct.


von Al - am 18.01.2018 18:58
Hi Al,
How do you handle Double Entry Accounting without transactions?

Steven Sitas

von Steven Sitas - am 18.01.2018 19:15
Hello Steven

The record contains links to both the credit and debit GL accounts. This makes the system very robust as to change the value I only change a single record - I don't have to find the second one. My GL always balances :)
The downside is that I have to jump through some hoops to extract the information for reports and displays usng aliases and views.


von Al - am 18.01.2018 19:22
Hi Al,
what happens if a GL entry has more than 1 Debit or Credit entries, like the following example: (?)

Sales example
[debit] Accounts receivable $1,050
[debit] Cost of goods sold for $650
[credit] Revenue for $1,000
[credit] Inventory for $650
[credit] Sales tax liability for $50

Steven Sitas

von Steven Sitas - am 19.01.2018 09:55

actually, it's even simpler than that, as EACH local DB contains the WXReplication file. And that file is a full log of all the writing in the DB... So you can just use it to recreate the DB at any point (except of course if you are NOT doing a FULL replication, of course)

And to answer to Guenter speed concerns, my code is able to process this file and write several hundreds of records per seconds back in the DB, on a regular hard drive (you will find some benchmarks in my replication articles on my site).

And of course, nothing ever prevents you to have a SECOND main server setup as a full client (ie a full backup of the BD), that you can switch for the main one if there is a problem.

Best regards

von Fabrice Harari - am 19.01.2018 10:35
Hi Steven

Sales example
[debit] Accounts receivable $1,050<
[debit] Cost of goods sold for $650
[credit] Revenue for $1,000
[credit] Inventory for $650
[credit] Sales tax liability for $50

von Fabrice Harari - am 19.01.2018 10:44

I always thought that transactions are to be used when more records are red -- and changed in one procedure.

For if it stops working in the procedure the procedure can have done 60 % and thus 60 % is changed and 40 % hasnot yet changed.

I donnot use trtansactions yet but I think I would need to .


von Allard - am 19.01.2018 11:43
Hi Fabrice,
I see how that would work if you do not have any tables with summarized data.
But, in some installations and industries, the quantity of the data and the need to have up to date information makes the use of sum tables a necessity.

In the next version of the alpha360 project, we have eliminated the use of sum tables - we now have just ONE for the Inventory transactions and ONE for the GL in the Central (Main) Database and NONE for the users that may be offline at some time.

And although I understand your concerns about "relying on an external process", you just can't avoid the RDBMS and you are always 100% dependent on the quality of your database - either it is HFSQL or SQL Server etc ...

Steven Sitas

von Steven Sitas - am 19.01.2018 13:20


This discussion started as a Btrieve discussion and then turned to a double entry accounting discussion.

However if somebody does know Btrieve/Pervasive how do I reference it from Windev 21.

The sample code from Btrieve to start a transaction is:

BTI_SINT status = 0;

status = PvStart(0);
// invoke multiple DTI calls
status = PvStop (0);

But Windev does not know anything about PvStart or PvStop.

The libraries that must be loaded first differ for each version of Pervasive but let's say I want to load

w3dbav90.lib2 which is for P.SQL v9

Thanks in advance

Ericus Steyn

von Ericus - am 18.07.2018 14:06



Btrieve is the core engine of PervasiveSQL. I do know Btrieve only up to v 6.15 but nothing more ...

Btrieve 5.1, 6.15 was running under DOS as well. I do own a 16-bit version 6.15 for Windows with a programmer's manual. 6.15 was meant for Windows 3.11.

Your start / stop commands seem to refer to the Btrieve Requester which has to be installed on the client machines of a Netware-Btrieve.

Your server environment seems to be Netware!? http://www.pervasive.com/database/Home/Products/PSQLv9.aspx

As the pervasive web site says, there is no support for a Windows workstation PSQL, support for the Netware thing ended 2014. I doubt that the requesters are still running under Windows 10. Is that so?

von GuenterP - am 18.07.2018 16:04
Hi Guenter

No this is a windows application and I have Pervasive. I want to automatically create the Pervasive database names. I managed to get his far.

BTISession is int
sResult is string
hInst is int
hInst = LoadDLL("w3dbav90.dll")
IF hInst = 0 THEN
BTISession = API("w3dbav90.dll", "PvStart",0)
//The call above returns 0 whereby I assume P.SQL started
//The problem is the line below to connect to the server - it fails with runtime error. No P.SQL Status code
//nConnection = API("w3dbav90.dll","PvConnectServer","MSIMONSTER", "", "", hInst)
sResult = API("w3dbav90.dll","PvCreateDatabase",hInst,"PLUM", "D:\\Pastel18\\_DEMO", "D:\\Pastel18\\CUBBS", "")

It is passing the last parameters in this line:

nConnection = API("w3dbav90.dll","PvConnectServer","MSIMONSTER", "", "", hInst)

the P.SQL documentation says
the Argument is In/Out

and it should be an Address of a long integer that receives the connection handle if connection is successful

Thanks for your help.

Ericus Steyn

von Ericus - am 18.07.2018 20:00
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.