Read/write mysql database

[RSV4]

Many thanks Erwin, it worked!

Just curious: what do you mean with HL & SL? I do not understand unfortunately...

[Erwin van der Zwart]

Hi,

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

BR,

Erwin

[RSV4]

Hi Erwin,
thank you for clarifying!
Best regards,
Robin

[RSV4]

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

[admin]

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 .. '")')

[RSV4]

Thank you very much, works like a charm!!!

[FatMax]

Erwin,

Could you please paste the exact os.execute command to install packages on a SL?

[Erwin van der Zwart]

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

[yeuwoo]

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.

[admin]

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/

[Thomas]

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?

[Carlos Padilla]

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

[Carlos Padilla]

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

[admin]

For your own data you should use storage instead of using internal database.

[Carlos Padilla]

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?

[admin]

This is for external MySQL server, connection parameters are pretty much the same as for other languages.

[Carlos Padilla]

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

[pentadom]

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!

[Daniel]

Check here
https://dl.openrb.com/lm-19-imx6/pkg/

[pentadom]

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.