How to settle HFSQL connection with ODBC via PDO for Php

gaël digard — Mon, 02 Jan 2023 09:29:44 +0000

I've been struggling for weeks before being able to connect and request an HyperFileSQL (HFSQL) database in PHP, using ODBC via PDO.

Here's a complete guide tested with PHP 8.0.15, Apache 2.4.23 and ODBC Drivers 26.0101d, on Windows 10 Entreprise 64 bits (10.0, build 19044).

HyperFileSQL

As a reminder, HFSQL is a database engine from the late 80's, developped by a french company named PC SOFT. This company is also responsible for WinDev, WebDev, and many others monstruosities. All that mess is natively Windows compatible, not working really well on Linux.

Source : https://fr.wikipedia.org/wiki/PC_SOFT

You'll notice there's no Wiki article about HFSQL, demonstrating its popularity. You barely can find useful information on stackOverflow either, or just some crumbs, that you have to gather, tries after tries.

Probable stack reminder

I think it works like that.

ODBC driver on the HFSQL server

You'll need to install ODBC drivers on the machine where your DB server is. I actually didn't work on this part, it was made by the owner of the DB. But I'm pretty sure he did what's explained in this part : HFSQl Drivers on the server side.

I've also found a vague note on the official website, here.

It doesn't matter if drivers aren't installed on your server, because we will explore a local solution for development. You can continue this tutorial anyway.

Now we will only focus on the app side, where your php is runing, on Windows.

Php on the app side

You need Php installed, and this extension turned on :

pdo_odbc

Locate you php.ini file :

;extension=pdo_odbc

Remove the semicolon before, save file and restart Apache.

Because we are on windows, you might be using WAMP stack, so, your file might be there :

C:\wamp64\bin\php\php8.0.15\php.ini

This extension will allow you to communicate with your DB via ODB, and request Db via PDO. But that's not all...

HFSQL drivers

You also need some drivers, to tell ODBC how to communicate with this nudnik which is HFSQL. They are available on PC SOFT website.

Source : https://pcsoft.fr/st/telec/modules-communs-26/wx26_101d.htm

Scroll down and select ODBC26PACK101d.exe to download HFSQL drivers for ODBC for Windows.

They're as well available for Linux, but, after 2 days of try hard, with 2 different persons, on 2 different linux systems (both Debian based and up-to-date), we were only facing "Segmentation fault" when trying to connect to any HFSQL DB from shell (not even from php). That's why this article will deal only with Windows platform.

Once downloaded, just double click to launch the installer, and "next" each step, reading or not what is written.

Local HFSQL database

HFSQL server

To test your installation, you can create a local DB by installing an HFSQL Server. This will help us to skip all the potential problems of remote authenticating / network / firewall.

If you're sure about your connectivity and your credential, skip this part and just go to PDO via ODBC

You can download HFSQL server application on the same webpage than the drivers :

Source : https://pcsoft.fr/st/telec/modules-communs-26/wx26_101d.htm

Scroll down and select HFSQL26INSTALL101.exe to download it.

While you'll install it, you'll be asked to create a new DB on the fly. You'll have to chose a directory where your data will be saved, anywhere will fit.
Check the server name and remove any blank space.
Check the port : 4900 by default.

If it's your first HFSQL DB server on this computer, default name and port will be ok, if it's not, you'll have to change your port and server name to something else (eg. ComputerName_2 & 4901)

Default username is "Admin" and there's no default password. You can set one if needed. Very well. Next.

Skip the SMTP part.
Skip the Active Directory part.
Accept or not to participate to survey.
Then the magic will happen.

HFSQL client

While you installed the server, PC SOFT installed a client as well. It's called "Centre de contrôle", or "Control Center" in very good english. It's your PhpMyAdmin or MySQL Workbench-like software.

It's located in : C:\Program Files\PC SOFT\CC HFSQL

Double-click CC260HF64.exe file
Check-in "New connection" and fill it up with 127.0.0.1 or localhostfor the server address or name, the port, user and password, as set in the previous step, when you were installing the server.

You should connect locally to your server. If not, don't go further and check everything back.
It should look like this :

You can make a new DB by clicking "Nouvelle base de données" near the button to shutdown the server (Arrêter le serveur). Enter the name of your DB and validate.

It should look like that if you logged in your DB.

I haven't been further into HFSQL by creating tables and inserting data here. I've just copy-past some files I got from the production server here :

C:\PC SOFT\Serveur HFSQL OrdinateurDeGael_2\BDD\devto-demo

Yep. That's all. .FIC and .NDX files dumped here and you got your DB. You can as well have directories into this directory, we will talk about this agony later.

Now you have proven your ability to connect to your local HFSQL server with a dedicated software, you can try to do the same it with Php, via ODBC/PDO.

PDO via ODBC

To connect to a HFSQL server you need a DSN, also known as Data Source Name. According to your version of the ODBC drivers, this long string can change. Reminder : we use version 26 of driver. And you should too.

