[TUT] SQLx - the newest AMXX SQL driver set (advantages and usage)

[`Rοmeο]

Before we begin, it is important to understand that this tutorial assumes you know the basics about Pawn (syntactically and the implementation of it in AMXX). You should be at least an intermediate level scripter, and have knowledge of SQL (the language itself) and perhaps DBI (the now outdated and deprecated, although still functional SQL driver that was used in AMXX for a time). I will do small sections explaining the SQL code itself, but for the most part you should know the basics.

 

SQLx is one of the new SQL driver sets and APIs that were incorporated into AMXX 1.75 (?) and made publicly available shortly after the release of AMXX 1.70. The important new features include the ability to thread queries (to save gameplay interruption, the queries to the database are dispatched to a new thread and then a callback is made when the query is completed or fails), better API abstraction (easier to write plugins with and more understandable), connection "tuples" (a system used to store information about a database but not connect to it - very useful for writing APIs where a connection should be allowed to be independent across differen plugins), and the ability to load multiple modules (can load MySQL, SQLite and any other drivers).

 

The most important thing to understand about SQLx is that the entire system works with "Handles", which store all information such as a connection, tuple, result and prepared query. This makes it very simple to both screw up (trying to connect to a query's result, due to lack of tag differentiation, for example) but at the same time makes things a lot simpler - for example, a connection is dropped by running SQL_FreeHandle, the same native used to free a result, free a prepared query and free a database tuple.

 

Let's look at a comparison between DBI and SQLx:

 

DBI:

 

 

#include <amxmodx>
#include <amxmisc>
#include <dbi>

new Sql:g_SqlConnection
new g_Error[512]

public plugin_init()
{
    register_plugin("DBI Demonstration","1.0","Hawk552")
   
    new Host[64],User[64],Pass[64],Db[64]
    // let's fetch the cvars we will use to connect
    // no pcvars because we're only fetching them once
    get_cvar_string("amx_sql_host",Host,63)
    get_cvar_string("amx_sql_user",User,63)
    get_cvar_string("amx_sql_pass",Pass,63)
    get_cvar_string("amx_sql_db",Db,63)
   
    // ok, we're ready to connect
    g_SqlConnection = dbi_connect(Host,User,Pass,Db,g_Error,511)
    // indicates that the connection failed (it's either SQL_OK or SQL_FAILED)
    if(g_SqlConnection < SQL_OK)
        // stop the plugin with an error message
        set_fail_state(g_Error)
       
    new Result:Results[3]
   
    // run some queries
    if((Results[0] = dbi_query(g_SqlConnection,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")) < RESULT_OK || (Results[1] = dbi_query(g_SqlConnection,"INSERT INTO zomg VALUES('1','2','3','4')")) < RESULT_OK || (Results[2] = dbi_query(g_SqlConnection,"INSERT INTO zomg VALUES('4','3','2','1')")) < RESULT_OK)
    {
        // fetch any error if there were problems
        dbi_error(g_SqlConnection,g_Error,511)
        // stop the plugin
        set_fail_state(g_Error)
    }
   
    // free the result handles
    for(new Count;Count < 3;Count++)
        dbi_free_result(Results[Count])
}

public client_disconnect(id)
{   
    // run a random query
    new Result:QueryResult = dbi_query(g_SqlConnection,"SELECT * FROM zomg WHERE you='1' OR you='4'")
   
    // query failed - database probably closed since the last time we used it
    if(!QueryResult)
    {
        dbi_error(g_SqlConnection,g_Error,511)
        set_fail_state(g_Error)
    }
   
    new Data
    // notice here that we run the dbi_nextrow first - one of the main reasons I don't like DBI.
    // you MUST do this, it doesn't start at the first row
    while(dbi_nextrow(Result))
    {
        // get the row
        // note that we could also use dbi_result(Result,"you")
        Data = dbi_field(Result,1)
        // tell the server console we have found data
        server_print("zomg, we have data: %d",Data)
    }
}

public plugin_end()
    // close the connection
    dbi_close(g_SqlConnection)

 

SQLx:

 

#include <amxmodx>
#include <amxmisc>
#include <sqlx>

new Handle:g_SqlTuple
new g_Error[512]

As you can see, there aren't too many differences when not using threaded querying (which, in case you were wondering, I did not include in this example).

Unfortunately, none of this demonstrates the true power of SQLx. The only thing it showcases really is the Handle: and SQL_FreeHandle system.

The true power lies in threaded querying. Threaded querying means nothing short of a rewrite of an entire plugin that's written with DBI, but has huge advantages, especially on slow connections. The idea of a threaded query is essentially that a new thread (a sub-process) with which its only goal is to send, monitor, and inform of the things that happen to a query. Because it is on a seperate thread, a query that would normally take 1 second (and 1 second of total connection loss for all clients in the server) still takes the same a-beep-t of time, but does not interrupt gameplay.

What are the disadvantages? As I wrote, it means basically a total rewrite, and a hell of a lot more code. When a threaded query is completed, it calls a function (that is declared in the prototype of SQL_ThreadQuery) that handles the result of the query. It could be any a-beep-t of time after this query is sent, meaning time sensitive events would be nearly impossible to work with.

Here's an example in action:

[code]#include <amxmodx>
#include <amxmisc>
#include <sqlx>

new Handle:g_SqlTuple
new g_Cache[512]

public plugin_init()
{
    register_plugin("SQLx Demonstration","1.0","Hawk552")
   
    new Host[64],User[64],Pass[64],Db[64]
    // let's fetch the cvars we will use to connect
    // no pcvars because we're only fetching them once
    get_cvar_string("amx_sql_host",Host,63)
    get_cvar_string("amx_sql_user",User,63)
    get_cvar_string("amx_sql_pass",Pass,63)
    get_cvar_string("amx_sql_db",Db,63)
   
    // we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    copy(g_Cache,511,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")
    SQL_ThreadQuery(g_SqlTuple,"TableHandle",g_Cache)
}

public TableHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    // lots of error checking
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
       
    SQL_ThreadQuery(g_SqlTuple,"QueryHandle","INSERT INTO zomg VALUES('1','2','3','4')")
    SQL_ThreadQuery(g_SqlTuple,"QueryHandle","INSERT INTO zomg VALUES('4','3','2','1')")
   
    // notice that we didn't free the query - you don't have to
   
    return PLUGIN_CONTINUE
}

public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    // lots of error checking
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
   
    return PLUGIN_CONTINUE
}

public client_disconnect(id)
    SQL_ThreadQuery(g_SqlTuple,"SelectHandle","SELECT * FROM zomg WHERE you='1' OR you='4'")

public SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
   
    new DataNum
    while(SQL_MoreResults(Query))
    {
        DataNum = SQL_ReadResult(Query,0)
       
        server_print("zomg, some data: %d",DataNum)
    
        SQL_NextRow(Query)
    }
   
    return PLUGIN_CONTINUE
}

public plugin_end()
    // free the tuple - note that this does not close the connection,
    // since it wasn't connected in the first place
    SQL_FreeHandle(g_SqlTuple)
public plugin_init()
{
    register_plugin("SQLx Demonstration","1.0","Hawk552")
   
    new Host[64],User[64],Pass[64],Db[64]
    // let's fetch the cvars we will use to connect
    // no pcvars because we're only fetching them once
    get_cvar_string("amx_sql_host",Host,63)
    get_cvar_string("amx_sql_user",User,63)
    get_cvar_string("amx_sql_pass",Pass,63)
    get_cvar_string("amx_sql_db",Db,63)
   
    // we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    // ok, we're ready to connect
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(SqlConnection == Empty_Handle)
        // stop the plugin with an error message
        set_fail_state(g_Error)
       
    new Handle:Queries[3]
    // we must now prepare some random queries
    Queries[0] = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS zomg (you INT(11),are INT(11),a INT(11),noob INT(11))")
    Queries[1] = SQL_PrepareQuery(SqlConnection,"INSERT INTO zomg VALUES('1','2','3','4')")
    Queries[2] = SQL_PrepareQuery(SqlConnection,"INSERT INTO zomg VALUES('4','3','2','1')")
   
    for(new Count;Count < 3;Count++)
    {
        // run the queries, check if they were alright
        // note that you can run the same query multiple times
        // we are not doing this here, but it's nice to have
        if(!SQL_Execute(Queries[Count]))
        {
            // if there were any problems
            SQL_QueryError(Queries[Count],g_Error,511)
            set_fail_state(g_Error)
        }
       
        // close the handle
        SQL_FreeHandle(Queries[Count])
    }
   
    // you free everything with SQL_FreeHandle
    SQL_FreeHandle(SqlConnection)   
}

public client_disconnect(id)
{   
    // ok, we're ready to connect
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)
    if(SqlConnection == Empty_Handle)
        // stop the plugin with an error message
        set_fail_state(g_Error)
   
    // run a random query
    new Handle:Query = SQL_PrepareQuery(SqlConnection,"SELECT * FROM zomg WHERE you='1' OR you='4'")
   
    // run the query
    if(!SQL_Execute(Query))
    {
        // if there were any problems
        SQL_QueryError(Query,g_Error,511)
        set_fail_state(g_Error)
    }
   
    // checks to make sure there's more results
    // notice that it starts at the first row, rather than null
    new Data
    while(SQL_MoreResults(Query))
    {
        // columns start at 0
        Data = SQL_ReadResult(Query,0)
       
        server_print("Found data: %d",Data)

        SQL_NextRow(Query)
    }
   
    // of course, free the handle
    SQL_FreeHandle(Query)
   
    // and of course, free the connection
    SQL_FreeHandle(SqlConnection)
}

public plugin_end()
    // free the tuple - note that this does not close the connection,
    // since it wasn't connected in the first place
    SQL_FreeHandle(g_SqlTuple)

[/code]

 

Not so hard - especially if the result you get doesn't matter.

 

Finally, multiple modules running can be accomplished by running SQL_GetAffinity, comparing it to the database type you want to run, and setting it with SQL_SetAffinity. An example of this can be found in the sqlx.inc header:

 

new set_type[12]
//...
get_cvar_string("amx_sql_type", set_type, 11)
//...
    SQL_GetAffinity(get_type, 12)
   
    if (!equali(get_type, set_type))
    {
        if (!SQL_SetAffinity(set_type))
        {
            log_amx("Failed to set affinity from %s to %s.", get_type, set_type)
        }

 

SQLx is also useful for dealing with multiple plugins - the "tuple" and "handle" system makes for easy transfer of information across unknown territory (specifically addon plugins).

 

Here is an example:

 

#include <amxmodx>
#include <amxmisc>
#include <sqlx>

new Handle:g_SqlTuple

public plugin_init()
    register_plugin("SQLx Demonstration - Core","1.0","Hawk552")

public plugin_natives()
{
    new Host[64],User[64],Pass[64],Db[64]
    // let's fetch the cvars we will use to connect
    // no pcvars because we're only fetching them once
    get_cvar_string("amx_sql_host",Host,63)
    get_cvar_string("amx_sql_user",User,63)
    get_cvar_string("amx_sql_pass",Pass,63)
    get_cvar_string("amx_sql_db",Db,63)
   
    // we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
   
    register_native("sql_demo_get_handle","_sql_demo_get_handle")
}
   
public _sql_demo_get_handle()
    return _:g_SqlTuple

public plugin_end()
    // free the tuple - note that this does not close the connection,
    // since it wasn't connected in the first place
    SQL_FreeHandle(g_SqlTuple)

 

#include <amxmodx>
#include <amxmisc>
#include <sqlx>

native Handle:sql_demo_get_handle()
   
public plugin_init()
{
    register_plugin("SQLx Demonstration - Attachment","1.0","Hawk552")
   
    SQL_ThreadQuery(sql_demo_get_handle(),"QueryHandle","CREATE TABLE IF NOT EXISTS zomg (rofl INT(11))")
}

public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
        return set_fail_state("Could not connect to SQL database.")
    else if(FailState == TQUERY_QUERY_FAILED)
        return set_fail_state("Query failed.")
   
    if(Errcode)
        return log_amx("Error on query: %s",Error)
       
    server_print("zomg, table made")

 

Accomplishing this in DBI is not hard, but a connection would constantly need to be left open, something that SQLx doesn't demand.

 

Remember that all SQLx modules contain support for DBI as well, and many people are more comfortable with it. DBI has its weaknesses however, and SQLx was designed to overcome them.

 

Like always, if you have any questions or comments, feel free to post.

 

By: Hawk552

[Dragic]

nice explained guide , thnx for share !

[`Rοmeο]

nice explained guide , thnx for share !

 

Thanks, np man ( :

[Guest Elfocrash]

http://forums.alliedmods.net/showthread.php?t=46779

 

waiting for my 2 hours to smite same person pass

[dodelez]

thx!