SQLi library

[EK.IceFlake]

To solve the political war between MySQL and SQLite, and to make life easier, I've made the SQLi library. The SQLi library is a cross-database (mysql or sqlite) abstracted library for using either MySQL or SQLite, with minimal differences between them both.
Here is the code to place in sqli.nut: http://pastebin.com/GPJNsdDP (also available as a module: http://pastebin.com/KVA5nENk)
Here is how to use it.
First, you instantiate an instance (this is one of the places where there is a difference between MySQL and SQLite). For example:

Code Select Expand

Inst <- mysqli("localhost:1444", "nub", "t0p532r37", "nubdb");or

Code Select Expand

Inst <- sqlitei("nub.sqli");Now, for doing a query, you'll call Inst.exec (which will throw an error describing what happened if the query fails):

Code Select Expand

Inst.exec("update players set kills = " + arguments + " where lower(name) = '" + player.Name.tolower() + "'");As you can see, this code is prone to SQL injection. However, this can easily be solved like this:

Code Select Expand

Inst.exec("update players set kills = ? where lower(name) = '?'", arguments, player.Name.tolower());You could also do multiple queries in a transaction

Code Select Expand

Inst.transact(["update players set kills = ? where lower(name) = '?'", arguments.tointeger(), player.Name.tolower()], ["update players set score = ? where lower(name) = '?'", arguments, player.Name.tolower()]);When you're doing a select query, this is the format for using its result: result[nRowID]["sColumnName"] (it returns an array which contains tables).
For example:

Code Select Expand

local result = Inst.exec("select * from players where name = 'EK.IceFlake'");
print(result[0]["Kills"]);Note that we do not free the query. This is because the query is automatically freed by the exec function.
You can close the connection using:

Code Select Expand

Inst.close();and create a new connection (which would overwrite the old connection if it is still alive) using:

Code Select Expand

Inst.connect(host, username, password, database);or

Code Select Expand

Inst.connect(path);
Here are some MySQL only functions:
Inst.rowsaffected();
Inst.info();
Inst.use(database);
These functions will throw an error if you try to use them on an SQLite instance.

If you plan to switch to another database later on, then as long as you aren't using the MySQL only functions, it'll be as simple as changing the instantiation line and all connect calls.

You'll need the official MySQL plugin and SLC's SQLite plugin.

Make sure to post your suggestions (or bugs, if any) below.

[EK.IceFlake]

Update 02.23.2017: Added native transaction support.

[KAKAN]

Suggestion: Add some comments detailing the parameters.
It was hard to understand that i_exec part.

[EK.IceFlake]

Suggestion: Add some comments detailing the parameters.
It was hard to understand that i_exec part.

The i_ in it means it's an internal function and you're not supposed to mess with it xD
Anyways, they are the same as for exec but instead of using vargv, you need to pass an explicit array.

[EK.IceFlake]

Update 03.18.2017: Removed redundant variable.