SQL Queries Using OptoDataLink

PAC Project
1

Can I query a SQL database using OptoScript and the ODL? I want the capabilities of a relational database for my project which is why I’m asking. I want to be able to query the database using SQL, get the data back, and read it into a table.

Thanks,

Dave

2

Dave, Do you have PAC Project Pro or OptoDataLink installed on your PC?
If so, check out document 1705 (OptoDataLink User Guide).
You will find a lot of help there.

3

I do, and I have looked over the ODL User Guide. It tells me all about manually connecting to the database but my question is can I send SQL queries using OptoScript either in MySQL or SQL Server.

Thanks,

Dave

4

Wouldn’t that be a good use of the Restful API? The database connection gives you a spot for the query

5

Thanks for pointing me to the REST interface. I think it will do exactly what I needed.

Dave

6

So I’ve spent some time looking over the site and I can’t find any reference to accessing a SQL database. When I say that, I mean I want to execute a simple SQL query like “SELECT * FROM PARTS_TBL” from within a strategy. I found a couple of tech notes, one for Excel and one for MS Access but not for MySQL or SQL Server. I looked at the REST API docs and there’s no mention there either.

Is this documented somewhere and can someone point me to it?

Thanks,

Dave

7

Dave - Did you ever figure this one out mate?
We are currently using Node Red to handle our SQL queries between PAC and GROOV but it seems a little flaky. I don’t think it’s the right thing to do.

8

I have used ODL ever since it was released and find Node Red much more reliable. On critical systems I actually use both writing to differnt DB’s, from different machines. This is unfortunate, but neither one has been totally reliable for critical data. I am not blaming the application here, sometimes Windows just loves to crash and there goes ODL… I don’t think ODL was ever intended to pass queries at the user configuration level.

9

My experience has been that the query needs to be created in the SQL database and then ODL needs to target that. The last release of ODL has been more reliable, but yeah, occasionally it just stops for no apparent reason and a reboot is required to get it back going…

10

We gave up on ODL around the time when I first posted this and went to NodeRED. While it certainly wasn’t the most intuitive software I’ve ever used, with a lot of help from the folks on the forum I was able to get it to do what I wanted. Been working ever since.

11

I still haven’t taken the NodeRED dive…

12

cool - we’re also stuck on Node Red for now. IT works, not 90% of the time all the time… but it works with some tweaking. I feel Opto22 lacks a little in this department (not a dig). If there was something more stable within Opto Script a lot could be achieved.