Hi,
I find that resultset.getMetaData().getColumnName(idx) gives the table column
name instead of assigned alias while resultset.getObject(<alias name>) returns
columns value. Is this expected?
I ran test with reflective schema (see attached code ReflectiveSpec.txt),
postgres with jdbc adaptor (see attached code PostgresSpec.txt) and also hqldb
with jdbc adaptor. I also ran test with base connection for postgres and
hsqldb where both returned alias name for resultset.getMetaData().getColumnName.
ReflectiveSpec: aliasTest
selectSomething - SQL: select V1 abc from test.t2
| V1(VARCHAR) | //
resultset.getMetaData().getColumnName
|_|zero|_|
|_|half|_|
|_|one|_|
|_|two|_|
|_|three|_|
|_|four|_|
|_|null|_|
selectSomething - SQL: select V1 as abc from test.t2
| V1(VARCHAR) | //
resultset.getMetaData().getColumnName
|_|zero|_|
|_|half|_|
|_|one|_|
|_|two|_|
|_|three|_|
|_|four|_|
|_|null|_|
selectWitName - SQL: select V1 as abc from test.t2
//using resultset.getObject("ABC")
|_|ABC: zero|_|
|_|ABC: half|_|
|_|ABC: one|_|
|_|ABC: two|_|
|_|ABC: three|_|
|_|ABC: four|_|
|_|ABC: null|_|
selectWitName - SQL: select V1 as abc from test.t2
//using resultset.getObject("V1")
java.sql.SQLException: column 'V1' not found
getColLabel - SQL: select V1 as abc from test.t2
//using resultset.getMetaData().getColumnLabel
| ABC(VARCHAR) |
Thanks & Regards,
Yuri Au Yong
Software Engineer - NPM | [email protected] | Desk: +60 3 7663 8372
Persistent Systems Ltd. | Partners in Innovation |
www.persistentsys.com<http://www.persistentsys.com/>
[sign1]<https://tnpmsupport.persistentsys.com/training>
DISCLAIMER
==========
This e-mail may contain privileged and confidential information which is the
property of Persistent Systems Ltd. It is intended only for the use of the
individual or entity to which it is addressed. If you are not the intended
recipient, you are not authorized to read, retain, copy, print, distribute or
use this message. If you have received this communication in error, please
notify the sender and delete all copies of this message. Persistent Systems
Ltd. does not accept any liability for virus infected mails.
package org.apache.calcite.jdbc;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class PostgresSpec {
final static String url = "jdbc:postgresql://192.168.56.101:5432/postgres";
final static String driver = "org.postgresql.Driver";
final static String usr = "postgres";
final static String pw = "postgres";
static Connection calciteConnection = null;
private static Connection getbaseConn() throws SQLException {
return DriverManager
.getConnection(url + "?user=postgres&password=postgres");
}
@Test
public void testAlias() throws SQLException {
System.out.println("PostgresSpec: aliasTest");
System.out
.println("Query with postgres + calcite Jdbc adaptor connection:");
Connection conn = getCalciteJdbcConnection();
Connection baseConn = getbaseConn();
selectSomething(conn, "select \"V1\" as ABC from \"T2\"");
selectSomething(conn, "select \"V1\" ABC from \"T2\"");
selectWitName(conn, "select \"V1\" as ABC from \"T2\"", new
String[]{"ABC"});
selectWitName(conn, "select \"V1\" as ABC from \"T2\"", new String[]{"V1"});
getColLabel(conn, "select \"V1\" as ABC from \"T2\"");
System.out.println("\nQuery with postgres base connection:");
selectSomething(baseConn, "select \"V1\" as ABC from \"T2\"");
selectSomething(baseConn, "select \"V1\" ABC from \"T2\"");
selectWitName(baseConn, "select \"V1\" as ABC from \"T2\"", new
String[]{"ABC"});
selectWitName(baseConn, "select \"V1\" as ABC from \"T2\"", new
String[]{"V1"});
getColLabel(baseConn, "select \"V1\" as ABC from \"T2\"");
}
public static Connection getCalciteJdbcConnection() throws SQLException {
if (calciteConnection == null) {
Properties info = new Properties();
String usrPwProp = "";
if (usr != null && pw != null) {
usrPwProp = " jdbcUser: '" + usr + "',\n"
+ " jdbcPassword: '" + pw + "',\n";
}
info.put("model", "inline:" + "{\n" + " version: '1.0',\n"
+ " defaultSchema: 'postgres',\n" + " schemas: [\n" + " {\n"
+ " type: 'jdbc',\n" + " name: 'postgres',\n"
+ " jdbcDriver: '" + driver + "',\n" + " jdbcUrl: '"
+ url + "',\n" + usrPwProp + " jdbcCatalog: null,\n"
+ " jdbcSchema: null\n" + " }\n" + " ]\n" + "}");
calciteConnection = DriverManager.getConnection("jdbc:calcite:", info);
}
return calciteConnection;
}
public static void selectSomething(Connection connection, String sql)
throws SQLException {
System.out.println("selectSomething - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
prnRs(rs);
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void selectWitName(Connection connection, String sql, String...
fieldNames)
throws SQLException {
System.out.println("selectWitName - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
while (rs.next()) {
for (String fieldName : fieldNames) {
System.out.print("|_|" + fieldName + ": " + rs.getObject(fieldName));
}
System.out.println("|_|");
}
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void getColLabel(Connection connection, String sql)
throws SQLException {
System.out.println("getColLabel - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
int colCnt = rs.getMetaData().getColumnCount();
for (int y = 1; y <= colCnt; y++) {
System.out.print("| " + rs.getMetaData().getColumnLabel(y) + "("
+ rs.getMetaData().getColumnTypeName(y) + ") ");
}
System.out.println("|");
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void prnRs(java.sql.ResultSet rs) throws SQLException {
int colCnt = rs.getMetaData().getColumnCount();
for (int y=1; y<=colCnt; y++) {
System.out.print("| " + rs.getMetaData().getColumnName(y)
+ "(" + rs.getMetaData().getColumnTypeName(y) + ") ");
}
System.out.println("|");
while(rs.next()) {
for (int i=1; i<=colCnt; i++) {
System.out.print("|_|" + rs.getObject(i));
}
System.out.println("|_|");
}
}
}package org.apache.calcite.reflective;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.junit.Test;
public class ReflectiveSpec {
@Test
public void testAlias() throws SQLException {
System.out.println("ReflectiveSpec: aliasTest");
Connection conn = getTestSchemaConn();
selectSomething(conn, "select V1 abc from test.t2");
selectSomething(conn, "select V1 as abc from test.t2");
selectWitName(conn, "select V1 abc, V2 from test.t2", new String[]{"ABC",
"V2"});
selectWitName(conn, "select V1 as abc from test.t2", new String[]{"ABC"});
selectWitName(conn, "select V1 as abc from test.t2", new String[]{"V1"});
selectWitName(conn, "select V1 abc from test.t2", new String[]{"V1"});
getColLabel(conn, "select V1 as abc from test.t2");
getColLabel(conn, "select V1 abc from test.t2");
}
public static Connection getTestSchemaConn() throws SQLException {
Properties info = new Properties();
info.put("model", "inline:" + TEST_MODEL);
Connection calciteConnection = DriverManager.getConnection("jdbc:calcite:",
info);
return calciteConnection;
}
public static void selectSomething(Connection connection, String sql)
throws SQLException {
System.out.println("selectSomething - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
prnRs(rs);
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void getColLabel(Connection connection, String sql)
throws SQLException {
System.out.println("getColLabel - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
int colCnt = rs.getMetaData().getColumnCount();
for (int y = 1; y <= colCnt; y++) {
System.out.print("| " + rs.getMetaData().getColumnLabel(y) + "("
+ rs.getMetaData().getColumnTypeName(y) + ") ");
}
System.out.println("|");
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void selectWitName(Connection connection, String sql, String...
fieldNames)
throws SQLException {
System.out.println("selectWitName - SQL: " + sql);
java.sql.ResultSet rs = null;
try {
rs = connection.prepareStatement(sql).executeQuery();
while (rs.next()) {
for (String fieldName : fieldNames) {
System.out.print("|_|" + fieldName + ": " + rs.getObject(fieldName));
}
System.out.println("|_|");
}
rs.close();
} catch (java.sql.SQLException e) {
e.printStackTrace();
if (rs != null)
rs.close();
}
}
public static void prnRs(java.sql.ResultSet rs) throws SQLException {
int colCnt = rs.getMetaData().getColumnCount();
for (int y = 1; y <= colCnt; y++) {
System.out.print("| " + rs.getMetaData().getColumnName(y) + "("
+ rs.getMetaData().getColumnTypeName(y) + ") ");
}
System.out.println("|");
while (rs.next()) {
for (int i = 1; i <= colCnt; i++) {
System.out.print("|_|" + rs.getObject(i));
}
System.out.println("|_|");
}
}
public static final String TEST_SCHEMA = " {\n"
+ " type: 'custom',\n" + " name: 'TEST',\n"
+ " factory: '" + ReflectiveSchema.Factory.class.getName() + "',\n"
+ " operand: {\n" + " class: '"
+ TestSchema.class.getName() + "'\n" + " }\n" + " }\n";
public static final String TEST_MODEL = "{\n" + " version: '1.0',\n"
+ " defaultSchema: 'TEST',\n" + " schemas: [\n" + TEST_SCHEMA
+ " ]\n" + "}";
public static class TestSchema {
@Override
public String toString() {
return "TestSchema";
}
public final Table1[] T1 = { new Table1(0, "null value", null),
new Table1(1, "a", "aa"), new Table1(2, "noBlank", ""),
new Table1(3, "oneBlank", " "), new Table1(4, "ltrOneBlank", "aa "), };
public final Table2[] T2 = {
new Table2(0, 0L, 0.0, "zero", "z", new java.sql.Timestamp(0L)),
new Table2(null, 0L, 0.5, "half", "h", new java.sql.Timestamp(0L)),
new Table2(1, 1L, 1.0, "one", "o", null),
new Table2(2, 2L, null, "two", "t", new java.sql.Timestamp(2L)),
new Table2(3, 3L, 3.0, "three", null, new java.sql.Timestamp(3L)),
new Table2(4, null, 4.0, "four", "f", new java.sql.Timestamp(4L)),
new Table2(5, 5L, 5.0, null, "n", new java.sql.Timestamp(5L)), };
}
public static class Table1 {
public final int ID;
public final String V1;
public final String V2;
public Table1(int id, String v1, String v2) {
this.ID = id;
this.V1 = v1;
this.V2 = v2;
}
public String toString() {
return "Table1 [ID: " + ID + ", V1: " + V1 + ", V2: " + V2 + "]";
}
}
public static class Table2 {
public final Integer CINT;
public final Long CBINT;
public final Double CDBL;
public final String V1;
public final String V2;
public final java.sql.Timestamp CTS;
public Table2(Integer cint, Long cbint, Double cdbl, String v1, String v2,
java.sql.Timestamp cts) {
this.CINT = cint;
this.CBINT = cbint;
this.CDBL = cdbl;
this.V1 = v1;
this.V2 = v2;
this.CTS = cts;
}
public String toString() {
return "Table1 [CINT: " + CINT + ",CBINT: " + CBINT + ", CDBL: " + CDBL
+ ", V1: " + V1 + ", V2: " + V2 + ", CTS: " + CTS + "]";
}
}
}
