Transferring a record set from database to Unity3D (C#)

[JoseFranco]

Good morning everyone.

I just started working on Unity3D and SmartFox. Starting with the Multiplayer Island demo available here, I modified one of the extensions to allow the game access to a H2 database.
Everything seems to work fine with the connection, client connects the server, server performs the sql query and send back to client the results of the query. When sending simple variables like a string or a number, I have no problem in recovering the data at the client side. However, and here is my question, I have no idea how to recover a whole array of data from the database.
To clarify it a little bit more, I'll post the code of the communications.

From the client side connecting to the server:

Code: Select all

                Hashtable h = new Hashtable();
      smartFoxClient.SendXtMessage("json", "getList", h, SmartFoxClient.XTMSG_TYPE_XML);
      Debug.Log("Enviando");

Handling the server connection and performing the database query:

Code: Select all

function handleRequest(cmd, params, user, fromRoom)
{
        if (cmd == "getList")
   {
      sendComputerList(user)
   }
}
   

function sendComputerList(user)
{
   var sql = "SELECT * FROM SAVEHTEST"      
      
   // execute query on DB
   // queryRes is a ResultSet object
   var queryRes = dbase.executeQuery(sql)
   
   if (queryRes != null)
   {
                trace("Entering the query")
      var response = {}
      response._cmd = "getList"
      
      /*
      * Here's the new SmartFoxServer 1.6 framework update:
      * You can pass the query ResultSet DIRECTLY to the send response
      * which will serialize the record set behind the scenes!
      */
      response.db = []

   
      // Cycle through all records in the ResultSet
    for (var i = 0; i < queryRes.size(); i++)
    {
        // Get a record
        var tempRow = queryRes.get(i)
                               
        // This object will hold the record data that we'll send to the client
        var item = {}
                               
        // From the record object we can get each field value
        item.id      = tempRow.getItem("ID")
        item.name    = tempRow.getItem("NAME")
                               
        response.db.push( item )
    }

      _server.sendResponse(response, -1, null, [user])
   }
else
   trace("Error in the query")

}

Reading the data again at the client side:

Code: Select all

public void OnExtensionResponse(object data, string type)
   {
      SFSObject dataObject = (SFSObject)data;
      double IDItem = 0;
      string nameItem = "";
      Hashtable results = (Hashtable)dataObject.Get("db");
      for (int i = 0; i < 2; i++)
      {
         Hashtable item = (Hashtable)results.Get(i);
         IDItem = (double)item[0];
         nameItem = item.GetString("name");
         Debug.Log("ENTRADA " + i + " : ID " + IDItem);
      }
   }

With this, I get problems at the line where I create the variable "results", it just won't work, it freezes and don't execute any other instruction in the function. The Hastable was the last type I tried for reading the data. I've tried with SFSObject, ArrayList, Arrays, and I don't know what else.

Maybe I'm doing something terribly wrong outside the function and I just can't figure it out, but my guess stays in that line. I've just been working a couple of weeks in Unity and SmartFox, hence my question in this forum: How can I read that "db" variable from the server side in the client side?

If more information is required, I'll post it. Thanks in advance!

José Franco

[Zelek]

I'm still learning as well, but here's how my code is set up to get an array of data sent from an extension:

Code: Select all

public void OnExtensionResponse(object data, string type) {
  if (type == SmartFoxClient.XTMSG_TYPE_XML) {
    SFSObject dataObject = (SFSObject)data;
    SFSObject myObject;

    switch (dataObject.GetString("_cmd")) {
      case "getchat":
         myObject = dataObject.GetObj("db");

         for (int i = 0; i < myObject.Size(); i++) {
           int chaID = int.Parse(myObject.GetObj("" + i).GetString("chaID"));
           int usrID = int.Parse(myObject.GetObj("" + i).GetString("usrID"));
           string usrName = myObject.GetObj("" + i).GetString("usrName");
           string chaText = myObject.GetObj("" + i).GetString("chaText");

           Message m = new Message(chaID, usrID, chaText, usrName);
           App.getChatHistory().addMessage(m);
         }

         break;
    }
  }
}


Hopefully that helps.

[JoseFranco]

Hi Zelek, and thanks for your reply.

I've tried that method and still I have problems when accessing the variable "db" (the array). Could you tell me what type of variable do you use for "db"? I mean, can you paste the function you have in the extension?

José Franco

[Zelek]

It looks like we have the same setup in the extension. Are you making sure the index passed to GetObj(...) is a string? Notice I'm concatting an empty string.

This is my extension code:

Code: Select all

case "getchat":
   var sql = "SELECT chaID, usrID, usrName, chaText FROM Chat ..."
   var queryRes = dbase.executeQuery(sql)

   var response = {}
   response._cmd = "getchat"
   response.db = []

   if (queryRes != null) {
      for (var i = 0; i < queryRes.size(); i++) {
         var tempRow = queryRes.get(i)
         var item = {}
         
         item.chaID = tempRow.getItem("chaID")
         item.usrID = tempRow.getItem("usrID")
         item.usrName = tempRow.getItem("usrName")
         item.chaText = tempRow.getItem("chaText")

         response.db.push( item )
      }
   }
      
   _server.sendResponse(response, -1, null, [user])
break


[JoseFranco]

Great Zelek, thanks!

I think there were two problems:
1.- I was using an int param for the function GetObj (I guess it only accepts strings). Using "" + i as the param, solved the problem.
2.- I was using the wrong function to recover the number stored in the "id" variable. Using int.Parse(item.GetString("id")) solved the problem.

Both of those issues froze the execution of the instruction.
Again, thank you!

José Franco