I am interested to be able to fetch data from an internal SQL DB directly from vera (e.g.SQL select function). using Lua script
For a luup.inet.wget function could we use an internal ip address like 192.xxx.xxx.xxx ? In my case I would like to fetch a data from an SQL intranet server.
Internal IP address is no different from any other IP addresses, so you should be able to access it with wget.
How the luup.inet.wget result is displayed on Vera UI
You can make a plugin for this, or you can log the response and watch it there (LuaUPnP.log). The plugin will consist of an input field for the query, a button to send the query, and a variable to store the response and display on the UI.
I attached a simple plugin that sends a message on the serial port and displays the response. You can modify it to do what you want.
Thanks. I will give it a try.
Trying the proposed plugin it seems to me that I’ve missed something. e.x. don’t I have to “require” a socket for a specific internal address ?
What I would like to do is:
open a TCP port
io.open a .txt file
io.read
display the result
close the file
It depends on what protocol the SQL server is using. @mcvflorin is right about luup.inet.wget() if the SQL server speaks HTTP. If the SQL server can be reached over a serial port, then the SimpleSerial plugin will suffice. Otherwise you will have to open the socket yourself, yes.
Some specifics would help. What software is the SQL server running? Do you have a link to the protocol that it uses? How much data do you expect to get back from the query?
As I know I could use an SQL Lua interface for connecting to the SQL. I haven’t tried this, so I try to get an SQL result through a .txt “buffer” file. In such a case the data is just a string of couple of Bytes.
I am talking about MySQL running on an internal server and I would like to make a connection but not using HTTP service.
Thank you, now we’re getting somewhere. Here is the protocol for communicating with a MySQL server over TCP. I stopped reading it after the first section on the handshake. You do not want to be coding this in native Lua.
There are also no command-line MySQL client packages for OpenWrt that you can install onto Vera, so you can’t just shell out of Lua to do your dirty work for you.
I’d say you’ve got two choices. One (which you’ve already thought of): Persevere with making Lua code act as a MySQL client, but use a library that someone else has written to manage all the hard bits. Searching “lua mysql” brings up a few likely candidates. Two: think of another way of getting the data across. For instance, run a small periodic script on some other machine, having it query the MySQL server, put the data into some more-convenient-to-MiOS format, and get your Lua code to talk to the script instead over a simpler protocol.
I’m afraid that I can’t be more specific with answers until you are more specific with your requirements.
Your proposed solutions are very usefull. Attached is the LUA code I’ve tried as a client server model.
The client part establishes a connection with the server part. The server part listens and sends back the .txt data (sensor results) which has been written by an MySQL query process.
The actual Mios plugin should be designed in respect to the Lua client part. How about it?
[quote=“nikos, post:12, topic:168494”]The client part establishes a connection with the server part. The server part listens and sends back the .txt data (sensor results) which has been written by an MySQL query process.
The actual Mios plugin should be designed in respect to the Lua client part. How about it?[/quote]
It’s probably doable. I haven’t done any TCP programming with the Lua socket library, but your code looks plausible. I’m sure you will find examples online that match what you want to do.
The server code isn’t going to run on the Vera, so you can do it in any language you like. Personally I’d use netcat with the -l option to listen on a port.
I use PHP scripts to query the SQL server and format the data in a nice way and make it accessible through HTTP GET/POST. This of course requires you to run a webserver with PHP-extension. Is that an option for you?
Hi all,
@ mikkelj,
I’m interesting in using a php script to send information from my zoneminder. Could you give us (me) an exemple code ?
I discover vera little by little but your solution is very interesting.
If it works, I could use it for my owfs server.