Scripting with a little bit of SQL

[arzzzae]

I know that this topic has been asked alot. I do have 0 base knowledge about SQL except for the fact that I know SQL stores server data like string, integer variables and etc. I want to know a lot more about rA scripting with a mix SQL Query.

For example, I want to add a new table in SQL that handles string variables and the new SQL Table will be called in scripts to check if the string variable is already in use.

---------------------------------------

*query_sql("your MySQL query"{, <array variable>{, <array variable>{, ...}}});
*query_logsql("your MySQL query"{, <array variable>{, <array variable>{, ...}}});

Executes an SQL query. A 'select' query can fill array variables with up to 128 rows of values,
and will return the number of rows (i.e. array size).

Note that 'query_sql' runs on the main database while 'query_logsql' runs on the log database.

Example:
set @nb, query_sql("select name,fame from `char` ORDER BY fame DESC LIMIT 5", @name$, @fame);
mes "Hall Of Fame: TOP5";
mes "1."+@name$[0]+"("+@fame[0]+")"; // Will return a person with the biggest fame value.
mes "2."+@name$[1]+"("+@fame[1]+")";
mes "3."+@name$[2]+"("+@fame[2]+")";
mes "4."+@name$[3]+"("+@fame[3]+")";
mes "5."+@name$[4]+"("+@fame[4]+")";

---------------------------------------

I have read that part in the script_commands.txt but I want to see more examples. Thank you.

[Brian]

The script command query_sql() returns the number of rows your SQL query returned.

So if(query_sql() == 0) will be true when the SQL query returned no rows, in this case meaning the code they entered was not found.

[Emistry]

you can refer this site to start learning for SQL stuff..

http://www.w3schools.com/sql/default.asp

 

the query_sql is just used to execute the SQL Query ... and retrieve / save the data returned into the variables assigned.

[arzzzae]

Thanks for the help! Will start reading it!

[Bahmut]

If possible avoid using sql querys in NPC scripts, because it will stop the server from running while the query is executed. This means you will have micro lags on your server when using a lot of sql querys or huge complicated querys which need long to execute.

 

If you just use them in a small amount or less executed scripts you should be fine.

[nanakiwurtz]

How many querries are considered to be 'too much' in a normal scripting usage that can produce problems such as lags etc?

[Bahmut]

It depends on a bunch of factors like usage of the script (relates to player amount), database server performance and probably more

[Capuche]

It depends on how many query your server do at a time finally, isn't it ?

 

prontera,159,180,5	script	hjkl	456,{

	.test++;
	while( .test%10 ) {
		donpcevent strnpcinfo(0)+"::OnTest";
		.test++;
	}
	dispbottom "Loop count: "+ .test;
	end;
OnTest:
	while (1) {
		query_sql( "SELECT `castle_id` FROM `guild_castle` WHERE `guild_id` = '25'", .@t );
		query_sql( "UPDATE `guild_castle` SET `guild_id` = '0' WHERE `castle_id` = 17" );
		query_sql( "UPDATE `guild_castle` SET `guild_id` = '25' WHERE `castle_id` = 17" );
		sleep 1;
	}
}

My computer start lagging when I make 100 loops (so ~300 query - 300000 query/sec)

at 700 I can't sit

Edited February 23, 2013 by Capuche

[arzzzae]

So, computer specs comes into the play.

It depends on how many query your server do at a time finally, isn't it ?

 

My computer start lagging when I make 100 loops (so ~300 query - 300000 query/sec)

at 700 I can't sit

Can I ask for your computer specs if you don't mind?

Edited February 23, 2013 by arzzzae

[Capuche]

Intel® Core i3 CPU @2.53GHz (4 CPUs)

4096MB RAM

[KeyWorld]

If possible avoid using sql querys in NPC scripts, because it will stop the server from running while the query is executed. This means you will have micro lags on your server when using a lot of sql querys or huge complicated querys which need long to execute.

If you just use them in a small amount or less executed scripts you should be fine.

There was an update some month ago to execute the query in another thread if i remember correctly, so you should not be able to freeze just by sending query.

[Bahmut]

I thought Sirius_Black started working on it but never finished it. Or was it fixed by someone else?

[arzzzae]

Can someone show me an example of calling values in a row from a table when a player input something? For example. I have a npc which requires input from the player then the npc will send a query and check and return values.

 

I'm currently trying to make an npc where if you enter a code, it will give you an item depending on what values you entered in the sql table.

 

mes "Please enter the code.";
next;
input .@pc$;

query_sql ("SELECT codes,reward FROM `pcodes` WHERE `codes`='"+escape_sql(#pc$)+"'", .@pc$,.@re);
mes "Congratulations";
next;
getitem .@re,1;
set .@pc$, 1;
close;

 

 

Sorry for terribad engrish.

[Brian]

Here's an example:

CREATE TABLE IF NOT EXISTS `reward_codes` (

  `code` VARCHAR(10) NOT NULL DEFAULT '',

  `nameid` INT(11) UNSIGNED NOT NULL DEFAULT '0',

  `amount` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',

  `redeem_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

  `char_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',

  PRIMARY KEY (`code`)

) ENGINE=MYISAM;

mes "Please enter the code:";

input .@pc$;

if (query_sql("SELECT nameid,amount,char_id FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'", .@item,.@amt,.@char_id) == 0) {

	mes "^FF0000 Reward code not found.";

} else if (.@char_id) {

	mes "^FF0000 This reward code has already been redeemed.";

} else if (!checkweight(.@item,.@amt)) {

	mes "You are overweight or carrying too many items.";

} else {

	getitem .@item,.@amt;

	mes "Congratulations";

	query_sql "UPDATE `reward_codes` SET `redeem_time`=NOW(), `char_id`="+getcharid(0)+" WHERE `code`='"+escape_sql(.@pc$)+"'";

}

close;

[arzzzae]

Thanks Brian! 

 

By the way, what does the ==0 in your query_sql does?

 

Edit: Got it now. 

Edited March 1, 2013 by arzzzae