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

x
?
Solved

ODBC Pervasive Database Connection

Posted on 2014-10-22
8
Medium Priority
?
2,104 Views
Last Modified: 2014-11-26
Hi Guys,

I am trying to setup an ODBC database connection,

I've downloaded and installed the Pervasive Workgroup Engine from Gold Star Software.
In the Windows tray, the Pervasive Engine is running.
In ODBC data source, I added a new data source, and selected the Pervasive ODBC Client Interface.

Data source name = "Test"
Description = "Pervasive ODBC Client Interface"
Server Name = "Local host"
Transport Hint = "Try TCP/IP first .."
Data Options = Database Name

Upon clicking "Create" or "Get List", I am receiving the following error message:
"Cannot connect to Pervasive.SQL Server Engine, Workgroup Engine, or Workstation Engine.  Make sure either the Pervasive.SQL server is started, or Pervasive.SQL Workstation Engine is installed."
0
Comment
Question by:Rupert Eghardt
  • 3
  • 3
8 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 40396915
The Server Name should be "localhost" (all one word) not "Local Host" (two words).  Localhost is the name of the local machine.  If you were connecting to a remote machine you would use that machine name.
0
 

Author Comment

by:Rupert Eghardt
ID: 40397013
Sorry the "localhost" is "localhost" by default, I just typed like that.

Still the same error message?
Pervasive.jpg
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40397049
There are a few different pieces to the puzzle.  I know that you have indicated above that you installed the PSQL v11 Workgroup Engine.  However, another thread implied that you may have installed the PSQL v11 Client instead.  The Client does not contain a full engine, but rather only the Client components to talk to the engine. (The Client does contain the "Client Cache Engine", or CCE, which WILL show up in the system tray, but this is not a complete standalone engine -- it caches data from a remote server.) Please re-verify exactly what was installed.

If you are intending to access local database files on your own computer, then you need the PSQL v11 Workgroup Engine installed and licensed (beyond the 30-day trial).  In this case, remove the PSQL Client, Install the PSQL v11 Workgroup, and your configuration (above) should work just fine.

However, if you are accessing database files on a remote server, then the Client is the correct piece to have installed.  In this case, change the configuration for Server Name to the name of the remote server, and verify that there are no firewalls blocking access to the engine (either client-side or server-side). You can also test remote access from the Pervasive Control Center by right-clicking Engines and selecting New/Server.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Rupert Eghardt
ID: 40398281
Thank you Billbach.

I downloaded the v11 Workgroup engine and is working.
I managed to setup a new "test" database, and was able to connect in ODBC.

I am however experiencing the following problems;

1.  If I right-click on databases and select "new database", enter a database name and specify a "test location", it asks me for a username & password.  No matter what I insert, it doesn't take it.  I've tried Master, (no psw), etc.
* I've added a screen shot

2.  When I right-click on the Engine, logout Anonymous, log back in as Anonymous, the "test" database then only shows in the list

3.  As a test, I've deleted all databases, but when I right-click databases and try to add a new database for example:  "demodata", it tells me that it already exists.  In the event of an existing database, such as "DemoData" ...
How to I open an existing database, instead of creating new?

4.  I managed to add a Pervasive 2000i database that I was trying to open, by specifying the DDF files location, as well as the data path.  The tables are all showing, but trying to view records in a table, it says "Invalid User Authorization Specification"
Is this a user account specific to the database I am trying to open?
Pervasive2.png
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 1500 total points
ID: 40400398
There are two parts to any database.  The first part is a "Named Database", which is an entry in the database file DBNAMES.CFG.  This is what the PCC is showing you in the tree on the left side.  The second part is an (optional) ODBC Engine Interface DSN, which is created by default for 32-bit application use.  You will NOT see this in the PCC, but will ONLY see it if you run the 32-bit ODBC Administrator (from Tools menu).  The errors you are showing are common for limited-access users, who do not have proper access to the ODBC Administrator.  

So, we now need to fix it.  First, log in as a true administrator, start the PCC, and then run the 32-bit ODBC Administrator from the Tools menu.  Once there, go to the System DSN tab, find any of the PSQL DSN's and remove them.  If you get an error doing this, then you are not a true administrator, and you may need to disable UAC first.

Once these old DSN's are removed, and assuming the DBNAMES.CFG is also empty (i.e. there are no databases in the PCC), you can create a new one.  Again, make sure you are Administrator first, then create the new database.  If you get the same problem, try unchecking the "Create 32-bit DSN" checkbox and see if that works.  If it does, then you'll have to MANUALLY create the 32-bit ODBC DSN's in the ODBC Administrator.  Not a show stopper, but painful to have to use two steps.

Alternatively, if a DSN like DEMODATA already exists and you just deleted the Named Database, right-click to create the new database, give it the name Demodata, and then uncheck the box to create the DSN (which will fail if it already exists).

Let's get this all working first, and let's get access to DEMODATA once again as a stepping stone.  Once we have the engine working correctly, we can move on to your last issue.
0
 

Author Comment

by:Rupert Eghardt
ID: 40405055
Thank you Billbach,

First, log in as a true administrator, start the PCC, and then run the 32-bit ODBC Administrator from the Tools menu.  Once there, go to the System DSN tab, find any of the PSQL DSN's and remove them.  If you get an error doing this, then you are not a true administrator, and you may need to disable UAC first.


I removed all the PSQL DSN's without an error

Once these old DSN's are removed, and assuming the DBNAMES.CFG is also empty (i.e. there are no databases in the PCC), you can create a new one.  Again, make sure you are Administrator first, then create the new database.  If you get the same problem, try unchecking the "Create 32-bit DSN" checkbox and see if that works.  If it does, then you'll have to MANUALLY create the 32-bit ODBC DSN's in the ODBC Administrator.  Not a show stopper, but painful to have to use two steps.


I don't know how to check the contents of the DBNAMES.CFG  ...
I tried adding a new database, but still received the login username / password prompt (even with unchecking the "create 32-bit DSN" option.

Alternatively, if a DSN like DEMODATA already exists and you just deleted the Named Database, right-click to create the new database, give it the name Demodata, and then uncheck the box to create the DSN (which will fail if it already exists).


I still couldn't delete the problematic database.


I uninstalled PSQL Workgroup, reinstalled, and only then was I able to add a new database.
The default databases were now also listed after the reinstall.

I am still unable to delete the problematic database,
7004:  General failure code (even with "run as administrator")
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40406412
Without screenshots or more detailed information, this is very hard to troubleshoot.  Again, a database is stored in TWO pieces, the DBName and the DSN.  If you see it in the Pervasive Control Center, then you are seeing the DBName.  If you see it in the ODBC Administrator, then you are seeing the DSN.  Which one are you trying to remove?

In the worst case, you can always remove everything by uninstalling the WGE, then deleting the DBNAMES.CFG file (left over in the C:\ProgramData\Pervasive Software directory), and then reinstall the Workgroup Engine.  All should be back to normal after a perfectly clean install.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

971 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