26.12.2013, 06:54
Improving your queries with db_parse!
Introduction
A lot of people use SQLite for every day data storage, such as accounts, houses, and more. There are several libraries released around the forum that allows you to optimize your queries and tidy them up!
Since the release topic is only designed to show the basics, I've decided to create a tutorial to show others how to use it, to avoid confusion and questions in the release topic.
What is db_parse?
One of my libraries that allows you to easily execute SQLite queries, without having to additionally format strings, execute the queries and check the rows.
This code:
Simply turns into this!
And that's it. I don't know about you folks, but I think the second one looks much more cleaner!
Fetching data
Another feature with db_parse is that you can fetch data easily, without having to manually fetch the result and convert the string to an integer.
As opposed to this code:
Which allows easier, more flexible fetching.
Freeing the result
To free the result, simply use this function:
This piece of code will automatically free the result, if it exists. If not, it simply does nothing.
Inception of queries
You can also execute queries inside of others, using this include.
That code will automatically free the stored result and overload it with the new one. However, I am designing a feature that allows easier control over this.
Getting the stored result
If you want to retrieve the ID of the stored result so you could use it with other SQLite functions, simply do this:
Looping through rows
If you have a house system for example, you could use db_parse to simplify it.
Old code:
New code:
It's very simple, isn't it?
Conclusion
This tutorial has proved the effectiveness and usefulness of this include. If you have any questions, feel free to ask me here.
You can download it here:
https://sampforum.blast.hk/showthread.php?tid=477550
Introduction
A lot of people use SQLite for every day data storage, such as accounts, houses, and more. There are several libraries released around the forum that allows you to optimize your queries and tidy them up!
Since the release topic is only designed to show the basics, I've decided to create a tutorial to show others how to use it, to avoid confusion and questions in the release topic.
What is db_parse?
One of my libraries that allows you to easily execute SQLite queries, without having to additionally format strings, execute the queries and check the rows.
This code:
pawn Код:
new
rows,
DBResult:result,
string[128];
format(string, sizeof(string), "SELECT `Money` FROM `Accounts` WHERE `Username` = '%s'", GetName(playerid));
result = db_query(database, string);
rows = db_num_rows(result);
if (rows)
{
db_get_field_assoc(result, "Money", string, sizeof(string));
PlayerInfo[playerid][pMoney] = strval(string);
}
db_free_result(result);
pawn Код:
new rows = db_parse(database, "Accounts", "`Username` = '%s'", GetName(playerid));
if (rows)
{
PlayerInfo[playerid][pMoney] = db_fetch_int("Money");
}
db_free_parse();
Fetching data
Another feature with db_parse is that you can fetch data easily, without having to manually fetch the result and convert the string to an integer.
pawn Код:
g_Money = db_fetch_int("Money");
pawn Код:
new
str[32];
db_get_field_assoc(result, "Money", str, sizeof(str));
g_Money = strval(str);
pawn Код:
forward db_fetch_int(field[]);
forward Float:db_fetch_float(field[]);
forward bool:db_fetch_bool(field[]);
forward db_fetch_string(field[], dest[]);
To free the result, simply use this function:
pawn Код:
db_free_parse();
Inception of queries
You can also execute queries inside of others, using this include.
pawn Код:
new rows = db_parse(database, "Accounts", "`Username` = '%s'", GetName(playerid));
if (rows)
{
PlayerInfo[playerid][pMoney] = db_fetch_int("Money");
rows = db_parse(database, "Houses", "`Owner` = '%s'", GetName(playerid));
if (rows)
{
// More code...
}
}
db_free_parse();
Getting the stored result
If you want to retrieve the ID of the stored result so you could use it with other SQLite functions, simply do this:
pawn Код:
new rows = db_parse(database, "Accounts", "`Username` = '%s'", GetName(playerid));
if (rows)
{
new DBResult:result;
db_get_result(result);
// Use "result" as needed.
}
db_free_parse();
If you have a house system for example, you could use db_parse to simplify it.
Old code:
pawn Код:
new
rows,
string[24],
DBResult:result;
result = db_query(HouseDB, "SELECT * FROM `Houses`");
rows = db_num_rows(result);
for (new i = 0; i < rows; i ++)
{
db_get_field_assoc(result, "hOwned", string, sizeof(string));
HouseInfo[i][hOwned] = strval(string);
db_get_field_assoc(result, "hOwner", string, sizeof(string));
format(HouseInfo[i][hOwner], MAX_PLAYER_NAME, string);
db_next_row(result);
}
db_free_result(result);
pawn Код:
new rows = db_parse(HouseDB, "Houses");
for (new i = 0; i < rows; i ++)
{
HouseInfo[i][hOwned] = db_fetch_bool("hOwned");
db_fetch_string("hOwner", HouseInfo[i][hOwner], MAX_PLAYER_NAME);
db_next_parse();
}
db_free_parse();
Conclusion
This tutorial has proved the effectiveness and usefulness of this include. If you have any questions, feel free to ask me here.
You can download it here:
https://sampforum.blast.hk/showthread.php?tid=477550
