SQL Help

[Ohk4y]

Hello there!

I did some research and finally figured out how to create a table with SQL Explorer, as well as make some columns and rows. I have a table that has Accounts as the name that has some columns that display:

PHP Code:

rowid Username Password EZ Level X Y Exp 


If I go to the Execute SQL tab and type in:

PHP Code:

SELECT Username,Password,EZ,Level,X,Y,Exp FROM Accounts 


and it outputs with some information that I manually typed in:

PHP Code:

3 rows returned
|Exp|EZ |Level         |Password|Username|X |Y |
------------------------
|56 |975|forest_test.nw|Test1   |Ohk4y   |30|30|
|80 |200|forest_test.nw|Test1   |Test    |28|31|
|0  |0  |0             |0       |Player  |0 |0 | 


My question here is; How can I request information from the table with a Weapon Script? Also, How could I write information, as well as add rows to it with a Script.

Thanks,
Ohk4y

[fowlplay4]

My preferred method is to create a DB-NPC called SQL.

PHP Code:

/**
    SQL Query Interface
*/

public function executeSQL(query, result, doecho) {
  temp.init = timevar2;
  temp.results = requestsql(query, result);
  if (result) {
    // Check Results
    if (results.error != "") {
      echo(format("SQL Error: %s", results.error));
      savelog2("sqlerrors.txt", results.error NL query);
      return NULL;
    }
    else if (results.rows.size() > 0 || query.starts("SELECT")) {
      if (doecho) echo(format("SQL returned %s rows.", results.rows.size()));
    }
    else if (results.affectedrows.size() > 0) {
      if (doecho) echo(format("SQL affected %s rows.", results.affectedrows.size()));
    }
    else if (results.completed) {
      if (doecho) echo(format("SQL %s Query completed successfully.", query.tokenize()[0].upper()));
    }
    temp.taken = timevar2 - temp.init;
    // Uncomment to log queries (for checking performance, etc.)
    //savelog2("sqlqueries.txt", query NL "Took " @ temp.taken @ " seconds");
    return results;
  }
} 


Then in my server-side code I can do the following:

PHP Code:

function onCreated() {
  temp.query = "SELECT Username,Password,EZ,Level,X,Y,Exp FROM Accounts";
  temp.result = SQL.executeSQL(temp.query, true);
  if (temp.result.rows.size() > 0) {
    for (temp.row: temp.result.rows) {
      echo(temp.row.Username);
    }
  }
} 


I threw this together quickly thought so I can't guarantee it'll work for your situation.

There's some other links here:
http://public.zodiacdev.com/index.ph...ntro_to_SQLite

[cbk1994]

Personally I'd do something like this instead

PHP Code:

/** 
    SQL Query Interface 
*/ 

public function executeSQL(db, query, result, doecho) { 
  temp.init = timevar2; 
  temp.results = requestsql2(db, query, result); 
  if (result) { 
    // Check Results 
    if (results.error != "") { 
      echo(format("SQL Error: %s", results.error)); 
      savelog2("sqlerrors.txt", results.error NL query); 
      return NULL; 
    } 
    else if (results.rows.size() > 0 || query.starts("SELECT")) { 
      if (doecho) echo(format("SQL returned %s rows.", results.rows.size())); 
    } 
    else if (results.affectedrows.size() > 0) { 
      if (doecho) echo(format("SQL affected %s rows.", results.affectedrows.size())); 
    } 
    else if (results.completed) { 
      if (doecho) echo(format("SQL %s Query completed successfully.", query.tokenize()[0].upper())); 
    } 
    temp.taken = timevar2 - temp.init; 
    // Uncomment to log queries (for checking performance, etc.) 
    //savelog2("sqlqueries.txt", query NL "Took " @ temp.taken @ " seconds"); 
    return results; 
  } 
} 


Small change, but it lets you use multiple databases.

Example:

PHP Code:

function onCreated() { 
  temp.query = "SELECT Username,Password,EZ,Level,X,Y,Exp FROM Accounts"; 
  temp.result = SQL.executeSQL("default", temp.query, true); 
  if (temp.result.rows.size() > 0) { 
    for (temp.row: temp.result.rows) { 
      echo(temp.row.Username); 
    } 
  } 
} 


[Ohk4y]

I added this as -Ohk4y/Query

PHP Code:

/** 
    SQL Query Interface 
*/ 

public function executeSQL(db, query, result, doecho) { 
  temp.init = timevar2; 
  temp.results = requestsql2(db, query, result); 
  if (result) { 
    // Check Results 
    if (results.error != "") { 
      echo(format("SQL Error: %s", results.error)); 
      savelog2("sqlerrors.txt", results.error NL query); 
      return NULL; 
    } 
    else if (results.rows.size() > 0 || query.starts("SELECT")) { 
      if (doecho) echo(format("SQL returned %s rows.", results.rows.size())); 
    } 
    else if (results.affectedrows.size() > 0) { 
      if (doecho) echo(format("SQL affected %s rows.", results.affectedrows.size())); 
    } 
    else if (results.completed) { 
      if (doecho) echo(format("SQL %s Query completed successfully.", query.tokenize()[0].upper())); 
    } 
    temp.taken = timevar2 - temp.init; 
    // Uncomment to log queries (for checking performance, etc.) 
    //savelog2("sqlqueries.txt", query NL "Took " @ temp.taken @ " seconds"); 
    return results; 
  } 
} 


And added this as -Ohk4y/Test3

PHP Code:

function onCreated() { 
  temp.query = "SELECT Username,Password,EZ,Level,X,Y,Exp FROM Accounts"; 
  temp.result = SQL.executeSQL("default", temp.query, true); 
  if (temp.result.rows.size() > 0) { 
    for (temp.row: temp.result.rows) { 
      echo(temp.row.Username);
    } 
  } 
} 


And recieved no echo in rc 0.0

[fowlplay4]

Re-read my post and you'll see I put executeSQL in a DB-NPC named SQL.

So you either need to update the Test script or create the DB-NPC as suggested.

[Ohk4y]

Okay thank you. I put the Query in a dbnpc named SQL and the other script in an accounts weapon, it echos everything fine, though, I can't quite figure out how to write data to the table.

[cbk1994]

Originally Posted by Ohk4y

Okay thank you. I put the Query in a dbnpc named SQL and the other script in an accounts weapon, it echos everything fine, though, I can't quite figure out how to write data to the table.

INSERT and UPDATE. If you google those along with SQLite you should see plenty of resources.

[Ohk4y]

Thanks.