Retrieve INT value from SQLite query

#1 The answer is no as far as i know.
Little bit of advice: Using update is slow. It is quicker to delete the entry then add a new one.

#2 Just use an insert? But I still need to somehow get an INT value out of that query and not a string. Is there a toInt() function I could use?

#3 Not sure about sqlLite with torque but with php and mysql you use:
%kills = %res['Kills'];
%kills++;

%result = $SqLite.query("UPDATE Accounts SET Kills = \'"@%kills@"\' WHERE Name = \'"@%user@"\'",0);

That should work.

Another question: Is your sql server on the same machine as your torque game server? If i is then don't worry, if if ain't, i wouldnt directly access sql with torque because it means receiving lots of data just to do simple things. Use a web server to access the mysql data and use torque to access the web server.

#4 Or just:

%result = $SqLite.query("UPDATE Accounts SET Kills = \'" @ (%res['Kills'] + 1) @ "\' WHERE Name = \'"@%user@"\'",0);

#5 I have had the same problems, for some reason the resource you are using does not like dealing with int's at all (both retrieving and sending). They way I worked around it is storing the kill amount as a string (varchar in mysql). This worked great for me as Torque will treat the data as an int a well as php even though in the db it is stored as a string.

I have had NO problems whatsover with this method thus far. I am doing the exact thing (storing kills and other such information on a server).

#6 @Kyle
The %res in your code example would hold an SQLite 'result object', not the single numeric result of your query. Therefore when your code does this: '%res++', you're just incrementing the object id of the result object (to what effect? who knows).

To retrieve a result of any type, use something like this:

%result = $SqLite.query("SELECT Kills FROM Accounts WHERE Name=\'" @ %user @ "\'",0);
%intVal = 0;
if(%result > 0)
{
	if(!$SqLite.endOfResult(%result)) //if you're sure you are only going to have one row
	{
		%intVal = $SqLite.getColumn(%result,"Kills");
		%intVal++;
		%updateresult = $SqLite.query("UPDATE Accounts SET Kills = "@%intVal@" WHERE Name = \'"@%user@"\'",0);
		//you should probably check the %updateresult to see if it worked
		$SqLite.clearResult(%updateresult);		
	}
	$SqLite.clearResult(%result);
}
else
	echo("SQLITE QUERY ERROR!");

In this particular case, it might be easier to just use this:

%result = $SqLite.query("UPDATE Accounts SET Kills = Kills+1 WHERE Name = \'"@%user@"\'",0);

#7 @Brian - Thanks for that help, your last little line of code works awesome!


I am just going to file this under the same thread because it is a very similar question and I hate being a thread whore..

This whole getColumn thing is crashing my server when it tries to run the line of code. Here is the function I am looking at now.

function ServerCmdGetClientStats(%user)
{
   warn("HERE!");
   %result = $SqLite.query("SELECT * FROM Accounts WHERE Name = \'"@%user@"\'",0);
   echo(%result); // Outputs some wierd number, but I ran this query in a Database Browser and %result contains the right information
   %STR=0;
   [b]%STR=$SqLite.getColumn(%result,"STR"); [/b][i]//CRASHES HERE[/i]
   echo(%STR);
   //echo(%result['STR'] @ "\' \'" @ %result['DEX'] @ "\' \'" @ %result['AGI'] @ "\' \'" @ %result['CON'] @ "\' \'" @ %result['WIS'] @ "\' \'" @ %result['INT'] @ "\' \'" @ %result['CHA'] @ "\' \'" @ %result['LUK'] @ "\' \'" @ %result['Kills'] @ "\' \'" @ %result[Deaths]);
   if(%result)
      CommandToClient(%client,'TellClientStats',%result['STR'],%result['DEX'],%result['AGI'],%result['CON'],%result['WIS'],%result['INT'],%result['CHA'],%result['LUK'],%result['Kills'],%result['Deaths']);
   else
      error("SQLITE QUERRY ERROR! : ServerCmdTellClientStats");
}

My question is how do I select a certain cell in the database?

#8 From what I remember of dealing with SQLite, I was under the impression that it was a sort of "typeless" database, where everything is stored as a string internally. If that's the case, then it would make sense to always retrieve fields as strings with SQLite.

But the fields are tagged with types and it can automagically cast them as that type when you retrieve them, so that's still a *little* odd.

Quote:My question is how do I select a certain cell in the database?

Like a certain field that you want returned? If you want to pick just a particular field, I would say to modify your original SQL select statement.

Something like: (change in bold)

%result = $SqLite.query("SELECT [b]STR[/b] FROM Accounts WHERE Name = \'"@%user@"\'",0);

If STR is the name of your field. If the field that you want to select is called KILLS or whatever, then replace it with that field name instead. "SELECT *" is generally a bad idea because if your table structure changes, old code might get back some fields that it's not expecting. It's safest to specify which fields you want exactly.

I *hope* that answered your question, but if I missed what you were asking, then please reload and I'll try and fire another answer. :)

Edit: Okay. I reread your post another time or two and it looks like I totally didn't answer your question at all. Sorry about that!

Quote:echo(%result); // Outputs some wierd number, but I ran this query in a Database Browser and %result contains the right information

Hrm. This makes me wonder if it's tagging the string, or if it's referring to a special kind of data block. Have you tried doing a %result.dump(); to get more information about that object?

Edit #2: Another thing I noticed...

Quote:

