Linux batch file to call SQL

[apsrbstar]

Hi all, I'm looking for some help with writing batch file to call an SQL Query on Linux Red Hat with Progress 10.1 OE. I've struggled to find anything on the forum, but like so many questions, there could be all sorts of keywords, so I hope I'm not duplicating anything.

Basically, we currently run SX.enterprise (I think it used to be Trend, but I'm sure this is a wider question) version 5.6.063, which (I believe) runs on Progress 9.1c or 9.1e. We use Version One DBForms to convert the spool files to readable documents. We are about to install version 5.7.060 (Which runs on Open Edge 10.1) in a test environment, and there is some document related functionality that won't be migrated. Version One advise me that I can call a batch file to run an SQL query.

I guess my questions are:

• Do I need any additional software?
• Would I need to configure anything on Red Hat?
• Would I use normal SQL-92 syntax?
• How would I connect to the database?

If there is any more information that would be useful to help answer these questions, I will endeavour to find it. Of course, I would be very​ grateful for any responses.

 

[TomBascom]

SX.e is the same as Trend, so you got that right.

Progress version 9 is ancient, obsolete and unsupported. You should upgrade. (And with SX.e it is possible. I've done it.)

Assuming that a SQL-92 broker is already running... to script SQL-92 access you use the Progress SQL Explorer tool -- the executable is $DLC/bin/sqlexp.

Sample syntax:

sqlexp -user userName -password passWord -db dnName -S servicePort -infile tmp/sample.sql -outfile /tmp/sample.log

"sample.sql" would contain standard SQL-92 commands.

 

[apsrbstar]

What can I say? Thank you for your quick response. I believe I can work with this information, I've found the executable, if I have any more trouble I know where to come!

As for upgrading Progress, as I say, if we move to SX.e 5.7.060, we'll be on (I'm fairly certain) Open Edge 10.1, so hopefully that's a step in the right direction. Do you feel we should be thinking about an even more recent version?

 

[Cringer]

Version 11 is the latest version (hot off the press), but I would definitely consider moving to 10.2A at the very least, even B.

 

[apsrbstar]

Do you know what? Scrub what I said before, I've just found an email stating that we will be using 10.2B for the latest version of SX.e.

Thank you both very much, I really do appreciate the advice.

 

[Cringer]

Excellent stuff - that will keep Tom happy

 

[apsrbstar]

Well, I ran a really simple query on the command line using a .sql file with some basic SQL, and returned the data to a .log file. That returned the expected data, so that worked a treat.

Now I need to pass a variable into that query somehow. Easy example, I would like to pass an order number in to the SQL to use in the WHERE clause, so I can return the related customer purchase order number.

Is it possible, rather than call a batch file, to run the SQL on the command line itself? I'm imagining something like:

sqlexp -user userName -password passWord -db dnName -S servicePort SELECT custpo FROM oeeh WHERE cono = 1 AND orderno = 123456 -outfile /tmp/sample.log​

Currently, in DBForm, I am doing this:

.../dlc/bin/sqlexp -user ##### -password ##### -db ##### -S ##### -infile .../dlc/bin/sample.sql -outfile .../dlc/bin/sample.log​

and sample.log is populated, so that clearly runs as if it were a command line syntax.


Another pitfall is getting the data out of sample.log, so maybe there's a better way of returning the field data. Currently, sample.log looks like this

custpo​

--------------------------------------------​

abcdefg​

and I just need the abcdefg.

Can anyone offer any guidance on these issues, or am I pushing my luck?

 

[Marian EDU]

Not sure why do you really have to run SQL commands like that as Version One DBForms claims it can connect and fetch data from 'any databases on the network'... now, they do want contact information even for downloading a stupid product brochure and I'm not really in the mood for that but I guess you have some documentation at hand so check how it can connect to those 'any databases' - depending on technology used should have an option for JDBC or ODBC both are available for Progress DB.

 

[apsrbstar]

Thanks, I'll look that up when I'm back in the office. Version One support advised me to attempt it this way.

 

[Marian EDU]

that might be simple because of the 'progress who' syndrome

 

[apsrbstar]

Ah. This will be an 'in' joke that I am unaware of

 

[TomBascom]

"Progress who?" is the perennial problem of nobody ever having heard of Progress. In this case the suspicion is that Version One is telling you to go through an arcane work-around because they have no idea of Progress' capabilities.