Arduino yun Joomla SQL communications.

Hi there,

Do you Know if is possible, and how, Arduino Yun send and write data on Joomla SQL?

I have no ideas right now.

Thanks in advance for any help....

Best regards,

Davide Marchetto

X subscribing

Sorry for the silly question: I know joomla by name but what is joomla sql?

I want send and write data to Sql installed on joomla.

X iscritto

is your joomla installed by an provider or in your own network ? if it is installed by an provider i think you can not access the joomla database from outside the providers sever.

otherwise if you can acces the database joomla stored his "artikles" in the table jos_content in this table each row is an artikel identified by artikel ID,( you will found them in joomla backend by the artikel) the fields introtext and fulltext includes the content of the artikel. if you update this fields the new content will shown on the artikels webside.

Yes Sql are installed on the provider.

So I have some other options for send and write dafa from-to my website with arduino Yun without any intermediate connections?

you can made your webside on your yun available from the internet and then include yuns side wia iframe or link on your webside

Joomla normally use mysql as db backend, Arduino yun Joomla SQL communications means yun to mysql communications.

  1. Create test db at mysql server for test purpose.
mysql> CREATE DATABASE books;
mysql> USE books;
mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
mysql> SHOW TABLES;
mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
  1. Grant access right for remote client. It might need modify my.cnf.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
  1. Use Lua to insert data
opkg update
opkg install luasql-mysql

nano mysql.lua

#!/usr/bin/lua
require "luasql.mysql"
env = assert (luasql.mysql())
con = assert (env:connect('books', 'root', 'password', '192.168.0.20'))
res = assert (con:execute("INSERT INTO authors (id,name,email) VALUES(2,'lua','lua@gmail.com')"))

Use python script to communication mysql:

Install python-mysql

opkg update
opkg install python-mysql

nano mysql.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys

try:
      con = _mysql.connect('192.168.0.20', 'root', 'password', 'books')
      #con.query("SELECT VERSION()")
      #result = con.use_result()
      #print "MySQL version: %s" %  result.fetch_row()[0]
    con.query("INSERT INTO authors (id,name,email) VALUES(3,'python','python@yahoo.com')")
    
except _mysql.Error, e:
  
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit(1)

finally:
    
      if con:
                con.close()

Use ash script to SQL communication,

install mysql client

opkg update
opkg install mysql-server

nano mysql

#!/bin/ash
mysql --host=192.168.0.20 --user=root --password=password books<< EOF
INSERT INTO authors (id,name,email) VALUES(4,'ash','ash@yahoo.com');
EOF

Use php for mysql communication,

There are three ways to do it:

  1. MySQL shared module
opkg update
opkg install php5-mod-mysql
  1. MySQL Improved Extension
opkg update
opkg install php5-mod-mysqli
  1. PHP Data Objects plus PDO driver for MySQL
opkg update
opkg install php5-mod-pdo
opkg install php5-mod-pdo-mysql

hello sonyyu

thanks for the great posts, i have learned a lot, but i think chetto,want to write into a sql database by an external provider not in a local database on yun.

do you think there is a possibility to do this ?

OttoF: hello sonyyu

thanks for the great posts, i have learned a lot, but i think chetto,want to write into a sql database by an external provider not in a local database on yun.

do you think there is a possibility to do this ?

192.168.0.20 set on an other linux box which setup mysql server for test.

modify my.cnf for communication from not only localhost but also internet, Grant access right for remote client. install opkg install mysql-server really for mysql-client only. I did not start mysql-server at yun. for internet access firewall need open 3306 port.

It depends on what traffic the provider will allow through their firewall.

Many of the very cheapest providers will not allow any access below the Web based Control panels they provide. Some providers will only allow access to the MySQL port from a static IP address. Some other providers don't allow access to the MySQL port but provide SSH access, in which case you can create a tunnel and run the client locally or remotely. Some providers do not advertise SSH or MySQL port access but will provide it unsupported on request - Always worth asking.

Thanks for the replay.

Unfortunately I can not access to my SQL because my provider don't give the grant from outside. If I install on arduino SQL I have problem to manage my dynamic internet IP.

My idea is one FTP connections (old style)

Folder: TEMP_RX ( wryte from arduino to www) RX ( data to be reed from WWW)

TEMP_TX ( WWW write to Arduino) TX ( Arduino reed)

arduino send one TXT file whith one string ( RX (string code, 0000 ( file counter), 2013-12-30, 00 (temperature), .......)

WWW send other file whit thinks to do.

One tasks every minute check new file.

WWW every week delete old file.

I know this solutions are only one triks.

If you have other ideas please feel free to replay.

You need webservice endpoints for Joomla/Mysql which use port 80 (WWW).

Webservice could be XMLRPC, RESTFul API, JSON...

Joomla has few XML-RPC plugins that let you do a few things, like the blogger API interface. or you could create your own XML-RPC/RESTFul API/JSON plugin to do the custom things you want. The Yun side you need wrote webservice client with your favorite language ( very easy).

Hello chetto983

the problem with the Dynamic IP can be to solve on this way ( I did it similar )
write a php skript
ip.php

<?php


$ip = "UNBEKANNT";
    if ($_SERVER["HTTP_CLIENT_IP"])
        $ip = $_SERVER["HTTP_CLIENT_IP"];
    else if($_SERVER["HTTP_X_FORWARDED_FOR"])
        $ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
    else if($_SERVER["REMOTE_ADDR"])
        $ip = $_SERVER["REMOTE_ADDR"];

     //echo "meine ip = ".$ip;

         $fh = fopen("home_ip.txt", "w");
        if($fh==false)
            echo("unable to create file");
        fputs ($fh, $ip);
        fclose ($fh);

?>

store this script in your homepage and call it from
arduino for example each minute.
On this way you can read your home ip from the file
home_ip.txt
on the hompage side by your provider.
open a route from your router at home to the ip from your arduino.
On this way you can connect to your datas at home.

Godo Stuff , I collect all your informations and I will try use SQL on arduino.

Soon I have some working code I will share.

Hello chetto

if you want to use sql you have to install following things

Yun für php und sql

Installieren

opkg update
opkg install …
php5
php5-cgi
php5-cli
php5-mod-sockets, // for php bridge
php5-mod-json // for php bridge
php5-mod-sqlite3
php5-mod-session
zoneinfo-core
zoneinfo-europe

nano ( editor )

edit In File
etc\config\uhttpd
list interpreter “.php=/usr/bin/php-cgi”
/etc/init.d/uhttpd restart

For communication php on yun side and 32u

Download bridge library for yun Php

YunBridge/bridge/php at master · arduino/YunBridge · GitHub

<?php

include_once ("bridgeclient.class.php");

$client = new bridgeclient();

if ($client->get("modus") == "a")
{
      $db=new SQLite3("/www/sd/Aqarium/Aquarium.db");

      $SQL = "select * from sollwertelicht order by Schaltzeit_ts";
      $resultSollwerteLicht = $db->query($SQL);
...............

 $client->put("command",$Senden);