Executing SQL92 in ABL routine

[Drammy]

Hi guys n girls,

Is there a way to execute some SQL92 from within an ABL routine?

 

[Cringer]

The version of SQL in play in ABL depends hugely on your version of Progress, but yes it's possible to do what you want. It will end in pain though. Don't do it

 

[Drammy]

Thanks Cringer,

So I'm talking OpenEdge 10.2 b and above. So how do I go about invoking SQL92?

I'm more than comfortable with SQL so am keen to better understand what the pain will be?

 

[Cringer]

There are a number of issues... You can just put SQL code into ABL AFAIK, but SQL is record set based where as ABL is record based so that will cause you headaches. Also, you may be au fait with SQL, but what about someone else who has to maintain your code? They might not be. Others will no doubt point out more technical issues with your approach, but I would avoid it like the plague.

 

[Rob Fitzpatrick]

Is there a way to execute some SQL92 from within an ABL routine?

That depends on what exactly you mean. Can you use ABL to automate a SQL client? Yes, but it's much less efficient than either writing the same query in native ABL or executing the same query in SQL using a SQL client, without ABL. Can you write SQL-92 statements in-line with ABL code and have it all understood and executed by the ABL compiler? No.

You can't execute SQL statements directly in ABL. The best you can do is use ABL to create a SQL script, then programmatically invoke SQL Explorer with that script as an input file (and, if you wish, consume the output via ABL). It's a kludge.

As with many tasks in programming, the fact that you can do something doesn't mean you should. If you want to write a chat client, you could start by writing your own TCP/IP stack in assembler. But you'd be foolish if you did.

If you are really keen to re-use your SQL knowledge with a Progress database, use a SQL client; don't use ABL, you'll just be making work for yourself.

If you are going to be doing meaningful work in a Progress database then sooner or later you will want to learn ABL. That is, unless someone else is going to be looking after the database and you're just going to be doing queries; in that case use your ODBC/JDBC/eSQL client of choice.

Only you know how you'll be using the database. I know of people who happily work on Progress databases in SQL all day and don't touch ABL. Use whatever tool or language suits your business need. But don't try to fit a square peg in a round hole. Don't write SQL code in an ABL editor (which is SQL-89 run by the 4GL query engine, not SQL-92 run by the SQL engine; also, it's deprecated and will never be enhanced), and don't try to use ABL to automate SQL. You won't be efficient. It's not what experienced Progress professionals do, whether they are SQL veterans or not.

 

[RealHeavyDude]

Even though there is this bad thing like limited SQL89 syntax built into the ABL - don't use it! It will only frustrate you. Plus, bear in mind that the ABL and SQL have completely different concepts on accessing data. The ABL is record oriented while SQL is record set orient. Mixing and matching these two completely different approaches won't produce any decent code.

Why would you want to use SQL syntax within the ABL. Anybody with decent knowledge of the ABL would not advice you too ...

Heavy Regards, RealHeavyDude.