How do I use SQLite?

Cubical · #1 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?

PHP Code:


			
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 · #2 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.

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.

PHP Code:


			
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.

PHP Code:


			
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

PHP Code:


			
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:

PHP Code:


			
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 · #3 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 · #4 03-11-2010, 10:01 AM

Quote:

Originally Posted by Cubical

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 · #5 03-11-2010, 10:10 AM

Quote:

Originally Posted by cbk1994

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 · #6 03-11-2010, 10:14 AM

Quote:

Originally Posted by Cubical

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

PHP Code:


			
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

PHP Code:


			
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 by Switch.

Cubical · #7 03-11-2010, 11:37 AM

Quote:

Originally Posted by cbk1994

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

PHP Code:


			
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

PHP Code:


			
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 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 · #8 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?

PHP Code:


			
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 · #9 03-11-2010, 12:47 PM

Quote:

Originally Posted by Cubical

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 · #10 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 · #11 03-11-2010, 01:19 PM

Quote:

Originally Posted by Cubical

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 · #12 03-11-2010, 01:40 PM

It's being created as is, I fixed what you were just talking about and still nothing.

PHP Code:


			

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 · #13 03-11-2010, 01:47 PM

When using SELECT you expect it to return something, so the second parameter should be 'true'.

Cubical · #14 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 · #15 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.