Graal Forums - SQL Help

Graal Forums (https://forums.graalonline.com/forums/index.php)

- NPC Scripting (https://forums.graalonline.com/forums/forumdisplay.php?f=8)

- - SQL Help (https://forums.graalonline.com/forums/showthread.php?t=134264635)

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

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

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); 
    } 
  } 
}

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

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.

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.

Quote:

Originally Posted by Ohk4y (Post 1669270)

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.