Database Usage

Hello Opto Users! We are running into an implementation problem and could use some ideas. Here is what we need to do:

We have an asphalt plant that might dispense product to 20 different companies who each might have 50 different trucks (so we have a database of 1000 entries). When a particular truck pulls up, we need to be able to quickly search and find this truck from the database and display its particular info (for now lets just say we need the weight of the truck when empty (Tare weight).

We’ve been told that the Opto DataLink (database s/w) does not support querying in this fashion. We are now exploring creating our own software in Java/C++ and interfacing via the Opto API, however it would be great if there was a way to do it within the confines of the current Opto IDE.

Does anyone have any thoughts on how to implement this function described above? Maybe using recipes in some fashion vs. a database?

Hi Maddosc,

Interesting question. Here are a few ideas/questions back at you.

Given that ODL (OptoDataLink) isn’t doing it for you, and you’re not adverse to writing a little of your own software, you might consider accessing your database from your strategy using a little PHP script. We have quite a few customers doing this, even a web page here that gives details, but here’s a high-level picture of the concept:


[The image mentions “insert” but you could pull data from the database using this method too.]

Since you mentioned recipes, and we’re only talking around 1000 or so entries, I wonder:

  1. How often would you add/change a truck? If it's very rarely, you could "hard code" this info into tables in your strategy. Here's a link that gives some tips on: [Initializing a big table](http://www.opto22.com/community/showthread.php?t=450&p=1628&viewfull=1#post1628).
  2. Can you generate a report or something to a text file that your strategy could read? Writing a little code to parse such a file into tables in your strategy wouldn't be too difficult, perhaps easier than creating another piece of customer software.
  3. How fast does this lookup need to be? (And what sort of PAC are you using? SoftPAC might be something to consider here.)

Those are my first thoughts. Lots of possible options!

-OptoMary

P.S.
Here’s info on how there’s lots of memory/file space on your PACs: SNAP PAC Memory Usage Technical Note

Is there any way to call MySQL with SQL queries directly from a PAC Control script?

Preferably avoiding writing PHP code and setting up a always-on web server.

Greetings Nathanspielman,

First up, welcome to the forums!

Like most questions like this, there is more than one answer, and there are always more questions!
So, I hope you are up for a bit of a Q&A, cause this rabbit hole could get pretty deep!

Please note, I don’t mean to offend, but for the sake of those that follow in our wake, I have assumed that the reader of this post knows very little about MySQL. If your a DB wiz, then all this will be trivial and so every ones millage will vary a fair bit.

My short answer to your question is, yes, but it will possibly drive you crazy.

Before I explain, I have to ask, you say that you don’t want to have an always on web server, but surely the computer running MySQL is on all the time… if so… why cant it also run Apache?

I just cant wrap my head around why you would not save yourself a huge amount of code and just run Apache and PHP on the same PC as your DB is running on… both those apps tread pretty light, they are not resource hogs…
Anyway, I look forward to your answer on that one… In the mean time, lets take a look at your question.

The easiest way to do this is with SQL.
Its a security hole, but you can set up SQL to accept and return queries from URL’s.
Here is an example of how to do that;
http://msdn.microsoft.com/en-us/library/aa226553(v=sql.80).aspx
So, you would simply make a string in OptoScript, open a comm handle to the PC, squirt your string (aka, the URL) out that comm handle and receive the characters that come back.
They will be in the XML format, so you can simply ‘FindString’ and parse it out into your desired data.
Pretty simple, pretty clean.

But. You said MySQL, not SQL.

To the best of my Google abilities, MySQL does not have the same feature. It used to, but it’s just too big of a security hole, so they took it out. (A fair few versions ago it would seem).
Now days, its trivial to hit MySQL with some PHP to do all the heavy lifting for you.
There is a whole gory forum post on the topic of getting PAC data into MySQL (and out if you reverse the process) here;
http://www.opto22.com/community/showthread.php?t=380

The beauty of this method is that the heavy lifting is not done by the PAC, as you can see the OptoScript to push the data into the DB is trivial. The reason for this is because the PHP is doing the actual pushing of the the data.

So, if you are still with me, I’m sure your looking for the ‘yes’ part of the first answer…
Take a deep breath, here we go…

First, you have to set up your MySQL to allow remote access.
Here are two walk-through’s that should get you started.
http://www.rackspace.com/knowledge_center/article/mysql-connect-to-your-database-remotely
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
Before you get all remote access crazy, you might like to look over this and have a think;

Ok, lets say that its intranet only, and you feel comfortable turning on remote access, you can now, from your computer, running Putty (or terminal if its Linux), connect to your MySQL database…

In a nut shell, all you have to do now, is program some OptoScript to emulate that connection and data exchange.
(Here is where you start to go crazy).
Spend some time at the MySQL command line, get it know, get to know it in your sleep, your going to be dreaming about it.

Everything you type in, you will need to write OptoScript for, every response you will need to write OptoScript for.
For example, a typical select statement, you are going to open a comm handle on port 3306 (The port to get the MySQL prompt), log in, parse the response, transmit the password, parse the response to ensure you are logged in, once your in, the select statement looks like this; [LEFT]mysql -e “select people, places from things”[/LEFT]
The response will come back as a string, but its laid out for a human viewing it on a terminal, so it looks like this;
±-------±------------+| people | places |±-------±------------+| Bill | Raleigh, NC |±-------±------------+

This is going to be pretty ugly to parse, doable, but it would be better if it was a little cleaner.
This post here shows you how to craft your select statement to get a more usable response;

Sure, the select statement is a little ugly, but its easy to build in OptoScript (cut and paste).

If you just want to do an insert, here is the command line syntax for that;
INSERT INTO tablename (col1, col2) VALUES(‘data1’, ‘data2’ )

This will be pretty straight forward, and honestly, if you only want to inserts and the odd select, then it might not be too bad.
So my question is, how much inserting and selecting do you need to do?
Do you even need to do any selecting, or is it just all inserts?

So, yeah, you can do it. Depending on what you need to do will depend on how much sleep you will lose.
Honestly, if it were me, and I could not install PHP on the MySQL PC, I would buy a RaspberryPi and build a headless LAMP server on it and use that as the middle man between my PAC Controller and the MySQL server and get some peacefully sleep… but that’s just me.

Cheers,

Ben.

Hey Ben,

Wow I really should have replied before you posted. Everything you just said I had found out and completely agree with. It is much easier just to set up a web server and run some PHP scripts. Specifically, I’m using the WAMP stack as my server with the scripts and the MySQL database. It works wonders and as long one does it right, you really don’t have to write that much PHP code to do this.

Thanks Ben and Mary!

I want to echo Nathan’s “thank you” (he is the guy actually coding this up for us). I also (once again) want to express my respect for the Opto22 Tech support team. It seems whenever we hit a wall someone is there to help us break through it. Thanks Opto22!

So can I do a select like:

SELECT col1, col2, FROM tablename;

Not sure if my syntax is completely correct but you get the idea. Just a simple query.

Thanks

Dave