Lua [LuaSQL] The basics of LuaSQL!

[Jonern]

Small LuaSQL tutorial made by Jonern.

This is just a little example of how to use LuaSQL in The Forgotten Server (0.2.5 and up)

First, you need to create an environment object. This is done like this:

env = assert(luasql.mysql()) -- change mysql to sqlite3 if you use SQLite

Now you need to connect to the database. This is done like this:

con = assert(env:connect("databaseName", "user", "password", "host OR ip", "port")) -- if port is 3306, you dont need that part.

Now that the connection is done, you can start doing queries.

Lets say we want to show in a window wich players that is over level 100 (to make it simple).
Then we need to do a query to the database. And we need to put the data together a way we can use it.

cur = assert(con:execute("SELECT `name`, `level` FROM `players` WHERE `level` >= 100;")) -- This is how you execute queries. You should give them the variable name "cur" as in "current".
row = cur:fetch({}, "a") -- {} is where to store the result, in this case it's making a table of the variable 'row'. "a" means that the rows will be indexed by field names.
while row do
    str = str .. "\n" .. row.name .. " [" .. row.level .. "]" -- this is to put the lines together into one string.
    -- reusing the table of results
    row = cur:fetch (row, "a")
end
doPlayerPopupFYI(cid, "List of players level 100 or higher:\n\n" .. str)

So let's put it all together, but first, we would like to get the database name, user, pw and so on from config.lua. This is quite easily done, just add in the beginning of the script:

dofile("./config.lua")

Lets put it all together:

function onSay(cid, words, param)
    dofile("./config.lua")
    env = assert(luasql.mysql())
    con = assert(env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost, mysqlPort))
    cur = assert(con:execute("SELECT `name`, `level` FROM `players` WHERE `level` >= 100;"))
    row = cur:fetch({}, "a")
    local str = ""
    while row do -- a loop to go trough all rows.
        str = str .. "\n" .. row.name .. " [" .. row.level .. "]"
        -- reusing the table of results
        row = cur:fetch (row, "a")
    end
    doPlayerPopupFYI(cid, "List of players level 100 or higher:\n\n" .. str) -- Opens a window and lists the players
end

This script is tested, and works, but is just a simple example.
There are also other functions to use in LuaSQL such as cur:numrows(). Look here for more functions and information.

Don't forget to give REP! (My first tutorial ^_^)

 

[Master-m]

Thnx man I think this is a very usefull tutorial

 

[kofel]

Thanks Jonern, I give you rep.

 

[Jonern]

Thank you!

 

[Avarian]

thanks for the tut man

 

[NorbisS]

Thanks for tutorial! I'm going to start scripting my server by LuaSQL. It's very useful

 

[Jonern]

It sure is useful! You get a lot more things that you can do

 

[Royalkux]

Usefull thanks

 

[Seifer]

Very thanks.

 

[Mokerhamer]

it looks great!, but why dident u explain what the functions do first..

 

[Jonern]

I am doing that?

Or are you thinking of that I should be a bit more precise in the third "code box"?

 

[x0r]

Jonern, Could you release a small tut to use LuaSQL in SQLite? I Prefer SQLite

 

[Jonern]

It would be the same way to use it, just look what I added in a comment in the first post.

env = assert(luasql.sqlite3())
con = assert(env:connect(sqliteDatabase))Thats all that needs to be changed.

Notice that the bolded part is a variable containing the SQLite dbfile in a string. If you just change those two lines in the script, the dofile command will load the variables from config.lua (and you won't have to change the bolded part).

 

[Mokerhamer]

It would be the same way to use it, just look what I added in a comment in the first post.

env = assert(luasql.sqlite3())
con = assert(env:connect(sqliteDatabase))Thats all that needs to be changed.

Notice that the bolded part is a variable containing the SQLite dbfile in a string. If you just change those two lines in the script, the dofile command will load the variables from config.lua (and you won't have to change the bolded part).

That's what i ment anywais very good tut

 

[x0r]

It would be the same way to use it, just look what I added in a comment in the first post.

env = assert(luasql.sqlite3())
con = assert(env:connect(sqliteDatabase))Thats all that needs to be changed.

Notice that the bolded part is a variable containing the SQLite dbfile in a string. If you just change those two lines in the script, the dofile command will load the variables from config.lua (and you won't have to change the bolded part).

Ahhh Thanks, il try it out once im home ;p
23 minutes left of I-Media, The past 1 hour and 30 minutes have been SOOOOO boring x.x

 

[ntmr]

Thanks!

It will help me a lot on my scripts

 

[Jonern]

Thank you all for good feedback!

 

[Master Exploder]

I prefer normal lua Im so used to it.

 

[Lithium]

very usefull thanks

 

[Jonern]

I prefer normal lua Im so used to it.

This is normal Lua. It's just that you can extend your usage of Lua and make it even more usable

Don't hesitate to ask if you didn't understand something or to give me REP!