echo(%result['STR'] @ "\' \'" @ %result['DEX'] @ "\' \'" @ %result['AGI'] @ "\' \'" @ %result['CON'] @ "\' \'" @ %result['WIS'] @ "\' \'" @ %result['INT'] @ "\' \'" @ %result['CHA'] @ "\' \'" @ %result['LUK'] @ "\' \'" @ %result['Kills'] @ "\' \'" @ %result[Deaths]);

You do know that you're tagging those strings by saying 'STR' and 'DEX' instead of "STR" and "DEX", right? I'm pretty new to Torque, but I don't think you'd want to tag strings unless you were sending those across the network right then. I could be very wrong about that.

--clint

#9 Hmm...this might be a bit complicated. On the surface of it, I don't see anything obviously wrong with your code.

One possibility that I can think of off the bat: You aren't using an '$SqLite.endOfResult(%result)' in there to check that it found some records before trying to retrieve column values. Can you be sure that the query is returning some rows? The SQLiteObject resource code can be fairly unforgiving of illegal operations in some places (I've had more than one blowup from it when I fed it nonsensical values).

Possible cause of confusion being, are you thinking that the '%user' variable contains a string copy of the username, or a numeric value? If that is a 'commandToServer' type function, the '%user' variable will be a numeric reference to the client ID. It might not be finding any records under the numeric client id. You have to do a bit more to get the client username string (if that's a problem). This would also explain why you get results when you run the query in a database browser -- you're running a different query. I ran into this exact problem.

To see if this is what's going on, store the whole query string in a variable, and echo it before you run the query.

One last, unrelated point, in your CommandToClient call lower down, you're using '%client' as the client parameter, but it's never set. You should probably use '%user' in this case.

#10 It is too early in the morning for me to make any sort of logical post about this, but I must give some thanks at this point to those that have helped me.

@ Paul Griffiths - Thank you for that incrament code, I have not fully tested it but that appears to be doing what it is supposed to.

When retrieving this information from the SQLite database I thought if the %result variable contained multiple columns of data you could reference each of them by using %result["Column Name"] but apparently you can't. Also is there a way to convert strings to intigers? Someone said that when you retrieve data from SQLite it always returns a string no matter what the datatype is set to in the database, is this true?

#11 Sqlite is not typeless. The type defined in the table will drive the result you get. For example I have an Attributes table, where the attributevalue field was defined as a float when I created the table. Now I can do the following:

function Character::loadStats(%this)
{
	%sqlite = new SQLiteObject(sqlite);
	// open database
	%sqlite.openDatabase("characters.db");
	%query = "SELECT AttributeName, AttributeValue FROM Attributes a, characters c WHERE a.CharacterID = c.CharacterID and c.CharacterName = '" @ %this.name @ "'";
	%result = %sqlite.query(%query, 0);
	// attempt to retrieve result data
	while (!%sqlite.endOfResult(%result)) {
		%attribute = %sqlite.getColumn(%result, "AttributeName");
		echo("get attribute " @ %attribute);
		switch$ (%attribute) { 
     	          case "Attack": 
		    %this.Attack = %sqlite.getColumn(%result, "AttributeValue");
		    echo("set attack to " @ %this.Attack);
		  case "Defense": 
		    %this.Defense = %sqlite.getColumn(%result, "AttributeValue");
		  case "Life": 
		    %this.LifeBase = %sqlite.getColumn(%result, "AttributeValue");
		    %this.LifeCurrent = %this.LifeBase;
		  case "Range": 
		    %this.Range = %sqlite.getColumn(%result, "AttributeValue");
		  case "Damage": 
		    %this.Damage = %sqlite.getColumn(%result, "AttributeValue");
		  case "Move": 
		    %this.MoveBase = %sqlite.getColumn(%result, "AttributeValue");
		    %this.MoveCurrent = %this.MoveBase;
		}  
		%sqlite.nextRow(%result);
	}
	%sqlite.clearResult(%result);
	%sqlite.closeDatabase();
}

So the other thing I want to mention really quick is that based on what I've seen of your database design, you may want to spend a little time planning out a good structure for your tables now, before you get too far into it. Time spent up front on a good design will pay big benefits down the road. What I've seen is that you have an account table that also has a character's strength, agility, dexterity, etc. stored in it in addition to their stats on kills. It seems like you could use some normalization. :)

I don't mean to range off topic too much, but think of the tables in your database kinda like the classes you would design in your code - each class has a specific purpose and where it makes sense to break out bits of one class into it's own reusable class, that's a good place to create new tables. For example - an account table has username, password and email address for example... and each account may have multiple characters, where each character has it's own name, race, class, etc. Then in a separate table you could store their skills/attributes with name/value pairs like you see in my example above. And then in a seperate table you would have their inventory. In another table, you could keep the character's statistics. There's a pretty good description here for this "normalization" process and how to create relationships between the tables: dev.mysql.com/tech-resources/articles/intro-to-normalization.html
You have a very powerful system their in sqlite, but with great power comes great responsibility :)

Enjoy and good luck!

Edit: you may also want to take a look at the freely available sqlite administrator tool for setting up all those tables and setting the datatypes on your columns. sqliteadmin.orbmu2k.de
-Andrew

#12 Kyle, i thought torque script was typeless? So it shouldn't matter what sql result's type is?
But if not try adding 0 to the result and see if it turns in to an integer? eg. result = result + 0;
It's a bit of a hack but it might work.