/** 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; }
/** FETCH cursor statement */ public Integer fetch(HplsqlParser.Fetch_stmtContext ctx) { trace(ctx, "FETCH"); String name = ctx.L_ID(0).toString(); Var cursor = exec.findVariable(name); if (cursor == null || cursor.type != Type.CURSOR) { trace(ctx, "Cursor not found: " + name); exec.setSqlCode(-1); exec.signal(Signal.Type.SQLEXCEPTION); return 1; } else if (exec.getOffline()) { exec.setSqlCode(100); exec.signal(Signal.Type.NOTFOUND); return 0; } // Assign values from the row to local variables try { Query query = (Query) cursor.value; ResultSet rs = query.getResultSet(); ResultSetMetaData rsm = null; if (rs != null) { rsm = rs.getMetaData(); } if (rs != null && rsm != null) { int cols = ctx.L_ID().size() - 1; if (rs.next()) { for (int i = 1; i <= cols; i++) { Var var = exec.findVariable(ctx.L_ID(i).getText()); if (var != null) { var.setValue(rs, rsm, i); if (trace) { trace(ctx, "COLUMN: " + rsm.getColumnName(i) + ", " + rsm.getColumnTypeName(i)); trace(ctx, "SET " + var.getName() + " = " + var.toString()); } } else if (trace) { trace(ctx, "Variable not found: " + ctx.L_ID(i).getText()); } } exec.incRowCount(); exec.setSqlSuccess(); } else { exec.setSqlCode(100); exec.signal(Signal.Type.NOTFOUND); } } } catch (SQLException e) { exec.setSqlCode(e); exec.signal(Signal.Type.SQLEXCEPTION, e.getMessage(), e); } return 0; }
/** OPEN cursor statement */ public Integer open(HplsqlParser.Open_stmtContext ctx) { trace(ctx, "OPEN"); Query query = null; Var var = null; String cursor = ctx.L_ID().toString(); String sql = null; // Dynamic SQL if (ctx.T_FOR() != null) { sql = evalPop(ctx.expr()).toString(); if (trace) { trace(ctx, cursor + ": " + sql); } query = new Query(sql); var = new Var(cursor, Type.CURSOR, query); exec.addVariable(var); } // Declared cursor else { var = exec.findVariable(cursor); if (var != null && var.type == Type.CURSOR) { query = (Query) var.value; if (query.sqlExpr != null) { sql = evalPop(query.sqlExpr).toString(); query.setSql(sql); } else if (query.sqlSelect != null) { sql = evalPop(query.sqlSelect).toString(); query.setSql(sql); } if (trace) { trace(ctx, cursor + ": " + sql); } } } // Open cursor now if (query != null) { exec.executeQuery(ctx, query, exec.conf.defaultConnection); if (query.error()) { exec.signal(query); return 1; } else if (!exec.getOffline()) { exec.setSqlCode(0); } } else { trace(ctx, "Cursor not found: " + cursor); exec.setSqlCode(-1); exec.signal(Signal.Type.SQLEXCEPTION); return 1; } 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; }
/** RESIGNAL statement */ public Integer resignal(HplsqlParser.Resignal_stmtContext ctx) { trace(ctx, "RESIGNAL"); if (ctx.T_SQLSTATE() != null) { String sqlstate = evalPop(ctx.expr(0)).toString(); String text = ""; if (ctx.T_MESSAGE_TEXT() != null) { text = evalPop(ctx.expr(1)).toString(); } SQLException exception = new SQLException(text, sqlstate, -1); Signal signal = new Signal(Signal.Type.SQLEXCEPTION, text, exception); exec.setSqlCode(exception); exec.resignal(signal); } else { exec.resignal(); } 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; }
/** 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; }