Progress to SQL Data Push

[Phillip]

Our company runs our database off of Progress 9.1 and we own a website that is run remotely off of a SQL server. What I would like to do is query information from our Progress database and push the value to a matching record in the SQL database. For Example:

Find Customers with Credit Terms --> Find matching record in the SQL database --> Update the column value in the SQL database to the value on our local server

Can this be done? I know that I can run OS-Commands through progress to run in command prompt but can I send scripts to a sql server? I would basically just be running commands similar to the following:

Find the record to make sure exists: SELECT * FROM ____ WHERE = co.contact

update the record: case where ([email] = ____), [Terms] = '2'

Please excuse any errors in the SQL commands. It's been a few months since I worked with SQL on a coding level so I'm getting back into it. Thank you.

 

[jongpau]

Depending on what tech the website and SQL db run on, your ability to add functionality to it and available time & budget you could consider to write a (secured) web service that you can call from within a Progress app. Progress v9 supports socket connections so you can connect to the web server and call a web service.

The problem you may encounter is lag in program execution (internet connections being relatively slow) and there is always a risk of the connection going down or the web server being unavailable. Because of this it would be best to go for some queuing mechanism that sends the messages out separately from your application.

As for the idea of scripts, I suppose that could work (far less reliably) with some FTP magic and a file monitor process on the web server that processes files as they come in. I certainly wouldn't choose such an option unless these was no other way.

I am sure there will be other options, but I thought I'd throw some ideas around.