More Database Woes - the console spits out an error(see Code)

King Nothing · Oct 21st 2016

Java

package Mail;


import java.sql.ResultSet;
import java.sql.SQLException;
import net.risingworld.api.Plugin;
import net.risingworld.api.Server;
import net.risingworld.api.database.Database;
import net.risingworld.api.events.EventMethod;
import net.risingworld.api.events.Listener;
import net.risingworld.api.events.player.PlayerCommandEvent;
//import net.risingworld.api.events.player.PlayerConnectEvent;
import net.risingworld.api.events.player.PlayerSpawnEvent;
import net.risingworld.api.objects.Player;


public class Mail extends Plugin implements Listener
{
	protected Database db	= null;


	@Override
	public void onEnable()
	{
		db = getSQLiteConnection(getPath() + "/Mail.db");
		db.execute("CREATE TABLE IF NOT EXISTS 'MailPeople' ('id' INTEGER PRIMARY KEY, "
			+"'player_name' CHAR(64) NOT NULL DEFAULT ('[NoName]'),"
			+"'recipient_name' CHAR(64) NOT NULL DEFAULT ('[NoName]'),"
			+"'time' CHAR(64) NOT NULL DEFAULT ('[NoTime]'),"
			+"'text' CHAR(64) NOT NULL DEFAULT('[NoText]'),"
			+"'read' INTEGER NOT NULL DEFAULT ( 0 ));");
//		db.close();
		registerEventListener(this);
	}


