PDA

View Full Version : How do I use SQLite?


Cubical
03-11-2010, 09:41 AM
I have read the wiki post on it but I cannot seem to grasp the whole concept. Could someone post a dumbed down tutorial on it or an example like:

creating a table then adding something to it then making the player.chat something from the table.

I can't seem to grasp how to make a table, would I just do something similar to this?

function onActionServerside(){
CREATE TABLE 'nicknames';
for (pl:allplayers){
INSERT INTO 'nicknames' pl.nick;
}
}
//#CLIENTSIDE
function onCreated(){
triggerserver("gui",name,NULL);
}

SQLite makes me feel like I have the brain power of an autistic 3 year old. I'm just trying to get over the first hump and it should be smooth sailing after that.

cbk1994
03-11-2010, 09:47 AM
I started writing a tutorial for it, but never finished. It's a good starting point, though. You can find it here (http://wiki.graal.net/index.php/Creation/Dev/Gscript/Using_SQLite).

Once you've gotten a basic understanding, I'm willing to answer any questions you have (and I'm sure others are too).

EDIT: Oh, I see what you mean.


function onCreated() {
temp.request = requestsql("SELECT * FROM bank", true);

waitFor(request, "onReceiveData", 60);

for (temp.row : request.rows) {
echo(row.account @ ": " @ row.money);
}
}


You use the command requestsql(str query, bool expectReturn). I find it useful to write your own wrapper function.


function req(query, expect) {
temp.req = requestsql(query, expect);

if (expect) {
waitFor(req, "onReceiveData", 60);
}

if (req.error != null) {
echo("SQL Error: " @ req.error);
echo(" Query: " @ query);
}

return req;
}


Then you would use it like


temp.query = req("SELECT * FROM bank", true);

for (temp.row : query.rows) {
echo(row.account @ ": " @ row.money);
}


Eventually you'll also want to be able to select a database, too... something like this:

function req(db, query, expect) {
temp.req = requestsql2(db, query, expect);

if (expect) {
waitFor(req, "onReceiveData", 60);
}

if (req.error != null) {
echo("SQL Error (" @ db @ ": " @ req.error);
echo(" Query: " @ query);
}

return req;
}

Cubical
03-11-2010, 10:00 AM
That's the article I was talking about, I don't really know anything other than req() is as it's the only GS2 function i see in there. All I really see is how to interact with SQLExplorer. If you could give me a simple example in GS how to store variables in a table I think I'll probably catch on to the other stuff.

cbk1994
03-11-2010, 10:01 AM
That's the article I was talking about, I don't really know anything other than req() is as it's the only GS2 function i see in there. All I really see is how to interact with SQLExplorer. If you could give me a simple example in GS how to store variables in a table I think I'll probably catch on to the other stuff.

I edited my original post. See if that helps.

Cubical
03-11-2010, 10:10 AM
I edited my original post. See if that helps. How would I create a table to begin with? I see requestsql and req, is there a sendsql() or inputsql()

cbk1994
03-11-2010, 10:14 AM
How would I create a table to begin with? I see requestsql and req, is there a sendsql() or inputsql()

The last parameter is whether or not to expect a response, so you would do


requestsql("CREATE TABLE bank (account TEXT UNIQUE NOT NULL DEFAULT '', money INT NOT NULL DEFAULT 0)", false);


or, if using a wrapper like the one specified earlier


req("CREATE TABLE bank (account TEXT UNIQUE NOT NULL DEFAULT '', money INT NOT NULL DEFAULT 0)", false);


You might also want to look for examples in the code gallery, such as this one (http://forums.graalonline.com/forums/showthread.php?t=134256362) by Switch.

Cubical
03-11-2010, 11:37 AM
The last parameter is whether or not to expect a response, so you would do


requestsql("CREATE TABLE bank (account TEXT UNIQUE NOT NULL DEFAULT '', money INT NOT NULL DEFAULT 0)", false);


or, if using a wrapper like the one specified earlier


req("CREATE TABLE bank (account TEXT UNIQUE NOT NULL DEFAULT '', money INT NOT NULL DEFAULT 0)", false);


You might also want to look for examples in the code gallery, such as this one (http://forums.graalonline.com/forums/showthread.php?t=134256362) by Switch.
I'm looking at this http://www.w3schools.com/sql/sql_create_table.asp and the post on the wiki I do not see what the TEXT UNIQUE NOT NULL DEFAULT is for.

edit: nevermind I found it.
edit edit: no i didnt ;[
edit edit edit: i think i found it this time

Cubical
03-11-2010, 12:19 PM
Well, I have dumbed it down as simple as I could and can't seem to get this working ;[, any ideas on what I'm doing wrong?

function onActionServerside(options){
temp.tokens = options.tokenize();
switch (temp.tokens[0]){
case ":create":
temp.statement = "CREATE TABLE " @ temp.tokens[1] @" (account,nickname DEFAULT 'unknown',currenttime DEFAULT '0')";
requestsql(temp.statement, false);
echo("CREATED TABLE: " @ temp.tokens[1]);
break;
case ":insert":
temp.statement = "INSERT INTO "@ temp.tokens[1] @" VALUES('"@player.account@"')";
requestsql(temp.statement, false);
echo("INSERTED: " @ player.account SPC player.nick SPC timevar2 @ " INTO " @ temp.tokens[1]);
break;
case ":select":
temp.statement = "SELECT * FROM " @ temp.tokens[1];
temp.i = requestsql(temp.statement, false);
echo(temp.i.rows.size());
break;
case ":update":
temp.statement = "";
requestsql(temp.statement, false);
break;
case ":delete":
temp.statement = "";
requestsql(temp.statement, false);
break;
}
}
//#CLIENTSIDE
function onPlayerChats(){
triggerserver("gui",name,player.chat);
}

cbk1994
03-11-2010, 12:47 PM
Well, I have dumbed it down as simple as I could and can't seem to get this working ;[, any ideas on what I'm doing wrong?


Try using the wrapper function I gave in a previous post. It will echo the errors in your SQL queries (there's at least one I noticed in the create statement).

Cubical
03-11-2010, 12:55 PM
It doesn't seem to be showing an error in it.
Edit: I'll mess with it tomorrow because I'm tired, I'm sure I'll be able to figure it out by myself when I'm rested.

cbk1994
03-11-2010, 01:19 PM
It doesn't seem to be showing an error in it.

What part isn't working? Have you checked if the table is being created (using SQL Explorer)?

What I noticed is that you didn't specify a type for "account" when creating the table.

Cubical
03-11-2010, 01:40 PM
It's being created as is, I fixed what you were just talking about and still nothing.


function onActionServerside(options){
temp.tokens = options.tokenize();
switch (temp.tokens[0]){
case ":create":
temp.statement = "CREATE TABLE " @ temp.tokens[1] @ " (account TEXT NOT NULL DEFAULT 'guest',nickname TEXT NOT NULL DEFAULT 'unknown',currenttime DEFAULT '0')";
req(temp.statement, false);
echo("CREATED TABLE: " @ temp.tokens[1]);
break;
case ":insert":
temp.statement = "INSERT INTO "@ temp.tokens[1] @" VALUES('"@player.account@"','"@player.nick@"',"@timevar2@")";
req(temp.statement, false);
echo("INSERTED: " @ player.account SPC player.nick SPC timevar2 @ " INTO " @ temp.tokens[1]);
break;
case ":select":
temp.statement = "SELECT * FROM " @ temp.tokens[1];
temp.i = req(temp.statement, false);
echo(temp.i.rows[0]);
break;
case ":update":
temp.statement = "";
requestsql(temp.statement, false);
break;
case ":delete":
temp.statement = "";
requestsql(temp.statement, false);
break;
}
}
function req(query, expect) {
temp.req = requestsql(query, expect);

if (expect) {
waitFor(req, "onReceiveData", 60);
}

if (req.error != null) {
echo("SQL Error: " @ req.error);
echo(" Query: " @ query);
}

return req;
}
//#CLIENTSIDE
function onPlayerChats(){
triggerserver("gui",name,player.chat);
}


when i try to select it returns the value 0

Edit: Wil Soul is wrong......

cbk1994
03-11-2010, 01:47 PM
When using SELECT you expect it to return something, so the second parameter should be 'true'.

Cubical
03-11-2010, 01:52 PM
That was totally the problem, thank you for being so awesome and helping. I wouldn't have been such a nuisance if I wasn't so tired haha.

Admins
03-11-2010, 02:04 PM
The second parameter for requestsql() is an optimization thing kindof: if you specify 'false' then it's just sending the query and forgetting about it, if you specify 'true' then it's getting the query result and possible errors.