NOTE: THIS IS AN OLD VERSION OF SQL, USE SQLX INSTEAD
So, you want to learn how to use SQL in AmxX plugins?
To do this, you need the following things
- You need to know the SQL commands, I can't teach you the basics of using it, if you don't understand SQL
- You need to know small. I won't be explaining things other then the SQL commands.
Note: This tutorial is going to be using the 0.20 version of SQL, not the 0.16 version. You can get the include file to look at it from the CVS.
And the plugin I will be explaining:
A note: In the interest of clarity, I don't tell the user why the plugin didn't work
Code:
#include <amxmodx>
#include <dbi>
new Sql:dbc
new Result:result
public plugin_init() {
register_plugin("SQL-Tut","0.1","devicenull")
register_concmd("sql_insert","sql_insert",ADMIN_KICK," <value> Inserts a value into our table")
register_concmd("sql_display","sql_display",ADMIN_KICK," Displays values")
set_task(Float:10.0,"sql_init")
}
public sql_init() {
new host[64], username[32], password[32], dbname[32], error[32]
get_cvar_string("amx_sql_host",host,64)
get_cvar_string("amx_sql_user",username,32)
get_cvar_string("amx_sql_pass",password,32)
get_cvar_string("amx_sql_db",dbname,32)
dbc = dbi_connect(host,username,password,dbname,error,32)
if (dbc == SQL_FAILED)
log_amx("[AMXX] SQL Connection Failed")
else
dbi_query(dbc,"CREATE TABLE IF NOT EXISTS `tutorial` ( `value` VARCHAR(32) NOT NULL )")
}
public sql_insert(id) {
if (dbc == SQL_FAILED) return PLUGIN_HANDLED
if (!(get_user_flags(id)&ADMIN_KICK)) return PLUGIN_HANDLED
if (read_argc() == 0) return PLUGIN_HANDLED
new arg[32]
read_argv(1,arg,32)
result = dbi_query(dbc,"INSERT INTO tutorial VALUES ( '%s' )",arg)
if (result == RESULT_OK) dbi_free_result(result)
if (result == RESULT_FAILED) return PLUGIN_HANDLED
new dbitype[32]
dbi_type(dbitype,32)
console_print(id,"[SQL] Inserted value into %s table succesfully",dbitype)
return PLUGIN_HANDLED
}
public sql_display(id) {
if (dbc == SQL_FAILED) return PLUGIN_HANDLED
if (!(get_user_flags(id)&ADMIN_KICK)) return PLUGIN_HANDLED
result = dbi_query(dbc,"SELECT * FROM tutorial WHERE 1 LIMIT 5")
new value[32]
for (new i=1;i<=dbi_num_rows(result);i++) {
dbi_nextrow(result)
dbi_result(result,"value",value[32])
console_print(id,"Value: %s",value)
}
dbi_free_result(result)
return PLUGIN_HANDLED
}
This really isn't that hard to explain..
Code:
new Sql:dbc
new Result:result
Data base connections are the type Sql, results are type Result
Code:
dbc = dbi_connect(host,username,password,dbname,error,32)
Connects to the DB.. get the proper values out of the cvars.. you can just copy the entire init_sql() sub and use it where ever you want
Code:
if (dbc == SQL_FAILED)
Makes sure we actually connected to the database, you can use dbi_error to get the error message if there is one
Code:
result = dbi_query(dbc,"INSERT INTO tutorial VALUES ( '%s' )",arg)
Does the insertion query, and puts a link to the result in the result variable, you could just do the dbi_query part for insert/delete/update, and not worry about what it returns
Code:
dbi_free_result(result)
You must do this when the returned variable is RESULT_OK, otherwise you get memory leaks, and memory leaks = BAD
Code:
dbi_type(dbitype,32)
Get the type of database you are using.. I really haven't thought of a use for this yet, but its here.
Code:
dbi_num_rows(result)
Returns the number of rows that are in the result.
Code:
dbi_nextrow(result)
Advances to the next row in result.
This is kind of annoying. Depending on the datatype expected, you have to use this differently.
Expecting: Use:
Int var = dbi_result(result,"fieldname")
Float dbi_result(result,"fieldname",Float:var)
String dbi_result(result,"fieldname",var,len)
Also, dbi_field() works the same way, with the field number instead of the field name
This would close the sql connection, its not needed unless you create a sql connection and close it in the same sub (Don't do that unless you have to, using one connection for the entire plugin is more efficient). Dbi connections are closed before plugin_end.
You can get most of this from reading the include file, but some of it is a bit annoying to figure out