What is wrong?

Get Help on scripting in POL with configurations, config setups, script trouble shooting, etc.

Moderator: POL Developer

Post Reply
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

What is wrong?

Post 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?
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post by boberski »

Also there is no SQL connection, DB is hosted locally.
Harley
Forum Regular
Posts: 360
Joined: Sat Mar 18, 2006 1:41 am
Location: Germany

Re: What is wrong?

Post 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!
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post 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 :(
Harley
Forum Regular
Posts: 360
Joined: Sat Mar 18, 2006 1:41 am
Location: Germany

Re: What is wrong?

Post 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" );
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post 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.
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post by boberski »

Mayby you can test it by yourself, database schema is on github of the project.
Turley
POL Developer
Posts: 670
Joined: Sun Feb 05, 2006 4:45 am

Re: What is wrong?

Post by Turley »

How do you start the script? I would search there for the reason why the sleeps are not working.
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post 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.
Harley
Forum Regular
Posts: 360
Joined: Sat Mar 18, 2006 1:41 am
Location: Germany

Re: What is wrong?

Post by Harley »

Do you have some port on your DB?
Maybe you have to disable it, if have.
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post by boberski »

What do you mean by that?
boberski
Grandmaster Poster
Posts: 275
Joined: Tue Oct 15, 2013 12:00 pm

Re: What is wrong?

Post 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
Harley
Forum Regular
Posts: 360
Joined: Sat Mar 18, 2006 1:41 am
Location: Germany

Re: What is wrong?

Post 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.
Turley
POL Developer
Posts: 670
Joined: Sun Feb 05, 2006 4:45 am

Re: What is wrong?

Post 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
Post Reply