DbManager doesn't seem to support AS?

[jurekf]

I have this query that I'm running through DbManager:

Code: Select all

select worlditem_id, item_type.name as item_type, item_id, enhancement, `mod`, quantity from inventory left join world_items on inventory.world_id=world_items.worlditem_id left join item_type on world_items.item_type=item_type.id where char_id=66 

It works fine when I run it through phpMyAdmin, but when I run it through DbManager the "item_type.name as item_type" doesn't appear in the result set, the DataRow's HashMap only has 5 entries instead of 6.

I've tried something as simple as

Code: Select all

select worlditem_id as testz from world_items

and while it works in phpMyAdmin, in my extension I get worlditem_id instead of testz.

Even if I don't use the AS it doesn't work properly either:

Code: Select all

select worlditem_id, item_type.name, item_id, enhancement, `mod`, quantity from inventory left join world_items on inventory.world_id=world_items.worlditem_id left join item_type on world_items.item_type=item_type.id where char_id=66 

This gives me back all the columns except for item_type.name. The DataRow only has 5 columns, whereas the same query run through phpMyAdmin gives me 6 columns as it should.

Does using the DbManager impose limits on what SQL queries we're allowed to use?

[BigFIsh]

I think it's because there is a conflict between the table name and column name. You can't have 'item_type' for both.

Try

..., item_type.name as item_type_name, item_id, ....

[jurekf]

Doesn't work either.

I've tried the following simplified query both with and without quotes just to make sure it's not a name collision or parsing issue:

Code: Select all

select name as 'character_name' from characters

and the fields returned do not have the correct column name. Ideas?

Edit: If it makes a difference, I'm using DbManager's executeQuery method.

[BigFIsh]

Weird.. that query looks fine. Which database (and version) are you using? Also, just to make sure - which SFS version are you using?

[jurekf]

From the startup screen it's SFS Pro 1.6.6, but we applied the patch so I'm guessing it's 1.6.7.

The database we're using is MySQL 5.1.33 on Win32.

[BigFIsh]

Can you please show us code snippet used to execute the SQL statement and fetching of the data?

It seems weird that

Code: Select all

select worlditem_id as testz from world_items

would return worlditem_id instead of testz. Maybe it is the way you fetch the data? What about

Code: Select all

SELECT Count(*) AS total FROM world_items

?

[jurekf]

I just tried Count(*). It's weird, Count(*) works, but when I changed it to a field value the rename fails.

Here's the relevant code:

Code: Select all

synchronized private void onServerReady() {
		
		DbManager db = ExtensionHelper.instance().getZone(this.getOwnerZone()).dbManager;
		ArrayList<DataRow> rows;
		rows = db.executeQuery("SELECT worlditem_id AS testz FROM world_items");
		for( DataRow row : rows )
		{
			String data = row.getItem("testz");
			trace("As testz = "+data);			// returns NULL
			data = row.getItem("worlditem_id");
			trace("As worlditem_id = "+data);	// returns correct value
		}
		

[jurekf]

Eh, bypassed DbManager and went directly through the Connection and the query works as expected with plain java.sql.

DbManager is broken somewhere.

[BigFIsh]

Apparently, it seems to capitalize the column names.

So for example - if my SQL statement was:

Code: Select all

SELECT username AS name FROM USERTABLE;

row.getItem("NAME") would give me the correct result, while row.getItem("name") gives me null.

This was tested using h2db and java.

[jurekf]

Apparently, it seems to capitalize the column names.

It's inconsistent. In my case with the mysql driver it's actually not capitalizing but instead not doing the rename at all.

Code: Select all

DbManager db = ExtensionHelper.instance().getZone(this.getOwnerZone()).dbManager;
		ArrayList<DataRow> rows;
		rows = db.executeQuery("SELECT name as username FROM users");
		for( DataRow row : rows )
		{
			String data = row.getItem("name");
			trace("As name = "+data);			// returns correct value
			data = row.getItem("USERNAME");
			trace("As USERNAME = "+data);			// returns NULL
			data = row.getItem("username");
			trace("As username = "+data);	// returns NULL
		}

I would've thought that DbManager would just pass my SQL text straight to the normal java.sql.Connector, but apparently it's not and instead it's degrading my queries.

[BigFIsh]

Seems like aliasing isn't supported for SQL

Yea, bypassing the DBManager is the way to go.

Just in case you need info about JDBC API, check out:
http://download.oracle.com/javase/tutorial/jdbc/basics/