Total Noob to Progress - Need help with tools

[Mark2457]

Hi Guys

We just acquired a company who has a manufacturing system that runs on Progress (OpenEdge 10.2B) -- that's what their OBDC driver says. I have no experience at all with Progress of 4GL (nor does their IT guy) and have been tasked with integrating their accounts system with Microsoft Dynamics (GP).

We have done this at other sites, by using a trigger on the GLBatch table that grabs all new posted batches and stores just the BatchID in a SQL table of records to process. We then use various tools to process each of the batches (get AP or AR invoices, line items, GL distributions, etc.) and finally mark the BatchID in our SQL table as processed (so we never need to write anything back to the source DB's tables). As we have no documentation about the database (it's a 3rd party vendor product), we have to reverse engineer most of it. On SQL Server based products it's easy, as we can run Query Profiler, post a batch and see the SQL being executed.

So I have two choices on how to proceed:

1. Just use the ODBC driver and poll the GLBatch table periodically to find new batches
2. Create a trigger in Progress that will write a record to a SQL table

I need to decide which to do, but No 2 requires singificantly more knowledge of Progress (no aversion to that). So Noob questions:

1. Is there an intro to 4GL. Any good resources?
2. What tools can I use to query the database, create triggers?
3. Is there an equivalent to SQL Query Profiler (shows all running database commands)?
   
