https://forums.polserver.com/

What is wrong?

https://forums.polserver.com/viewtopic.php?t=5977

Page 1 of 1

What is wrong?

Posted: Tue Jun 13, 2017 2:41 am
by boberski
I have script for SQL connection, but it is not working properly and I do not have idea why:

Code: Select all

use uo;
use os;
use sql;

include ":webApi:accounts";
include ":webApi:settings";

program GlobalControl()
	var acct, chars, db, settings, index;

	print("Initializing web API!");
	settings := GetSettingsCfgElem( "Settings" );

	while( 1 )
		db := mysql_connect(settings.DBHost,settings.DBUser,settings.DBPass);
		print (settings.DBHost+" "+settings.DBUser+" "+settings.DBPass);
		if( db )
			mysql_select_db(db,settings.DBName);
			
			mysql_query(db,"TRUNCATE TABLE "+settings.CharTable);
			mysql_query(db,"TRUNCATE TABLE "+settings.CharLayers);
			
			print("TRUNCATE TABLE "+settings.CharTable);
			print("TRUNCATE TABLE "+settings.CharLayers);
			
			index := 0;

			foreach acct_name in ( ListAccounts() )
				acct := FindAccount( acct_name );
				
				if ( GetNumCharacters( acct ) > 0 )
					chars := GetCharacters( acct );
					foreach charRef in ( chars )
						if ( charRef )
							var race := "";
							
							if (charRef.trueobjtype == 0x190 || charRef.trueobjtype == 0x191)
								race := "Human";
							elseif (charRef.trueobjtype == 0x25D || charRef.trueobjtype == 0x25E)
								race := "Elf";
							elseif (charRef.trueobjtype == 0x29A || charRef.trueobjtype == 0x29B)
								race := "Gargoyle";
							else 
								race := "Other";
							endif
						
							mysql_query(db,"INSERT INTO "+settings.CharTable+" (char_id, char_name, char_title, char_race, char_body, char_female, char_bodyhue, char_public ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",array{index, charRef.name, charRef.title_prefix, race, charRef.trueobjtype, charRef.gender, charRef.truecolor, 0});
							
							var x, i;
							for (i := 1; i <= 24; i := i + 1)
								x := GetEquipmentByLayer(charRef, i);
								
								if ( (i == 0x0b) || (i == 0x10) || (i == 0x15) )
									mysql_query(db,"INSERT INTO "+settings.CharLayers+" (char_id, layer_id, item_id, item_hue) VALUES (?, ?, ?, ?)",array{index, 0, x.objtype, x.color});
								else
									mysql_query(db,"INSERT INTO "+settings.CharLayers+" (char_id, layer_id, item_id, item_hue) VALUES (?, ?, ?, ?)",array{index, i, x.objtype, x.color});
								endif
							endfor
							
						endif
						
						index := index + 1;
						SleepMs(2);
					endforeach
				endif
				
				Sleep(1);
			endforeach
			
			mysql_close(db);
		endif
		
		Sleep(1800);
	endwhile
endprogram
POL console constantly print result of:
print (settings.DBHost+" "+settings.DBUser+" "+settings.DBPass);

and do not respects this: Sleep(1800);

Where do I make mistake?

Re: What is wrong?

Posted: Tue Jun 13, 2017 2:44 am
by boberski
Also there is no SQL connection, DB is hosted locally.

Re: What is wrong?

Posted: Tue Jun 13, 2017 2:55 am
by Harley
This is example of my SQL connection. And it's working pretty good!

Code: Select all

	var connection := mysql_connect("IP","BASE NAME",password := "PASS");
	if (connection)
		//WriteHtml("MySQL Connection Successful");
		syslog("MySQL Guild List Connection Successful!");
	else
		//WriteHtml("Connection failed: " + connection.errortext );
		syslog("MySQL Guild List Connection Failed: " + connection.errortext );
	endif

	var selectdb := mysql_select_db(connection, "TABLE NAME" );
	if (selectdb)
		//WriteHtml("Database set");
		syslog("MySQL Guild List Database Set!");
	endif
 
	query:="TRUNCATE guildlist";
	query:= mysql_query(connection,query);
.....
			query:="INSERT INTO guildlist ( guild_name , guild_abv , guild_faction , .....
								)
			VALUES ('"+ guild_name +"', '"+ guild_abv +"', '"+ guild_faction +"', .....
						)";

			query:= mysql_query(connection,query);

			if (query)
				//WriteHtml("Row inserted"); // DEBUG
				sleepms( 5 );
			else
				//WriteHtml("Row ERROR: " + query.errortext ); // DEBUG
				//syslog("MySQL Guild List Row Error: " + query.errortext );
				sleepms( 5 );
			endif

	syslog("MySQL Guild List Connection Closed!");
	mysql_close(connection);
I hope that will help you! Thanks for your work!

Re: What is wrong?

Posted: Tue Jun 13, 2017 3:13 am
by boberski
I have changed script to look like:

Code: Select all

use uo;
use os;
use sql;

include ":webApi:accounts";
include ":webApi:settings";

