Solved

Pervasive Database Connection

Posted on 2014-07-22
13
1,101 Views
Last Modified: 2014-08-12
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;
0
Comment
Question by:Rupert Eghardt
  • 7
  • 6
13 Comments
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
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.
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
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?
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
Ok, on DEMODATA I can import the table and see field information :-)
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
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.
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
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?
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
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:
    http://www.goldstarsoftware.com/whitepapers-accessing-data.asp

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....
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Rupert Eghardt
Comment Utility
I did access the DDB builder from the server;

IN PCC:
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.
image1.gif
Image2.gif
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
Can you post the COLUMNS tab view of the SupplierMaster table as well?
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
--- Kindly find attached;
image3.gif
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
Comment Utility
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.]
0
 

Author Comment

by:Rupert Eghardt
Comment Utility
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 ...
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
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.
0
 

Author Closing Comment

by:Rupert Eghardt
Comment Utility
Eventually I had to setup the fields manually (matching the SQL database), which worked :-)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now