Page 1 of 1

SQL Alias Column Names not working??

Posted: 09 Dec 2008, 00:12
by jamieyg3
I have code similar to this....

Code: Select all

sql="select id as myid from users";
row.getItem("myid"); //does not work
row.getItem("id"); //does work

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

Posted: 09 Dec 2008, 08:20
by Lapo
Check this post -> viewtopic.php?p=3612#3612

Posted: 09 Dec 2008, 16:34
by 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

Posted: 09 Dec 2008, 16:42
by Lapo
Then you can use getConnection() on the dbManager object and access the full power of the JDBC API ... index.html

Re: SQL Alias Column Names not working??

Posted: 31 May 2012, 20:25
by kalani96746
Hi Lapo,

So we have this...

Code: Select all

   user.first_name, user.last_name, user.user_name, user.password, AS roleName, AS parterName, partner.prefix AS partnerPrefix
FROM user
   role ON user.r_role =
   partner ON user.r_partner =
WHERE user_name = ?

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 (, 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.



Re: SQL Alias Column Names not working??

Posted: 02 Jun 2012, 09:19
by 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 ... index.html


Posted: 21 Jun 2012, 03:49
by mistermind
Lapo wrote:Check this post -> viewtopic.php?p=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