/** Create a temporary table statement */
public Integer createTemporaryTable(
ParserRuleContext ctx, Create_table_columnsContext colCtx, String name) {
String managedName = null;
String sql = null;
String columns = exec.getFormattedText(colCtx);
if (conf.tempTables == Conf.TempTables.NATIVE) {
sql = "CREATE TEMPORARY TABLE " + name + "\n(" + columns + "\n)";
} else if (conf.tempTables == Conf.TempTables.MANAGED) {
managedName = name + "_" + UUID.randomUUID().toString().replace("-", "");
if (!conf.tempTablesSchema.isEmpty()) {
managedName = conf.tempTablesSchema + "." + managedName;
}
sql = "CREATE TABLE " + managedName + "\n(" + columns + "\n)";
if (!conf.tempTablesLocation.isEmpty()) {
sql += "\nLOCATION '" + conf.tempTablesLocation + "/" + managedName + "'";
}
if (trace) {
trace(ctx, "Managed table name: " + managedName);
}
}
if (sql != null) {
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
if (managedName != null) {
exec.addManagedTable(name, managedName);
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
}
return 0;
}
/** CREATE TABLE statement */
public Integer createTable(HplsqlParser.Create_table_stmtContext ctx) {
trace(ctx, "CREATE TABLE");
StringBuilder sql = new StringBuilder();
sql.append(exec.getText(ctx, ctx.T_CREATE().getSymbol(), ctx.T_OPEN_P().getSymbol()));
int cnt = ctx.create_table_columns().create_table_columns_item().size();
int cols = 0;
for (int i = 0; i < cnt; i++) {
Create_table_columns_itemContext col =
ctx.create_table_columns().create_table_columns_item(i);
if (col.create_table_column_cons() != null) {
continue;
}
if (cols > 0) {
sql.append(",\n");
}
sql.append(col.ident().getText());
sql.append(" ");
sql.append(exec.evalPop(col.dtype(), col.dtype_len()));
cols++;
}
sql.append("\n)");
if (ctx.create_table_options() != null) {
sql.append(" " + evalPop(ctx.create_table_options()).toString());
}
trace(ctx, sql.toString());
Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** FOR cursor statement */
public Integer forCursor(HplsqlParser.For_cursor_stmtContext ctx) {
trace(ctx, "FOR CURSOR - ENTERED");
exec.enterScope(Scope.Type.LOOP);
String cursor = ctx.L_ID().getText();
String sql = evalPop(ctx.select_stmt()).toString();
trace(ctx, sql);
Query query = exec.executeQuery(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
trace(ctx, "SELECT completed successfully");
exec.setSqlSuccess();
try {
ResultSet rs = query.getResultSet();
if (rs != null) {
ResultSetMetaData rm = rs.getMetaData();
int cols = rm.getColumnCount();
Var[] vars = new Var[cols];
for (int i = 0; i < cols; i++) {
vars[i] = new Var();
vars[i].setName(cursor + "." + rm.getColumnName(i + 1));
vars[i].setType(rm.getColumnType(i + 1));
exec.addVariable(vars[i]);
if (trace) {
trace(ctx, "Column: " + vars[i].getName() + " " + rm.getColumnTypeName(i + 1));
}
}
while (rs.next()) {
for (int i = 0; i < cols; i++) {
vars[i].setValue(rs, rm, i + 1);
}
visit(ctx.block());
exec.incRowCount();
}
}
} catch (SQLException e) {
exec.signal(e);
exec.closeQuery(query, exec.conf.defaultConnection);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
exec.leaveScope();
trace(ctx, "FOR CURSOR - LEFT");
return 0;
}
/** EXEC, EXECUTE and EXECUTE IMMEDIATE statement to execute dynamic SQL */
public Integer exec(HplsqlParser.Exec_stmtContext ctx) {
if (trace) {
trace(ctx, "EXECUTE");
}
Var vsql = evalPop(ctx.expr());
String sql = vsql.toString();
if (trace) {
trace(ctx, "Query: " + sql);
}
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
ResultSet rs = query.getResultSet();
if (rs != null) {
try {
ResultSetMetaData rsm = rs.getMetaData();
// Assign to variables
if (ctx.T_INTO() != null) {
int cols = ctx.L_ID().size();
if (rs.next()) {
for (int i = 0; i < cols; i++) {
Var var = exec.findVariable(ctx.L_ID(i).getText());
if (var != null) {
var.setValue(rs, rsm, i + 1);
if (trace) {
trace(
ctx,
"COLUMN: " + rsm.getColumnName(i + 1) + ", " + rsm.getColumnTypeName(i + 1));
trace(ctx, "SET " + var.getName() + " = " + var.toString());
}
} else if (trace) {
trace(ctx, "Variable not found: " + ctx.L_ID(i).getText());
}
}
exec.setSqlCode(0);
}
}
// Print the results
else {
int cols = rsm.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
if (i > 1) {
System.out.print("\t");
}
System.out.print(rs.getString(i));
}
System.out.println("");
}
}
} catch (SQLException e) {
exec.setSqlCode(e);
}
}
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** SQL INSERT VALUES statement */
public Integer insertValues(HplsqlParser.Insert_stmtContext ctx) {
trace(ctx, "INSERT VALUES");
String table = evalPop(ctx.table_name()).toString();
String conn = exec.getObjectConnection(ctx.table_name().getText());
Conn.Type type = exec.getConnectionType(conn);
StringBuilder sql = new StringBuilder();
if (type == Conn.Type.HIVE) {
sql.append("INSERT INTO TABLE " + table + " ");
if (conf.insertValues == Conf.InsertValues.NATIVE) {
sql.append("VALUES\n(");
}
} else {
sql.append("INSERT INTO " + table);
if (ctx.insert_stmt_cols() != null) {
sql.append(" " + exec.getFormattedText(ctx.insert_stmt_cols()));
}
sql.append(" VALUES\n(");
}
int rows = ctx.insert_stmt_rows().insert_stmt_row().size();
for (int i = 0; i < rows; i++) {
HplsqlParser.Insert_stmt_rowContext row = ctx.insert_stmt_rows().insert_stmt_row(i);
int cols = row.expr().size();
for (int j = 0; j < cols; j++) {
String value = evalPop(row.expr(j)).toSqlString();
if (j == 0 && type == Conn.Type.HIVE && conf.insertValues == Conf.InsertValues.SELECT) {
sql.append("SELECT ");
}
sql.append(value);
if (j + 1 != cols) {
sql.append(", ");
}
}
if (type != Conn.Type.HIVE || conf.insertValues == Conf.InsertValues.NATIVE) {
if (i + 1 == rows) {
sql.append(")");
} else {
sql.append("),\n(");
}
} else if (type == Conn.Type.HIVE && conf.insertValues == Conf.InsertValues.SELECT) {
sql.append(" FROM " + conf.dualTable);
if (i + 1 < rows) {
sql.append("\nUNION ALL\n");
}
}
}
if (trace) {
trace(ctx, sql.toString());
}
Query query = exec.executeSql(ctx, sql.toString(), conn);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** CLOSE cursor statement */
public Integer close(HplsqlParser.Close_stmtContext ctx) {
trace(ctx, "CLOSE");
String name = ctx.L_ID().toString();
Var var = exec.findVariable(name);
if (var != null && var.type == Type.CURSOR) {
exec.closeQuery((Query) var.value, exec.conf.defaultConnection);
exec.setSqlCode(0);
} else if (trace) {
trace(ctx, "Cursor not found: " + name);
}
return 0;
}
/** MERGE statement */
public Integer merge(HplsqlParser.Merge_stmtContext ctx) {
trace(ctx, "MERGE");
String sql = exec.getFormattedText(ctx);
trace(ctx, sql);
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** SQL INSERT SELECT statement */
public Integer insertSelect(HplsqlParser.Insert_stmtContext ctx) {
trace(ctx, "INSERT SELECT");
String table = evalPop(ctx.table_name()).toString();
String select = evalPop(ctx.select_stmt()).toString();
String sql = "INSERT INTO TABLE " + table + " " + select;
trace(ctx, sql);
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** USE statement */
public Integer use(HplsqlParser.Use_stmtContext ctx) {
if (trace) {
trace(ctx, "USE");
}
String sql = ctx.T_USE().toString() + " " + evalPop(ctx.expr()).toString();
if (trace) {
trace(ctx, "Query: " + sql);
}
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlCode(0);
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** Assignment from SELECT statement */
public Integer assignFromSelect(HplsqlParser.Assignment_stmt_select_itemContext ctx) {
String sql = evalPop(ctx.select_stmt()).toString();
if (trace) {
trace(ctx, sql.toString());
}
String conn = exec.getStatementConnection();
Query query = exec.executeQuery(ctx, sql.toString(), conn);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
try {
ResultSet rs = query.getResultSet();
ResultSetMetaData rm = null;
if (rs != null) {
rm = rs.getMetaData();
int cnt = ctx.ident().size();
if (rs.next()) {
for (int i = 1; i <= cnt; i++) {
Var var = exec.findVariable(ctx.ident(i - 1).getText());
if (var != null) {
var.setValue(rs, rm, i);
if (trace) {
trace(ctx, "COLUMN: " + rm.getColumnName(i) + ", " + rm.getColumnTypeName(i));
trace(ctx, "SET " + var.getName() + " = " + var.toString());
}
} else if (trace) {
trace(ctx, "Variable not found: " + ctx.ident(i - 1).getText());
}
}
exec.incRowCount();
exec.setSqlSuccess();
} else {
exec.setSqlCode(100);
exec.signal(Signal.Type.NOTFOUND);
}
}
} catch (SQLException e) {
exec.signal(query);
return 1;
} finally {
exec.closeQuery(query, conn);
}
return 0;
}
/** DELETE statement */
public Integer delete(HplsqlParser.Delete_stmtContext ctx) {
trace(ctx, "DELETE");
String table = evalPop(ctx.table_name()).toString();
StringBuilder sql = new StringBuilder();
sql.append("DELETE FROM ");
sql.append(table);
if (ctx.where_clause() != null) {
boolean oldBuildSql = exec.buildSql;
exec.buildSql = true;
sql.append(" " + evalPop(ctx.where_clause()).toString());
exec.buildSql = oldBuildSql;
}
trace(ctx, sql.toString());
Query query = exec.executeSql(ctx, sql.toString(), exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
/** DROP statement */
public Integer drop(HplsqlParser.Drop_stmtContext ctx) {
trace(ctx, "DROP");
String sql = null;
if (ctx.T_TABLE() != null) {
sql = "DROP TABLE ";
if (ctx.T_EXISTS() != null) {
sql += "IF NOT EXISTS ";
}
sql += evalPop(ctx.table_name()).toString();
}
if (sql != null) {
trace(ctx, sql);
Query query = exec.executeSql(ctx, sql, exec.conf.defaultConnection);
if (query.error()) {
exec.signal(query);
return 1;
}
exec.setSqlSuccess();
exec.closeQuery(query, exec.conf.defaultConnection);
}
return 0;
}
/** Executing or building SELECT statement */
public Integer select(HplsqlParser.Select_stmtContext ctx) {
if (ctx.parent instanceof HplsqlParser.StmtContext) {
exec.stmtConnList.clear();
trace(ctx, "SELECT");
}
boolean oldBuildSql = exec.buildSql;
exec.buildSql = true;
StringBuilder sql = new StringBuilder();
if (ctx.cte_select_stmt() != null) {
sql.append(evalPop(ctx.cte_select_stmt()).toString());
sql.append("\n");
}
sql.append(evalPop(ctx.fullselect_stmt()).toString());
exec.buildSql = oldBuildSql;
if (!(ctx.parent instanceof HplsqlParser.StmtContext)) { // No need to execute at this stage
exec.stackPush(sql);
return 0;
}
if (trace && ctx.parent instanceof HplsqlParser.StmtContext) {
trace(ctx, sql.toString());
}
if (exec.getOffline()) {
trace(ctx, "Not executed - offline mode set");
return 0;
}
String conn = exec.getStatementConnection();
Query query = exec.executeQuery(ctx, sql.toString(), conn);
if (query.error()) {
exec.signal(query);
return 1;
}
trace(ctx, "SELECT completed successfully");
exec.setSqlSuccess();
try {
ResultSet rs = query.getResultSet();
ResultSetMetaData rm = null;
if (rs != null) {
rm = rs.getMetaData();
}
int into_cnt = getIntoCount(ctx);
if (into_cnt > 0) {
trace(ctx, "SELECT INTO statement executed");
if (rs.next()) {
for (int i = 1; i <= into_cnt; i++) {
String into_name = getIntoVariable(ctx, i - 1);
Var var = exec.findVariable(into_name);
if (var != null) {
var.setValue(rs, rm, i);
if (trace) {
trace(ctx, "COLUMN: " + rm.getColumnName(i) + ", " + rm.getColumnTypeName(i));
trace(ctx, "SET " + var.getName() + " = " + var.toString());
}
} else {
trace(ctx, "Variable not found: " + into_name);
}
}
exec.incRowCount();
exec.setSqlSuccess();
} else {
exec.setSqlCode(100);
exec.signal(Signal.Type.NOTFOUND);
}
}
// Print all results for standalone SELECT statement
else if (ctx.parent instanceof HplsqlParser.StmtContext) {
int cols = rm.getColumnCount();
if (trace) {
trace(ctx, "Standalone SELECT executed: " + cols + " columns in the result set");
}
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
if (i > 1) {
System.out.print("\t");
}
System.out.print(rs.getString(i));
}
System.out.println("");
exec.incRowCount();
}
}
// Scalar subquery
else {
trace(ctx, "Scalar subquery executed, first row and first column fetched only");
if (rs.next()) {
exec.stackPush(new Var().setValue(rs, rm, 1));
exec.setSqlSuccess();
} else {
evalNull();
exec.setSqlCode(100);
}
}
} catch (SQLException e) {
exec.signal(query);
exec.closeQuery(query, exec.conf.defaultConnection);
return 1;
}
exec.closeQuery(query, exec.conf.defaultConnection);
return 0;
}