4. Is 4GL actually SQL (i.e. is it just Progress's name for SQL like Micro$ofts Transact-SQL)?
5. If it's not SQL, how similar is it to SQL, or is there an option to use SQL (other than via ODBC)?

Sorry for the real naive questions, but I'm literally starting for zero.

TIA

Mark

 

[Cringer]

Hi Mark, welcome to ProgressTalk. I can't help on all your points, but there are others who can. I will answer what I can though.
4GL <> SQL. You can use SQL in the 4GL editor, but it's SQL 92 IIRC so quite limited.
One option would be to get someone in to help you get started. Tom Bascom who posts here is based in the USA and does consultancy on various things. An alternative would be to contact Cultura Technologies in the UK. They work with both Progress and Dynamics (or they did 3 years ago when I left them). The two systems don't talk to each other, but they have/had staff who were pursuant in both technologies and therefore might be able to consult more accurately.

 

[TomBascom]

What is the Progress based application? (There may be solutions that already exist. or it may be an application known to someone listening.)

To clear some confusion, hopefully before it is too late: Rule #1 -- Progress is NOT SQL. Progress sort of supports SQL but thinking about Progress in SQL terms rarely ends well.

Option #1 usually ends up having to scan the whole table and check it against the previous version to detect changes. Or just dump it all. Which doesn't often work very well.

Your option #2 might be sensible. Or it might not -- it depends on the application and its schema. But just for kicks we'll assume that updates to a single table are sufficient to drive a trigger. In that case you could add a 4GL trigger that copies the appropriate data to a table that you have added to the schema. Perhaps with some useful fields like a datetime to indicate when it was added (and maybe make it easy to purge this expoirted data). That table would need to be a "4gl table" (because 4gl triggers and SQL triggers are invisible to each other). But that is ok because 4gl tables are presented to SQL clients as the PUB schema. You could then read that table with your ODBC. (Or you could just write the data to a file and do whatever you want with that file.)

Your questions:

1. Is there an intro to 4GL. Any good resources? Sure, "progress.com" has all the docs and web based training etc. The documentation is also installed with the product if it is Windows. Look for the ? icon in the OpenEdge program group.
   
   
2. What tools can I use to query the database, create triggers? The 4gl. SQL will only lead to frustration. But, if you insist, any SQL query tool will work. Frustration #1 will be that 4gl applications do NOT respect the SQL width -- "overstuffing" fields is extremely common and it gives SQL tools fits.
   
   
3. Is there an equivalent to SQL Query Profiler (shows all running database commands)? Sort of. You can enable "client statement caching" for any (or all) sessions and see either the SQL query (for SQL connections) or the program name and line of code executing for 4GL connections.
   
   
4. Is 4GL actually SQL (i.e. is it just Progress's name for SQL like Micro$ofts Transact-SQL)? No. Not even remotely. The 4GL is a complete development language that happens to be tightly integrated with a database. More like LINQ than Transact-SQL.
   
5. If it's not SQL, how similar is it to SQL, or is there an option to use SQL (other than via ODBC)? There are 2 SQL options. The first is embedded SQL-89 inside the 4gl. It's not very complete and is fairly frustrating for SQL people to use. The second is SQL-92 which is external to the language and what you use with ODBC and JDBC drivers. See the comment above regarding width

 

[Cringer]

Just to clarify any concerns - this would be the Tom Bascom I was referring to earlier

 

[GregTomkins]

Does this imply the possible existence of TWO Tom Bascom's? We could get people off v9.1D twice as fast!!!

I'm not all that strong on triggers, so either version of Tom Bascom can correct me ... but I *think* .. unlike "regular SQL", Progress DB triggers run client-side ... which means (I think) you need to recompile everything to make them effective. And if it's a third-party application, that might be difficult depending on your arrangement. (Arguably for good reasons - notwithstanding the OP's legitimate issue - *our* shop would have a fit if someone tried to "sneak" DB updates into our code like that).

I guess you could use session triggers, but that would also require altering the environment of the third-party application ... it sounds more feasible though.

 

[Cringer]

I can neither confirm nor deny the existence of multiple Tom Bascoms.

As for 4GL Triggers, yes they are run client side.

 

[TomBascom]

I didn't say it would be trivial did I?

 

[TheMadDBA]

If you are running MFG/PRO QAD there are some options for integration beyond just roll your own. Not sure about some of the other Progress based packages.

 

[TomBascom]

To get around the "triggers run on the client" and the "4gl and sql triggers and data don't mix" problems you could, perhaps, subvert OE Auditing if the customer has an OE Auditing license (or wouldn't mind buying one).

That might be swatting a fly with a sledgehammer and then cleaning up with a bulldozer but it's another option

 

[Rob Fitzpatrick]

<snip>...you could, perhaps, subvert OE Auditing if the customer has an OE Auditing license (or wouldn't mind buying one).

OE Auditing is a database capability, not a licensed product. It can be enabled with any Progress RDBMS license. There's a bit of a learning curve of course, but nothing extra to buy.

 

[TomBascom]

... not a licensed product.

I was so surprised by that that I had to dig up and check the price list. Rob is absolutely correct. That the pricing committee somehow missed that is one of the wonders of the age...

 

[Cringer]

They probably decided it's so confusing to work with they couldn't possibly charge people to use it.
Oh. Wait.

 

[Mark2457]

Hi Mark, welcome to ProgressTalk. I can't help on all your points, but there are others who can. I will answer what I can though.
4GL <> SQL. You can use SQL in the 4GL editor, but it's SQL 92 IIRC so quite limited.
One option would be to get someone in to help you get started. Tom Bascom who posts here is based in the USA and does consultancy on various things. An alternative would be to contact Cultura Technologies in the UK. They work with both Progress and Dynamics (or they did 3 years ago when I left them). The two systems don't talk to each other, but they have/had staff who were pursuant in both technologies and therefore might be able to consult more accurately.

Thanks Cringer! We're in US

 

[Rob Fitzpatrick]

Is there an intro to 4GL. Any good resources?

It is not specifically an intro to the 4GL (aka ABL), but the Progress Knowledge Base is an invaluable resource.
Online: http://knowledgebase.progress.com/pkb_Home?l=en_US&c=Product_Group:OpenEdge&pg=kbase
Offline: http://download.progress.com/open/products/prokb/ProKBsetup.exe

Lots of good detailed product knowledge can also be obtained from the slide decks and recordings of technical sessions at past user conferences.
PUG Challenge Americas: http://pugchallenge.org/downloads.html
Progress Exchange: https://community.progress.com/technicalusers/w/exchange/default.aspx

Enabling SQL statement logging (from the Knowledge Base):
SQL-92: How to turn SQL statement logging on and off?
http://knowledgebase.progress.com/articles/Article/P119161/p

 

[Mark2457]

What is the Progress based application? (There may be solutions that already exist. or it may be an application known to someone listening.)

To clear some confusion, hopefully before it is too late: Rule #1 -- Progress is NOT SQL. Progress sort of supports SQL but thinking about Progress in SQL terms rarely ends well.

Option #1 usually ends up having to scan the whole table and check it against the previous version to detect changes. Or just dump it all. Which doesn't often work very well.

Your option #2 might be sensible. Or it might not -- it depends on the application and its schema. But just for kicks we'll assume that updates to a single table are sufficient to drive a trigger. In that case you could add a 4GL trigger that copies the appropriate data to a table that you have added to the schema. Perhaps with some useful fields like a datetime to indicate when it was added (and maybe make it easy to purge this expoirted data). That table would need to be a "4gl table" (because 4gl triggers and SQL triggers are invisible to each other). But that is ok because 4gl tables are presented to SQL clients as the PUB schema. You could then read that table with your ODBC. (Or you could just write the data to a file and do whatever you want with that file.)

Your questions:

1. Is there an intro to 4GL. Any good resources? Sure, "progress.com" has all the docs and web based training etc. The documentation is also installed with the product if it is Windows. Look for the ? icon in the OpenEdge program group.
   
   
2. What tools can I use to query the database, create triggers? The 4gl. SQL will only lead to frustration. But, if you insist, any SQL query tool will work. Frustration #1 will be that 4gl applications do NOT respect the SQL width -- "overstuffing" fields is extremely common and it gives SQL tools fits.
   
   
3. Is there an equivalent to SQL Query Profiler (shows all running database commands)? Sort of. You can enable "client statement caching" for any (or all) sessions and see either the SQL query (for SQL connections) or the program name and line of code executing for 4GL connections.
   
   
4. Is 4GL actually SQL (i.e. is it just Progress's name for SQL like Micro$ofts Transact-SQL)? No. Not even remotely. The 4GL is a complete development language that happens to be tightly integrated with a database. More like LINQ than Transact-SQL.
5. If it's not SQL, how similar is it to SQL, or is there an option to use SQL (other than via ODBC)? There are 2 SQL options. The first is embedded SQL-89 inside the 4gl. It's not very complete and is fairly frustrating for SQL people to use. The second is SQL-92 which is external to the language and what you use with ODBC and JDBC drivers. See the comment above regarding width

Thanks a lot Tom

The application is from EFI.com and called Monarch, formerly Hagen, but is a heavily modified version (by the companies previous owner)

Already experience the over stuffing issue. Very annoying

Looks like client statement caching is a good tool. Excuse my ignorance but where do i find that?

I've been doing some playing with SQL. If I use a where clause like [Date] > CAST(dateadd(day, -3, getdate()) as date), I get a small set of records that's easy to compare with my already processed list. Ideally, I'll be able to find a spare field and update that with a processed flag, which will hopefully be more efficient than a date comparison. The query will probably run every 15 mins or so (if we use option 1)

Regards

Mark

 

[Rob Fitzpatrick]

Also, if you're going to be using the SQL-92 query engine in the Progress database you should get to know about updating statistics (table, index, and column meta-data).

How to generate SQL-92 script to execute UPDATE STATISTICS for all user tables of a database using 4GL?
http://knowledgebase.progress.com/articles/Article/P115266/p

SQL-92: 'Update Statistics' Explained
http://knowledgebase.progress.com/articles/Article/20992/p

Hints to improve the performance of SQL queries?
http://knowledgebase.progress.com/articles/Article/P117623/p

There are lots of other articles that may be of interest when you search the KB for "UPDATE STATISTICS".

 

[Mark2457]

Does this imply the possible existence of TWO Tom Bascom's? We could get people off v9.1D twice as fast!!!

I'm not all that strong on triggers, so either version of Tom Bascom can correct me ... but I *think* .. unlike "regular SQL", Progress DB triggers run client-side ... which means (I think) you need to recompile everything to make them effective. And if it's a third-party application, that might be difficult depending on your arrangement. (Arguably for good reasons - notwithstanding the OP's legitimate issue - *our* shop would have a fit if someone tried to "sneak" DB updates into our code like that).

I guess you could use session triggers, but that would also require altering the environment of the third-party application ... it sounds more feasible though.

Sounds triggers is not the way to go. Have no qualms (other than debugging nightmares) about using triggers in SQL, but if they're going to run client-side and/or affect vendors code, we don;t want to go there (support issues). We're planning to migrate the app to SQL in the next 6-9 months, so it's a relatively short-term solution

 

[Mark2457]

To get around the "triggers run on the client" and the "4gl and sql triggers and data don't mix" problems you could, perhaps, subvert OE Auditing if the customer has an OE Auditing license (or wouldn't mind buying one).

That might be swatting a fly with a sledgehammer and then cleaning up with a bulldozer but it's another option

Thanks. Will investigate

 

[Mark2457]

It is not specifically an intro to the 4GL (aka ABL), but the Progress Knowledge Base is an invaluable resource.
Online: http://knowledgebase.progress.com/pkb_Home?l=en_US&c=Product_Group:OpenEdge&pg=kbase
Offline: http://download.progress.com/open/products/prokb/ProKBsetup.exe

Lots of good detailed product knowledge can also be obtained from the slide decks and recordings of technical sessions at past user conferences.
PUG Challenge Americas: http://pugchallenge.org/downloads.html
Progress Exchange: https://community.progress.com/technicalusers/w/exchange/default.aspx

Enabling SQL statement logging (from the Knowledge Base):
SQL-92: How to turn SQL statement logging on and off?
http://knowledgebase.progress.com/articles/Article/P119161/p

Thanks!

 

[Rob Fitzpatrick]

Already experience the over stuffing issue. Very annoying

In the Progress database meta-schema, each field has a defined value called MAX-WIDTH (aka SQL WIDTH). This is the maximum length of the data in that field that can be retrieved by a SQL client. This value can be modified.

There is a command-line tool called dbtool that can be run against a database to read field values, find their widths, and set MAX-WIDTH values appropriate for the data so you don't get SQL client errors. Search for dbtool in the KB.

What is DBTOOL?
http://knowledgebase.progress.com/articles/Article/P24496/p