How to use SQL select result in a 4GL procedure

[popsin]

Hi,

Can somebody tell me how to use result of a SQL select statement in a
Progress 4GL procedure?
When I run procedure with SQL statement it shows me results on the
screen but I don't know how to put that records into a temp-table for
instance.
I tried to find something in documentation but no success so far.

Thanks for any advice.

 

[Jupiter]

Hi,

Can somebody tell me how to use result of a SQL select statement in a
Progress 4GL procedure?

If it is really necessary, you may take the output of the SELECT query in a text file and populate a temp table afterwards.

 

[popsin]

Thanks Jupiter,

I got the same idea myself but I thought that there was something less cumbersome than that.

 

[TomBascom]

The correct answer is "don't".

Using SQL-89 within the 4GL is an exercise in frustration. Don't do it. It is unnatural and anti-productive. Use the 4GL, that's what it is there for.

 

[DevTeam]

If your Select clause returns only 1 row (a Select count, for instance), you can use the "INTO" syntax to retrieve data... But as Tom said, don't !

 

[hakane]

Hi Popsin,

As far as I understand you are looking for something like that:


Define Variable iNum As Integer No-undo.
Define Variable cName As Character No-undo.
Define Temp-table ttCust
Field tNum Like Sports2000.Customer.CustNum
Field tName Like Sports2000.Customer.Name.
Declare cCust Cursor For
Select Distinct Custnum,Name From Sports2000.Customer.
Open cCust.
Repeat:
Fetch cCust Into iNum,cName.
Create ttCust.
Assign
ttCust.tName = cName
ttCust.tNum = iNum.
End.
For Each ttCust:
Disp
ttCust.tNum
ttCust.tname.
End.

 

[Casper]

But the real answer was:

The correct answer is "don't".

Using SQL-89 within the 4GL is an exercise in frustration. Don't do it. It is unnatural and anti-productive. Use the 4GL, that's what it is there for.

Casper

 

[hakane]

The question is not which one is better, it is how to put SQL SELECT statement's results into temp table. Yours is "real" suggestion.

 

[Casper]

You are right offcourse.
But had to say it. It is a deprecated feature which in my opinion shouldn't have been there in the first place....

Casper.

 

[tamhas]

Tom's remarks are not just a casual suggestion that there is a better way to accomplish the purpose. SQL-89 is deprecated and never was one of the shining spots in the ABL. I would love for them to provide a way for us to access the SQL-92 engine from within ABL, but until and if this happens, the real answer is that, if you are writing in ABL, use ABL constructs, not SQL. Yes, this is not the answer to the question as asked, but it is the answer to what should be done to address the problem.

 

[popsin]

Thanks everyone for suggestions but I really had to do it that way.
The corresponding 4GL way (FOR EACH...) returned me an error.
So I solved the problem by outputing result of the SQL query into a flat file. Then loading that file into a temp-table.
Looks ugly, but solves my problem
I also tryed with cursor but it returns the same error as FOR EACH...

 

[tamhas]

Perhaps if you posted the FOR EACH code and error, we could fix it for you. SQL->FF->TT is really pretty kludgy.

 

[popsin]

Well the problem is not in the FOR EACH itself. It's fairly easy query like:
FOR EACH some_table
BREAK BY some_field:

Problem is that whenever you reference that table the program returns error:
ORACLE error -24347 see "ORACLE Error Messages and Codes Manual". (1252)
** Warning of a NULL column in an aggregate function

And when I run SQL query it doesn't return that error.
We are upgrading from Progress 9 to Progress 10, and with the old Progress this procedure works.
So I assume it's something between Progress and Oracle.

 

[Casper]

Ah, you failed to tell us that you are using dataserver, not such a small detail :awink:
And looking at the error the query also seems to be a bit more complex then I thought. Do you use any kind of aggregation in the query? If so maybe you could check for null (e.g. unkown) values and wonder if this is what you really want to do.

Casper.

 

[popsin]

It's very strange but it doesn't matter if you use agregation or not.
I tried to run quite a simple program against that table, something like:
FOR EACH table:
DISPLAY key_filed.
END.
And I know for sure that key_field does not contain null values.

As I said, procedure runs perfectly when run from Progress 9 environment but fails from Progress 10. Oracle database is the same in both cases.

I needed a quick fix so the trick with SQL quiry and flat-file works fine for now. I load temp-table form the file and then later in the procedure I just changed the name of the database table with the temp-table name.

 

[TomBascom]

As a work-around for what looks very much like a bug I suppose that it's "ok".

But be careful to make it really clear (perhaps by commenting the code with dire warnings in CAPS!!!) that that is what it is -- these things have a way of becoming "standards" when some innocent programmer clones them for another project...

And don't forget to submit a bug and then go back and remove the work-around once Progress fixes the underlying bug.

IMHO it's something of a miracle that the SQL-89 engine is correctly handling something that the data-server does not. I'm wondering if there might be some sort of option that fixes the problem that you're just unaware of and that TS might be able to point out if they knew what you were really doing (IOW if the actual problem query and the data definitions were known...)

 

[popsin]

Sorry for my ignorance but what does TS stand for?

 

[TomBascom]

Tech Support

 

[2m.lt]

Is this still not quite possible?

For example I can use this statement to select result to a variable if select returns only one row

DEF VAR ch AS CHAR NO-UNDO.
SELECT name INTO ch FROM customer WHERE customer_id = 123

However, it does not seem to be working with temp tables or handles to temp tables.

 

[RealHeavyDude]

I've never tried it. But the embedded SQL-89 was introduced into the language long before Temp-Tables were and has not been enhanced ever since. That would be the explanation as to why it won't work with Temp-Table. Still, I'm curious as to why one would want to use SQL syntax with Temp-Tables in the first place. Don't get me wrong, but I would not recommend anybody to use any SQL embedded in the ABL whatsoever. Instead I would strongly recommend you to not use it. Mixing these up will not only produce bad code, you are also trying to mix two things that won't mix: While SQL is record set oriented, the ABL is purely record orientd and these two approaches don't go well together ...

Heavy Regards, RealHeavyDude.