Get at Progress data via Dump/Export/ODBC/ADO.Net

[Ed B]

Hi All,

I am new to progress, so apologies if i say anything stupid.

Our App is .Net 2.0/Winforms/Sql Server 2005. A minor but necesary feature is required that has to read data from another vendors progress database that we have no control over.

I've spent a few days trying evaluate how to get at the progress data with little luck. My requirements are:

1) Low installation footprint (we don't want to have to ship a whole heap of components just to get access to progress for this one feature)

2) Free or Royality free runtime (we don't mind paying for a component but we are shipping to a large number of client sites so we don't want to pay for every site unless its very cheap)

3) Works with Progress 9.1D and higher

4) Some level of automation - end users are not necesarily computer savvy, but they can follow instructions if required (ie if they have to stop the DB server or something like that)


I have the other vendors product installed on my machine (along with Progress 9.1D - Workgroup Database Licence - which was shipped with that product).

I have tried to download progress source files and compiled program files but if I try to execute them, progress reports that I cannot compile programs with the version I have installed - so it appears that will be of no use when deployed to client sites.

I can dump using proutil.exe but it results in a binary file that I'm no so keen on trying to interpret.

I have tried the Merant ODBC drivers, but get "Error in Network Daemon", I get exactly the same error trying to use the progress "SQL Explorer Tool". I have the "Client Networking" component installed, not sure if I am missing anything else.

I looked into the OpenLink ADO.Net Provider but they sell per server licences and there is too much junk to install.


Can anyone point me towards a possible solution?

for example, a component that can read the binary files dumped from proutil, or what i need to do to get around "Error in Network Daemon"; or if I buy the developer version of progress can i ship compiled programs that will work on the limited progress version on client sites without paying extra royalties?

Any ideas would be welcome.

Thanks.

Ed.

 

[TomBascom]

How much data do you need to be able to read?

All of it? Or some arbitrary, but well defined, subset?

Since you are requiring 9.1D or higher the simplest, most reliable, easiest to install and so forth solution is probably a pre-compiled dot-r that uses dynamic queries to pull the required data out in a format that you can use.

I have such a thing laying around that I would be happy to customize to your needs but it won't be free...

 

[tamhas]

There are several questions here.

Yes, if you had a developer's license for Progress you could write and compile a program to do a data extract and you could ship the compiled code to the sites, but you may have issues about the CRCs on the databases versus your code unless this is all very controlled. If there is a development license at the site, you could ship source and do a one time compile, but if there is only runtime at the site, then you somehow have to piggyback on the process by which they are getting compiled code to those machines.

But, I would think that the more natural thing for you would be to get the SQL working. Of course, that would imply that there was Client Networking or SQL Client available at the sites. Is it? In versions 10+ it is free, but does need to be installed.

No ideas off the top about the network daemon, but you might wander over to the SQL list and work on that.

 

[TomBascom]

Personally I think that it is too complicated to contemplate getting a bunch of users that you don't control to get SQL working reliably.

On the other hand a decent dynamic query based data extractor (thus no schema dependencies and crc issues) distributed as r-code should be pretty easy to put in their hands.

But it does depend a bit on how much data, how often it is needed, how well defined the data set is and how it is supposed to be packaged for this other application. A little time spent defining all that and building the right tool would be time well spent.

 

[Casper]

If it isn't lots of data you need to read, then I think SQL is the most easy way to use for you (not the best ), but then again SQL access in 9.1D isn't very stable. It all depends on the nature of the data and the complexity of the queries if SQL will suffice in your situation.

Using a compiled progress program gives you mare reliable data access, but you will need to use apsserver and proxygen to cummunicate with your .NET application. If your not familiar with that and you don't have control of the apllication then this can be a difficult route.

About the error you get: "error in network daemon".
This is a general error which indicates problems with the connection to the database.
Where does the database reside?
Is there a firewall between the client and the server where the database resides?
How did you set up DSN?
Is the database configured with a secondary SQL broker?
Can you ping the server where the database resides?
you say:

.......that has to read data from another vendors progress database that we have no control over.

How much no control is no control?

Casper

 

[Ed B]

Hi All,

Thankyou all for your prompt and informative replies, they have been very helpful. Sorry I didn't get back to you quicky, we just had a three day weekend.

The pre-compiled .r solution has been used by one of our competitors who has had to solve the same problem so this looks like the avenue we will have to take. I have submitted a query to our local progress office to see how much a developer licence will cost for our needs.

I have provided answers to <hopefully> all of the questions asked below.

Tom, your offer of a consulting service could be attractive depending on how much the progress developer licence is. If you still think a dynamic extract is possible after reading the below let me know and I will prepare a specification for you to quote on.

Thankyou,

Ed.


Tom:

