stored procedure send-sql-statement in progress

[schaapie]

In some of the documentation the mention a stored procedure "send-sql-statement" which should be there by default.

But...
Is it only available in an Oracle-db?
Can i even have stored-procedures on an progress-db?
If so, how can i create a stored-procedure in a progress-db which allows me to send a sql-statement. And how do I access the dataset which is returned?

Thanks
Schaapie

 

[Casper]

http://tinyurl.com/769v4

and/or

http://tinyurl.com/cnlv2

 

[schaapie]

OK, so I can have stored procedures, but I'm no Java-programmer. I thought maybe someone has this already figured out?

 

[Casper]

figured what out?
You mean something like:
http://www.progresstalk.com/showthread.php?t=91261 ?

There are examples from stored procedures in the links I gave you as well. There is also explained how to acces the values which are returned. (To java or to sql)
Also look through the KB there are some examples there as well.

Casper.

 

[schaapie]

I mean figured out the exact syntax for a "send-sql-statement" stored procedure.
I reckon this is something more people would want.

 

[Lee Curzon]

There are plenty of references for calling a stored procedure using send-sql-statement in another database (eg. Oracle/MS SQL) in the docs.

However, you seem to be wanting it the other way round, ie. calling a Progress stored procedure using that syntax.

According to a well-known authority, this is not possible.

http://www.peg.com/lists/sql92/web/msg00558.html


HTH

 

[schaapie]

So I must disect the sql-explorer or Access or Crystal...
see how it does it...
then step outside Progress with com-handles or whatever...
let the engine of the external program issue the sql-query...
and find a way to get the dataset back in...

Hmmm...,
I think I'll let my colleges cut and paste the thing in the sql-explorer, Crystal or Access.

it's just to show "if and what a generated sql-query would return".

 

[Lee Curzon]

I'm still not quite sure what you are trying to do - but if you are trying to execute a dynamic SQL query through the 4GL, I don't believe you can do this.

KB P37440
Title: "Is there any way to use dynamic SQL queries in a 4GL program?"

http://tinyurl.com/9rrfb


You can of course use static SQL, or dynamic 4GL queries.

Perhaps you could write a program to dynamically generate a temporary procedure (.p) file containing your SQL query, and execute that.

However, I would check with a more knowledgeable resource (eg. Progress Tech Support) before taking this long winded route.

 

[schaapie]

OK from the beginning.
My collegues are developing reports with Crystal Reports.
Sometimes it is neccesary to write the sql-query themselves for performence.
Because they are lazy :biggrin: and because we have a "logical" db-scheme with very long but very clear/self-explanatory table/field-names they asked me to write a "query-builder" in which they could click and point around to get a sql-query.
Last step in this is to see if what they have clicked works.
So I wanted to send the generated sql-statement to the database and show them what they got back.

The last option you gave (generate a .p) is a possibility, it's just for my fellow-developers who have a developer-version of progress ie they can compile/run the .p and it seems easy to build (at first glance)

 

[Lee Curzon]

After generating the .p, all you need is the RUN VALUE(sqlFile) command.

or of course, simply (eg) RUN c:\temp\sqlscript.p.

You can execute this after building and saving the SELECT statement, assuming you are using Progress to build the file.

But like I said, there may be a simpler way, I don't know.

 

[Lee Curzon]

eg. something like:

PROCEDURE executeSQLStatement.

    DEF INPUT PARAMETER cStatement AS CHARACTER FORMAT 'x(255)' NO-UNDO.

    DEF VAR cSQLFile AS CHARACTER NO-UNDO
        INITIAL 'c:\temp\sqlScript.p'.


    OUTPUT TO VALUE(cSQLFile).

    PUT 'OUTPUT TO c:\temp\sqloutput.txt.' SKIP
        cStatement '.' SKIP
        'OUTPUT CLOSE.' SKIP.

    OUTPUT CLOSE.

    RUN VALUE(cSQLFile).

END.


RUN executeSQLStatement ('select * from myTable').

Then do what you want with sqloutput.txt. You will have to wait for it to be generated though.

 

[Casper]

I saw a demo once in which someone wrote a sample java wrapper (using java beans connectivity) from which cystal could directly call an appserver. The appserver then returns the results to crystal in XML format. Pretty need. Would that be a solution to your problem?
There was a nice presentation at ptw 2005:
http://www.progress.com/progress/ptw/2005/emea/docs/ptw_012.ppt


HTH,

Casper.

 

[schaapie]

It works got it to "export the sql-output" and import it in a temp-table which i can show in a browse.

 

[Lee Curzon]

There was a nice presentation at ptw 2005:
http://www.progress.com/progress/ptw/2005/emea/docs/ptw_012.ppt

I've downloaded that some time ago, but never really had time to look at it, as we don't use Crystal here (but may do in the future). Some of these PPTs, including that one, are excellent.