+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Read/write mysql database (/showthread.php?tid=193)
RE: Read/write mysql database - RSV4 - 18.12.2016
Many thanks Erwin, it worked!
Just curious: what do you mean with HL & SL? I do not understand unfortunately...
RE: Read/write mysql database - Erwin van der Zwart - 18.12.2016
Hi,
SL and HL means our homeLYnk and spaceLYnk controllers that are developed by EMBS and Schneider Electric.
BR,
Erwin
RE: Read/write mysql database - RSV4 - 18.12.2016
Hi Erwin,
thank you for clarifying!
Best regards,
Robin
RE: Read/write mysql database - RSV4 - 11.01.2017
Hi all,
I've got my connection working and am successfully writing data to my database on the server. However, I cannot write to a MySQL field of the type "datetime". It should have the format "YYYY-MM-DD HH:MM:SS".
I am doing this and getting an error:
Code:
-- create timestamp
curdatetime = os.date("%Y-%m-%d %H:%M:%S")
-- write to database
cursor = dbcon:execute('INSERT INTO sensor_data (LAST_UPDATE) VALUES (STR_TO_DATE(' .. curdatetime .. ',"%Y-%m-%d %h:%i:%s"))')Sorry for asking MySQL related questions here which actually have nothing to do with the LogicMacine itself, but I hope some expert here can tell me where the error in the above code is.
Many thanks and best regards
RE: Read/write mysql database - admin - 12.01.2017
You don't need STR_TO_DATE, date value must be a string. This should work, note the double quotes for date value.
Code:
dbcon:execute('INSERT INTO sensor_data (LAST_UPDATE) VALUES ("' .. curdatetime .. '")')RE: Read/write mysql database - RSV4 - 12.01.2017
Thank you very much, works like a charm!!!
RE: Read/write mysql database - FatMax - 17.02.2017
Erwin,
Could you please paste the exact os.execute command to install packages on a SL?
RE: Read/write mysql database - Erwin van der Zwart - 17.02.2017
Hi,
Sorry can't help you on this one..
We have the policy to test and validate the firmware and packages in our lab before we use them in our HL/SL.
If you install packages that are not tested and validated by our lab (with os.execute) we can't guarantee a correct working controller.
For this reason i won't be able to share the requested information, sorry..
BR,
Erwin
RE: Read/write mysql database - yeuwoo - 14.03.2017
Hi,
Wondering if anyone has succeeded in interfacing with Microsoft SQL server 2008?
Tried the MySQL script and it worked with no problems.
Wondering if anyone has tried something similar with Microsoft SQL server.
RE: Read/write mysql database - admin - 14.03.2017
You can try using ODBC, though I'm not sure if anybody tried it before.
Packages can be found here: https://dl.openrb.com/lm-16.09/pkg/
RE: Read/write mysql database - Thomas - 14.03.2017
Hi
In the LM -> DB direction I use SOAP call. My DB is SAP SQL Anywhere (former Sybase). As I know MSSQL supports SOAP calls too. Wouldn't it be a way for you in case you just want inform your database about a group object change?
RE: Read/write mysql database - Carlos Padilla - 04.09.2017
Hi Edgar, where do you get the selection from my table? How do you know that you selected that table? Where do you think the table? please
RE: Read/write mysql database - Carlos Padilla - 05.09.2017
How do I call that table, like you did? in the query select * from mytable ?, if it is an example how I can actually do it, which table should I select or how do I create a table and do I create it, please wait for your answer
RE: Read/write mysql database - admin - 05.09.2017
For your own data you should use storage instead of using internal database.
RE: Read/write mysql database - Carlos Padilla - 07.09.2017
(14.01.2016, 10:03)admin Wrote: Install packages:
https://dl.openrb.com/pkg/zlib_1.2.7-1_mxs.ipk
https://dl.openrb.com/pkg/libmysqlclient_5.1.73-1_mxs.ipk
https://dl.openrb.com/pkg/liblua-mysql_2.2.0-2_mxs.ipk
Docs:
http://keplerproject.github.io/luasql/doc/us/manual.html
Short example, don't use db as a variable name or you will mess up LM internal database connection
Code:require('luasql.mysql')
env = luasql.mysql()
dbcon = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)
cursor = dbcon:execute('SELECT * FROM mytable')
while true do
row = cursor:fetch({}, 'a')
if row then
log(row)
else
break
end
end
cursor:close()
dbcon:close()
env:close()
Hi a question
as that table select as in your case "select * from mytable"
where do you get
the user, password, dbname, server and port for function connect?
RE: Read/write mysql database - admin - 07.09.2017
This is for external MySQL server, connection parameters are pretty much the same as for other languages.
RE: Read/write mysql database - Carlos Padilla - 07.09.2017
(07.09.2017, 14:47)admin Wrote: This is for external MySQL server, connection parameters are pretty much the same as for other languages.
that means that you use a MySQL database to call it and you put the parameters of that database in logic machine
RE: Read/write mysql database - pentadom - 18.11.2019
(07.09.2017, 14:47)admin Wrote: This is for external MySQL server, connection parameters are pretty much the same as for other languages.
Hello,
I have a LM5 lite. I want to register my trend logs into a SQL database. I can do it with the LuaSQL packages? where can I download it? the links seems to be broken.
Thanks!
RE: Read/write mysql database - Daniel - 19.11.2019
Check here
https://dl.openrb.com/lm-19-imx6/pkg/
RE: Read/write mysql database - pentadom - 19.11.2019
(19.11.2019, 08:28)Daniel. Wrote: Check here
https://dl.openrb.com/lm-19-imx6/pkg/
Thanks Daniel!
Which code I need If want to register the LM trends in a SQL database?
Sorry but I'm a KNX integrator without no knowledge of LUA. I would be very grateful if someone can help me.