	@Override
	public void onDisable()
	{
		if (db != null)
			db.close();
	}
/*
	@EventMethod
	public void OnConnect(PlayerConnectEvent event)
	{
	}
*/
	@EventMethod
	public void onSpawn(PlayerSpawnEvent event) throws SQLException
	{
		Player player = event.getPlayer();
		player.sendTextMessage("Checking Your Mailbox...");
		boolean	somethingToRead	= false;
		String query = "SELECT * FROM 'MailPeople' WHERE 'recipient_name' = '" + player.getName()
				+ "' AND 'read' = 0";
		try (ResultSet result = db.executeQuery(query))
		{
			//String fromwhom = result.getString("player_name");
			if(result.next())
			{
				String fromwhom = result.getString("player_name"); 
				String recipient = result.getString("recipient_name");
				String timestamp = result.getString("time");
				String body = result.getString("text");
				int ifread = result.getInt("read");
//				String fromwhom = result.getString("player_name");
				if (ifread == 0)
					somethingToRead = true;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		if (somethingToRead)
			player.sendTextMessage("You Got Mail, You Lucky Stiff!");
		else
			player.sendTextMessage("Nothing but Dust and Spiders in your Mailbox");
		Player sender = event.getPlayer();	// ???
		Server server = getServer();		// ???
	}


	@EventMethod
	public void onCommand(PlayerCommandEvent event) throws SQLException
	{
		Player player = event.getPlayer();
		Player sender = event.getPlayer();		// never used ???
		String command = event.getCommand();
		Server server = getServer();
		//split the command
		String[] cmd = command.split(" ");
		//check the command
		if(cmd[0].equals("/mail"))
		{
			//check if player Entered a Recipient
			if(cmd.length > 1)
			{
				String recipient = command.substring(5);
				String fromwhom = player.getName();
				String timestamp = server.getGameTime();
				String body = "I love you." ;
				player.sendTextMessage("Sent to:" + recipient);
				String query = "INSERT INTO MailPeople ('player_name','recipient_name','time','text','read') VALUES ('"+fromwhom+"','"+recipient+"','"+timestamp+"','"+body+"' ,0);";
				db.executeUpdate(query);
//				db.close();
			} 
		}
		if(cmd[0].equals("/read"))
		{
//			Database db = getSQLiteConnection(getPath() + "/Mail.db");
//	this happens when an SQL exception occurs. You have to catch this
//	exception. If you just want to print the error, you could call
//	e.printStackTrace();
			String query = "SELECT * FROM 'MailPeople' WHERE 'recipient_name' = '" + player.getName()
					+ "' AND 'read' = 0";
			try (ResultSet result = db.executeQuery(query))
			{
//				if(result.next())
				while(result.next())
				{
					String fromwhom = result.getString("player_name"); 
					String Recipiant = result.getString("recipient_name");
					String timestamp = result.getString("time");
					String body = result.getString("text");			// never used ???
					int ifread = result.getInt("read");				// never used ???
					int id = result.getInt("id");
					player.sendTextMessage("I love: " + fromwhom); 
					player.sendTextMessage("I farted on: " + Recipiant);
					player.sendTextMessage("the time: " + timestamp);
					db.executeUpdate("UPDATE MailPeople SET UPDATE MailPeople SET 'read' = 1 WHERE 'id' = " + id);
					player.sendTextMessage("REad");
//					db.close();
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}

Display More

java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at pluginapi.PluginEventHandler.triggerEvent(SourceFile:166)
at aF.b.a(SourceFile:171)
at aF.b.messageReceived(SourceFile:121)
at k.e.a(SourceFile:109)
at k.c.run(SourceFile:65)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 1
at Commands.Commands.onCommand(Commands.java:79)
... 9 more
Mirrawe helped me and I still cannont figure this out....

red51 · Oct 21st 2016

Please use the code tag for posting any source code, otherwise the indentings get lost, which makes the code difficult to read:

When writing a plugin, it's also helpful to have a look at the output console, since it will print errors in case something goes wrong. On a server, just check the server output, on the client (when playing singleplayer), you can set "game_debug_console" in the config.properties file to true.

In this case the INSERT statement throws an SQL exception, since no column "text" was found. The first CREATE statement does not create a column "text" (it neither creates a column "read")

King Nothing · Oct 21st 2016

Java

package Mail;
import static java.awt.SystemColor.text;
import static java.rmi.server.ObjID.read;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
    import net.risingworld.api.Plugin;
import net.risingworld.api.Server;
import net.risingworld.api.database.Database;
    import net.risingworld.api.events.EventMethod;
    import net.risingworld.api.events.Listener;
    import net.risingworld.api.events.player.PlayerCommandEvent;
    import net.risingworld.api.objects.Player;


   public class Mail extends Plugin implements Listener{


        @Override
        public void onEnable(){




              registerEventListener(this);
        }
        @Override
        public void onDisable(){
            //...
        }
        @EventMethod
        public void onCommand(PlayerCommandEvent event) throws SQLException{
        Player player = event.getPlayer();
        String command = event.getCommand();
        Server server = getServer();
        //split the command
        String[] cmd = command.split(" ");
        //check the command
        if(cmd[0].equals("/mail")){


            //check if player Entered a Recipeant
            if(cmd.length > 1){            
             String Recipiant = command.substring(5);  
             String fromwhom = command.substring(2);
             String timestamp = server.getGameTime();
             String body = "I love you." ;
             player.sendTextMessage("Sent to:" + Recipiant); 
             Database db = getSQLiteConnection(getPath() + "/Mail.db");

db.execute("CREATE TABLE IF NOT EXISTS 'MailPeople' ('player_name' CHAR(64) NOT NULL DEFAULT ('[NoName]'),'recipient_name' CHAR(64) NOT NULL DEFAULT ('[NoName]'),'time' CHAR(64) NOT NULL DEFAULT ('[NoName]'),'text' CHAR(64) NOT NULL DEFAULT('[NoTime]'), 'read');");
  //Note: if AutoCommit is set to false on this connection (by default it is set to true),


    //you'll have to call "prep.commit();" to commit the changes




db.executeUpdate(
"INSERT INTO MailPeople ('player_name','recipient_name','time','text','read') VALUES (Recipiant,fromwhom,'booger','Booty Call' ,0);");






            }



    //Note: if AutoCommit is set to false on this connection (by default it is set to true),



   }
}


       }

Display More

Now the console says "Recpiant is a "Column," it is clearly in the VALUES I've tried everything to write a variable, coukd someone please tell me what the heck I am doing wrong?

red51 · Oct 21st 2016

If you want to enter a text, you always have to use single quotes ('), i.e. your INSERT statement should look like this: INSERT INTO MailPeople ('player_name', 'recipient_name', 'time', 'text', 'read') VALUES ('Recipiant', 'fromwhom', 'booger', 'Booty Call', 0);

King Nothing · Oct 21st 2016

the VALUES are variables, not fixed values.

XccesS · Oct 21st 2016

Java

"INSERT INTO MailPeople ('player_name','recipient_name','time','text','read') VALUES ("+Recipiant+","+fromwhom+",'booger','Booty Call' ,0);");

You add Variables to an String in Java like this-> String var = "text"+variable;

If your Variables are an String, then it have to look like:

Java

"INSERT INTO MailPeople ('player_name','recipient_name','time','text','read') VALUES ('"+Recipiant+"','"+fromwhom+"','booger','Booty Call' ,0);");

Blacky1004 · Oct 21st 2016

Hi,

a better way ist to use Preparedstatements here is a simple example

Java

private pluginDatabse = getMySQLConnection("dbname","localhost",3306,"username","password");


String insert = "INSERT INTO MailPeople ('player_name', 'recipient_name','time','text','read') "
     + "VALUES(?,?,?,?,?)";


Connection connection = pluginDatabase.getConnection();
PreparedStatement prep = conn.prepareStatement(insert);
prep.setString(1, Recipiant);
prep.setString(2,fromwhom);
prep.setTimestamp(3,time) //or prep.setString(3,"booger") when the time column a type varchar is
prep.setString(4,text)
prep.setBoolean(5, false) // or prep.setInt(5, 0) when the column an type of integer is.
if(prep.executeUpdate() > 0){
   System.out.println("The insert was successfuly!");

Display More

have a nice day and best regards

Blacky1004

King Nothing · Oct 23rd 2016

the above code SHOULD work and update the record.

Participate now!