Methods for interacting with a Pervasive SQL database

Question

I'm in the process of developing a web interface to a service business management application that uses a Pervasive SQL database to store information (not my choice, by the way). At the moment, I'm using the official Pervasive SqlClient implementation with a custom set of query generation classes so I can write code like:

new SelectQuery().From("CUSTOMER").Fields("CUSTOMERNBR", "NAME");

It's working fine so far, but I'm running into little problems here and there. Rather than keep going in this direction, I'd rather use a proven DAL. However, I'm not having very much luck in finding a DAL system that can interact with a Pervasive database.

Opf3 has a Pervasive storage provider, but I've never heard of that framework before and the website only displays the Pervasive v8 logo, while I need something that will work with v9.5 and, in the future, v10.

I tried writing an NHibernate provider, but that ended up being even more of a headache than my current query generation system.

What do you suggest? I'm on a very rushed timeline, so I'd like something that will integrate as easily as possible.

Answer 1

You should be able to find the ODBC drivers from Pervasive - they must be solid or Pervasive would be out of business by now. And most higher level abstraction software can deal with ODBC-level data. I think that would be your best bet, if you feel you really need to abstract it further than the ODBC itself.

Answer 2

Pervasive.SQL has a pretty solid ADO Adapter, and rocks over ODBC in most cases; I have used if successufully for years, but not without the headaches of Pervasive's syntax. In Pervasive's defense their relational engine does comply with SQL-92 and most of SQL-99.

What I am curious about is what parts of the syntax are you having a problem with, what is causing your grief?

What version of Pervasive's engine are you using?