Solved

Pervasive Database Connection

Posted on 2014-07-22
13
1,313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40212076
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
ID: 40212111
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
ID: 40212116
Ok, on DEMODATA I can import the table and see field information :-)
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 28

Expert Comment

by:Bill Bach
ID: 40212282
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
ID: 40212411
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
ID: 40212451
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
 

Author Comment

by:Rupert Eghardt
ID: 40212679
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
ID: 40212700
Can you post the COLUMNS tab view of the SupplierMaster table as well?
0
 

Author Comment

by:Rupert Eghardt
ID: 40212726
--- Kindly find attached;
image3.gif
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 40212809
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
ID: 40212857
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
ID: 40212909
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
ID: 40256078
Eventually I had to setup the fields manually (matching the SQL database), which worked :-)
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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