Page 1 of 1
DbManager doesn't seem to support AS?
Posted: 20 Jan 2011, 08:55
by 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?
Posted: 20 Jan 2011, 21:38
by 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, ....
Posted: 20 Jan 2011, 22:43
by 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.
Posted: 20 Jan 2011, 22:46
by BigFIsh
Weird.. that query looks fine. Which database (and version) are you using? Also, just to make sure - which SFS version are you using?
Posted: 20 Jan 2011, 23:39
by 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.
Posted: 21 Jan 2011, 00:58
by 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
?
Posted: 21 Jan 2011, 02:23
by 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
}
Posted: 21 Jan 2011, 05:46
by jurekf
Eh, bypassed DbManager and went directly through the Connection and the query works as expected with plain java.sql.
DbManager is broken somewhere.
Posted: 21 Jan 2011, 18:51
by 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.
Posted: 24 Jan 2011, 00:34
by 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.
Posted: 24 Jan 2011, 06:58
by 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/