[INACTIVE] SQLite and MySQL Tutorial/Library

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

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

    SwearWord

    @alta189
    It just stopped. Not happening anymore for the past few hours.

    Strange.
     
  2. Offline

    alta189

    Yah, I dont get it... It very odd behavior
     
  3. Offline

    Jayjay110

    when are you going to add my addition to the tut?
     
  4. Offline

    Kalman Olah

    I've changed the checkConnection() to this:
    Code:
        public boolean checkConnection() {
    
            if (connection == null) {
    
                if(!openConnection()){
    
                    return false;
    
                }
    
            }
    
            return true;
    
          }
    Any idea what could be causing this?
    Code:
    19:33:06 [WARNING] [OKB3] Error at SQL Query: No operations allowed after connec
    tion closed. Connection was implicitly closed by the driver.
     
  5. Offline

    alta189

    Code:
    public Boolean checkConnection() {
            Connection con = this.manageDB.getConnection();
    
            if (con != null) {
                try {
                    if (con.isClosed()) return false;
                } catch (SQLException e) {
                    return false;
                }
                return true;
            }
            return false;
        }
     
  6. Offline

    wouter0100

    Little suggestion..
    can you make a converter :S?
    to mysql > sqlite and sqlite > mysql?
    else i need to write it :(
     
  7. Offline

    alta189

    I don't have the time ;) Sorry :(
     
  8. Offline

    wouter0100

    okay..
    thanks for the fast reply.
     
  9. Offline

    alta189

    No Problem ;)
     
  10. Offline

    DrAgonmoray

    @alta189
    Any idea what could be causing this error?
    Code:
    [SEVERE] [City] [SQLITE_ERROR] SQL error or missing database (near ";": syntax error)
    PHP:
             } else { //Use SQLite
                
    manageSQLite = new sqlCore(logprefix"City"config.directory);
                
    manageSQLite.initialize();
                if (!
    manageSQLite.checkTable("cities")) {
                    
    log.info(prefix+"Creating database table 'cities'");
                    
    manageSQLite.createTable("CREATE TABLE cities ('id' INTEGER PRIMARY KEY, 'name' VARCHAR(255), 'owner' VARCHAR(255), 'world' VARCHAR(255), 'funds' INTEGER, 'pop' INTEGER, 'low' INTEGER, 'high' INTEGER;");
                }
                if (!
    manageSQLite.checkTable("cityplots")) {
                    
    log.info(prefix+"Creating database table 'cityplots'");
                    
    manageSQLite.createTable("CREATE TABLE cityplots ('id' INTEGER PRIMARY KEY, 'owner' VARCHAR(255), 'x' INTEGER, 'y' INTEGER;");
                }
            }
     
  11. You've forgotten your closing brackets. I've fixed it up for you in the quote
     
  12. Offline

    DrAgonmoray

    ohhh. Silly me. :p
    Thanks so much! I'll be sure to bother you again if I run into more problems.

    @alta189
    How would I go about using COUNT to count the number of.. Like this:
    PHP:
    manageSQLite.sqlQuery("SELECT COUNT(*) FROM cities WHERE name='"+args[1]+"';");
    How would I get the amount that it counted?

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

    alta189

    Code:
    manageSQLite.sqlQuery("SELECT COUNT(*)as count FROM cities WHERE name='"+args[1]+"';");
    int count = 0;
    if (result.next()) {
    count = result.getInt("count");
    }
     
  14. Offline

    DrAgonmoray

    @alta189 Ohhh that makes sense. Thanks! :D
     
  15. Offline

    alta189

    no problem :)

    @DrAgonmoray @Lolmewn @Adamki11s @Other people I don't remember :p
    I will be rewriting the SQL Library this weekend, and hopefully have a better, easier to use library next week :)

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

    DrAgonmoray

    :eek: sweet! Thanks for the updates!
    Out of curiosity, with the above example you gave me, if it were to count 0, would result.next() return false (or null)?
     
  17. Offline

    alta189

    false
     
  18. Offline

    DrAgonmoray

    Oh goodie, that's great to know. I can make my code a ton more efficient now.
     
  19. Offline

    alta189

    :)
     
  20. Offline

    DrAgonmoray

    More questions to bug you with :D

    If one of the fields is NULL in an SQLite database (say a REAL field containing either a float or NULL)
    I want to check if that field is null, or if it has a value. How would I do that?

    Edit Nevermind, I did a little reading and the little Eclipse info button says it will return 0 if NULL.

    Edit. New question:

    @alta189 @Adamki11s
    I get this error:
    Code:
    Error at SQL Query: [SQLITE_ERROR] SQL error or missing database (no such column: id)
    But I'm pretty sure id is a column. Here's where I create the table:
    PHP:
    manageSQLite.createTable("CREATE TABLE cityplots ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'owner' VARCHAR(255), 'city' VARCHAR(255), 'world' VARCHAR(255), 'x' INTEGER, 'y' INTEGER, 'cost' REAL DEFAULT NULL);");
    And here's where the error is coming from:
    PHP:
    result manageSQLite.sqlQuery("SELECT id, owner, city, world, cost WHERE x="+x+" AND y="+y+" LIMIT 1;");
    while (
    result != null && result.next()) {
        
    player.sendMessage(ChatColor.YELLOW+prefix+"Plot information:");
        
    player.sendMessage(ChatColor.GREEN+"ID: "+ChatColor.BLUE+result.getInt("id"));
    }
     
  21. Offline

    alta189

    Im leaving to go somewhere, but check your query, also get an SQLite browser and double check that its there
     
  22. Offline

    DrAgonmoray

    The SQLite browser I just got is neat, and while I was looking for the problem, it occurred to me. I forgot the FROM statement. :p
     
  23. Offline

    Kalman Olah

    So, first of all I'd like to let you know that your SQLite/MySQL library is literally the only thing I use when making any sort of plugin. It's great.

    Sadly, some of my plugin users have been nagging about crashes after a server /reload.
    It's basically a whole load of this:
    Code:
    09:33:48 [WARNING] [OKB3] Error at SQL Query: query does not return ResultSet
    followed by a crash.

    This only happens after a /reload, and keeps spamming the console until the server crashes, even though it is caused by 1 single query.

    I was wondering if you could provide some information on whether or not this is fixed in the latest release, or if you could provide a solution :).
     
  24. I was getting this same error on the new plugin I'm making. It's because when the database is being read from it gets locked because SQLite is intenteded for only 1 connection at a time. What I did was do an infinite while loop to keep trying to execute the query when the connection is read only. Ever since editing the code in the DatabaseManager class I have not come accross this problem. Here is what I have modified for an update query. You can change this to suit your need for different querys also.
    PHP:
    try {
                
    boolean statementProcessed false;

                while(!
    statementProcessed){
                    
    Connection connection getConnection();
                    if(!
    connection.isClosed() && !connection.isReadOnly()){
                        
    Statement statement connection.createStatement();
                        try{
                            
    statement.executeUpdate(query);
                            
    statementProcessed true;
                            return 
    true;
                        } catch (
    Exception ex){
                            
    ex.printStackTrace();
                        }
                    }
                }
              return 
    false;

            } catch (
    SQLException ex) {
                if (
    ex.getMessage().toLowerCase().contains("locking") || ex.getMessage().toLowerCase().contains("locked") || ex.getMessage().toLowerCase().contains("transaction")) {
                    
    retry(query);
                    return 
    false;
                }else{
                    if (!
    ex.toString().contains("not return ResultSet")) core.writeError("Error at SQL UPDATE Query: " exfalse);
                    return 
    false;
                }
            }
    Also I would be extremely grateful if you could post the code that is giving you this error because I've been unable to reproduce it as it happens randomly.
     
  25. Offline

    alta189

    I have had this happen, so I am working to find the error, and will have an update sometime early next week
     
  26. Offline

    Kalman Olah

    I'll add your changes after I've had dinner :p.
    My example code:
    Code:
    public void UpdateNormal(CommandSender sender, Player player, String plrname, String user, String pass, Boolean force){
    
            String query1 = null;
    
            if(OKmain.enctype.equals("1") || OKmain.enctype.equals("2") || OKmain.enctype.equals("4")){
    
                query1 = "SELECT " + OKmain.field3 + " FROM " + OKmain.table1 + " WHERE " + OKmain.field1 + " = '" + user + "' AND " + OKmain.field2 + " = '" + pass + "'";
    
            }else{
    
                query1 = "SELECT " + OKmain.field3 + "," + OKmain.field2 + " FROM " + OKmain.table1 + "," + OKmain.table2 + " WHERE " + OKmain.table1 + "." + OKmain.field1 + " = '" + user + "'  AND " + OKmain.table1 + "." + OKmain.field4 + "=" + OKmain.table2 + "." + OKmain.field5 + "";
    
            }
    
            int i = 0;
    
            try {
    
                ResultSet rs = OKDatabase.dbm.sqlQuery(query1);
    
                if (rs.next()){
    
                    do{
    
                        if(OKmain.enctype.equals("1") || OKmain.enctype.equals("2") || OKmain.enctype.equals("4")){
    
                            changeGroup(plrname,rs.getString(OKmain.field3),"nope", true);
    
                            OKBanDB.dbm.insertQuery("INSERT INTO players (player,user,encpass) VALUES ('" + plrname + "','" + user + "','" + pass + "')");
    
                            if (force == false){
    
                                sendMessage(sender, colorizeText("Synchronization successful.", ChatColor.GOLD));
    
                                OKLogger.info(plrname + "'s ranks successfully updated.");
    
                            }else{
                                sendMessage(sender, colorizeText("Synchronization successful for ",ChatColor.GOLD) + plrname + colorizeText(".", ChatColor.GOLD));
    
                                OKLogger.info(plrname + "'s ranks successfully updated by " + getName(sender) + ".");
    
                            }
    
                        }else{
    
                            if(OKmain.CheckStringContains(pass,rs.getString(OKmain.field2)).equals("true")){
    
                                changeGroup(plrname,rs.getString(OKmain.field3),"nope", true);
    
                                OKBanDB.dbm.insertQuery("INSERT INTO players (player,user,encpass) VALUES ('" + plrname + "','" + user + "','" + pass + "')");
    
                                if (force == false){
    
                                    sendMessage(sender, colorizeText("Synchronization successful.", ChatColor.GOLD));
    
                                    OKLogger.info(plrname + "'s ranks successfully updated.");
    
                                }else{
    
                                    sendMessage(sender, colorizeText("Synchronization successful for ",ChatColor.GOLD) + plrname + colorizeText(".", ChatColor.GOLD));
    
                                    OKLogger.info(plrname + "'s ranks successfully updated by " + getName(sender) + ".");
    
                                }
    
                            }else if(i == 0){
    
                                sendMessage(sender, colorizeText("An error occurred while updating the forum database.", ChatColor.LIGHT_PURPLE));
                                sendMessage(sender, colorizeText("--- Make sure you are a registered user on the forums.", ChatColor.LIGHT_PURPLE));
                                sendMessage(sender, colorizeText("--- Please double-check your username and password.", ChatColor.LIGHT_PURPLE));
    
                                OKLogger.info(plrname + "'s rank update failed.");
    
                                i++;
    
                            }
    
                        }
    
                    }while (rs.next());
    
                }else{
    
                    if (force == false){
    
                        sendMessage(sender, colorizeText("An error occurred while updating the forum database.", ChatColor.LIGHT_PURPLE));
                        sendMessage(sender, colorizeText("--- Make sure you are a registered user on the forums.", ChatColor.LIGHT_PURPLE));
                        sendMessage(sender, colorizeText("--- Please double-check your username and password.", ChatColor.LIGHT_PURPLE));
    
                        OKLogger.info(plrname + "'s rank update failed.");
    
                    }else{
    
                        sendMessage(sender, colorizeText("Error: Could not update ranks for ", ChatColor.LIGHT_PURPLE) + plrname + colorizeText(".", ChatColor.LIGHT_PURPLE));
    
                    }
    
                }
    
                rs.close();
    
            } catch (MalformedURLException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            if (OKmain.nicksync.equals("true")){
    
                OKmain.UpdateNick(player);
    
            }
    
        }
    It's caused by the MySQL queries, not the SQLite ones. I think.
    EDIT: The OKBanDB.dbm queries are the SQLite ones.
    EDIT2: Never mind, I'm a retard. It was caused by the SQLite queries, yeah.

    Awesome :)

    So...It would appear I can fix the issue with SQLite DBs only supporting 1 active connection by actually putting the close() into my onDisable().... Err...Sorry.

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

    cholo71796

    @alta189
    How can I iterate over a database?
    Code:java
    1.  
    2. while (results.next()) {
    3. Log.info("subcycled");
    4. int money = results.getInt("amount");
    5. String player = results.getString("player");
    6.  
    7. if (money + PowerConfig.getAmountPerDistribution() > PowerConfig.getMaximumAmount())
    8. money = PowerConfig.getMaximumAmount();
    9. else
    10. money += PowerConfig.getAmountPerDistribution();
    11.  
    12. manageSQLite.insertQuery("INSERT INTO power (player, amount) VALUES ('" + player + "', " + money + ");");
    13. }

    Going through this loop repeats "subcycled" infinitely in console.
     
  28. Offline

    alta189

    Where is the query for the select
     
  29. Offline

    cholo71796

    @alta189
    I was going to post more source, but Bukkit.org went down.
    Code:java
    1. try {
    2. ResultSet results = manageSQLite.sqlQuery("SELECT * FROM power");
    3. while (results.next()) {
    4. Log.info("subcycled");
    5. int money = results.getInt("amount");
    6. String player = results.getString("player");
    7.  
    8. if (money + PowerConfig.getAmountPerDistribution() > PowerConfig.getMaximumAmount())
    9. money = PowerConfig.getMaximumAmount();
    10. else
    11. money += PowerConfig.getAmountPerDistribution();
    12.  
    13. manageSQLite.insertQuery("INSERT INTO power (player, amount) VALUES ('" + player + "', " + money + ");");
    14. }
    15. results.close();
    16. } catch (SQLException ex) {
    17. Logger.getLogger(Power.class.getName()).log(Level.SEVERE, null, ex);
    18. }

    Do I need to be more specific in the query?
     
  30. Offline

    alta189

    What is the insert query for?
     
Thread Status:
Not open for further replies.

Share This Page