Create a New Database in the PCC for Relational Engine Access

Release Date: 12/15/2017
Modifed Date: 06/30/2020

Elliott Business Software relies on PSQL database engines.  PSQL consists of two types 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 allow 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 this 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
We also provide various web solutions like eStore, eOrder, Payment Portals and Web Services, which require the relational engine access.  

Create a New Database
To access the relational engine, you first need to create a PSQL database.  This can be done through the 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:
To add a new DATABASE, right click on the "Databases" node and choose "New" -> "Database."  See sample screen below:

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:
  1. Database Name: Please look at Elliott Database Naming Convention.
  2. Location: Please use the server local path.  Do not use the mapped drive path.
  3. Create  dictionary file (if they do not exist) - By default this is checked, so please uncheck it.
  4. Relational integrity enforced - By default this is checked, so please uncheck it.
Database Name
Elliott adopts the following database naming conventions, so please do so as well:
  • For V7.x Database, use ELLIOTTDATA for company 1. For the 2nd company and onward, use ELLIOTTDATA02...etc.
  • For V8.0 - 8.2 Database, use ELIDATA for company 1. For the 2nd company and onward, use ELIDATA02...etc.
  • For V8.5, use ELI85DATA for company 1, For the 2nd company and onward, use ELI85DATA02...etc.  
  • For V8.6, use ELI86DATA for company 1, For the 2nd company and onward, use ELI86DATA02...etc. For the root directory data, use ELI86ROOT.  
Please refer to the following KB article on Elliott database naming conventions:
In the Location field, enter the place where the 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 in <ElliottRoot>\DDF40
  • For V8.0 - 8.2, the DDF is located in <ElliottRoot>\Bin\DDF40
  • For V8.5 and after, the DDF is located in <ElliottRoot>Bin85\DDF83.  For root directory data, the corresponding DDF is located in <ElliottRoot>\Bin85\DDFROOT
In the above example, we were creating an Elliott 8.2 database ELIDATA. We entered D:\ACCT\Elliott7\Bin\DDF40. Note that we used the local drive D: instead of the mapped drive. Do not use the mapped drive path.  Enter the local drive path for the PSQL server where the DDF can be located.

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 instead of the network path. See sample screen below:

Then click "OK."  Now your new PSQL database is ready for access through the relational engine.  The PSQL relational engine can be accessed by a third party through ODBC (including ODBC.NET) or ADO.NET.  Based on 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.


Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master
  32. PSQL 13, Micrsoft SQL Integration Service & Pervasive PSQL OLE DB Provider
  33. Your Firewall Needs to Allow Outbound Traffic to the Netherlands for PSQL Licensing Server Purposes
  34. A Case of Btrieve Error 046 on ARCRCLOG A/R Credit Card Log File
  35. A Support Case of Migrating to Different Version of DDF
  36. How to Clear the Message "Unable to Read your Users record (9/068)"
  37. Setup of the PSQL 13 Report Engine
  38. How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
  39. How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019
  40. Elliott Database Naming Convention
  41. What Does Btrieve Error 080 Mean?

Feedback and Knowledge Base