Library MySQL Prepared Statements

PatrickGTR · 2019-06-07, 07:42 PM

Introduction
Hi, I'll keep it short and simple.
This emulates prepared statement when using the MySQL plugin.
Thank you for reading this really long introduction.

Download: Click me
Important: sampctl required! check github repository for installation instructions.

Reading Data (using inline)

Code:
new stmt_readloop = MySQL_PrepareStatement(MySQLHandle, "SELECT * FROM spawns");

// Run Threaded on statement

inline OnSpawnsLoad() {

    new

    spawnID,

    Float:spawnX,

    Float:spawnY,

    Float:spawnZ,

    Float:spawnA;

    MySQL_BindResultInt(stmt_readloop, 0, spawnID);

    MySQL_BindResultFloat(stmt_readloop, 1, spawnX);

    MySQL_BindResultFloat(stmt_readloop, 2, spawnY);

    MySQL_BindResultFloat(stmt_readloop, 3, spawnZ);

    MySQL_BindResultFloat(stmt_readloop, 4, spawnA);

    while(MySQL_Statement_FetchRow(stmt_readloop)) {

        printf("%i, %.3f, %.3f, %.3f", spawnID, spawnX, spawnY, spawnZ, spawnA);

    }

    MySQL_StatementClose(stmt_readloop);

}

MySQL_ExecuteThreaded_Inline(stmt_readloop, using inline OnSpawnsLoad);

Writing Data

Code:
new Statement: stmt_insert = MySQL_PrepareStatement(MySQLHandle, "INSERT INTO accounts(username, password, salt, money, kills, deaths) VALUES (?,?,?,?,?,?) " );

// Arrow values in questions (first 0, second is 1, etc ...)

MySQL_Bind(stmt_insert, 0 , "patrickgtr");

MySQL_Bind(stmt_insert, 1 , "patrickgtrpassword");

MySQL_Bind(stmt_insert, 2 , "pgtrhash");

MySQL_BindInt(stmt_insert, 3, 100);

MySQL_BindInt(stmt_insert, 4, 200);

MySQL_BindInt(stmt_insert, 5, 300);

MySQL_ExecuteParallel(stmt_insert);

MySQL_StatementClose(stmt_insert);

Functions

Open/Close

Code:
MySQL_StatementClose(Statement:statement)

MySQL_PrepareStatement(MySQL:handle, const query[])

MySQL_Statement_RowsLeft(&Statement:statement)

MySQL_Statement_FetchRow(Statement:statement)

Writing

Code:
MySQL_Bind(Statement:statement, param, const str[]) 

MySQL_BindInt(Statement:statement, param, value)

MySQL_BindFloat(Statement:statement, param, Float:value)

Reading

Code:
MySQL_BindResult(Statement:statement, field, const result[], len = sizeof(result))

MySQL_BindResultInt(Statement:statement, field, &result)

MySQL_BindResultFloat(Statement:statement, field, &Float:result)

Executing

Code:
MySQL_ExecuteThreaded(Statement:statement, const callback[] = "", const fmat[] = "", {Float,_}:...)

MySQL_ExecuteParallel(Statement:statement, const callback[] = "", const fmat[] = "", {Float,_}:...)

MySQL_ExecuteThreaded_Inline(Statement:statement, Func:callback<>)

MySQL_ExecuteParallel_Inline(Statement:statement, Func:callback<>)

PatrickGTR · 2019-06-07, 11:39 PM

Added account example script.

https://github.com/PatrickGTR/MySQL-Prepared-Statements/blob/master/example/account.pwn

**Y_Less** · 2019-06-08, 01:21 AM

Very nice.

Shadow_ · 2020-06-03, 02:39 PM

Love it - will switch to this.

PatrickGTR · 2020-06-04, 10:50 PM

(2020-06-03, 02:39 PM)Shadow_ Wrote: Love it - will switch to this.

Ah thank you, glad you find it useful.

XoMoX · 2020-12-07, 04:04 AM

Yes to avoid SQL injections very very nice

PatrickGTR · 2020-12-09, 06:52 PM

Code updated!

- Fix the issue where a data would get loaded DESC instead of ASC.
- stmt_fetch_row is not depending on loop to retrieve next row.
- fix typo in Debug of stmt_fetch_row.
- format code a lil bit more (like it matters much lol)

PatrickGTR · 2021-05-07, 11:15 AM

added bcrypt example to Prepared Statement as requested.

https://github.com/PatrickGTR/MySQL-Prepared-Statements/blob/master/example/account_bcrypt.pwn