How to retrieve system catalog using ODBC

[Tony@DataLynx]

Hi, I'm new to Progress so please be gentle with me!

I have a customer who has a Progress database and I have been asked to perform some Data Quality evaluation of their data. My company has written a data profiling tool which connects via ODBC, returns the schema and allows us to browse the data and to define and apply data quality rules.

I have managed to set up the ODBC connection and it seems to work ok (test connection seems is ok), but our software cannot retrieve the database catalog. We use standard ODBC functions calls which are have previously worked with Oracle, Sql-Server, Access, Postgres and other relational databases. The are no obvious errors returned from the query, just a blank list.

Unfortunately I don't have my own copy of Progress and I don't easily have the facility to debug our software at our client's site.

Under the hood I believe our software will make this call to

SQLTables(hstmt,
NULL, 0, // All qualifiers
NULL, 0, // User specified
NULL, 0, // All tables
NULL, 0); // All columns )

I can see that I have not specified a database name here, so would you expect this to work?

Is there something I need to know to set up the ODBC driver?

I'd be grateful for any assistance.

Tony

 

[Tony@DataLynx]

As I am a bit stumped so far on this problem I thought I'd try to connect via Access as suggested in this thread: http://www.progresstalk.com/showthread.php?115552-Accessing-system-tables-ODBC

Access is able to get a list of the tables but fails with the following error when I try to create a link table:

Reserved error (-7748); There is no message for this error

Doh! Anybody have any ideas?

 

[vinod_home]

what does SQLTables do?
Regular sql commands would work and you would be able to get a list of Tables that are in the Progress database, but unless you have grants to read the data you will not be able to do what you need. You can get the table-names from a system table _File. (Example: select "_File-Name" from pub."_File"; )

Under the hood I believe our software will make this call to

SQLTables(hstmt,
NULL, 0, // All qualifiers
NULL, 0, // User specified
NULL, 0, // All tables
NULL, 0); // All columns )

 

[Tony@DataLynx]

what does SQLTables do?
Regular sql commands would work and you would be able to get a list of Tables that are in the Progress database, but unless you have grants to read the data you will not be able to do what you need. You can get the table-names from a system table _File. (Example: select "_File-Name" from pub."_File"; )

SQLTables is a standard ODBC function that returns the list of table, catalog, or schema names, and table types, stored in a specific data source.

http://msdn.microsoft.com/en-us/library/ms711831%28VS.85%29.aspx

If necessary I could amend the program to run a specific query instead of making this call but I'm struggling to find much documentation relating to what system tables there are are in Progress.

I need to retrieve a list of tables in the database, along with their schema (owner) and for each table I would need to run a query which returns for each column:

• column name
• data type
• precision
• whether it is can be null
• position in the primary key of the table

Can you point me in the right direction? Do you have a link to a Progress Manual perhaps? I've been looking but I can't find one that seems to tell me how to do this.

 

[Tony@DataLynx]

Ok, I think I have found something:

OpenEdgeTM Data Management:
SQL Reference

http://www.progress.com/progress/products/documentation/docs/dmsrf/dmsrf.pdf

So I think I should be able to do something like this:

select tbl
, owner
, tbltype
from sysprogress.SYSTABLES

and then for each returned table, to get the columns:

select col
, coltype
, nullflag
, width
, scale
from sysprogress.SYSCOLUMNS
where tbl = <table_name>
and owner = <table_owner>
order by id

I'm sorry to ask such a dumb question but is there an application which I can use to try running these queries to see if they work? Something like Sql Server's Management Studio?

 

[Tony@DataLynx]

As I am a bit stumped so far on this problem I thought I'd try to connect via Access as suggested in this thread: http://www.progresstalk.com/showthread.php?115552-Accessing-system-tables-ODBC

Access is able to get a list of the tables but fails with the following error when I try to create a link table:

Reserved error (-7748); There is no message for this error

Doh! Anybody have any ideas?

I have found a fix for this, editing the windows registry entry and adding a new entry WorkArounds2=8192 into the ODBC driver properties has enable me to at least create link tables to MS Access. See this link for a better explanation;

http://www.progresstalk.com/showthread.php?114231-WorkArounds2-8192


Now I'm going to check if I can point my software at Access and use Access as a gateway to examine the data within Progress. It's a pretty cruddy solution but hey, if it works .....

 

[vinod_home]

There is sqlexplorer as part of Progress installation where you can run the sql queries and test it. Do you know what version of Progress is being used. I guess you already mentioned that you cannot get your hands on Progress on the client side. Do you have the progress odbc installed on your side to connect to the progress database, if you do then you should be able to use Excel and pull up a web query.

HTH

 

[Tony@DataLynx]

Hi, Yes I am working with version OpenEdge 10.1A

I have managed a bit of a work around; if I create link tables in Access I can interrogate the database catalog from Access. Once I have the meta data I can then switch the odbc connection to point directly at the OpenEdge database and things seem to work ok.

I'd still love to know why the system does not respond to bog-standard ODBC calls however. Normally I have found the Data-Direct ODBC drivers to be excellent and so I am surprised that they are not working. I imagine that it is something to do with my code but since this call works perfectly on Oracle, SqlServer, Postgres, Paradox, MS Access & MySQL it's my guess that there is a quirk in Progress that I need to be aware of; perhaps I need to pass some parameters other than NULL and 0 in the function call but what I have no idea!

 

[RealHeavyDude]

IMHO:

The Progress/OpenEdge database is not a SQL database in the first place. I think that Progress did a pretty solid job supporting SQL92 alt least since OpenEdge 10. But it's still not a SQL database and AFAIK it only supports the pure SQL92 standard - nothing more, nothing less. And there are some other issues you might run into, for example the authorization concept of the ABL and SQL are completely different ( revoke vs. grant ), or that you need to set up the SYSPROGRESS login credentials unless you use the login credentials of the OS account under which the database was created when you connect to it with SQL the first time.

Regards, RealHeavyDude.