SQLite

[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.

[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.

[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()?

[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.

[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?

[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.

[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.

[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 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.

[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.

[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.

[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.