AlliedModders - NEW DBI API (MySQL /SQL)

AlliedModders (https://forums.alliedmods.net/index.php)

- Code Snippets/Tutorials (https://forums.alliedmods.net/forumdisplay.php?f=83)

- - NEW DBI API (MySQL /SQL) (https://forums.alliedmods.net/showthread.php?t=2632)

NEW DBI API (MySQL /SQL)

This is very important for scripters! For AMXx 0.20, I have significantly changed the SQL interface. All plugins will be rendered broken, so you will need to upgrade.

If you need any help with this, just ask. I can help you convert your plugin or answer technical questions. Remember though, this is for 0.20 and you should be prepared to swap in the new code close to the release date (we will announce this).

This new DBI is important because it gets rid of the shoddy interface from AMX 0.9.x. Plugin writers now get resource handles for both result sets and connections, as well as a better guarantee that the driver will not crash.

To best explain this, here is a sample program:

Code:

//Create a connection
    new Sql:mysql = dbi_connect("localhost", "dvander", "pass", "dbase")

//If the connection is less than 1, it is bad   
    if (mysql < SQL_OK) {
        new err[255]
        new errNum = dbi_error(mysql, err, 254)
        server_print("error1: %s|%d", err, errNum)
        return 1
    }
    
    server_print("Connection handle: %d", mysql)
//Run a query
    new Result:ret = dbi_query(mysql, "INSERT INTO config (keyname, val) VALUES ('amx', 'yes')")

//If the query is less than 0, it failed    
    if (ret < RESULT_NONE) {
        new err[255]
        new errNum = dbi_error(mysql, err, 254)
        server_print("error2: %s|%d", err, errNum)
        return 1
    }

//Do a select query 
    new Result:res = dbi_query(mysql, "SELECT * FROM config")

//If the query is greater than 0, you got a handle to the result set    
    if (res <= RESULT_NONE) {
        new err[255]
        new errNum = dbi_error(mysql, err, 254)
        server_print("error3: %s|%d", err, errNum)
        return 1
    }
    
    server_print("Result handle: %d", res)

//Loop through the result set   
    while (res && dbi_nextrow(res)>0) {
        new qry[32]
//Get the column/field called "keyname" from the result set
        dbi_result(res, "keyname", qry, 32)
        server_print("result: %s", qry)
    }

//Free the result set   
    dbi_free_result(res)

//Close the connection  
    ret = dbi_close(mysql)
    if (ret <= RESULT_NONE) {
        new err[255]
        new errNum = dbi_error(mysql, err, 254)
        server_print("error4: %s|%d", err, errNum)
        return 1
    }

These differences are subtle and important. It means you can create nested or recursive SQL queries and not have the previous ones erased. It also gives you much better errorchecking/debugging control. You can also reference fields by name instead of number.

To see more, look in the dbi.inc file from CVS.

nice :)

Support for lastinsertid ?

Code:

//If the query is greater than 0, you got a handle to the result set    
    if (res <= 0) {
        new err[255]
        new errNum = dbi_error(mysql, err, 254)
        server_print("error3: %s|%d", err, errNum)
        return 1
    }
    
    server_print("Result handle: %d", res)

What about if the result is an "Empty set"? What is it supposed to return then?

If the result is an empty set, the return value for the result handle (in this program, it is res), will be 0.

That's why I did <0 for INSERT, to check if the insert succeeded, and <=0 for SELECT, to make sure there were items in the table.

Last insert id is a MySQL only feature so I won't add it to the DBI I think. You can do this:
SELECT MAX(field) FROM table
and get the first (only) result.

Quote:

Originally Posted by BAILOPAN

yes, but one query more ...

Quote:

Originally Posted by BAILOPAN

If it's 0 for an "Empty set," why would you be printing an error if res == 0? An "Empty set" is a perfectly legitimate response from the DB, is it not?

Function not found (name "dbi_close")

CS16STEAM, Linux Server.

I downloaded from CVS all new plugins and include files (dbi.inc also).
All plugins are version 2.0 now.

I get these errors.

L 07/05/2004 - 09:45:15: AMX Mod X log file started (file "addons/amxx/logs/L0705021.log") (version "0.16")
L 07/05/2004 - 09:45:15: [AMXX] Function not found (name "dbi_close") (plugin "admin.amx")

I suppose this is because I do not know how to make the modules.

If my supposition is right,
Can you help getting the modules made for us !

Thank you for any advice.
Rasvan

Don't use 0.20 stuff unless you know what you are doing ;]
We put it in CVS for collaboration, not for public use.

Quote:

Originally Posted by BAILOPAN

Don't use 0.20 stuff unless you know what you are doing ;]
We put it in CVS for collaboration, not for public use.

BAILOPAN,

The CVS 0.20 version works great for me.

As for the error:
L 07/05/2004 - 11:20:42: [AMXX] Function not found (name "dbi_close") (plugin "admin.amx")
I just commented out this line in admin.sma:
//#define USING_SQL 1

And no more problems.

NOTE: I suppose it is not illegal to use CVS, is it ?

Thank you for everything,
Rasvan

Of course not, but if you're using 0.20 plugins that use 0.20 natives, it won't work.

I guess the bottom line is: Don't ask for support for stuff that isn't released :wink: