SQL Alias Column Names not working??

[jamieyg3]

I have code similar to this....

Code: Select all

sql="select id as myid from users";
queryRes=db.executeQuery(sql);
row=(DataRow)queryRes.get(0);
row.getItem("myid"); //does not work
row.getItem("id"); //does work

please tell me there is a way to get this to work?!

[Lapo]

Check this post -> http://forums.smartfoxserver.com/viewto ... =3612#3612

[jamieyg3]

I have read that post, but that's a pretty lame way of doing it.

If I ever have to change a query I'd have to change all the numbers around also

[Lapo]

Then you can use getConnection() on the dbManager object and access the full power of the JDBC API

http://java.sun.com/docs/books/tutorial/jdbc/basics/
http://java.sun.com/developer/Books/JDB ... index.html

[kalani96746]

Hi Lapo,

So we have this...

Code: Select all

SELECT 
	user.first_name, user.last_name, user.user_name, user.password, role.name AS roleName, partner.name AS parterName, partner.prefix AS partnerPrefix 
FROM user 
INNER JOIN 
	role ON user.r_role = role.id 
INNER JOIN 
	partner ON user.r_partner = partner.id 
WHERE user_name = ? 
LIMIT 1;

And then this...

Code: Select all

String roleName		= row.getSFSObject(0).getUtfString("roleName"); //role name
String partnerName	= row.getSFSObject(0).getUtfString("partnerName"); //role name

Which both end up being null..Obviously SQL aliases aren't being respected.
I then trace out the JSON...

Code: Select all

trace(ExtensionLogLevel.WARN,"row JSON: " + row.getSFSObject(0).toJson().toString());

Which ends up outputting this..

Code: Select all

{"user_name":"testuser","first_name":"John","name":"All Partners","prefix":"","last_name":"Smith","password":"testpass"} 

Problem is name is used twice (role.name, partner.name) without the AS alias its the same thing...the SFS overwrites the same property twice which means I only get one value out the other end WITHOUT the sql AS alias.

Which means its more than just the alias. Can't have more that one column named the same thing.
Please consider adding support for this in the next release! I can see this being a big problem for users.

Thanks.

kb

[Lapo]

I am sorry I will have to repeat what I have said in my last comment:
You can use getConnection() on the dbManager object and access the full power of the JDBC API

http://java.sun.com/docs/books/tutorial/jdbc/basics/
http://java.sun.com/developer/Books/JDB ... index.html

[mistermind]

Check this post -> http://forums.smartfoxserver.com/viewto ... =3612#3612

I had the same problem recently so I just wanted to drop a word here. The solution lapo gave on that post might definitely help you the way it did me.
My problem was even stranger: On local tests, connected to a database online, I was able to use alias for connections just fine, but when I started the extension from the server weird database bugs started happening like the inability to read alias and even case sensitive fields on queries.

Lapo posted something that for some reason worked for me:

Code: Select all

_server.executeQuery(theSql, _server.QUERY_INT_KEYS)

Instead of field names, the result is brought up as an array of values, starting from 1 (aka, tempRow.getItem(1)). Unless you are extremely dependable on the name of the field that comes out of the search, this will definitely solve the problem. In case you need to, you can also trace those values. In the case of javascript (as1), use this:

Code: Select all

trace(tempRow.getData());

Cheers