Pervasive Database Connection

Hi Guys,

I am having difficulty connecting to a Pervasive Database with ODBC and/or Clarion.
I can see (access) the database tables, but unable to access field-information.
I suspect the problem is somehow database rights related.

Adding a new database:
In PCC under Engines, Server Name, Databases, I've selected the option for new database, and browsed to the data-set for the new database.
The database added successfully.
I've ticked the box "Enable Security" under the "Database Security" tab and entered a password.

However, even when using the "Master" account with the assigned password, I am unable to access the field names/information in the database tables from ODBC or Clarion.

There is a "BOUND DATABASE" check-box under the General tab, I am not sure if this should be enabled,
However, when I tick this box, I get a warning message
"7004: General failure code (Database session could not be started using the given username & password)"

When I try to remove the database that I added, I get an error code:
7004: General failure code

Any help or ideas will be appreciated;
Rupert EghardtProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresidentCommented:
By default, Security is NOT enabled on PSQL databases.  If you disable security, then you will have full rights to the database, and no longin will be required.  This should eliminate the rights question.  Also, do NOT check the BOUND checkbox -- this may break your application.

Two other things:
1) Have you tried connecting to the built-in test database, called DEMODATA?  If this works, then the problem is with your own database, not the PSQL engine.  At least this will eliminate your own code from the problem.
2) Have you tried opening your database from the Pervasive Control Center? This is the reverse of the above.  Again, you should be able to open BOTH the DEMODATA and your own database and see tables inside.  Souble-click on any table, and you should get a SELECT * query which will show you the fields.  If your own database fails this test, then perhaps the data dictionary files (DDF's) are bad.  Contact the application developer for a new set of DDF's.
Rupert EghardtProgrammerAuthor Commented:
Thanks Billbach,

I did not enable the BOUND checkbox, simply because it refused to.
I tried accessing the table and field information without the MASTER account / password enabled.  Still failed,

I haven't tried connecting to DEMODATA yet, will try this.

From PCC I am able to open the database table, view fields and database records in the table.

I agree that the rights should then be fine, but why am I unable to remove the database that I just added,
Receiving error "7004: General failure code"

What else could be wrong that I am able to see the tables from ODBC / Clarion, but no field-information?
Rupert EghardtProgrammerAuthor Commented:
Ok, on DEMODATA I can import the table and see field information :-)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bill BachPresidentCommented:
There are multiple sets of rights.  First, to access data, you have SQL rights.  This is what you enabled when you set the Master password.  Second, though, is server administrator rights, which is needed to create or remove a database.  In some older systems (or systems that were upgraded from older systems), doing administrative tasks via Remote Desktop is not permitted by default.  Try using the server console itself and see if that gets you past the admin issues.  If it does, let me know, and I can look up the registry change you need to make to fix that side of things.

If DEMODATA works, then the problem is likely with your DDF's.  It is good that the PCC works, though.  So, try running the DDF Builder and use the Check Database option to confirm that your DDF's are defined properly.
Rupert EghardtProgrammerAuthor Commented:
You are correct, the DDF's are missing.
So every table should have a DDF accompanying file?

I ran the DDF builder "check database" option, and the following errors were reported for each table;
- Unable to retrieve physical file statistic information
- Btrieve open failed for xxx, btrieve error 175

Is there a simple (SAFE) way to rebuild / create the DDF files for each table?
Bill BachPresidentCommented:
Data dictionaries, like the system catalog of other SQL engines, provide the table and column structures of the low-level Btrieve/MKDE records to the SQL engine.  There should be a single set of DDF's for the database as a whole, and they are usually stored within the same folder as the data files (though not always).  At minimum, you should find FILE.DDF, FIELD.DDF, and INDEX.DDF. You are likely to also see ATTRIB.DDF, VIEW.DDF, USER.DDF, RIGHTS.DDF, and more.  (Newer databases with V2 metadata might use PVFILE, PVFIELD, etc.)  

In a vast majority of cases, DDF's are provided by the developer with the application.  Any changes to the data files may also require a change in the DDF's, too, so it is imperative that they be kept in sync.  In rare cases, developers may NOT provide DDF's, and it may be up to the end user to build his/her own.  Check this white paper for additional details on DDF's and why they are needed:

I have never seen a DDF set throw off Status 175's -- so you have me on that one.  Are you running this on a client or on the server?  It is best to run the DDF Builder on the server itself.  

However, you said that you COULD see things from the PCC, so let's go back there.  In the PCC window, open up your database, then System Tables.  Double-click on X$File (which relates to FILE.DDF or PVFILE.DDF) and look at the Xf$Loc fields.  Are these simple filenames?  Or do they contain entire paths?  Post an example, if possible, of one of your tables that you are trying to access.  Then, if it works, try right-clicking on the same table in the Tables list, and selecting Properties.  Post the statistics and Columns views, if you can.  This will give us a lot more info....
Rupert EghardtProgrammerAuthor Commented:
I did access the DDB builder from the server;

I double-clicked X$File (which relates to FILE.DDF or PVFILE.DDF) and looked at the Xf$Loc fields:
These are simple filenames.  They don't contain entire paths.  
--- See example attached.
Bill BachPresidentCommented:
Can you post the COLUMNS tab view of the SupplierMaster table as well?
Rupert EghardtProgrammerAuthor Commented:
--- Kindly find attached;
Bill BachPresidentCommented:
Awesome, thanks.  So, the PCC *is* able to see everything just fine, including the column definitions.  This is good news.  Sometimes, the fields look OK in a SELECT query, but the PCC is unable to parse all of them, and this brings up an error.  Luckily, this is not the case.

The nest step is to try ODBC without using Clarion.  Download any ODBC-based database access tool.  Personally, I use SQLExec, which is quite easy to use from a Command Prompt window, which eliminates a lot of headaches in the GUI.  Then, issue a simple SQL statement to this table.  Using SQLExec, the command would be only:
   SQLEXEC ACOS_____2013  "Select top 10 * from SupplierMaster"
You blanked out the database name for security reasons, so you'll need to provide the right database name on the command line.

[Note that MS Access is a possible option, but you will need to create an ODBC Pass-through Query to avoid having the Jet engine get in the way of seeing your data.]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rupert EghardtProgrammerAuthor Commented:
I've setup another ODBC data source under Admin Tools in Windows.
Entered the server IP address and "GetDNSList" produced the available databases.
I selected the database and the connection test successful.

Importing the ODBC source into Clarion, I am now able to view the table field information.

Importing directly from PervasiveSQL still only works for the "DEMO-DATA" database.

Not sure why the database I've setup would be different from the DEMO-DATA ...
Bill BachPresidentCommented:
It is possible that there is some sort of odd data type that Clarion doesn't handle properly with native access.  I am not a Clarion developer, but am just guessing.  

Does this resolve the issue?  Or did you still want to troubleshoot further?  

The next steps would be to try to fix the DDF Builder so that this works, and so that you can see if there is anything unusual about your DDF's.
Rupert EghardtProgrammerAuthor Commented:
Eventually I had to setup the fields manually (matching the SQL database), which worked :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.