Elliott Business Software relies on PSQL database engines. PSQL consists of two type of engines: transactional and relational. The transactional engine used to be called "Btrieve," which is a record manager. The relational engine is added on top of the transactional engine to perform database access by using SQL statements. This type of arrangement has its unique benefits because a transactional engine is very fast when you add, change or delete a record. On the other hand, the relational engine is better suited if you need to access a bunch of records in single batch. Therefore, reporting typically will run faster if using a relational engine.
As of the writing, Elliott Business Software relies only on the transactional engine. However, we also support third-party applications to access Elliott's database through the relational engine. This may include, but is not limited to, the following:
- Crystal Reports
- Excel Query
- Microsoft Access
- Starship or Other Third Party Manifest Software
Create a New Database
To access the relational engine, you first need to create a PSQL database. This can be done through PCC (Pervasive Control Center). Even though you can do so from the workstation side, we recommend that you perform this directly on the server where the PSQL engine is installed. Choose Start -> either Pervasive PSQL 11, or Actian PSQL 12 -> PSQL Control Center & Documentations. In the PCC window, expand the server node under "Engines." Then expand the "Databases" node. You should see the following three databases if you haven't done any customization yet:
The following window will pop up and ask for additional information about the new database:
The key information you need to pay attention to is:
- Database Name
- Create 32-bit Engine DSN
Database NameElliott adopts the following database name conventions, so please do so as well:
- For V7.x Database, use ELLIOTTDATA for the company 1. For the 2nd company and onward, use ELLIOTTDATA02...etc.
- For V8.0 - 8.2 Database, use ELIDATA for the company 1. For the 2nd company and onward, use ELIDATA02...etc.
- For V8.3 and after, use ELI83DATA for the company 1, For the 2nd company and onward, use ELI83DATA02...etc. For the root directory data, use ELIROOT.
In the Location field, enter the place where DDF resides. DDF stands for "Data Definition File," which is the database schema of the Elliott database files. Depending on your Elliott version, the DDF can be in different places:
- For V7.x, the DDF is located at <ElliottRoot>\DDF40
- For V8.0 - 8.2, the DDF is located at <ElliottRoot>\Bin\DDF40
- For V8.3 and after, the DDF is located at <ElliottRoot>Bin\DDF83. For root directory data, the corresponding DDF is located at <ElliottRoot>
Create 32-bit Engine DSN
If you are still using a 32-bit application to access the PSQL relational engine, then you should checck the "Create 32-bit Engine DSN" box. It is recommended you do so.
Once you click the "Finish" button, you will notice the new database (i.e., ELIDATA in the case) is created under the Database node.
Modify the Database Path
By default, when a new database is created, PCC assumes the DDF files and data files are stored in the same location. That is not the case for Elliott Business Software. Elliott data files are stored in <ElliottRoot>\DATA, DATA_02, DATA_03...etc. depending on which company. Therefore, we need to modify the database we just created to indicate a different data path. To do so, right click on the database we just created (i.e., ELIDATA), and choose "Properties." In the pop-up "Properties" window, choose "directories." Remove the data directory that points to the DDF folder. See sample screen below:
Then click on "New" to add the directory that corresponds to the data folder. Note we use the local path insteand of the nework path. See sample screen below:
Then click "OK." Now your new PSQL database is ready for access through the relational engine. PSQL relational engine can be accessed by third party through ODBC (including ODBC.NET) or ADO.NET. From our testing, ADO.NET is faster than ODBC (or ODBC.NET.) Any third-party application that supports these two interfaces will be able to access the database just created.