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 [I]also [/I]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;
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;
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.
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;
[B]INSERT INTO tablename (col1, col2) VALUES(‘data1’, ‘data2’ )[/B]
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.