Receive and Output H2 Database Column Value

[coolboy714cp]

I've been trying to do this for a few days now and I can't figure out what the command would be to get the value from an H2 database column and output it on the server inside a trace() command. From there I would be able to use the _server.sendResponse command to send it to the client.

Anyway, I've read the documentation and looked over the example files that had anything to do with databases. The closest thing I have gotten to getting the values from a column is the server tracing this:

, ,

However, I need the server to obtain and be able to trace out the correct values in the column.

Does anyone know what I would have to do to acheive this?

[rjgtav]

Hi. You could use:

for (var i = 0; i < queryRes.size(); i++){
var Row = queryRes.get(i)
value = Row.getItem("COLLUM NAME");
trace("value retrieved from the database: "+value);
}

[coolboy714cp]

Tried it about 10 times with editing the code a little on each one. None of them worked. The value always came out as null.

[BigFIsh]

Please show us your code used to fetch values from the database.

[coolboy714cp]

Hello BigFish, I just recently tested with the SmartFoxServer Server Side API example, and the code I used was this:

Code: Select all

function handleRequest(cmd, params, user, fromRoom)
{
	// create a SQL statement
	var sql = "SELECT * FROM contacts ORDER BY name"		

// execute query on DB
// queryRes is a ResultSet object
	var queryRes = dbase.executeQuery(sql)

// If the queryRes is not null the query was successfull
	if (queryRes != null) {
	// Cycle through all records in the ResultSet
		for (var i = 0; i < queryRes.size(); i++) {
		// Get a record
			var tempRow = queryRes.get(i)
		
			trace("Record n." + i)
			trace("Name: " + tempRow.getItem("name"))
			trace("Location: " + tempRow.getItem("location"))
			trace("Email: " + tempRow.getItem("email"))
			trace("-------------------------------------------")
		}
	}
}

The name, location, and email values all traced out as null, but the Record n. value traced out correctly.

[BigFIsh]

That looks fine to me.

The only explainable reason for null values is that those columns does not exist in your database. I think the column names are case sensitive.

[coolboy714cp]

The only explainable reason for null values is that those columns does not exist in your database. I think the column names are case sensitive.

I know that, but I don't think that could be the problem. The columns and tables appear in my H2 Database, and when I created a testing table, for the table name I used the following:

test

I then went on to create two tables which were:

id

and

name

When I edited the exdtension's coding, it looked something like this:

Code: Select all

function handleRequest(cmd, params, user, fromRoom)
{
   // create a SQL statement
   var sql = "SELECT * FROM test ORDER BY name"      

// execute query on DB
// queryRes is a ResultSet object
   var queryRes = dbase.executeQuery(sql)

// If the queryRes is not null the query was successfull
   if (queryRes != null) {
   // Cycle through all records in the ResultSet
      for (var i = 0; i < queryRes.size(); i++) {
      // Get a record
         var tempRow = queryRes.get(i)
      
         trace("Record n." + i)
         trace("Name: " + tempRow.getItem("name"))
         trace("Id: " + tempRow.getItem("id"))
         trace("-------------------------------------------")
      }
   }
}

The values still traced out as null.

[BigFIsh]

Maybe the cell data have null values in them? Apart from that, I can't think of any other 'obvious' explanation to the problem.

[rjgtav]

also, you could try NAME and ID instead of name and id

[coolboy714cp]

I have tried name and id. And also the tables don't have any null values in them.

[rjgtav]

is the table name correct? Running out of clues..

[coolboy714cp]

Yes, the table name is correct.

[coolboy714cp]

So I'm guessing no one knows what the problem is for me...

[BigFIsh]

Yea.. I have already ran out of clues.

I suggest starting again from scratch, using the tutorials as a template.

[coolboy714cp]

Alright, I'll give that a go within the next couple of days.