Raised This Month: $81 Target: $400
 20% 

Are there transactions in sqlx?


Post New Thread Reply   
 
Thread Tools Display Modes
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 11-21-2023 , 15:44   Re: Are there transactions in sqlx?
Reply With Quote #11

Just prevent things from going wrong..
__________________
Bugsy is offline
damage220
Member
Join Date: Jul 2022
Location: Ukraine
Old 11-21-2023 , 15:47   Re: Are there transactions in sqlx?
Reply With Quote #12

Quote:
Originally Posted by Alon2k2 View Post
Thank you, Too bad there isn't, is something very important when working with database
You may try to use START TRANSACTION, COMMIT, and ROLLBACK SQL statements directly. I have not tested it but it should work. At least when you write them in single query.
PHP Code:
START TRANSACTION;
UPDATE tblPoints SET Points=(current points 1WHERE ID=PlayerA
UPDATE tblPoints SET Points
=(current points 1WHERE ID=PlayerB
COMMIT

damage220 is offline
damage220
Member
Join Date: Jul 2022
Location: Ukraine
Old 11-21-2023 , 15:50   Re: Are there transactions in sqlx?
Reply With Quote #13

Quote:
Originally Posted by Bugsy View Post
Just prevent things from going wrong..
How is it possible to prevent power loss or engineering works of your ISP?
damage220 is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 11-21-2023 , 16:49   Re: Are there transactions in sqlx?
Reply With Quote #14

How about this:

1. Execute the SQL transaction to deduct a point
2. In the callback/handler for that transaction, decide what to do based on success/failure (if success, add point for player, if failed, re-send or do nothing)
__________________
Bugsy is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 11-21-2023 , 17:53   Re: Are there transactions in sqlx?
Reply With Quote #15

Not tested
PHP Code:
#include <amxmodx>
#include <sqlx>

enum TransType
{
    
ttTakePoints,
    
ttGivePoints
}

enum TransInfo
{
    
tiPlayerIDLosePoints,
    
tiPlayerIDGainPoints,
    
TransType:ttType,
    
tiNumPoints
}

new 
Handle:g_SQLTuple;
new 
g_szBuffer512 ];
new 
tiDataTransInfo ];


public 
plugin_init() 
{
    
SQL_SetAffinity"sqlite" );
    
    
g_SQLTuple SQL_MakeDbTuple"" "" "" "TestPlugin" );
    
    new 
Handle:SQLConnection ErrorCode;
    
SQLConnection SQL_Connectg_SQLTuple ErrorCode g_szBuffer charsmaxg_szBuffer ) );
    
    if( 
SQLConnection == Empty_Handle 
        
set_fail_stateg_szBuffer );
        
    
RunQuerySQLConnection "CREATE TABLE IF NOT EXISTS 'tblPlayers' ( `ID` INTEGER PRIMARY KEY AUTOINCREMENT, `steamid` TEXT UNIQUE, `points` INTEGER );" );    
}

public 
plugin_end() 
{
    
SQL_FreeHandleg_SQLTuple );
}

public 
TransferPointsidLosePoint idGainPoint iNumPoints )
{
    new 
szAuthID35 ];
    
get_user_authididLosePoint szAuthID charsmaxszAuthID ) );
    
    
tiDatatiPlayerIDLosePoints ] = idLosePoint;
    
tiDatatiPlayerIDGainPoints ] = idGainPoint;
    
tiDatattType ] = ttTakePoints;
    
tiDatatiNumPoints ] = iNumPoints;
    
    
formatexg_szBuffer charsmaxg_szBuffer ) , "UPDATE `tblPlayers` SET Points=Points-%d WHERE `SteamID='%s';" iNumPoints szAuthID );
    
    
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer _:tiDatatiPlayerIDLosePoints ] , _:TransInfo );
}

public 
SQLCallBack(FailStateHandle:QueryError[], ErrcodeDataTransInfo ], DataSize)
{
    new 
idGainPoint _:DatatiPlayerIDGainPoints ];
    
    
//No error and taking points was successful
    
if ( !Errcode && ( DatattType ] == ttTakePoints ) )
    {
        
//Change transaction type to give
        
DatattType ] = ttGivePoints;
        
        new 
szAuthID35 ];
        
get_user_authididGainPoint szAuthID charsmaxszAuthID ) );
    
        
formatexg_szBuffer charsmaxg_szBuffer ), "UPDATE `tblPlayers` SET Points=Points+%d WHERE `SteamID='%s';" DatatiNumPoints ] , szAuthID );
    
        
SQL_ThreadQueryg_SQLTuple "SQLCallBack" g_szBuffer ,  Data[  tiPlayerIDLosePoints ] , _:TransInfo  );
    }
    else if ( 
Errcode )
    {
        switch ( 
DatattType ] )
        {
            case 
ttTakePoints:
            {
                
//Take points failed, retry? 
            
}
            case 
ttGivePoints:
            {
                
//Give points failed, retry?     
            
}
        }
    }


//Here's how to execute a trans immediately (non asynchronous)
public RunQueryHandle:SQLConnection , const szQuery[] )
{
    new 
Handle:Query SQL_PrepareQuerySQLConnection szQuery );
    
    if( !
SQL_ExecuteQuery ) )
    {
        
set_fail_stateg_szBuffer );
    }

__________________

Last edited by Bugsy; 11-24-2023 at 15:54.
Bugsy is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 11-21-2023 , 22:53   Re: Are there transactions in sqlx?
Reply With Quote #16

Good news

Transactions do work with SQLx, though I am getting odd results with this, it's subtracting and adding 3 opposed to 1 point.

PHP Code:
#include <amxmodx>
#include <sqlx>

new Handle:g_SQLTuple;
new 
g_szBuffer512 ];

public 
plugin_init() 
{
    new 
Handle:SQLConnection ErrorCode;
    
    
SQL_SetAffinity"sqlite" );
    
g_SQLTuple SQL_MakeDbTuple"" "" "" "TestPlugin" );
    
SQLConnection SQL_Connectg_SQLTuple ErrorCode g_szBuffer charsmaxg_szBuffer ) );
    
    if ( 
SQLConnection == Empty_Handle 
    {
        
set_fail_stateg_szBuffer );
    }
    else
    {
        
copyg_szBuffer charsmaxg_szBuffer ) , "BEGIN TRANSACTION; UPDATE `playerData` SET Points=Points-1 WHERE SteamID='STEAM:0:12345'; UPDATE `playerData` SET Points=Points+1 WHERE SteamID='STEAM:0:11111'; COMMIT;"  );
        
RunQuerySQLConnection g_szBuffer );
    }
}

public 
plugin_end() 
{
    
SQL_FreeHandleg_SQLTuple );
}

public 
RunQueryHandle:SQLConnection , const szQuery[] )
{
    new 
Handle:Query SQL_PrepareQuerySQLConnection szQuery );
    new 
szErr33 ];
    
    if ( !
SQL_ExecuteQuery ) )
    {
        
SQL_QueryErrorQuery  szErr charsmaxszErr ) );
        
set_fail_stateszErr );
    }

__________________
Bugsy is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 21:07.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode