Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1546
  • Last Modified:

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;
0
Rupert Eghardt
Asked:
Rupert Eghardt
  • 7
  • 6
1 Solution
 
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.
0
 
Rupert EghardtAuthor 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?
0
 
Rupert EghardtAuthor Commented:
Ok, on DEMODATA I can import the table and see field information :-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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.
0
 
Rupert EghardtAuthor 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?
0
 
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:
    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
 
Rupert EghardtAuthor Commented:
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
 
Bill BachPresidentCommented:
Can you post the COLUMNS tab view of the SupplierMaster table as well?
0
 
Rupert EghardtAuthor Commented:
--- Kindly find attached;
image3.gif
0
 
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.]
0
 
Rupert EghardtAuthor 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 ...
0
 
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.
0
 
Rupert EghardtAuthor Commented:
Eventually I had to setup the fields manually (matching the SQL database), which worked :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now