There is several API already provided with haXe. One of the useful ones enable you to access MySQL databases very easily.

Connection to the Database

The first step is to connect to the database. Here's a sample that shows how to establish a connection:

class Test {
    static function main() {
        var cnx = neko.db.Mysql.connect({ 
            host : "localhost",
            port : 3306,
            user : "root",
            pass : "",
            socket : null,
            database : "MyBase"
        });
        // ...
        cnx.close();
    }
}

This will establish a connection on localhost:3306 with user root and empty password. You can also use a socket on Unix systems to connect to the database. If you want to connect using standard way, set socket to null.

Once the connection is established, a Connection object is returned. You can use it to select the database on which you want to operate.

After you're done with using the connection, you can simply close it.

Request and Result

You can execute a request using the cnx.request("QUERY") method. This will return a ResultSet containing all the rows. Here's an example :

var rset = cnx.request("SELECT * FROM Users");
neko.Lib.print("Found "+rset.length+" users");
for( row in rset ) {
    neko.Lib.print("User "+row.name+" is "+row.age+" years old ");
}

If the field you select does not have a name, you can use the get methods from the result set :

var r = cnx.request("SELECT COUNT(*), SUM(money) FROM Users WHERE age >= 18");
neko.Lib.print("Users count = " + r.getIntResult(0));
neko.Lib.print("Total money = " + r.getFloatResult(1));

In the case of an UPDATE request, the length with tell the number of rows modified :

var rset = cnx.request("UPDATE Users SET age = age + 1");
neko.Lib.print("Updated "+rset.length+" users");

Type Conversions

When you execute a request, the rows in the ResultSet are anonymous objects containing table values. The MySQL types are automaticaly converted to the corresponding haXe types :

MySQL         haXe
-------------------------------
TINYINT(1)    Bool
TINYINT       Int
SHORT         Int
LONG          Int
INT24         Int
LONGLONG      Float
DECIMAL       Float
FLOAT         Float
DOUBLE        Float
DATE          Date
DATETIME      Date
BLOB          String*
other         String

(*) : can contain \0 character

Escaping

If you want to use Strings inside your SQL requests, don't forget to escape them so they can't be forged to execute additional SQL. For example :

var r = cnx.request("SELECT * FROM Users WHERE name = '"+cnx.escape(name)+"'");

Or by using the quote method :

var r = cnx.request("SELECT * FROM Users WHERE name = "+cnx.quote(name));

Possible problems

Error: Exception : Neko_error(mysql.c(438) : Failed to connect to mysql server : Client does not support authentication protocol requested by server; consider upgrading MySQL client)

Cause: MySQL 4.1 and up uses an authentication protocol based on a password hashing algorithm that is incompatible with older clients.

Solution: Run mysql and login as root.

mysql -uroot -p

Then update your mysql user (change somepassword, someuser, somehost as needed)

UPDATE `mysql`.`user`
SET `password`=OLD_PASSWORD('somepasswod')
WHERE `user`='someuser'
AND `host`='somehost';
flush privileges;

See

See the following API references pages :

• Mysql
• Connection
• ResultSet

You can also read the SPOD: Object-Database Modeling Tutorial for a more highlevel API.