[INACTIVE] SQLite and MySQL Tutorial/Library

Discussion in 'Resources' started by alta189, May 12, 2011.

Thread Status:
Not open for further replies.
  1. Offline

    Kalman Olah

    Your CheckConnection(); seems to always be returning true. Am I using an older version, or am I doing something wrong?
     
  2. Offline

    alta189

    SQLite or MySQL?
     
  3. Offline

    Kalman Olah

    MySQL.
     
  4. Offline

    alta189

    umm... I havent run much debugging on the checkConnection function. I am on vacation this week, but when I get back I have a few things that need to be fixed and updated. :D
     
  5. Offline

    Kalman Olah

    Awesome :D. Enjoy the rest of your vacation.
     
  6. Offline

    totokaka

    I'm having some problems on checking if the player i sin the database.
    And have this code:
    PHP:
        public void onPlayerJoin(PlayerJoinEvent event){
            
    Player player null;
            
    player event.getPlayer();

            
    String query "SELECT Player FROM Dead WHERE Player='" player "';";
        
    ResultSet result null;
            
    result plugin.manageSQLite.sqlQuery(query);
            if (
    result != null){
                if (
    isDead(player)){
                    
    player.kickPlayer("you are dead!!");
                    return;
                }else{
                    
    player.sendMessage(ChatColor.BLUE "You are not Dead!!");
                    return;
                }
            }else{
                
    String query2 "INSERT INTO Dead (Player) VALUES (" player ");";
                
    result plugin.manageSQLite.sqlQuery(query2);
                
    player.sendMessage("You are added to the database!");
                return;
            }

        }
    but on every player join i get:
    Code:
    2011-06-14 16:28:43 [INFO] totokaka [/127.0.0.1:50813] logged in with entity id 123 at (-127.34375, 78.0, 100.1875)
    2011-06-14 16:28:43 [WARNING] [Survival]Error at SQL Query: [SQLITE_ERROR] SQL error or missing database (no such column: Player)
    
     
  7. Offline

    Lolmewn

    How do you create the table?
     
  8. Offline

    alta189

    Player is not a valid SQL object
    visit http://www.w3schools.com/sql/sql_create_table.asp to get help on creating a good table. you want to store the player name
     
  9. Offline

    Lolmewn

    --^ What he said.
     
  10. Offline

    totokaka

    ohh so i need to make player a string?

    I make the table like this:
    PHP:
    if(!manageSQLite.checkTable("Dead")){
                
    log.info(logPrefix "Creating table Dead");
                
    String query "CREATE TABLE Dead (id INT AUTO_INCREMENT PRIMARY_KEY, Player VARCHAR(255), Dead BOOLEAN DEAFUALT 'false');";
                
    manageSQLite.createTable(query);
            } 
    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jul 16, 2016
  11. Offline

    Lolmewn

    You forgot the ' thingys, look @ my code:
    Code:
    String query = "CREATE TABLE Skillz ('id' INT PRIMARY KEY, 'player' TEXT NOT NULL, 'skill' TEXT NOT NULL, 'xp' int , 'level' int ) ;";
     
  12. Offline

    totokaka

    ok, now it looks like this:
    PHP:
    String query "CREATE TABLE Dead ('id' INT AUTO_INCREMENT PRIMARY_KEY, 'Player' VARCHAR(255), 'Dead' BOOLEAN DEAFUALT 'false');";
    testing now

    still same error:
    [Survival]Error at SQL Query: [SQLITE_ERROR] SQL error or missing database (no such column: Player)

    Opend the file in SQLite browser after my first login on the server, and i was not added.
    her is my current code:
    making the database:
    PHP:
             log.info("[Survival]Survival Island is initializing");
            
    manageSQLite = new sqlCore(loglogPrefix"SurvivalI"pFolder.getPath());
            
    manageSQLite.initialize();
            if(!
    manageSQLite.checkTable("Dead")){
                
    log.info(logPrefix "Creating table Dead");
                
    String query "CREATE TABLE Dead ('id' INT PRIMARY_KEY, 'player' TEXT NOT NULL, 'Dead' BOOLEAN DEAFUALT 'false');";
                
    manageSQLite.createTable(query);
                
    log.info(logPrefix "Table created!");
            }
            
    PluginManager pm getServer().getPluginManager();
            
    pm.registerEvent(Event.Type.PLAYER_JOINplayerListenerEvent.Priority.Normalthis);
            
    pm.registerEvent(Event.Type.PLAYER_RESPAWNplayerListenerEvent.Priority.Normalthis);
            
    log.info("[Survival]Survival Island v: " getDescription().getVersion() + " has initialized!");
    onPlayerJoin:
    PHP:
        public void onPlayerJoin(PlayerJoinEvent event){
            
    Player player null;
            
    player event.getPlayer();
            
    String playerst player.getName();
            
    String query "SELECT player FROM Dead WHERE player='" playerst "';";
        
    ResultSet result null;
            
    result plugin.manageSQLite.sqlQuery(query);
            if (
    result != null){
                if (
    isDead(player)){
                    
    player.kickPlayer("you are dead!!");
                    return;
                }else{
                    
    player.sendMessage(ChatColor.BLUE "You are not Dead!!");
                    return;
                }
            }else{
                
    String query2 "INSERT INTO Dead (player) VALUES (" playerst ");";
                
    result plugin.manageSQLite.sqlQuery(query2);
                
    player.sendMessage("You are added to the database!");
                return;
            }

        }

        public 
    boolean isDead(Player player){
            
    String query "SELECT player WHERE player = '" player "' AND Dead = 'true';";
            
    ResultSet result null;
            
    result plugin.manageSQLite.sqlQuery(query);
            if(
    result != null){
                return 
    true;
            }else{
                return 
    false;
            }

        }
    ..

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jul 16, 2016
  13. Offline

    Lolmewn

    I Would do
    "SELECT * FROM Dead WHERE Player='" + playerst + "';";
     
  14. Offline

    totokaka

    ok, but will this return 'null' if the record don't exist?
    PHP:
    String query "SELECT * FROM Dead WHERE Playerid='" playerid "';";
    ResultSet result null;
    result plugin.manageSQLite.sqlQuery(query);
    edited the post above

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jul 16, 2016
  15. Offline

    Lolmewn

    Well, I don't relly on this null thingy. I do
    if(!result.next()){
    //Makethatnewentryhere
    }
     
  16. Offline

    totokaka

  17. Offline

    Lolmewn

    Ye that should work. I might look @ source later.
     
  18. Offline

    alta189

    Thanks for helping him out, its been busy for me... Applying for jobs :D. I will look at his source as well :D
     
  19. Offline

    Denney

    I was having the same problem. Looking at your code, you seem to be checking if a connection is available and if not, opening a connection. The problem, I think, is you're then assuming that the connection is open and not testing if there is now actually a connection.

    I believe your checkConnection() function (in DatabaseHandler.java) should read like so (notice the new "if" statement - well, I've updated mine and it works as intended now):
    Code:
    public Boolean checkConnection() {
        if (connection == null) {
            try {
                openConnection();
                if (connection != null) {
                    return true;
                }
            } catch (MalformedURLException ex) {
                core.writeError("MalformedURLException! " + ex.getMessage(), true);
            } catch (InstantiationException ex) {
                core.writeError("InstantiationExceptioon! " + ex.getMessage(), true);
            } catch (IllegalAccessException ex) {
                core.writeError("IllegalAccessException! " + ex.getMessage(), true);
            }
            return false;
        }
        return true;
    }
    Hope that helps
     
  20. Offline

    AlbireoX

    I applaud you on doing this, but I don't see why you need so many functions for different queries like UPDATE and INSERT. You could just do what I do:

    Code:
    public void execute(String query) {
            try {
                PreparedStatement stmt;
                stmt = this.link.prepareStatement(query);
                stmt.executeUpdate();
            } catch(SQLException ex) {
                ar.log.info("[AlbieRPG] SQLException: " + ex.getMessage());
                ar.log.info("[AlbieRPG] SQLState: " + ex.getSQLState());
                ar.log.info("[AlbieRPG] VendorError: " + ex.getErrorCode());
            }
        }
    
     
  21. Offline

    alta189

    To help people look at their code and understand what they are doing better and more easily
     
  22. Offline

    untergrundbiber

    Thanks for this great Library :)

    Hope you can help me a little with my problem.

    I will show a list in chat with the result from the query, but with this code it will show only the first line.

    I know i must something to do with "for" but i don't now how :)

    Code is here:

    http://pastebin.com/buEeGwEC

    Bye :)
     
  23. Offline

    e_zach

    Code:
     if (result != null && result.next())
    
    // should be:
    while(result && result.next())
    as .next() will return null if there isn't another row,
    and using an if statement will only call that code once.
    a while will keep executing the next() statement until there isn't a new row
     
  24. Offline

    alta189

    Code:
    String query6 = "SELECT * FROM unlock WHERE unlock = '0';";

    If unlock is an integer, then it should be
    Code:
    String query6 = "SELECT * FROM unlock WHERE unlock = 0;";


     
  25. Offline

    untergrundbiber

    Big Thx, works great :D

    Now i must only code an page-feature :D


    Thx for the tip
    It's a bool and i fix it :)
     
  26. Offline

    alta189

    @untergrundbiber no problem :D any other questions/issues feel free to ask :D
     
  27. Offline

    WinSock

    @alta189 , umm you should see the error right away with this
    File.pathSeparator is ';' or ':' and is used to separate filenames in a path variable.
    File.separator is '/' or '\' is used to separate directories in a qualified filename.

    Edit:
    And also all of your changes will conflict when two plugins use two different versions of your class i propose you to add a version number to the classpath, like com.alta189.sqlLibraryV2
     
  28. Offline

    alta189

    it is File.seperator (I think I just typed the wrong one when I sent it to you
    No one else has reported conflicts and I run multiple plugins using it on my server
     
  29. Offline

    SwearWord

    My plugin works on an initial server load but once I reload it, any SQL command using method spams me an error about the query not returning a result set.

    I thought this was because I didn't close the connection, but I did. Any ideas? I am using both MySQL and sqlite but testing with only sqlite.
     
  30. Offline

    alta189

    happening to me too, trying to fix it
     
Thread Status:
Not open for further replies.

Share This Page