Accessing to SQLite Database
SQLite is a very lightweight SQL database that stores its content into a single file on the hard drive. It means that it's perfectly suited for desktop application or when developing a website.
Connection to the Database
The first step is to create/open the database. Here's a sample that will use the "mybase.db" to store the database content :
class Test { static function main() { var cnx = neko.db.Sqlite.open("mybase.db"); // ... cnx.close(); } }
Please note, if you are using SPOD Macros you will need to replace `neko.db.Sqlite.open(...)` with `sys.db.Sqlite.open(...)` as this will use the newer Connection type.
Once the database is either opened or created, a Connection object is returned. After you're done with using the connection, you can simply close it.
Creating a table
You can execute a request by using the cnx.request("QUERY")
method. You can use this to create a new table for instance :
cnx.request(" CREATE TABLE IF NOT EXISTS User ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, money DOUBLE ) ");
Once the table is created you can use SQL queries to insert some values into it :
cnx.request("INSERT INTO User (name,age,money) VALUES ('John',32,100.45)"); cnx.request("INSERT INTO User (name,age,money) VALUES ('Bob',14,4.50)");
If you close the application, the data will be saved in the database file so next time you restart it will still be available.
Request and Result
When a request is execute, it will return a ResultSet containing all the result rows.
Here's an example :
var rset = cnx.request("SELECT * FROM User"); 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 User 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 User SET age = age + 1"); neko.Lib.print("Updated "+rset.length+" users");
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 rset = cnx.request("SELECT * FROM Users WHERE name = "+cnx.quote(name));
See
The following classes documentation might be helpful :
See also the SPOD: Object-Database Modeling Tutorial for a more highlevel API.