mysql select statement with alias fails with column not found

mysql select statement with alias fails with column not found

mysql select statement with alias fails with column not found
Developemt Environment Reference: Fedora FC18 Tomcat Server 7.0.39
(localhost config) Eclipse Juno Release 2 Mysql-connecor-java Ver. 5.1.26
(jar) Mysql-server Ver. 5.5.32 (jar)
The following select statment fails with "column 'depth' not found:
"select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) -
1) as depth from ENTITY as node, ENTITY as parent where node.lft between
parent.lft and parent.rgtgroup by node.subEntityID order by node.lft";
This select statement succeeds using command level mysql as use dbName;
select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) -
1) as depth from ENTITY as node, ENTITY as parent where node.lft between
parent.lft and parent.rgtgroup by node.subEntityID order by node.lft;
The select statement also succeeds when using an sql query in Mysql
Workbench Ver. 5.2.4.7
The relevant java code fragment is given below:
rs = stmt.executeQuery(typeEntityList);
// The depth alias does not print out in this for loop
for (int i=1; i<rs.getMetaData().getColumnCount()+1; i++) {
System.out.println(rs.getMetaData().getColumnName(i));
System.out.println(rs.getMetaData().getColumnLabel(i));
}
while (rs.next()) {
// "depth" fails at the following statement
System.out.println("depth: " + rs.getInt("depth"));
String s = rs.getString("lft") + " "
+ rs.getString("subEntityID") + " "
+ rs.getString("rgt");
System.out.println(s);
entityList.add(s);
}
Also, I found on a Google search that mysql alias behavior changed in
recent versions of mysql. A suggested fix was to append
"?useOldAliasMetadataBehavior=true" to the mysql connection string to
revert back to the original behavior, but that did not correct the problem
Any help with this problem is greatly appreciated.
Thanks, Roy W. roycwhitt@gmail.com