Progress 91d and Procedure Editor Question:

L

linuxdawg

New Member
Hello, all. This is my first post; please be gentle. :awink:

I am relatively new to the progress database environment, but not a newbie to RDBMS. I have hopefully a simple question for you all. I run a progress Application on Linux. To run simple queries and such, there is a procedure editor within progress that will allow me to access the progress database directly. Anyway, I have a nice SQL statement that I can run within this editor. The editor will run this SQL and display the results. However, I would like to:

1. Put this output to a file on my OS's filesystem
2. Email the output to someone.

One of the two would be wonderful. I would like to set up a cron job to do this daily, but I guess the first step is how to "put" the data somewhere.

If there's a better method of accessing the database other than the Procedure Editor, I'm all ears. Many thanks in advance to you all.

BTW: Great Forum!!!! :blush:
 
Hello dawg, welcome to the forum.

We are always gentle here.

I can't help you with your functionality problem at the moment (like many Progress programmers, I don't do SQL), but I just wanted to point out a couple of things that you will need to be aware of if you intend to query a Progress database through a language interface regularly.

If you want to use SQL, then you will be best off using a 3rd party Interface like Squirrel or WinSQL or whatever the Linux equivalent is. Alternatively you can use the Progress SQL Explorer Tool which comes with Progress. All of these will take some configuring, so it will be useful if you can get someone to help you do that.

Although you can create limited SQL queries through the Procedure Editor, that is not what its main purpose is.

The thing that distinguishes Progress from all other DBs on the market, the thing that most of us like best about Progress is its row-oriented 4GL - much more powerful for programming than set-oriented SQL, and if you intend interrogating the DB regularly, then it will help if you get familiar with it. I'm assuming that as you have the procedure editor you have a development license.

4GL example:


OUTPUT TO 'mypath/myfile'.

FOR EACH customer NO-LOCK
WHERE customer.surname = 'smith':

EXPORT customer.

END.

OUTPUT CLOSE.


All 'smith' customers records are now exported to the file.

You get the idea.

The Progress development environment is essentially a 'VB6 with 4GL' type affair, where you create Visual Front ends using the Appbuilder (the Procedure editor is a non-visual equivalent, ie. you can't drag and drop widgets on it, only on the Appbuilder - NB. I've never developed with Unix as Front End, only Windows Client to Unix Back end, so can't tell you how your environment handles Appbuilder), and use them to query the data by executing 4GL procedures at the business end, without having to go through all that fiddly build connect string/build query string nonsense in every call.

Anyway, here's a link to the 4GL manuals to give you something to chew on.

http://www.progress.com/products/documentation/start/index.ssp

HTH a little.
 
Summary:

4GL = similar to VB6 language with Comprehensive row-oriented DB Access language

SQL Explorer = Visual interface for executing SQL commands, like Squirrel.

Procedure Editor = similar to VB6 Code editor, used for entering 4GL.

Appbuilder = similar to VB6 Studio, contains section editors, which are 'mini procedure editors'.
 
Incidentally, if you insist on using SQL in the procedure editor, then you may be able to get away with replacing the FOR EACH block I posted (the bit between the OUTPUT statements) with your SQL command.

I haven't tested it, and I'm not sure if this will work as you require though.

eg.

OUTPUT TO ...

SELECT ...

OUTPUT CLOSE.
 
I think I'm having a compatibility issue. I've tried exporting with SQL or the similar statement that you've posted, but my procedure editor says it can't understand line 1 (the OUTPUT TO statement).

Even if we manage to figure out the procedure editor, I'm still uncertain how I would set this up to run via cron job (scheduled task). I'll try to find a cmd line client of some sorts. I'll post my findings
(if any! :awink: )

Joe
 
Did you type the final period (needs to go at end of each line)?

This works for me on windows:

OUTPUT TO 'c:\temp\testsql.txt'.

SELECT * FROM <tablename>.

OUTPUT CLOSE.

Good luck.
 
ps. Creating reports, ad-hoc or otherwise, and emailing them to users on or off a schedule is a very normal process, but I've only ever done it through the 4GL.

There are 3rd party programs (some free) and components that can help you in that respect.

Just letting you know that if you do need to go further in, you should be able to do most of what you want to through the 4GL. Will take some learning though, and may not be worth the effort if you will not use it to any great extent.
 
Even if we manage to figure out the procedure editor, I'm still uncertain how I would set this up to run via cron job (scheduled task). I'll try to find a cmd line client of some sorts. I'll post my findings
Click to expand...

If I understood correctly you want to run your query using cron and post its results to somewhere. You need to write your query in Procedure Editor and save it as a file with .p extension, for instance 'query.p'. If you want to run it as background process you should use the following command line:
$DLC/bpro <database connection parameters> -p query.p -U <username> -P <password> >> queryresults.log
where $DLC - the PROGRESS installation folder.

You can add this line into crontab as well.

You can use it OUTPUT statement for the query results but it's not necessary in your case.

HTH
 
Lee, bulklodd,
Thanks so much!!!!! With both of your inputs I was successful with the cron job!!!! Lee, I used the procedure editor to build my ".p" SQL (you started me on the right track). Bulklodd, you lead me to the bpro cmd, but since the DB was in multi-user mode, your suggested comment lead me to the "mbpro" cmd, which is basically the same as "bpro" but with multi-user support! Guys, you have allowed me to access and create some very cool stuff with the data that I can pull from the db! GREAT!!!! You guys rock! :D :D :D :D
 
You must log in or register to reply here.
Back
Top