PC SOFT

FOROS PROFESIONALES
WINDEVWEBDEV y WINDEV Mobile

Inicio â†’ WINDEV 2024 â†’ How Bomb Proof is HFSQL C/S ?
How Bomb Proof is HFSQL C/S ?
Iniciado por Steven Sitas, 18,ene. 2018 12:06 - 18 respuestas
Publicado el 18,enero 2018 - 12:06
Hi,
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.

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

Regards
Steven Sitas
Publicado el 18,enero 2018 - 13:45
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!
Publicado el 18,enero 2018 - 14:00
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.

Regards
Steven Sitas
Publicado el 18,enero 2018 - 16:01
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?

Regs

King
Publicado el 18,enero 2018 - 16:31
Hi friends, and now ... ta-taa!

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

http://www.pervasive.com/database/Home/Products/Btrieve12.aspx
https://www.actian.com/wp-content/uploads/2017/04/btrieve12_getting_started.pdf
Publicado el 18,enero 2018 - 17:07
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.
Publicado el 18,enero 2018 - 17:46
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.
---------------------------------------------------------------

Regards
Steven Sitas
Publicado el 18,enero 2018 - 18:03
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.
Publicado el 18,enero 2018 - 18:09
To carry on the bullet proofing of HFSQL...

Quote
GuenterP

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.

Thoughts?
Publicado el 18,enero 2018 - 18:35
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?

Regards
Steven Sitas
Publicado el 18,enero 2018 - 18:41
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.
Publicado el 18,enero 2018 - 19:58
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.

Regards
Al
Publicado el 18,enero 2018 - 20:15
Hi Al,
How do you handle Double Entry Accounting without transactions?

Regards
Steven Sitas
Publicado el 18,enero 2018 - 20:22
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.

Regards
Al
Publicado el 19,enero 2018 - 10:55
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

Regards
Steven Sitas
Publicado el 19,enero 2018 - 11:35
Hi

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
Publicado el 19,enero 2018 - 11:44
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
<<

What I like to do in a case like that (critical process) is to start by storing ALL of it in ONE record (in a memo text, ini like format. So in your case, I would have a record with:
ID used in the other filed
Memo containing
D1ACCOUNT=Accounts receivable
D1AMOUNT=$1,050
D2ACCOUNT=Cost of goods sold
D2AMOUNT= $650
C1ACCOUNT=Revenue
...

THEN, I would call the process that reads that record and create all the regular/transactional records...
THEN, I would delete the first record

And that means that when I restart the program, if there are records in that file, I just have to call my regular process (the one just above) to save the regular records. Because the complete/original records contains the ID to use for the other files, I can easily add the missing records, and not create duplicate..

I KNOW, it is my own writing of the transaction process BUT, the reasons why:
- I do not rely on an external process to maintain the integrity of the DB
- I can debug the whole thing is there is a problem
- I can use the same system for other tasks (replication of whole transaction in ONE record, sandbox, etc)...
- and of course, my system is completely independent of the type of DB used

Best regards
Publicado el 19,enero 2018 - 12:43
Hm

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 .

regards
Allard