From Ape Wiki

Jump to: navigation, search


Contents

[edit] Serverside JS module using MySQL

This sample code shows you how to :

  • Connect your APE module to a MySQL server
  • Register a command to allow users to receive data from a MySQL server

[edit] Server side code

File MySQLDemo.js : 

    function MySQLConnect(ip, user, password, database) {
        var sql = new Ape.MySQL(ip + ":3306", user, password, database);
 
        //onConnect callback
        sql.onConnect = function() {
            Ape.log('You are now connected to MySQL server');
        }
 
        //onError callback
        sql.onError = function(errorNo) {
            Ape.log('Connection error ' + errorNo +' '+ this.errorString());
        }   
 
        return sql;
    }
 
    //connect to MySQL Server
    /**
     * /!\ You must specify a user and password, mysql module does not yet support connecting with a user without password. /!\
     */
    var sql = MySQLConnect('127.0.0.1', 'toor', 'root', 'ape'); //The ''ip'' value must be a valid IP and not a host name.
 
 
    //Set up a poller to send keep alive request each 2minutes
    (function() {
        sql.query('SELECT 1', function(res, errorNo) {
            if (errorNo == 8) { //Something went wrong, connection has been closed
                sql =  MySQLConnect('127.0.0.1', 'root', 'toor', 'ape'); //Reconnect to MySQL Server
            }
        }.bind(this));
    }).periodical(1000*60*2);
 
    //Register getInfo command
    Ape.registerCmd('getInfo', true, function(params, cmd) {
        //Get data from MySQL table
        sql.query('SELECT age, city FROM users WHERE user = "' + Ape.MySQL.escape(params.user) + '" LIMIT 1', function(res, errorNo) {
             if (errorNo) {
                Ape.log('Request error : ' + errorNo + ' : '+ this.errorString());
                return ['101', 'MYSQL_ERROR'];
            } else {
                //Display to logs data received from mysql
                Ape.log('Fetching ' + res.length + ' result(s)');
 
                Ape.log('- Age : ' + res[0].age + '\n- City : ' + res[0].city);
 
                cmd.sendResponse('info', res[0]); //Send first result to client
             }
        });
    })

Copy this code in a file named MySQLDemo.js and put it into APE's scripts directory.


Now edit main.ape.js and add : 

include('MySQLDemo.js');

Remember to restart your APE server after changing the main.ape.js file.

[edit] Client side code (HTML) 

<!DOCTYPE html PUBLIC "-//W4C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" dir="ltr" lang="en">
<head>
<script type="text/javaScript" src="Clients/JavaScript.js"></script>
<script type="text/javaScript" src="Demos/config.js"></script>
</head>
<body>
<script>
        var client = new APE.Client();
 
        //Load APE client
        client.load();
 
        client.addEvent('load', function() {
            client.core.start({'name': '' + Date.now() + ''});//Start with a random name
        });
 
        client.addEvent('ready', function() {
            //send getInfo command
            client.core.request.send('getInfo', {'user': 'efyx'});
 
            //////////////////////////////////////
	    // EDIT: by shaggy [05-Jul-2011]: 
	    //  Cannot call variable 'params' not defined yet, commented out.
 	    //  This will work after the "onRaw info callback".
            //      console.log('efyx: '+params.data.age+'-'+params.data.city);
            //////////////////////////////////////       
 
            //onRaw info callback
            client.onRaw('info', function(params) {
 
                    //////////////////////////////////////
	            // EDIT: by shaggy [05-Jul-2011]: 
	            //  Variable 'params' can now be accessed.
                        console.log('efyx: '+params.data.age+'-'+params.data.city);
                    //////////////////////////////////////   
 
                console.log(params);//Log params in JavaScript Debugger's console
            });
        });
</script>
</body>
</html>

[edit] MySQL Table structure and content 

--
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `user` VARCHAR(32) NOT NULL,
  `age` INT(11) NOT NULL,
  `city` VARCHAR(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`user`, `age`, `city`) VALUES
('efyx', 24, 'Montpellier'),
('korri', 20, 'Montpellier');

[edit] Common issues

  • If after you execute the server side script, you receive Ape.MySQL is not a constructor this means you did not compiled APE Server with MySQL support. Try to install MySQL header file and run build.sh again (on debian apt-get install libmysqlclient-dev).
  • Ape.MySQL requires a user and a password. It is not possible to use a user without a password.
  • Selecting an empty field in MySQL might cause a bug. Avoid this.
  • The ip value passed to MySQLConnect MUST be a valid IP address and not a host name. A host name will not work.
Retrieved from "http://www.ape-project.org/wiki/index.php/Serverside_js_module_using_MySQL"