This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm that you accept these cookies being set.

Read/write mysql database
#21
Many thanks Erwin, it worked!

Just curious: what do you mean with HL & SL? I do not understand unfortunately...
Reply
#22
Hi,

SL and HL means our homeLYnk and spaceLYnk controllers that are developed by EMBS and Schneider Electric.

BR,

Erwin
Reply
#23
Hi Erwin,
thank you for clarifying!
Best regards,
Robin
Reply
#24
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
Reply
#25
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 .. '")')
Reply
#26
Thank you very much, works like a charm!!!
Reply
#27
Erwin,

Could you please paste the exact os.execute command to install packages on a SL?
Reply
#28
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
Reply
#29
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.
Reply
#30
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/
Reply
#31
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?
LM5Lp, firmware: 2018.08.22 and 2021.12.15, FlashSYS v2, ARMv7 Processor rev 5 (v7l), kernel 4.4.151 and 4.4.259
Reply
#32
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
Reply
#33
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
Reply
#34
For your own data you should use storage instead of using internal database.
Reply
#35
(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...-1_mxs.ipk
https://dl.openrb.com/pkg/liblua-mysql_2.2.0-2_mxs.ipk

Docs:
http://keplerproject.github.io/luasql/do...anual.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?


Reply
#36
This is for external MySQL server, connection parameters are pretty much the same as for other languages.
Reply
#37
(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
Reply
#38
(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!
Reply
#39
Check here
https://dl.openrb.com/lm-19-imx6/pkg/
------------------------------
Ctrl+F5
Reply
#40
(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.
Reply


Forum Jump: