NEW DBI API (MySQL /SQL)

[BAILOPAN]

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:


//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 (http://www.tcwonline.org/cgi-bin/viewcvs.cgi/amxmodx/plugins/include/dbi.inc) file from CVS.

[Sonic]

nice :)

Support for lastinsertid ?

[Downtown1]

//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?

[BAILOPAN]

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.

[Sonic]

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.

yes, but one query more ...

[Downtown1]

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.

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?

[rasvan]

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

[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.

[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.

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

[BAILOPAN]

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:

[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:

I understand you.
I will not bother you on the forum until 0.20. will be released.

Waiting for the release !

Good luck for it,
Rasvan

[BAILOPAN]

I have updated a small change to the DBI API.

It now uses tags and enums.

[BigDog]

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.



Is there any support for opening a MySQL session on another port (not 3306). I routinely run my MySQL server on a port in the 808x range. I had written some code that was implemented in the original AMX that allowed the use of a different port (127.0.0.1:8082, for example).

Thanks...

[BAILOPAN]

Yes, I will make sure connecting with different ports works.

[jtp10181]

one thing that would be nice is the ability to connect to "localhost" instead of having to use 127.0.0.1. then you dont have to make a new sql user that allows connctions form 127.0.0.1

[BAILOPAN]

You can specify any sort of hostname or IP.

[Dygear]

Is there not a linux error that will not allow you to connect the an MySQL db with out using the IP and not the reverse DNS, was the fixed?

(For thoese of you who dont know, the R-DNS for 127.0.0.1 is loopback)

[jtp10181]

Sorry I guess I'm retarded.... I thought that had not been fixed yet from AMX 0.9.8

Good work then! :)

[[AoL]Demandred]

A little help, possibly - the person who generally handles any of the mysql plugin modifications and such is out of town on vacation, and I wanted to try to get some of our plugins ported. I just can't quite grasp a couple of the variations:

Here's the entire section of pertinent code from a plugin:

public read_list(){
new mhost[64],muser[32],mpass[32],mdb[32],error[128]
new counter = 0
new line[48], name[32], steam[32]
get_cvar_string("amx_mysql_host",mhost,64)
get_cvar_string("amx_mysql_user",muser,32)
get_cvar_string("amx_mysql_pass",mpass,32)
get_cvar_string("amx_mysql_db",mdb,32)

new mysql = mysql_connect(mhost,muser,mpass,mdb,error,127 )
if(mysql < 1){
server_print("[CLANTAG]: MySQL error: can't connect: '%s'",error)
return PLUGIN_HANDLED
}

// Here is where we read from the database. tagprotect is the table.
// steamid and name are varchar fields in the table. You have to create
// the table and fields and populate them for this to work. - Lucky
if(mysql_query(mysql,"SELECT name,steamid FROM tagprotect") < 1) {
mysql_error(mysql,error,127)
server_print("[CLANTAG]: MySQL error: can't load names and steamids: '%s'",error)
return PLUGIN_HANDLED
}

// This loop really isn't quite what it should be, but it works for now - Lucky
while( mysql_nextrow(mysql) > 0 )
{
mysql_getfield(mysql, 1, line ,32)
if(strtrim(name,line))
mysql_getfield(mysql, 2, line ,32)
if(strtrim(steam,line))
add_clantag(name,steam)
counter++
}
server_print("[CLANTAG]: Loaded %d tag protections.",counter)

return PLUGIN_CONTINUE
}

I know most of what needs changed in this code in order to convert it to the new dbi interface (amx_sql_*, dbi_connect, etc), but I'm failing to figure out what I need to do with the mysql_getfield entries in the last part, there. I've looked through the other code in this particular plugin, and this is the only part I'm having problems with.

How do dbi_field & dbi_result work? Which would be better to use in this case? Or is a more comprehensive rewrite in order for this usage?

[BAILOPAN]

Rewrite:


public read_list(){
new mhost[64],muser[32],mpass[32],mdb[32],error[128]
new counter = 0
new line[48], name[32], steam[32]
get_cvar_string("amx_mysql_host",mhost,64)
get_cvar_string("amx_mysql_user",muser,32)
get_cvar_string("amx_mysql_pass",mpass,32)
get_cvar_string("amx_mysql_db",mdb,32)

new Sql:sql = dbi_connect(mhost,muser,mpass,mdb,error,127)
if(sql < 1){
server_print("[CLANTAG]: SQL error: can't connect: '%s'",error)
return PLUGIN_HANDLED
}

// Here is where we read from the database. tagprotect is the table.
// steamid and name are varchar fields in the table. You have to create
// the table and fields and populate them for this to work. - Lucky
new Result:res = dbi_query(sql, SELECT name,steamid FROM tagprotect")
if (res < 0)
dbi_error(sql,error,127)
server_print("[CLANTAG]: SQL error: can't load names and steamids: '%s'",error)
return PLUGIN_HANDLED
}

// This loop really isn't quite what it should be, but it works for now - Lucky
while( dbi_nextrow(res) > 0 )
{
dbi_field(res, 1, line ,32)
if(strtrim(name,line))
dbi_field(res, 2, line ,32)
if(strtrim(steam,line))
add_clantag(name,steam)
counter++
}
if (res > 0)
dbi_free_result(res)
server_print("[CLANTAG]: Loaded %d tag protections.",counter)

return PLUGIN_CONTINUE
}


I think that looks right.
The difference between dbi_field and dbi_result is that field gets a result by column number, while dbi_result expects a column name.

[[AoL]Demandred]

public read_list(){
new mhost[64],muser[32],mpass[32],mdb[32],error[128]
new counter = 0
new line[48], name[32], steam[32]
get_cvar_string("amx_sql_host",mhost,64)
get_cvar_string("amx_sql_user",muser,32)
get_cvar_string("amx_sql_pass",mpass,32)
get_cvar_string("amx_sql_db",mdb,32)

new Sql:sql = dbi_connect(mhost,muser,mpass,mdb,error,127)
if(sql < 1){
server_print("[CLANTAG]: SQL error: can't connect: '%s'",error)
return PLUGIN_HANDLED
}

// Here is where we read from the database. tagprotect is the table.
// steamid and name are varchar fields in the table. You have to create
// the table and fields and populate them for this to work. - Lucky
new Result:res = dbi_query(sql, SELECT name,steamid FROM tagprotect")
if (res < 0)
dbi_error(sql,error,127)
server_print("[CLANTAG]: SQL error: can't load names and steamids: '%s'",error)
return PLUGIN_HANDLED
}

// This loop really isn't quite what it should be, but it works for now - Lucky
while( dbi_nextrow(res) > 0 )
{
dbi_field(res, 1, line ,32)
if(strtrim(name,line))
dbi_field(res, 2, line ,32)
if(strtrim(steam,line))
add_clantag(name,steam)
counter++
}
if (res > 0)
dbi_free_result(res)
server_print("[CLANTAG]: Loaded %d tag protections.",counter)

return PLUGIN_CONTINUE
}

Ah, so you actually can just change mysql_getfield to dbi_field and it would be okay?

And I see where you entered the dbi_free_result . . . those always need to be done AFTER the entire result/lookup? (also, you'll see I changed the amx_mysql_host and such to amx_sql_host . . . is that correct?)

[BAILOPAN]

mysql_getfield -> dbi_field
Not quite, because the first parameter changes from "sql" to "result".

dbi_free_result needs to be called on any result which is greater than 0, after you're finished with it.

Yes, your CVAR changes are correct. Sorry about that.

[[AoL]Demandred]

hehe, I suspect that if I knew MySQL/SQL better than I do, that this would make A LOT more sense to me . . . I'm working from the perspective of small coding knowledge more than SQL knowledge.

hrm, so a dbi_free_result is NOT needed every time you do a query, but only when the query is > 0? And would it probably ALWAYS need to be enclosed like this?:

if (res > 0)
dbi_free_result(res)

If so, is there any reason that shouldn't be the format of the builtin dbi_free_result code? Or are there times where you'd have an un-ifnested dbi_free_result?

[BAILOPAN]

in the new mysql module, queries are cached in memory instead of piece by piece.

this means that if you make a query which returns results, you must then free the results (unless we work in garbage collection or something).

the dbi_free_result line will almost always look like that unless your logic flow is a bit different.

[[AoL]Demandred]

Okay, I tried to compile that plugin, and got some nasty errors and such. Not sure about a couple of them.

Here is the full plugin - it is the clan_tag_protection plugin from original AMX converted to use with mysql, then further converted to AMXx.

See attached .sma

[[AoL]Demandred]

Okay, I just added a { and a " in two places, and it compiled, albeit with three warnings:

clan_tag_mysql.sma(126) : warning 213: tag mismatch
clan_tag_mysql.sma(136) : warning 213: tag mismatch
clan_tag_mysql.sma(153) : warning 213: tag mismatch

I'm going to be trying it soon (hopefully) with an existing database.

:D

Here is the compiled version, so you can see what I did - do you perchance know why I'm gettign tag mismatch on those three if statements???

--EDIT--

Actually, looking at it, the only times I'm getting tag mismatch are on the if(sql < 1), if (res < 0) and if (res > 0) lines . . .

[[AoL]Demandred]

Okay, I have another one here, and need to be sure of a few things . . . here is the SQL section of the code - I think I can handle most of this, now, it's a fairly simple little plugin . . . this is the errors I get on compile:

contrack.sma(55) : warning 219: local variable "sql" shadows a variable at a preceding level
contrack.sma(56) : warning 213: tag mismatch
contrack.sma(107) : warning 213: tag mismatch
contrack.sma(109) : warning 213: tag mismatch
contrack.sma(111) : warning 213: tag mismatch
contrack.sma(117) : warning 219: local variable "res" shadows a variable at a preceding level
contrack.sma(117) : warning 213: tag mismatch
contrack.sma(118) : warning 213: tag mismatch
contrack.sma(120) : warning 213: tag mismatch
contrack.sma(124) : error 021: symbol already defined: "res"
contrack.sma(125) : warning 213: tag mismatch
contrack.sma(127) : warning 213: tag mismatch
contrack.sma(131) : warning 213: tag mismatch

And here is the section of code containing the SQL stuff:

enter_info(steamid[], name[], client_ip[])
{
if(equal(steamid,"HLTV"))
return PLUGIN_CONTINUE

new error[128], query[1025]
format(query,1024,"INSERT INTO `connections` (`id`, `name`, `ip`, `server_ip`, `server_port`, `connects`, `last_connect`) VALUES ('%s','%s','%s','%s','%s',1,now())", steamid, name, client_ip, server_ip, server_port)

new Result:res = dbi_query(sql,query)

if(res < 1)
{
dbi_error(sql,error,127)
server_print("[CONTRACK]: SQL error: can't insert new connection record: '%s'",error)
}
if(containi(error,"Duplicate entry") != -1)
{
format(query,1024,"UPDATE `connections` SET connects=connects+1 where id='%s' AND name='%s' AND ip='%s' AND server_ip='%s' AND server_port='%s'", steamid, name, client_ip, server_ip, server_port)
new Result:res = dbi_query(sql,query)
if(res < 1)
{
dbi_error(sql,error,127)
server_print("[CONTRACK]: SQL error: can't update connection record count: '%s'",error)
}
format(query,1024,"UPDATE `connections` SET last_connect=now() where id='%s' AND name='%s' AND ip='%s' AND server_ip='%s' AND server_port='%s'", steamid, name, client_ip, server_ip, server_port)
new Result:res = dbi_query(sql,query)
if(res < 1)
{
dbi_error(sql,error,127)
server_print("[CONTRACK]: SQL error: can't update connection record last connect date/time: '%s'",error)
}
}
if (res > 0)
dbi_free_result(res)

return PLUGIN_CONTINUE
}

I'll also pin the .sma here, if you want to look at the rest of the code.

A couple of the errors are fairly obvious, but I'm not sure what the *BEST* way to resolve them is.

1) First off, I'm ignoring the "local shadows a preceding level" errors. Should I be?

2) The only error is "res is already defined" (in a nutshell) - I'm guessing this is here:

new Result:res = dbi_query(sql,query)
if(res < 1)
{
dbi_error(sql,error,127)
server_print("[CONTRACK]: SQL error: can't update connection record count: '%s'",error)
}
format(query,1024,"UPDATE `connections` SET last_connect=now() where id='%s' AND name='%s' AND ip='%s' AND server_ip='%s' AND server_port='%s'", steamid, name, client_ip, server_ip, server_port)
new Result:res = dbi_query(sql,query)

Can I just do this on the second time:
Result:res = dbi_query(sql,query)
?

This conversion I've done on my own, and want to be sure I understand what's happening, and where. Also, is my dbi_free_result placed well? I'm assuming I need to leave the result until the code is done working with it, and it doesn't LOOK like it was done until then . . .

--EDIT--

Oh, and I did go ahead and modify it to Result:res = ... and it gave me a couple of DIFFERENT warnings, then I realized I was stupid, and removed the Result: - and it compiled with just these warnings:

contrack.sma(55) : warning 219: local variable "sql" shadows a variable at a preceding level
contrack.sma(56) : warning 213: tag mismatch
contrack.sma(107) : warning 213: tag mismatch
contrack.sma(109) : warning 213: tag mismatch
contrack.sma(111) : warning 213: tag mismatch
contrack.sma(117) : warning 219: local variable "res" shadows a variable at a preceding level
contrack.sma(117) : warning 213: tag mismatch
contrack.sma(118) : warning 213: tag mismatch
contrack.sma(120) : warning 213: tag mismatch
contrack.sma(124) : warning 213: tag mismatch
contrack.sma(125) : warning 213: tag mismatch
contrack.sma(127) : warning 213: tag mismatch
contrack.sma(131) : warning 213: tag mismatch

The attached .sma is the one that compiles with those warnings :D

[BAILOPAN]

I apologize
You should not use -1, 0, 1


enum Sql
{
SQL_FAILED=0,
SQL_OK //1
}

enum Result
{
RESULT_FAILED=-1,
RESULT_NONE, //0
RESULT_OK //1
}


Use those instead

[[AoL]Demandred]

I apologize
You should not use -1, 0, 1


enum Sql
{
SQL_FAILED=0,
SQL_OK //1
}

enum Result
{
RESULT_FAILED=-1,
RESULT_NONE, //0
RESULT_OK //1
}


Use those instead

How would that change my code? I'm not following on this one . . .

Are you referring to the:

if (res < 1) / if (res > 1) / if (sql < 0) / etc...

?

If so, how should I proceed?

Oh, and please, no need to apologize - you are WAY more knowledgeable than me on this, and I'm very barely scratching the surface of learning a lot of this stuff. :D

[[AoL]Demandred]

Nevermind on my last one, I understand now ---

if (res == RESULT_FAILED)

etc...

[Freecode]

Never got around asking this but if
new Result:res = dbi_query(mysql, "SELECT * FROM config")
wouldnt res be an array if the config table has 5 fields?

[Johnny got his gun]

No it's still just a "result handle" if I understand your question. :?:

[Freecode]

ok

new Result:res = dbi_query(mysql, "SELECT * FROM config")

is the same as

$result = mysql_query("SELECT * FROM config");
$data = mysql_fetch_row($result);

Now $data is now an array because say config has 5 fields now field 1 is stored in $data[0] and field 2 is stored in $data[1] and so on.

Now what will res be after it selects everything from config. its not gonna hold all 5 fields in one string is it?

[BAILOPAN]

That's just not how this works :]

Result will always have an id in it.

If you do dbi_result(res, ...) and dbi_nextrow(res) eventually it will stop working because the result handle is no longer valid.

Each time you use dbi_nextrow(res), res does not change, but internally it has a new row. (And yes, all rows are stored inside, but you can only access the current one).

If you call dbi_free_result(res) then res will be officially marked invalid and set to 0.

[Freecode]

ahh gotcha tnx

[Ryan]

question, I've been receiving some errors at map changes with the new dbi as follows:

L 10/22/2004 - 04:32:22: [AMXX] Run time error 10 (native) on line 1494 (plugin "war3x.amxx").
L 10/22/2004 - 04:32:22: [MySQL] Invalid database handle -1

the trace brings it back to the dbi_close() function. It looks to me as if the error is generated because the connection to the database has already been closed, but I havent closed it yet. Does the module automatically kill connections at the map change? Or is there another reason this error is coming up?

[BAILOPAN]

Connections are killed at map change.

dbi_close() will automatically reset the handle you made to -1 so you don't try to use it again.

[Ryan]

ok thanks, bailo. so assuming my plugin only makes one connection to the database at map start (which it does), and keeps the connection open until the map change, i dont even need to close the connection after it's opened because it will close automatically?

[BAILOPAN]

No, you still should ;] it's good practice. but in theory it should be fine

[Ryan]

hmm, well, like mentioned .. doing so causes the error :) I will hold off on closing the connection on my next NB to see.

