SQLite

[Admins]

Using requestsql2 with an unconfigured database will result in silence, may be can set the error variable. For the future it could be possible to configure additional databases in the server options but right now it's not possible.

[LoneAngelIbesu]

Originally Posted by Inverness

Stefan said in an earlier post that requestsql2() is for choosing between mysql and sqlite databases, so at the moment it is useless to you and you shouldn't use it.

The /scripthelp entry should be updated as such, then.

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

[Admins]

The requestsql2 information is correct, it lets you select the (configured) database. Right now you cannot configure databases, so there is only "default".

[Inverness]

Originally Posted by LoneAngelIbesu

As good as all of this information is, none of it helps with my original question of how to create 'sub-tables'. Just a light reminder...

No such thing as sub-tables in this case. The syntax you're thinking of is for creating a table in a database other than the default, which is useless in this case since there is only one database. Just create a normal table.

[Inverness]

I tried using string.escape() as you suggested, but like I thought, it escaped single quotes using a backslash which is incorrect, you're supposed to escape single quotes using another single quote.

I also suggest adding "parameterized queries." Which is basically when question marks in the query are replaced by function parameters to protect against SQL injection and stuff. Example:

executesql("INSERT INTO options VALUES (?,?)", {"profit", 9001});

[LoneAngelIbesu]

Originally Posted by Inverness

No such thing as sub-tables in this case. The syntax you're thinking of is for creating a table in a database other than the default, which is useless in this case since there is only one database. Just create a normal table.

Simply because the syntax is incorrect doesn't mean that what I'm trying to do is impossible, mind you. I've considered using a separate table, but would favor my original plan to avoid overly-complicated referencing.

I've looked in to creating VIEWS, but I'm not sure if I'm doing it correctly.

PHP Code:

  sql.execute(
    "CREATE TABLE IF NOT EXISTS shops (
      title TEXT NOT NULL DEFAULT 'Shop', 
      stype TEXT NOT NULL DEFAULT 'Server', 
      owner TEXT NOT NULL DEFAULT 'Unknown', 
      arch TEXT NOT NULL DEFAULT 'Unknown', 
      price INT DEFAULT 0, 
      stock INT DEFAULT 0, 
      kerou INT NOT NULL DEFAULT 5000, 
      desc TEXT NOT NULL DEFAULT 'Description text.', 
      createtime INT NOT NULL DEFAULT CURRENT_TIMESTAMP)", false);
  sql.execute(
    "CREATE VIEW shopitems AS SELECT arch, price, stock FROM shops", false);
  sql.execute(
    "CREATE TRIGGER insert_item INSTEAD OF INSERT ON shopitems
      BEGIN
        INSERT INTO shops (arch, price, stock) VALUES (arch, price, stock);
      END;
     CREATE TRIGGER update_item INSTEAD OF UPDATE ON shopitems
      BEGIN
        UPDATE shops SET arch = new.arch;
        UPDATE shops SET price = new.price;
        UPDATE shops SET stock = new.stock;
      END;", false); 


[devilsknite1]

Would using this in an onPlayerLogin() still work? I tried Stefan's sample age code thing, and I get the working output, but if i try to edit it the slightest bit and use findPlayer( p.account ); (p is a param in the onPlayerLogin() function) I get this:

NPC Code:

---

Accounts in database: 0

---

Here's what I'm using:

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


Like I said, editing the code SLIGHTLY just gives me an output of 0. I didn't go through the whole SQLite language either, which I plan on doing later tomorrow.

[napo_p2p]

Originally Posted by devilsknite1

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


It looks like there's an error in one of your queries (you forgot the single quotes). Try this:

PHP Code:

requestsql("INSERT INTO test VALUES ('" @ p.account @ "'," @ p.id @ ")", false); 


Let me know the result, as I don't have a means to test it right now .

Also, you don't really need the first sql query if you have already created the table (but I guess it doesn't really hurt to include it). And, you don't need to do the findplayer(), since the 'p' passed in is a player object (so you could use temp.p.account and temp.p.id right off the bat).

[Codein]

Originally Posted by devilsknite1

Would using this in an onPlayerLogin() still work? I tried Stefan's sample age code thing, and I get the working output, but if i try to edit it the slightest bit and use findPlayer( p.account ); (p is a param in the onPlayerLogin() function) I get this:

NPC Code:

---

Accounts in database: 0

---

Here's what I'm using:

PHP Code:

