SQL with Select Count(*) As TOTOL From <table> Where <column> LIKE ' name__ '

angriff · Mar 26th 2024

I can run this query on the database in the executeSQL tab and it runs like a charm. But trying to put this in a string I keep getting literal errors and exceptions when it runs. I cannot get the single apostrophes into the string without it thinking they are literals. Is there a better way to get a count of the specific items in a column?

database.executeQuery("SELECT COUNT(*) AS TOTAL FROM 'npcs' WHERE 'name' LIKE '"+playernm+"'__")){ This line is without trying to get the literal apostrophes but adding wild cards at the end.

Is there is better way to get a count of the names in the WorldDatabase.Target.Npcs ? The Total part was an attempt to extract the Total without the Resultsset. I get nothing but exceptions so I am sure it is likely syntax in the command string.

yahgiggle · Mar 26th 2024

Not tested but this should work

Code

// Don't forget to also get the connection to the world database 
WorldDatabase database = getWorldDatabase(WorldDatabase.Target.Npcs);

database.executeQuery("SELECT count(*) FROM `npcs` WHERE 'name' LIKE '"+playernm+"'")) { 

// get the result
int db_count = Result.getInt(1);


}

angriff · Mar 26th 2024

I tried something similar to that but similar no results. The issue is the database name has an apostrophe character in it and you have to use wild cards to get it to work in the SQL tab in the Database browser execution tab so I thought when you used like you needed the apostrophers. on both side.

I will fool with it along your lines in the morning.

I have a different way to do what I want but I wanted to use the World Database for the tracking and not another database or player attribute.

yahgiggle · Mar 26th 2024

In the npcs table under names the default is null so doing a count of that if no names are added, will result in a null

Read above made a edit and added more info, sorry i did not see your reply till after i refreshing the forum for some reason oO

yahgiggle · Mar 26th 2024

ok this works ive tested it

Code

          // get the connection
          WorldDatabase NPCdatabase = getWorldDatabase(WorldDatabase.Target.Npcs);

          // set the chat command to test
          if (cmd[0].equalsIgnoreCase("/test")) {

          // set the variable
          String Count = "Billy";

          // Query DB
          try (ResultSet Result = NPCdatabase.executeQuery("SELECT COUNT(*) AS count_name FROM npcs WHERE name LIKE '"+Count+"'")) {

          // check for a result   
          if (Result.next()) {
          // output the result to text message 
          int db_count = Result.getInt("count_name");
          player.sendTextMessage("count="+db_count);
          }
          }catch(SQLException sQLException) {}
          }

Display More

red51 · Mar 26th 2024

There is unfortunately a bug when accessing a ResultSet via index... in JDBC, the leftmost column in the ResultSet begins at 1. So in an SQLite or MySQL database created through the Plugin API, you could access the count like that:

Java

try (ResultSet result = db.executeQuery("SELECT COUNT(*) FROM `mytable`;")) {
    if (result.next()) {
        System.out.println("Count: " + result.getInt(1));
    }
}
catch (Exception e) {
    e.printStackTrace();
}

However, the world database uses our native SQLite implementation instead. In SQLite, the equivalent of result.getInt() is sqlite3_column_int(), where the leftmost column begins at 0 (instead of 1).

You could fix that by accessing index 0 in the result set, however, since this isn't consistent with the regular JDBC implementation, we will change that with the next update (so I wouldn't recommend using that now).

Instead it's probably better for now to use an alias for the count and access the result through that name:

Java

try (ResultSet result = db.executeQuery("SELECT COUNT(*) AS count FROM `mytable`;")) {
    if (result.next()) {
        System.out.println("Count: " + result.getInt("count"));
    }
}
catch (Exception e) {
    e.printStackTrace();
}

angriff · Mar 26th 2024

Thanks to the both of you. But, can I use the SQLite handle with an apostrophe or wild cards in the count? That is part of the issue here I am searching for variations that might contain special characters and almost always has an apostrophe. The apostrophe unfortunately is used to denote literals in the command string and it keeps getting errors flags in Netbeans for that. Also the underscore wild card, I think will work fine but wanted to clarify. This is looking to count the string using LIKE that has apostrophes and even that throws the DBBrowser tab search flags

Bear in mind guys syntax is my dearest friend and strongest fiend at the same time.

angriff · Mar 26th 2024

Quote from yahgiggle

In the npcs table under names the default is null so doing a count of that if no names are added, will result in a null
Read above made a edit and added more info, sorry i did not see your reply till after i refreshing the forum for some reason oO

yes, I was looking for nulls and that return a 0 count which is valid in my search. Maybe I need to strip the special characters out ('s) and then submit it with wildcards.

So notice how the DbBrowser execution requires LIKE 'angriff__' to work. This indicates to me that to make it work i would have to use apostrophes on each side of the literal ' '"+playernm+"' '" but this does not work.

red51 · Mar 26th 2024

Yes, wildcards work in combination with a LIKE operator when working with the world database. Make sure to put apostrophes (') around the name (single quotes represent SQL strings), i.e. one on the left, one on the right. For example, if you have a player name "angriff" and want to find all npcs which are called "angriff" followed by 2 digits or letters (e.g. "angriff01", "angriff42", "angriffXY" etc), the SQL statement would look like SELECT COUNT(*) AS count FROM npcs WHERE name LIKE 'angriff__';. Since your player name is a variable, you have to use string concatenation. The code could look like this:

Java

String playerName = "angriff";

WorldDatabase db = getWorldDatabase(WorldDatabase.Target.Npcs);

try (ResultSet result = db.executeQuery("SELECT COUNT(*) AS count FROM npcs WHERE name LIKE '" + playerName + "__';")) {
    if (result.next()) {
        System.out.println("Count: " + result.getInt("count"));
    }
}
catch (Exception e) {
    e.printStackTrace();
}

Display More

Please bear in mind that the _ wildcard character represents one character, while % represents zero, one or more characters. So looking for "angriff__" (with 2 underlines) will find "angriff01", "angriff42" etc, but not "angriff1", for example.

angriff · Mar 26th 2024

is that semicolon behind the quotes needed?

red51 · Mar 26th 2024

Quote from angriff

is that semicolon behind the quotes needed?

No, it's not required

angriff · Mar 26th 2024

Well it worked with the semicolon perfectly. Thanks so much Red and Yahgigle for your help. It was the thought that needed the apostrophe in front of the name to mimic that DbBrowser method.

Participate now!