Question about SQL. - Printable Version

Question about SQL. - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: Question about SQL. (/showthread.php?tid=249853)

Question about SQL. - Zh3r0 - 20.04.2011

Which way is it better? Open a database on OnGameModeInit, write/read data and close it on OnGameModeExit

or just simply open, write/read, close each time I want to write/read?

I need a good explanation, as I don't want memory leaks or anything, I just want the best option.

Re: Question about SQL. - xxmitsu - 20.04.2011

You mean, persistent connection to database ?

Of course creating a persistent connection to database in OnGameModeInit would be better because connecting to sql server each time you perform a query would add additional delay/overhead.

There will be no memory leak as long as you use a stable plugin without any leak-related-issues and if you free the result each time after storing a result that is no longer needed.

Re: Question about SQL. - Zh3r0 - 20.04.2011

Quote:

Originally Posted by xxmitsu

You mean, persistent connection to database ?

Of course creating a persistent connection to database in OnGameModeInit would be better because connecting to sql server each time you perform a query would add additional delay/overhead.

There will be no memory leak as long as you use a stable plugin without any leak-related-issues and if you free the result each time after storing a result that is no longer needed.

That's the problem, since I installed SQL in my script it went crazy! Whenever i add a new function or something, PAWN crashes, whenever I add db_free_result i CRASH!

So I converted it all into Open, write/read, Close, and the crazyness settled down...but still, does these crazy stuff like not allowing me to add a new var; ...

Re: Question about SQL. - xxmitsu - 20.04.2011

Oh, you're talking about built-in SQLite. I've thought that you're using mySQL with 3rd party plugin.
Db_free_result (from sqllite) must have as a parameter the result to free.

Can you post a snip of code that causes the server to crash?

Re: Question about SQL. - Zh3r0 - 20.04.2011

Quote:

Originally Posted by xxmitsu

Oh, you're talking about built-in SQLite. I've thought that you're using mySQL with 3rd party plugin.
Db_free_result (from sqllite) must have as a parameter the result to free.

Can you post a snip of code that causes the server to crash?

Here some example: https://sampforum.blast.hk/showthread.php?tid=249761

Re: Question about SQL. - xxmitsu - 20.04.2011

Try this:

pawn Код:

new pQue[ 256 ];
            format( pQue, sizeof (pQue), "SELECT * FROM `Muted` WHERE `IP` = '%s'", GetPlayerIPEx( params[ 0 ] ) );
            new DBResult:RESULT = db_query( UsefullDatabase, pQue );
            
            P_DATA[ params[ 0 ] ][ Muted ] = 1;
            P_DATA[ params[ 0 ] ][ MutedTime ] = 0;

            if ( db_num_rows( RESULT ) ) format( pQue, sizeof (pQue),"UPDATE `Muted` SET `IP` = '%s', `Muted` = '1', `Time` = '0', `RealName` = '%s'",GetPlayerIPEx( params[ 0 ] ), Name( params[ 0 ] ) );
            else format( pQue, sizeof (pQue), "INSERT INTO `Muted` ( `IP`, `Muted`, `Time`, `RealName` ) VALUES ('%s', '1', '0', '%s')", GetPlayerIPEx( params[ 0 ] ), Name( params[ 0 ] ) );

            db_free_result( RESULT );// freeing the first result before executing second query

            db_free_result(db_query( UsefullDatabase, pQue )); // second sql query + result freed

Re: Question about SQL. - Zh3r0 - 20.04.2011

Quote:

Originally Posted by xxmitsu

Try this:

pawn Код:

new pQue[ 256 ];
            format( pQue, sizeof (pQue), "SELECT * FROM `Muted` WHERE `IP` = '%s'", GetPlayerIPEx( params[ 0 ] ) );
            new DBResult:RESULT = db_query( UsefullDatabase, pQue );
            
            P_DATA[ params[ 0 ] ][ Muted ] = 1;
            P_DATA[ params[ 0 ] ][ MutedTime ] = 0;

            if ( db_num_rows( RESULT ) ) format( pQue, sizeof (pQue),"UPDATE `Muted` SET `IP` = '%s', `Muted` = '1', `Time` = '0', `RealName` = '%s'",GetPlayerIPEx( params[ 0 ] ), Name( params[ 0 ] ) );
            else format( pQue, sizeof (pQue), "INSERT INTO `Muted` ( `IP`, `Muted`, `Time`, `RealName` ) VALUES ('%s', '1', '0', '%s')", GetPlayerIPEx( params[ 0 ] ), Name( params[ 0 ] ) );

            db_free_result( RESULT );// freeing the first result before executing second query

            db_free_result(db_query( UsefullDatabase, pQue )); // second sql query + result freed

That's the problem, check my lasts comments in that topic, i solved it, but i don't know, this is so random...
Everywhere in the script, when i try to add something new, pawn crashes...

If it would atleast give me some sort of error code, but no, it has to crash

I could've use even

pawn Код:

db_free_result( alatrala );

It would have been the same! Crash..

Re: Question about SQL. - xxmitsu - 20.04.2011

Always keep in mind freeing the result as soon as you don't need it anymore. And always free it before performing aditional query.

I haven't "played" with SQLite but I'am also a mysql/php programmer and in mysql if you use another query before freeing the result you get "Commands out of sync" error. So I guess you don't get any error here in SQLite but instead it just ends up in a crash.

Re: Question about SQL. - Zh3r0 - 20.04.2011

Quote:

Originally Posted by xxmitsu

Always keep in mind freeing the result as soon as you don't need it anymore. And always free it before performing aditional query.

I haven't "played" with SQLite but I'am also a mysql/php programmer and in mysql if you use another query before freeing the result you get "Commands out of sync" error. So I guess you don't get any error here in SQLite but instead it just ends up in a crash.

I noticed that i need to free the result only when reading and storing.

Do i have to do this even when writing?

I have this code in OnGameModeInit

pawn Код:

db_free_result( db_query( _DB, "CREATE TABLE IF NOT EXISTS `Top` (`Name`,`Kills`,`Deaths`,`Hours`,`Rank`,`Ratio`,`Negative`,`Positive`)" ) );

And 3 more next to it.

so I guess i need to free the result even when writing by doing db_free_result( db_query(..) );

right?

E:

I did as you said, strange enough that the crash occurs in PAWN while it should occur in-game.

PS: Works like charm!
Thanks xxmitsu!

Re: Question about SQL. - xxmitsu - 20.04.2011

Db_query would return "the query result" on success and "false" on sql failure, so I guess it would be a better practice to verify if there's a valid result before freeing it.

I mean (from the code above):

pawn Код:

if(RESULT) db_free_result(RESULT);// freeing the result IF exists and not needed anymore.