function onPlayerLogin( p )
{
  p = findPlayer( player.account );
  requestsql("CREATE TABLE IF NOT EXISTS test (account varchar not null default '' primary key, id int not null default 1)", false);
  requestsql("INSERT INTO test VALUES (" @ p.account @ "," @ p.id @ ")", false);
  temp.req = requestsql("SELECT * FROM test", true);
  if (!temp.req.completed)
    waitfor(temp.req,"onReceiveData",60);
  echo("Accounts in database: " @ temp.req.rows.size());
  for (temp.row: temp.req.rows)
    echo("  Account: " @ temp.row.account @ ", ID: " @ temp.row.id);  
} 


Like I said, editing the code SLIGHTLY just gives me an output of 0. I didn't go through the whole SQLite language either, which I plan on doing later tomorrow.

If you log on Dev Astrid, you can view my testing NPC. I managed to get it working, eventually, haha.

[Admins]

It's better to format queries like this:

PHP Code:

temp.query = format("SELECT * FROM test WHERE account='%s'", player.account.escape());
temp.req = requestsql(temp.query, true); 


Makes it easier to avoid SQL-injection.

[Inverness]

Originally Posted by Stefan

<snip>

You're ignoring the problem with string.escape(), it's supposed to escape single quotes with another single quote in SQL and string.escape() does not do this. So have an sqlescape() global to do this.

[devilsknite1]

Originally Posted by napo_p2p

It looks like there's an error in one of your queries (you forgot the single quotes). Try this:

PHP Code:

requestsql("INSERT INTO test VALUES ('" @ p.account @ "'," @ p.id @ ")", false); 


Output:

NPC Code:

---

Accounts in database: 0

---

Although, not sure since it could have been Stefan's suggestion that broke it ;o

[Inverness]

Originally Posted by devilsknite1

Output:

NPC Code:

---

Accounts in database: 0

---

Although, not sure since it could have been Stefan's suggestion that broke it ;o

You restarted the NPC-Server already to enable the SQL right?

Also, SQLite only has a few column types: TEXT, NUMERIC, INTEGER, REAL, NONE. Other types work for compatibility but they're just aliases for one of these types, I suggest these be used for clarity.

http://www.sqlite.org/datatype3.html

[Admins]

Originally Posted by Inverness

You're ignoring the problem with string.escape(), it's supposed to escape single quotes with another single quote in SQL and string.escape() does not do this. So have an sqlescape() global to do this.

Hmmm I've tested and seen there are some problems yes, it's possible that mysql accepted the format but not sqlite. Will try if it can be modified and made working for both databases.
Update: fixed it, will work fine with the next npcserver update.

[fowlplay4]

Well until the next update I've been using this as a work around.

PHP Code:

function sqlescape(str) {
  if (str.pos("'") >= 0) {
    temp.result = str.escape().tokenize("\\\'");;
    for (temp.piece: result) 
      temp.nresult @= piece @ "''";
    return temp.nresult.substring(0,nresult.length() - 2);
  }
  else return str.escape();
} 


As well as format2 to accomplish that "parameterized query" request.

[Inverness]

Originally Posted by fowlplay4

As well as format2 to accomplish that "parameterized query" request.

Parameterized queries are distinct in the fact that they use the question marks for ambiguity.

[Inverness]

Originally Posted by Stefan

Update: fixed it, will work fine with the next npcserver update.

Well please post when Valikorlia should restart to have the npcserver update.

Also I noticed that you seem to have changed the behavior of trigger(), if this was intentional than I'm very annoyed because that's not something you should change without warning. Example:

#1. trigger onSpecial() with trigger("Special", ...): FAIL
#2. trigger onSpecial() with trigger("onSpecial", ...): SUCCEED
#3. trigger Special() with trigger("Special", ...): FAIL

#1 is how it used to work, #2 is how it works now, and #3 shouldn't work either way, but you would think it would if #2 does.

I assume you've changed this to eliminate the inconsistency with functions that use event names and whether the "on" prefix is part of the event name.

Note: This is all on the same object, if it behaves differently when triggering other objects then I'd like to know.

Edit: #1 works fine when triggering to a different object, I guess doing #1 on self makes the event catchable by catchevent()?

[fowlplay4]

Originally Posted by Inverness

Parameterized queries are distinct in the fact that they use the question marks for ambiguity.

Oh i was not aware of this, hope it gets added then! Using just format made things get veryyy ugly.

[Admins]

Originally Posted by Inverness

I guess doing #1 on self makes the event catchable by catchevent()?

I'm not sure what exactly you were doing, but a few months ago a way has been added to write custom GUI controls (in v6) which allows you do emulate stuff like onMouseDown events with trigger("mousedown") if you trigger it on yourself.

[Admins]

Originally Posted by fowlplay4