program GlobalControl()
	var acct, chars, settings, index;

	print("Initializing web API!");
	settings := GetSettingsCfgElem( "Settings" );

	while( 1 )
		var db := mysql_connect(settings.DBHost,settings.DBUser,password := settings.DBPass);
		//print (settings.DBHost+" "+settings.DBUser+" "+settings.DBPass);
		if( db )
			mysql_select_db(db,settings.DBName);
			
			mysql_query(db,"TRUNCATE TABLE "+settings.CharTable);
			sleepms(5);
			mysql_query(db,"TRUNCATE TABLE "+settings.CharLayers);
			sleepms(5);
			
			//print("TRUNCATE TABLE "+settings.CharTable);
			//print("TRUNCATE TABLE "+settings.CharLayers);
			
			index := 0;

			foreach acct_name in ( ListAccounts() )
				acct := FindAccount( acct_name );
				
				if ( GetNumCharacters( acct ) > 0 )
					chars := GetCharacters( acct );
					foreach charRef in ( chars )
						if ( charRef )
							var race := "";
							
							if (charRef.trueobjtype == 0x190 || charRef.trueobjtype == 0x191)
								race := "Human";
							elseif (charRef.trueobjtype == 0x25D || charRef.trueobjtype == 0x25E)
								race := "Elf";
							elseif (charRef.trueobjtype == 0x29A || charRef.trueobjtype == 0x29B)
								race := "Gargoyle";
							else 
								race := "Other";
							endif
						
							mysql_query(db,"INSERT INTO "+settings.CharTable+" (char_id, char_name, char_title, char_race, char_body, char_female, char_bodyhue, char_public ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",array{index, charRef.name, charRef.title_prefix, race, charRef.trueobjtype, charRef.gender, charRef.truecolor, 0});
							
							sleepms(5);
							
							var x, i;
							for (i := 1; i <= 24; i := i + 1)
								x := GetEquipmentByLayer(charRef, i);
								
								if ( (i == 0x0b) || (i == 0x10) || (i == 0x15) )
									mysql_query(db,"INSERT INTO "+settings.CharLayers+" (char_id, layer_id, item_id, item_hue) VALUES (?, ?, ?, ?)",array{index, 0, x.objtype, x.color});
								else
									mysql_query(db,"INSERT INTO "+settings.CharLayers+" (char_id, layer_id, item_id, item_hue) VALUES (?, ?, ?, ?)",array{index, i, x.objtype, x.color});
								endif
								
								sleepms(5);
							endfor
							
						endif
						
						index := index + 1;
						sleepms(2);
					endforeach
				endif
				
				sleep(1);
			endforeach
			
			mysql_close(db);
		else
			print ("DBError: "+db.errortext);
		endif
		
		sleep(1800);
	endwhile
endprogram
And I get:
DBError: error{ errortext = "Object does not support members" } in a loop still sleep(1800); is not working :(

Re: What is wrong?

Posted: Tue Jun 13, 2017 3:17 am
by Harley
boberski wrote: ↑Tue Jun 13, 2017 3:13 am I have changed script to look like:

Code: Select all

		var db := mysql_connect(settings.DBHost,settings.DBUser,password := settings.DBPass);
		//print (settings.DBHost+" "+settings.DBUser+" "+settings.DBPass);
		if( db )
			mysql_select_db(db,settings.DBName);
And I get:
DBError: error{ errortext = "Object does not support members" } in a loop still sleep(1800); is not working :(
Look at my script please.
There is "' - quotes. I think problem is in it.

I have them:

Code: Select all

	var connection := mysql_connect("IP","BASE NAME",password := "PASS");
	...
	var selectdb := mysql_select_db(connection, "TABLE NAME" );

Re: What is wrong?

Posted: Tue Jun 13, 2017 4:47 am
by boberski
var db := mysql_connect("localhost","root",password := "pass");
I already tried this and this:
var db := mysql_connect("localhost","root", "pass");

Same efect.

Re: What is wrong?

Posted: Tue Jun 13, 2017 6:37 am
by boberski
Mayby you can test it by yourself, database schema is on github of the project.

Re: What is wrong?

Posted: Wed Jun 14, 2017 9:08 am
by Turley
How do you start the script? I would search there for the reason why the sleeps are not working.

Re: What is wrong?

Posted: Wed Jun 14, 2017 12:00 pm
by boberski
This is a start.src script so it is starting along side of pol. Do you have any idea why db is not working with following error? DB credentials are 100% correct.

Re: What is wrong?

Posted: Wed Jun 14, 2017 12:45 pm
by Harley
Do you have some port on your DB?
Maybe you have to disable it, if have.

Re: What is wrong?

Posted: Wed Jun 14, 2017 1:07 pm
by boberski
What do you mean by that?

Re: What is wrong?

Posted: Wed Jun 14, 2017 1:59 pm
by boberski
I solve a problem by exporting main code from start.src to another file and only start script from start.src.
I don't know why it also fixed the db but id did :P

Re: What is wrong?

Posted: Thu Jun 15, 2017 2:24 am
by Harley
boberski wrote: ↑Wed Jun 14, 2017 1:59 pm I solve a problem by exporting main code from start.src to another file and only start script from start.src.
I don't know why it also fixed the db but id did :P
Glad to read that! I hoped that you solve that problem.

Re: What is wrong?

Posted: Fri Jun 16, 2017 8:58 am
by Turley
A start.src is called critical. Which means all sleeps are ignored. Plus each db query simply block your complete shard since they need time.
To sum it up never ever try to connect or query something in a critical script ;) basically it's a bug that we even allow it
All times are UTC-08:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited