Solved SQL Command: SELECT COUNT*) FROM {};

I had another thread on this but I thought it would be appropriate to move this to a new thread. I have the following which I want to receive:

Code:

                var sql = Sql.Builder.Append($"SELECT COUNT(*) FROM messages;");
                _mySql.Query(sql, _mySqlConnection, list =>
                {                });            

This should give me a count but it does not(It gives one while SQL gives me

Code:

mysql> SELECT COUNT(*) FROM messages;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

For the question is there a way to pull the value of COUNT() from that list? Or would I do this another way?

 

Solved: Probably isn't the most effective method but it works well for me(issue is you have to provide a table directory(so ID or NAME - etc)

Code:

        private int GetTableCount(string table, string tray){
            var DB = new MySqlConnection($"Server={Cfg.Host}; database={Cfg.Database}; UID={Cfg.User}; password={Cfg.Password}");
            DB.Open();
      
            var cmd = new MySqlCommand($"SELECT * FROM {table};", DB);
            var reader = cmd.ExecuteReader();
            var i = 0;
            while(reader.Read())
            {
                string msg = reader.GetString(reader.GetOrdinal(tray));
                i++;
            }
            return i;
            
            DB.Close();  
        }

 

Please do not use non-concurrent queries, especially in MySQL.
Aside from that, your code is prone to resource leaks! (if the database is correctly opened but it cannot connect to the db when executing the query, the database will never be closed).
I am really starting to doubt if it's a good idea that people can even access the stdlib MySQL API.

 

How do you suggest I preform that sql command then?

 

You use the Oxide API, as you did in your first post.
You probably tried to use list.Count. list.Count is *not* the count you receive, it's the amount of rows the select returned.
The "list" variable is a list of rows, while each row is a dictionary (something like List<Dictionary<String, Object>>).
To retrieve the value of the "Count" column of the first row you'd use something like list[0]["Count"].

 

Thanks! , doing that also seems to have made the code much shorter:

Code:

            private int GetTableCount(string table){
                 var k = 0;
                _mySql.Query(Sql.Builder.Append($"SELECT COUNT(*) FROM {table};"), _mySqlConnection, list =>
                {
                    for(var i = 0; i < list.Count; i++){
                        k = (int)list[i]["COUNT(*)"];
                    }
                });
                return k;
            }

 

Had to edit that as it seems the variable didn't carry after the query(had to make it all in the query) for example:

Code:

                    _mySql.Query(Sql.Builder.Append($"SELECT * FROM commands{Cfg.serverName} WHERE server = '{Cfg.serverName}';"), _mySqlConnection, list =>
                    {
                        for(var i = 0; i < list.Count; i++){
                            var msg = list[i]["command"];
                            var auth = list[i]["authenticator"];
                            commandsR.Add(msg.ToString());
                            commandsA.Add(auth.ToString());
                            k++;
                        }                        if(k != 0){
                            Puts($"Executing the following commands on {Cfg.serverName}:");
                            Puts("|:|      Command      |:|      Authenticator     |:|");
                            var newk = 0;
                            foreach(var entry in commandsR){
                                Puts($"|:|      {entry}      |:|      {commandsA[newk]}     |:|");
                                commandDelete(entry);
                                ExecuteCommand(entry);
                                newk++;
                            }
                        }
                    });

 

Queries are concurrent. This is what's different from the C# SQL standard library. When you use _mySql.Query, that query is executed at some point in the future on a seperate thread. Because it's executed in the future, you need to pass a function to execute when the query is done. As that function is executed at some point in the future, while the other code in the same scope as _mySql.Query is executed now, on the main thread, it is obvious that you cannot and should not use any variables from the future in code that is executed now. In this case, scope is preventing you from doing this. Scope doesn't always prevent you from doing this, though.
If you do not understand concurrency and its pitfalls, I suggest staying away from SQL until you properly understand concurrency. Concurrent code is a lot harder to reason about than regular code, and if you do not understand it, you will make it very hard for yourself. Trial and error barely ever works here.
Not using the concurrent API is bad as well (see: Using MySQL without callbacks | Oxide).