Oh i was not aware of this, hope it gets added then! Using just format made things get veryyy ugly.

Why? It's basicly a different way of doing the same, format works fine

[fowlplay4]

I was mainly referring to using format instead of an array and format2

Think your player account example but with 19 parameters, 18 of which were variables with .escape() on the end.

[xXziroXx]

Originally Posted by Stefan

Update: fixed it, will work fine with the next npcserver update.

Have that update been yet? If not, could you please update Maloria's NPC server with it?

[Admins]

Originally Posted by fowlplay4

I was mainly referring to using format instead of an array and format2

Think your player account example but with 19 parameters, 18 of which were variables with .escape() on the end.

Well what is the problem then to just loop through the array and call escape() on them?

[fowlplay4]

Yeah that's what I'm doing now.

[Admins]

The npcserver has been uploaded to all machines, restart it manually to get the new escape() command.

[xXziroXx]

Originally Posted by Stefan

The npcserver has been uploaded to all machines, restart it manually to get the new escape() command.

Cheers.

[devilsknite1]

Originally Posted by Inverness

You restarted the NPC-Server already to enable the SQL right?

Yes, I've done this. Multiple times...

[Inverness]

Currently NULL in SQL is represented as "" in Graal, this is a problem and there needs to be a way to differentiate between zero, an empty string, and NULL.

I was thinking perhaps a literal like "\\NULL\\" or "\\0" in GScript. c = '\0' just causes a compiler error.

[Inverness]

TSQLRequest.affectedrows doesn't appear to be working with my update statement even though I can see the change in a later query.

[Admins]

affectedrows is currently only used for mysql, is not touched by the sqlite database.

[[email protected]]

Any idea how to use the auto increment option as a primary key? I can't figure it out- thanks

[Skyld]

Originally Posted by [email protected]

Any idea how to use the auto increment option as a primary key? I can't figure it out- thanks

http://www.sqlite.org/faq.html#q1

Google will serve you well.

[[email protected]]

Originally Posted by Skyld

http://www.sqlite.org/faq.html#q1

Google will serve you well.

Thanks beautiful :}

[TESTRETIS]

Sorry for the bump, but is it possible in a future RC Update to have a console specifically for SQL Management, and to place and receive queries, with the ability to view a table, without relying on an Echo to RC, to do possible click sorting, displaying and indicating primary keys, composite keys, surrogate, etc.

[cbk1994]

Originally Posted by TESTRETIS

Sorry for the bump, but is it possible in a future RC Update to have a console specifically for SQL Management, and to place and receive queries, with the ability to view a table, without relying on an Echo to RC, to do possible click sorting, displaying and indicating primary keys, composite keys, surrogate, etc.

Would be useful. I just downloaded a lightweight SQLite database manager, and set it as the default application for 'db' files, so when I double click it in file browser, it opens up.

I also built a basic in-game "SQL Explorer". If it was finished, it could eventually be added to client-RC (would actually be quite a nifty feature).

[TESTRETIS]

Originally Posted by cbk1994

Would be useful. I just downloaded a lightweight SQLite database manager, and set it as the default application for 'db' files, so when I double click it in file browser, it opens up.

I also built a basic in-game "SQL Explorer". If it was finished, it could eventually be added to client-RC (would actually be quite a nifty feature).

Wow, that's awesome.

[cbk1994]

Originally Posted by TESTRETIS

Wow, that's awesome.

It'd be a lot more awesome if it supported half the stuff I planned. Right now it can only be used for viewing, but eventually I'd like to implement creating tables, inserting/deleting rows, removing/adding columns, adding/removing indexes, etc.

[cbk1994]

Originally Posted by xXziroXx

I've ran into this problem again.


string.escape(), does - as stated - add another ' infront of ', instead of a backslash.

SQL refuses to accept '', so we really need a command to escape ' with a backslash. Funny enough, I found this:

escapestring(str) - returns string - adds \ in front of ", ' and \, and removes non-printable characters

... which is a lie, or doesn't work. That too escapes ' with a '

str.escape() has been working fine for us.

[cbk1994]

Originally Posted by xXziroXx

... which SQL refuses to accept.

Like I said, works fine for me.

EDIT: What exactly are you trying to do?

PHP Code:

temp.str = "God's Angel";
req("INSERT INTO 'bank' VALUES('" @ str.escape() @ "', 1111, 1)"); 


EDIT: I have noticed that it unnecessarily escapes quotations.

[xXziroXx]

Actually, nevermind. Seems another part of the code was parsing it wrong, making me think it wasn't working. I'll go ahead and delete my previous posts.