[nightscreem]

i don't get what this should do
i'm a complete noob at mysql
new Result:res = dbi_query(mysql, "SELECT * FROM config")

[Freecode]

* means everything
So it will SELECT EVERYTHING FROM config

[aragon]

Connections are killed at map change.

dbi_close() will automatically reset the handle you made to -1 so you don't try to use it again.

How about connection lost due to other issues? Network problems, etc?
Will the handle obtainded from dbi_connect be changed, so I can detect this situation just by:
if(connection_handle < SQL_OK ) {
// try to reconnect or exit
}
? If not, what is the proper way to do it? And is it possible to get info about the cause of the disconnection?

[BAILOPAN]

No, the handle will not be changed. If the connection suddenly dies, you will have to close and re-open it. You can check for a dead connection by seeing if a query died when it shouldn't (RESULT_FAILED).

[Eiver]

It seems that the DBI API cannot handle multiple result sets from stored procedures.
Executing queries like the one below will always fail:

dbi_query(dbhandle,"call MyStoredProc('Some input','Some more input')");

The query above works only if the stored procedure does not return anything. If it returns something, it will always fail.

I know that other APIs (like for example PHP and its mysql_query()) also had this problem. However in PHP it was possible to set client options, so that it would accept result sets from a stored procedure.

Is there a way to get around this problem in DBI API in AMX MOD X?

P.S
Since SQLX also has the same issue a new thread was created to deal with this problem in general.
The discussion is continued here http://forums.alliedmods.net/showthread.php?t=54852

[Podunk]

http://bugs.alliedmods.net/index.php?do=details&task_id=281