Read/write mysql database

[gjniewenhuijse]

Is it possible to query/insert data into a Mysql database in a LM4?

[admin]

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()

[gjniewenhuijse]

perfect, it works!

[RSV4]

Hi,
are these the correct packages for the LM3 Reactor?
Thanks

[admin]

Yes, you can use them on all devices except for Load Balancer and old LM2.

[RSV4]

Great, thank you!

[gjniewenhuijse]

i have problems using mysql in the latest beta version

[admin]

Yes, older packages will not work. Please install these:
https://dl.openrb.com/lm-16.09-rc2/pkg/z...-1_mxs.ipk
https://dl.openrb.com/lm-16.09-rc2/pkg/l...-1_mxs.ipk
https://dl.openrb.com/lm-16.09-rc2/pkg/l...-4_mxs.ipk

[gjniewenhuijse]

Yes, older packages will not work. Please install these:
https://dl.openrb.com/lm-16.09-rc2/pkg/z...-1_mxs.ipk
https://dl.openrb.com/lm-16.09-rc2/pkg/l...-1_mxs.ipk
https://dl.openrb.com/lm-16.09-rc2/pkg/l...-4_mxs.ipk

thanks, it works.

[g2g2g2]

Hey, how can i write from my LM to mysql?
docs - error 404!

sorry for my bad english...

[gjniewenhuijse]

Hey, how can i write from my LM to mysql?
docs - error 404!

sorry for my bad english...

require('luasql.mysql')
env = luasql.mysql()
dbcon = env:connect('db', 'user', 'password', 'host', 'port')
if dbcon then
  cursor = dbcon:execute( 'your sql statement' ) 
  dbcon:close()
  env:close()
end

[g2g2g2]

hey.. it not work!
i have LM4 reactor lite
all lib install successful

here is my script

require("luasql.mysql");
env = assert(luasql.mysql());
log(env); -- // answer is "MySQL environment (0xb6ed5fa0)"
dbcon = env:connect('base_test', 'root','pass', '192.168.1.40');
log(dbcon); -- // answer is "nil"
cursor = dbcon:execute ( 'INSERT INTO devices (name) VALUES (\"test1\")' );
dbcon:close();
env:close();

[admin]

Try this, it should tell you what's going on. Most probably your user cannot access the database remotely (privileges).

Code:

dbcon, err = env:connect('base_test', 'root','pass', '192.168.1.40')
log(dbcon, err)

[RSV4]

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

The packages are no longer available? 404?

[admin]

All available packages can be found here:
https://dl.openrb.com/lm-16.09/pkg/

[RSV4]

Thank you! Do I need to have the latest firmware for this packages? And where do I see the current firmware the LM is running?

[admin]

These are the same packages that come with the latest release firmware. Some are not installed by default (like MySQL) or are model-dependent. Current version can be seen in LM interface bottom left corner.

[RSV4]

Ok, will update to the latest firmware and then install the MySQL packages.

[RSV4]

Now running FW 20160927, but for all three packages I get the error "Update package file is invalid" when I try to install them (on the start page I go to "Logic Machine", enter my credentials, then go to "Utilities" tab, click "Install updates", click browse, select one of the three files, click "Save", then the "Update package file is invalid" message appears). What am I doing wrong? Thanks!

[Erwin van der Zwart]

Hi,

You follow the wrong steps (:

Packages can be installed by system config -> system -> packages -> action -> + and browse to add package.

Or with script with a os.execute command. (HL and SL can only be done through script as the menu options to install and remove packages are not default enabled in our FW)

BR,

Erwin