Pervasive Data in AwareIM

Contains tips for configurators working with Aware IM
Post Reply
customaware
Posts: 2392
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Pervasive Data in AwareIM

Post by customaware »

AwareIM is awesome out of the box for those wanting to use, for example, Derby, MySQL or MS-SQL.

But what about other popular databases?

I know there are a few people in the AwareIm community who have come across from the Magic/UniPaas community ( I am one of them) which has been around for 30 years now and the default database for that environment is Pervasive (Btrieve). For those who do not know, Btrieve has been around for a long long time and was behind many excellent accounting packages due to it's strength as a transactional database. The product has evolved over the years into Pervasive which now can access data both transactionally and relationally at the same time providing the best of both real world requirements. Transactional strength for data entry/processing and Relational strength for large batch/reporting tasks.

Regardless of the tool you use, having the ability to tap into and use data contained in your Clients legacy data can be critical to the success or otherwise of the take up of your Aware development. Many Clients simply are not prepared to give up on all that legacy data but if you can provide additional capability with Aware while also being able to tap into all that historical IP..... Well..... Happy Days!

So, if you ever have a Client that wants you to use legacy data in Pervasive then this is for you.

Firstly, from a pure data perspective, the only difference between the Transactional side (Btrieve) of Pervasive and the Relational side of the product is the existence of the appropriate set of DDF files. These are special files that describe the database file structure. If you don't have them then you are stuck in the Transactional side. But if you do have them (and there are a number of tools out there that can create them for you) then you are able to get to the data with AwareIM.

Prerequisites:

Make sure you have the DDF files
Know where the physical files are located
The name of the Pervasive database that you want to access
The name of the data file in that database that you want to access

Then...

Step 1:
Find the following files in the C:\Program Files (x86)\Pervasive Software\PSQL\bin folder of your Pervasive installation.

jpscs.jar
pvjdbc2.jar
pvjdbc2x.jar

and copy them into your C:\AwareIM\CustomJars folder. If you do not have a CustomJars folder, then create it.

Step 2:

In AwareIM, create a New BO with the Persistence set to Database: existing external.
Click the Settings button immediately to the right to open the dialog.

Click Add at the Top Right to configure a new Database.

Name the Database whatever you want.... Let's say Pervasive

You are presented with two option in terms of connection....

a) Use JDBC driver to connect

or

b) Use data source.

We are going to choose the first, using JDBC

In the Driver Class Name, enter : com.pervasive.jdbc.v2.Driver

In the Connection URL enter: jdbc:pervasive://ipaddress:1583/YOURDATABASENAME

if you are doing this on a local machine then use localhost...

jdbc:pervasive://localhost:1583/YOURDATABASENAME

Now at the bottom of the dialog, enter yyyy-MM-dd for both the Default Date and TimeStamp format.
You may need to change TimeStamp format if you are using TimeStamps but I don't and yyyy-MM-dd works.

Step 3:
Close that Dialog and return to the Database: existing external dialog.

Enter the name of the datafile you wish to access in the Table or View space.

Now, if all is correct, then you can click Discover Attributes and all of the Pervasive field names will appear in the Attributes
table below the FileName.

Step 4:
You MUST select at least one of the attributes as Primary Key.

Close the dialog.

All done.

You can now use the file in any query as you wish.

Things to keep in mind.

1. Be careful how you use the file. If you make changes in Aware to the structure of the data then you may well mess up your legacy applications ability to access the file correctly.
2. Aware does not manage references and the like for this table in the same way as it does for a native Aware BO. I would recommend that if you want to change anything then copy all the data to a native Aware BO and treat the Pervasive file as Read Only. This does not mean you cannot update / modify that actual data in the file. Of course you can. But changing any of the structure, I suspect, will get you into a big pile of poo.

Enjoy.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Post by ACDC »

excellent, thanks for this information, I have an upcoming project to connect to an Accounting system using Pervasive , this is going to come in very handy and save me a lot of time - thanks again you are a star
Post Reply