Into your php test file, type down :

$hf_dsn = sprintf("odbc:DRIVER={HFSQL};Server Name=%s;Server Port=%s;Database=%s;UID=%s;PWD=%s;", '127.0.0.1', '4900', 'database_name', 'Admin', '');

Please note that {HFSQL} is necessary and might not be replaced by any other value.

You can find on old forums, some of these values :

{Hyper File 7}, {HyperFileSQL} # WRONG

They're are not working since version 26 (that we are using).

For the rest : host, port, database name (that you might have created before), username (Admin is default on your local) and password (here I wrote no password).

Now we got a complete DSN, we can try to connect via PDO :

$hf_hostname = "127.0.0.1";
$hf_port = "4900";
$hf_database = "devto-demo";
$hf_user = "Admin";
$hf_password = "";

$hf_dsn = sprintf("odbc:DRIVER={HFSQL};Server Name=%s;Server Port=%s;Database=%s;UID=%s;PWD=%s;", $hf_hostname, $hf_port, $hf_database, $hf_user, $hf_password);

try{
    $hf_dbh = new PDO($hf_dsn, $hf_user,$hf_password, [ PDO::ATTR_PERSISTENT => true]);

}
catch(PDOException $ex){
    echo($ex->getMessage());

}

You will notice I've stored credential into variables, because they are passed twice : once in the DSN, and once in PDO. Dunno why. But it has to.

If everything if fine, nothing will appear. If an error occurs, you messed up somewhere. The most common mistake I got was the wrong driver name {HFSQL}, or the driver not installed (but we addressed these steps above). If it still not works, check login, password, server, port, a bit like with MySQL.

Queries, syntax and subtilities

Queries

Now it's time to query your DB.

try{
 $query = "SELECT * FROM [articles] WHERE (Author='JeanMatteo')";
 $stmt = $hf_dbh->query($query); 
 $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
 var_dump($result);

}
catch(PDOException $ex){
    echo($ex->getMessage());

}

Here's a simple request which get all the articles wrote by author named JeanMatteo : table name is between square brackets and the WHERE condition is between parentheses.
The rest of the code is common PDO code.

Diacritics

Guess what ? You can put accents, or diacritics (like é, à, ù) in your tables NAME, and, ALSO in your cols name. What a pleasure to request a french 🥖 database.

Column name

Imagine that our table articles has a column named Libellé, which means "Label" in english. Character encoding will make some mess, and will return some �. So, how to access our column ?

With array index. First of all, fetch all data in FETCH_BOTH mode :

// ...
$result = $stmt->fetchAll(PDO::FETCH_BOTH);
// ...

this will returns an array both with numerical and associative indexes :

Now you can access to your Libell� attribute like this :

 $article->{'2'}; //   $article->Libellé won't work because of the DB encoding ;

As you see, DB encoding isn't matching the one of our app. I couldn't set the DB connection encoding to utf-8, and I didn't want to change all my php app encoding to something else.

Table name + column name containing diacritics

Imagine a table named Détails. With a column named IDDétail?
How to query it ?

$query = 'SELECT * FROM ['utf8_decode('Détail).'] WHERE ('.utf8_decode('IDDétail').' = 69') ';

utf8_decode will be your friend.

Subdirectory (nesting)

As told previously, you can have directories into your DB directory. They can be used to differenciate deptartments, or companies. In HFSQL language, it's easy to access a subdirectory via backslash :

  SELECT * FROM [CompanyA\articles] WHERE (Author='JeanMatteo');

in PHP you will replace the backslash with an underscore :

 $query = "SELECT * FROM [CompanyA_articles] WHERE (Author='JeanMatteo')";

Slowness

If you access a big database, with a lot of tables and a lot of columns in each, with subdirectories and all, you will probably face delay issues.

This is because HFSQL engine sends the totality of the tables name, structure, plus, (IMO) indexes. Because of this stunning behavior, I could see transiting between my Apache and the HFSQL remote server, around 10mb/s of data for 2 or 3 minutes, before the website shows up. It's totaly useless for a web usage.

If your DB isn't that huge but connection is still slow, you can specify a parameter to your PDO connection method :

$hf_dbh = new PDO($hf_dsn, $hf_user,$hf_password, [
       PDO::ATTR_PERSISTENT => true
]);

The PDO::ATTR_PERSISTENT => true in the constructor won't close the HFSQL connection at the end of the script, as it usualy do.

Then the visitor won't have to reconnect at every page he loads in its browser. It means the first page loading will still be slow, but not the next ones. It will be almost instant.

The connection will be closed after a certain amount of time, but I couldn't find out how much, and how to set it (no documentation, no parameter).

But if your DB is way to huge, you wont have any other solution to make another 'lite' database, containing only the few tables you are using, and syncing them with the original DB sometimes. That's how we proceed in our project.

Good luck !

Forem: gaël digard