Q: How much data do you need to be able to read? All of it? Or some arbitrary, but well defined, subset?
A: A well defined subset, however the subset may change in future releases eg. Once every couple of years. This is a partial data conversion in an accounting application. There are significant business rules to enforce so our clients will perform conversions for a small number of their customers at a time, they may need to cleanup their data and then try the conversion again, or set up data in our application before converting each customer. So basically the size of the required data will be quite small (5-50MB) for each iteration of the conversion. We will need to use where clauses (or the eqivalent in progress) to restrict data to specific ledgers (customers of our clients) and we may need to add or remove exported tables in future releases. A (select *) approach (all columns) is desirable.

Q: How well defined the data set is and how it is supposed to be packaged for this other application
A: In terms of packaged basically we need to read the data into a .net application, load into our business objects, execute a whole heap of business rule and constraint checks and then store into SQL server. So the ability to directly read from progress would be fantastic, but if we go down the .r utility route then a .csv per table or a .dbf or any non-propriety easily accessible data format would be an appropriate package.

Tamhas:

Q: If there is a development license at the site
A: No client site will have a development licence.

Q: Client Networking and SQL client at sites?
A: The other vendors app can be deployed on either a standalone or networked environment. I currently have a standalone environment installed and I do not think Client Networking and or SQL Client were installed (I installed the Client networking later). I will test a network deployment next and see what is set up. However I need to cater for both environments.

Casper:

Q: the complexity of the queries
A: The maximum complexity will be something like this in SQL:
select * from <tablename> where <charfield1> = 'somevalue' and <datefield2> >= '20060630' and <datefield2> <= '20070701'

Q: use apsserver and proxygen to cummunicate with your .NET application. If your not familiar with that and you don't have control of the apllication then this can be a difficult route.
A: No I am not familiar with these technologies. I suspect the installation footprint will be too great to go down this path.

Q: Is there a firewall between the client and the server where the database resides?
A: Its all on the same machine at this stage, however you have a very good point, I have not checked for any firewall issues, I will do so today.

Q: How did you set up DSN?
A: Using Test Connect in the windows ODBC Data Source Administrator. Driver: "MERANT 3.6 32 Bit Progress SQL92 v 9.1D" Params: Hostname=localhost, Port Number=5000, Databasename = <Alias of my DB>, User ID = "username", Password = "mypassword". I added the port number to my "Services" file in Windows\System32\ and started the progress server on that port. I checked this and _progress.exe was listening on Port 5000. The username and password are the ones that I sucessfully use to log in to the other vendors application. I also tried my local up address instead of "localhost"

Q: Is the database configured with a secondary SQL broker?
A: I don't know, I suspect not. How would I check this. And would this be a component that I would have to ship to my clients?

Q: Can you ping the server where the database resides?
A: Yes.

Q: How much no control is no control?
A: We will have no consultants and possibly no IT savvy person at the end user site. We need to be able to ship our application with instructions that can easily be followed by an accountant. So as we need to automate whatever configuration changes are necesary with a pretty good level of confidence that there will be few failures. The client will be able to provide us with information such as the location of the db file, or the server name and db alias and that sort of simple information. They will be able to follow insturctions such as "Log on to your server as an administrator and run this program".

 

[Ed B]

I got ODBC working in the end.

This is what worked for me in case anyone reads this looking for a solution to connect to a progress DB where:

- You cannot access the database (eg a Workgroup edition with no access to the the admin" menu option in Data Administrator).
- You don't know any usernames or passwords to the DB.
- The _users table in your db is empty (ok so i had to cheat and download a native progress data viewing tool to work this out). If its not empty this solution won't work - You'll have to know a username and password.

1) Ensure database is started with -S <PortNumber> switch. (Forget the "Services" file, just stick in a free port number.)

If you need to view listening ports use cmd line app "netstat -a -n -o -p TCP", use the PID column to match the relevant progress process id - cmd line "tasklist" will give you a list of process names and PID's.

2) Using either SqlExplorer or an ODBC connection perform the following steps. Make sure you have a backup of your DB!!! It is possible to completely lock yourself out of the DB via a sql tool if you do not have access via any other data editing tool.

- Login as "Administrator" with a blank password.
- execute: select "_sysdbauth"."_grantee" from pub."_sysdbauth"
- this will return a list of usernames. The first one should be the DB Owner you will need this in the next step.
- Close the connection

- Login as the DBOwner with a blank password
- execute: create user 'SYSPROGRESS', 'any_new_password'
- NOTE: you must create a password, otherwise you will be locked out of the DB unless you can empty the _users table.
- Close the connection

- Login as SYSPROGRESS with your new password
- You will now have full access to all tables and DBA rights.

I hope someone finds the helpful in the future, it was an utter bastard to work it out from a basis of zero progress knowledge and zero access to the DB.