SQL Alias Column Names not working??

Post here your questions about Actionscript and Java server side extensions development.

Moderators: Lapo, Bax

jamieyg3
Posts: 84
Joined: 25 Sep 2008, 16:01

SQL Alias Column Names not working??

Postby jamieyg3 » 09 Dec 2008, 00:12

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?!
User avatar
Lapo
Site Admin
Posts: 21588
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 09 Dec 2008, 08:20

Check this post -> viewtopic.php?p=3612#3612
Lapo
--
gotoAndPlay()
...addicted to flash games
jamieyg3
Posts: 84
Joined: 25 Sep 2008, 16:01

Postby jamieyg3 » 09 Dec 2008, 16:34

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
User avatar
Lapo
Site Admin
Posts: 21588
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 09 Dec 2008, 16:42

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
Lapo

--

gotoAndPlay()

...addicted to flash games
kalani96746
Posts: 6
Joined: 21 Jun 2008, 17:24

Re: SQL Alias Column Names not working??

Postby kalani96746 » 31 May 2012, 20:25

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
User avatar
Lapo
Site Admin
Posts: 21588
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: SQL Alias Column Names not working??

Postby Lapo » 02 Jun 2012, 09:19

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
Lapo

--

gotoAndPlay()

...addicted to flash games
User avatar
mistermind
Posts: 131
Joined: 15 Sep 2007, 01:33
Contact:

Re:

Postby mistermind » 21 Jun 2012, 03:49

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

trace(tempRow.getData());


Cheers
SELECT * FROM users WHERE clue > 0
0 rows returned.

Return to “Server Side Extension Development”

Who is online

Users browsing this forum: Google [Bot] and 6 guests