H2 Database Output

[coolboy714cp]

Hello everyone.

I'm having a problem with my extension, which I've had for a very long time now. I posted a thread on the forum a while back and tried all the responses I got there in Flash. None of them worked; they all traced out on the server as null.

Here is what my extension currently outputs on the server:

[itemTest.as]: [it.gotoandplay.smartfoxserver.db.DataRow@a4a89a, it.gotoandplay.
smartfoxserver.db.DataRow@3917d1]
[itemTest.as]: Record Number: 0
[itemTest.as]: Inventory Item: null
[itemTest.as]: Record Number: 1
[itemTest.as]: Inventory Item: null

Here is my extension's coding so far:

Code: Select all

	if (cmd == "getItems") {
		var sql = "SELECT * FROM inv";
		var query = dbase.executeQuery(sql);
		trace(query);
		if (query != null) {
			for (var i = 0; i < query.size(); i++) {
				var theData = query.get(i);
				trace("Record Number: " + i);
				trace("Inventory Item: " + theData.getItem("inv"));
			}
		}
	}

Can someone please tell me what's wrong with it?

Thanks for any help I may receive in advance.

[BigFIsh]

Hm..

Maybe there is a conflict between your table and column name in which they are both named 'inv'. Try renaming your table to something else.

[coolboy714cp]

The table name was actually itemname. When I was making the coding I typed it fast and made that mistake. I changed it to itemname, but it gave me the same results still.

[BigFIsh]

So, in your table, are there two rows, a column named 'inv' which is a string type and has non-null values - right?

If so, then I have absolutely no idea what could be wrong - unless you were using the wrong database the whole time..

[coolboy714cp]

In my database, I have two columns. The first column is named id. It's an integer and it is also the primary key. The second column is named itemname. The only property I defined for this column was VARCHAR(255).

Sql coding I used:

Code: Select all

CREATE TABLE inv(id INT PRIMARY KEY, itemname VARCHAR(255));

[rjgtav]

So if the collum is called itemname and the table is called inv you need to use this code instead:

Code: Select all

if(cmd == "getItems"){
      var sql = "SELECT * FROM inv";
      var query = dbase.executeQuery(sql);
      trace(query);
      if(query != null){
            for (var i = 0; i < query.size(); i ++){
                  var theData = query.get(0);
                  trace("Record Number: " + i);
                  [b]trace("Inventory Item: " + theData.getItem("itemname"));[/b]
            }
      }
}

The line that i changed is in bold. Im on mobile so the code maybe has some errors

[coolboy714cp]

I know, I already tried the code you posted right after I posted this thread.

[rjgtav]

if you execute that query in the h2 console, what does it return?

[coolboy714cp]

I get this error if I run the above SQL coding in the H2 Database Console:

CREATE TABLE inv(id INT PRIMARY KEY, itemname VARCHAR(255));
Table INV already exists [42S01-60]

[Rutter]

I believe rjgtav wants you to run this in the H2 console:

SELECT * FROM inv;

what does the result set look like?

[coolboy714cp]

I finally found the problem!

The problem was this:

When Rgtav asked me what the output was when I tried to run the code above in the H2 Console, it said this:

Table INV already exists.

I then tried capitalizing the table name and the column names in my extension.

Right after I did that, the extension worked perfectly.

Thanks BigFish and Rjgtav, a lot! xD

[rjgtav]

just for reference, when working with h2 you always need to use upcase letters in the commands and queries. For example SELECT * FROM USERS

[Rutter]

Hmmm.... The original question was why was the select statement returning nulls. Therefore the select statement worked fine. The error message that the table already exists only occurs if you use the create table statement where the table name exists. Perhaps the create table statement was included in the extension. Either way this is a very screwy thread and I must remind myself not to follow any of coolboys issues.