HIVE-11254 Process result sets returned by a stored procedure (Dmitry Tolpeko via gates)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/2240dbd6 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/2240dbd6 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/2240dbd6 Branch: refs/heads/spark Commit: 2240dbd6dfddf3f14fb1538bb765833b3fdea898 Parents: 57242e3 Author: Alan Gates <ga...@hortonworks.com> Authored: Wed Jul 22 10:26:55 2015 -0700 Committer: Alan Gates <ga...@hortonworks.com> Committed: Wed Jul 22 10:26:55 2015 -0700 ---------------------------------------------------------------------- .../antlr4/org/apache/hive/hplsql/Hplsql.g4 | 40 +++++- .../main/java/org/apache/hive/hplsql/Conn.java | 10 +- .../main/java/org/apache/hive/hplsql/Exec.java | 97 ++++++++++++- .../main/java/org/apache/hive/hplsql/Query.java | 16 +++ .../main/java/org/apache/hive/hplsql/Stmt.java | 69 ++++++++- .../main/java/org/apache/hive/hplsql/Utils.java | 7 + .../main/java/org/apache/hive/hplsql/Var.java | 9 +- .../apache/hive/hplsql/functions/Function.java | 12 +- hplsql/src/main/resources/hplsql-site.xml | 95 +++++++++++++ .../org/apache/hive/hplsql/TestHplsqlLocal.java | 26 +--- .../db/create_procedure_return_cursor.sql | 53 +++++++ .../db/create_procedure_return_cursor2.sql | 59 ++++++++ hplsql/src/test/queries/local/exception2.sql | 10 -- hplsql/src/test/queries/local/exception3.sql | 5 - hplsql/src/test/queries/local/exception4.sql | 7 - hplsql/src/test/queries/local/exception5.sql | 10 -- .../db/create_procedure_return_cursor.out.txt | 135 ++++++++++++++++++ .../db/create_procedure_return_cursor2.out.txt | 139 +++++++++++++++++++ 18 files changed, 718 insertions(+), 81 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 ---------------------------------------------------------------------- diff --git a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 index 852716b..70312b2 100644 --- a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 +++ b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 @@ -33,6 +33,8 @@ single_block_stmt : // Single BEGIN END blo stmt : assignment_stmt + | allocate_cursor_stmt + | associate_locator_stmt | break_stmt | call_stmt | close_stmt @@ -117,6 +119,14 @@ assignment_stmt_select_item : (ident | (T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P)) T_COLON? T_EQUAL T_OPEN_P select_stmt T_CLOSE_P ; +allocate_cursor_stmt: + T_ALLOCATE ident T_CURSOR T_FOR ((T_RESULT T_SET) | T_PROCEDURE) ident + ; + +associate_locator_stmt : + T_ASSOCIATE (T_RESULT T_SET)? (T_LOCATOR | T_LOCATORS) T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P T_WITH T_PROCEDURE ident + ; + break_stmt : T_BREAK ; @@ -151,12 +161,15 @@ declare_condition_item : // Condition declaration ; declare_cursor_item : // Cursor declaration - (T_CURSOR ident | ident T_CURSOR) declare_cursor_return? (T_IS | T_AS | T_FOR) (select_stmt | expr ) + (T_CURSOR ident | ident T_CURSOR) (cursor_with_return | cursor_without_return)? (T_IS | T_AS | T_FOR) (select_stmt | expr ) + ; + +cursor_with_return : + T_WITH T_RETURN T_ONLY? (T_TO (T_CALLER | T_CLIENT))? ; -declare_cursor_return : +cursor_without_return : T_WITHOUT T_RETURN - | T_WITH T_RETURN T_ONLY? (T_TO (T_CALLER | T_CLIENT))? ; declare_handler_item : // Condition handler declaration @@ -238,6 +251,7 @@ dtype : // Data types | T_INT | T_INTEGER | T_NUMBER + | T_RESULT_SET_LOCATOR T_VARYING | T_SMALLINT | T_STRING | T_TIMESTAMP @@ -261,7 +275,7 @@ dtype_default : // Default clause in variable declaration ; create_function_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) T_FUNCTION ident create_routine_params create_function_return (T_AS | T_IS)? single_block_stmt + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) T_FUNCTION ident create_routine_params? create_function_return (T_AS | T_IS)? single_block_stmt ; create_function_return : @@ -269,7 +283,7 @@ create_function_return : ; create_procedure_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) (T_PROCEDURE | T_PROC) ident create_routine_params create_routine_options? (T_AS | T_IS)? label? single_block_stmt (ident T_SEMICOLON)? + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) (T_PROCEDURE | T_PROC) ident create_routine_params? create_routine_options? (T_AS | T_IS)? label? single_block_stmt (ident T_SEMICOLON)? ; create_routine_params : @@ -287,7 +301,7 @@ create_routine_options : create_routine_option : T_LANGUAGE T_SQL | T_SQL T_SECURITY (T_CREATOR | T_DEFINER | T_INVOKER | T_OWNER) - | T_DYNAMIC T_RESULT T_SETS L_INT + | T_DYNAMIC? T_RESULT T_SETS L_INT ; drop_stmt : // DROP statement @@ -886,10 +900,12 @@ null_const : // NULL constant non_reserved_words : // Tokens that are not reserved words and can be used as identifiers T_ACTIVITY_COUNT | T_ALL + | T_ALLOCATE | T_ALTER | T_AND | T_AS - | T_ASC + | T_ASC + | T_ASSOCIATE | T_AT | T_AVG | T_BATCHSIZE @@ -1004,6 +1020,8 @@ non_reserved_words : // Tokens that are not reserved words | T_LIMIT | T_LINES | T_LOCAL + | T_LOCATOR + | T_LOCATORS | T_LOGGED | T_LOOP | T_MAP @@ -1042,6 +1060,7 @@ non_reserved_words : // Tokens that are not reserved words | T_REPLACE | T_RESIGNAL | T_RESULT + | T_RESULT_SET_LOCATOR | T_RETURN | T_RETURNS | T_REVERSE @@ -1092,6 +1111,7 @@ non_reserved_words : // Tokens that are not reserved words | T_VAR | T_VARCHAR | T_VARCHAR2 + | T_VARYING | T_VARIANCE | T_VOLATILE // T_WHEN reserved word @@ -1104,10 +1124,12 @@ non_reserved_words : // Tokens that are not reserved words // Lexer rules T_ALL : A L L ; +T_ALLOCATE : A L L O C A T E ; T_ALTER : A L T E R ; T_AND : A N D ; T_AS : A S ; T_ASC : A S C ; +T_ASSOCIATE : A S S O C I A T E ; T_AT : A T ; T_AVG : A V G ; T_BATCHSIZE : B A T C H S I Z E ; @@ -1214,6 +1236,8 @@ T_LIKE : L I K E ; T_LIMIT : L I M I T ; T_LINES : L I N E S ; T_LOCAL : L O C A L ; +T_LOCATOR : L O C A T O R ; +T_LOCATORS : L O C A T O R S ; T_LOGGED : L O G G E D ; T_LOOP : L O O P ; T_MAP : M A P ; @@ -1249,6 +1273,7 @@ T_REGEXP : R E G E X P ; T_REPLACE : R E P L A C E ; T_RESIGNAL : R E S I G N A L ; T_RESULT : R E S U L T ; +T_RESULT_SET_LOCATOR : R E S U L T '_' S E T '_' L O C A T O R ; T_RETURN : R E T U R N ; T_RETURNS : R E T U R N S ; T_REVERSE : R E V E R S E ; @@ -1296,6 +1321,7 @@ T_VALUES : V A L U E S ; T_VAR : V A R ; T_VARCHAR : V A R C H A R ; T_VARCHAR2 : V A R C H A R '2' ; +T_VARYING : V A R Y I N G ; T_VOLATILE : V O L A T I L E ; T_WHEN : W H E N ; T_WHERE : W H E R E ; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java b/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java index 828fbc3..ac4b521 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Conn.java @@ -41,10 +41,12 @@ public class Conn { Exec exec; Timer timer = new Timer(); boolean trace = false; + boolean info = false; Conn(Exec e) { exec = e; trace = exec.getTrace(); + info = exec.getInfo(); } /** @@ -59,8 +61,8 @@ public class Conn { ResultSet rs = stmt.executeQuery(query.sql); timer.stop(); query.set(conn, stmt, rs); - if (trace) { - exec.trace(null, "Query executed successfully (" + timer.format() + ")"); + if (info) { + exec.info(null, "Query executed successfully (" + timer.format() + ")"); } } catch (Exception e) { query.setError(e); @@ -169,8 +171,8 @@ public class Conn { timer.start(); Connection conn = DriverManager.getConnection(url, usr, pwd); timer.stop(); - if (trace) { - exec.trace(null, "Open connection: " + url + " (" + timer.format() + ")"); + if (info) { + exec.info(null, "Open connection: " + url + " (" + timer.format() + ")"); } return conn; } http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java index 40fdc82..b35344f 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java @@ -39,6 +39,7 @@ import org.antlr.v4.runtime.Token; import org.antlr.v4.runtime.misc.NotNull; import org.antlr.v4.runtime.tree.ParseTree; import org.apache.commons.io.FileUtils; +import org.apache.hive.hplsql.Var.Type; import org.apache.hive.hplsql.functions.*; /** @@ -63,6 +64,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { Stack<Var> stack = new Stack<Var>(); Stack<String> labels = new Stack<String>(); + Stack<String> callStack = new Stack<String>(); Stack<Signal> signals = new Stack<Signal>(); Signal currentSignal; @@ -72,9 +74,10 @@ public class Exec extends HplsqlBaseVisitor<Integer> { HashMap<String, String> managedTables = new HashMap<String, String>(); HashMap<String, String> objectMap = new HashMap<String, String>(); HashMap<String, String> objectConnMap = new HashMap<String, String>(); + HashMap<String, ArrayList<Var>> returnCursors = new HashMap<String, ArrayList<Var>>(); public ArrayList<String> stmtConnList = new ArrayList<String>(); - + Arguments arguments = new Arguments(); public Conf conf; Expression expr; @@ -183,6 +186,32 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * Add a return cursor visible to procedure callers and clients + */ + public void addReturnCursor(Var var) { + String routine = callStackPeek(); + ArrayList<Var> cursors = returnCursors.get(routine); + if (cursors == null) { + cursors = new ArrayList<Var>(); + returnCursors.put(routine, cursors); + } + cursors.add(var); + } + + /** + * Get the return cursor defined in the specified procedure + */ + public Var consumeReturnCursor(String routine) { + ArrayList<Var> cursors = returnCursors.get(routine.toUpperCase()); + if (cursors == null) { + return null; + } + Var var = cursors.get(0); + cursors.remove(0); + return var; + } + + /** * Push a value to the stack */ public void stackPush(Var var) { @@ -224,6 +253,33 @@ public class Exec extends HplsqlBaseVisitor<Integer> { return null; } + /** + * Push a value to the call stack + */ + public void callStackPush(String val) { + exec.callStack.push(val.toUpperCase()); + } + + /** + * Select a value from the call stack, but not remove + */ + public String callStackPeek() { + if (!exec.callStack.isEmpty()) { + return exec.callStack.peek(); + } + return null; + } + + /** + * Pop a value from the call stack + */ + public String callStackPop() { + if (!exec.callStack.isEmpty()) { + return exec.callStack.pop(); + } + return null; + } + /** * Find an existing variable by name */ @@ -250,6 +306,17 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * Find a cursor variable by name + */ + public Var findCursor(String name) { + Var cursor = exec.findVariable(name); + if (cursor != null && cursor.type == Type.CURSOR) { + return cursor; + } + return null; + } + + /** * Enter a new scope */ public void enterScope(Scope.Type type) { @@ -286,10 +353,12 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } public void signal(Signal.Type type, String value) { + setSqlCode(-1); signal(type, value, null); } public void signal(Signal.Type type) { + setSqlCode(-1); signal(type, null, null); } @@ -480,20 +549,20 @@ public class Exec extends HplsqlBaseVisitor<Integer> { Entry<String,String> item = (Entry<String,String>)i.next(); String key = (String)item.getKey(); String value = (String)item.getValue(); - if (key == null || value == null) { + if (key == null || value == null || !key.startsWith("hplsql.")) { continue; } else if (key.compareToIgnoreCase(Conf.CONN_DEFAULT) == 0) { exec.conf.defaultConnection = value; } else if (key.startsWith("hplsql.conn.init.")) { - exec.conn.addConnectionInit(key.substring(16), value); + exec.conn.addConnectionInit(key.substring(17), value); } else if (key.startsWith(Conf.CONN_CONVERT)) { - exec.conf.setConnectionConvert(key.substring(19), value); + exec.conf.setConnectionConvert(key.substring(20), value); } else if (key.startsWith("hplsql.conn.")) { - exec.conn.addConnection(key.substring(11), value); + exec.conn.addConnection(key.substring(12), value); } else if (key.startsWith("hplsql.")) { exec.conf.setOption(key, value); @@ -940,7 +1009,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { */ @Override public Integer visitDeclare_var_item(HplsqlParser.Declare_var_itemContext ctx) { - String type = ctx.dtype().getText(); + String type = getFormattedText(ctx.dtype()); String len = null; String scale = null; Var default_ = null; @@ -969,6 +1038,22 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } return 0; } + + /** + * ALLOCATE CURSOR statement + */ + @Override + public Integer visitAllocate_cursor_stmt(HplsqlParser.Allocate_cursor_stmtContext ctx) { + return exec.stmt.allocateCursor(ctx); + } + + /** + * ASSOCIATE LOCATOR statement + */ + @Override + public Integer visitAssociate_locator_stmt(HplsqlParser.Associate_locator_stmtContext ctx) { + return exec.stmt.associateLocator(ctx); + } /** * DECLARE cursor statement http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Query.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Query.java b/hplsql/src/main/java/org/apache/hive/hplsql/Query.java index 23d963f..eaaaa67 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Query.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Query.java @@ -35,6 +35,8 @@ public class Query { ResultSet rs; Exception exception; + boolean withReturn = false; + Query() { } @@ -103,6 +105,13 @@ public class Query { } /** + * Set whether the cursor is returned to the caller + */ + public void setWithReturn(boolean withReturn) { + this.withReturn = withReturn; + } + + /** * Set an execution error */ public void setError(Exception e) { @@ -133,6 +142,13 @@ public class Query { } /** + * Check if the cursor defined as a return cursor to client + */ + public boolean getWithReturn() { + return withReturn; + } + + /** * Return error information */ public boolean error() { http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java index acc4907..bfb76cd 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java @@ -48,6 +48,58 @@ public class Stmt { } /** + * ALLOCATE CURSOR statement + */ + public Integer allocateCursor(HplsqlParser.Allocate_cursor_stmtContext ctx) { + trace(ctx, "ALLOCATE CURSOR"); + String name = ctx.ident(0).getText(); + Var cur = null; + if (ctx.T_PROCEDURE() != null) { + cur = exec.consumeReturnCursor(ctx.ident(1).getText()); + } + else if (ctx.T_RESULT() != null) { + cur = exec.findVariable(ctx.ident(1).getText()); + if (cur != null && cur.type != Type.RS_LOCATOR) { + cur = null; + } + } + if (cur == null) { + trace(ctx, "Cursor for procedure not found: " + name); + exec.signal(Signal.Type.SQLEXCEPTION); + return -1; + } + exec.addVariable(new Var(name, Type.CURSOR, cur.value)); + return 0; + } + + /** + * ASSOCIATE LOCATOR statement + */ + public Integer associateLocator(HplsqlParser.Associate_locator_stmtContext ctx) { + trace(ctx, "ASSOCIATE LOCATOR"); + int cnt = ctx.ident().size(); + if (cnt < 2) { + return -1; + } + String procedure = ctx.ident(cnt - 1).getText(); + for (int i = 0; i < cnt - 1; i++) { + Var cur = exec.consumeReturnCursor(procedure); + if (cur != null) { + String name = ctx.ident(i).getText(); + Var loc = exec.findVariable(name); + if (loc == null) { + loc = new Var(name, Type.RS_LOCATOR, cur.value); + exec.addVariable(loc); + } + else { + loc.setValue(cur.value); + } + } + } + return 0; + } + + /** * DECLARE cursor statement */ public Integer declareCursor(HplsqlParser.Declare_cursor_itemContext ctx) { @@ -62,7 +114,11 @@ public class Stmt { else if (ctx.select_stmt() != null) { query.setSelectCtx(ctx.select_stmt()); } - exec.addVariable(new Var(name, Type.CURSOR, query)); + if (ctx.cursor_with_return() != null) { + query.setWithReturn(true); + } + Var var = new Var(name, Type.CURSOR, query); + exec.addVariable(var); return 0; } @@ -262,6 +318,9 @@ public class Stmt { else if (!exec.getOffline()) { exec.setSqlCode(0); } + if (query.getWithReturn()) { + exec.addReturnCursor(var); + } } else { trace(ctx, "Cursor not found: " + cursor); @@ -278,8 +337,8 @@ public class Stmt { 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) { + Var cursor = exec.findCursor(name); + if (cursor == null) { trace(ctx, "Cursor not found: " + name); exec.setSqlCode(-1); exec.signal(Signal.Type.SQLEXCEPTION); @@ -319,9 +378,11 @@ public class Stmt { } else { exec.setSqlCode(100); - exec.signal(Signal.Type.NOTFOUND); } } + else { + exec.setSqlCode(-1); + } } catch (SQLException e) { exec.setSqlCode(e); http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java index da0d878..1815deb 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Utils.java @@ -286,4 +286,11 @@ public class Utils { float bytesPerSec = ((float)bytes)/msElapsed*1000; return Utils.formatSizeInBytes((long)bytesPerSec, "/sec"); } + + /** + * Note. This stub is to resolve name conflict with ANTLR generated source using org.antlr.v4.runtime.misc.Utils.join + */ + static <T> String join(T[] array, String separator) { + return org.antlr.v4.runtime.misc.Utils.join(array, separator); + } } http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/Var.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java index 0a4ead2..87b42f9 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java @@ -32,7 +32,7 @@ import java.sql.Timestamp; public class Var { // Data types - public enum Type {BOOL, CURSOR, DATE, DEC, FILE, IDENT, BIGINT, INTERVAL, STRING, STRINGLIST, TIMESTAMP, NULL}; + public enum Type {BOOL, CURSOR, DATE, DEC, FILE, IDENT, BIGINT, INTERVAL, RS_LOCATOR, STRING, STRINGLIST, TIMESTAMP, NULL}; public static Var Empty = new Var(); public static Var Null = new Var(Type.NULL); @@ -194,6 +194,10 @@ public class Var { return this; } + public void setValue(Object value) { + this.value = value; + } + /** * Set the new value from a result set */ @@ -244,6 +248,9 @@ public class Var { else if (type.equalsIgnoreCase("UTL_FILE.FILE_TYPE")) { return Type.FILE; } + else if (type.toUpperCase().startsWith("RESULT_SET_LOCATOR")) { + return Type.RS_LOCATOR; + } return Type.NULL; } http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java index 9895b5e..394598b 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java @@ -188,8 +188,12 @@ public class Function { return false; } exec.enterScope(Scope.Type.ROUTINE); - setCallParameters(procCtx.create_routine_params()); + exec.callStackPush(name); + if (procCtx.create_routine_params() != null) { + setCallParameters(procCtx.create_routine_params()); + } visit(procCtx.single_block_stmt()); + exec.callStackPop(); exec.leaveScope(); return true; } @@ -208,8 +212,12 @@ public class Function { } HashMap<String, Var> out = new HashMap<String, Var>(); exec.enterScope(Scope.Type.ROUTINE); - setCallParameters(ctx, procCtx.create_routine_params(), out); + exec.callStackPush(name); + if (procCtx.create_routine_params() != null) { + setCallParameters(ctx, procCtx.create_routine_params(), out); + } visit(procCtx.single_block_stmt()); + exec.callStackPop(); exec.leaveScope(); for (Map.Entry<String, Var> i : out.entrySet()) { // Set OUT parameters exec.setVariable(i.getKey(), i.getValue()); http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/main/resources/hplsql-site.xml ---------------------------------------------------------------------- diff --git a/hplsql/src/main/resources/hplsql-site.xml b/hplsql/src/main/resources/hplsql-site.xml new file mode 100644 index 0000000..1a3202a --- /dev/null +++ b/hplsql/src/main/resources/hplsql-site.xml @@ -0,0 +1,95 @@ +<configuration> +<property> + <name>hplsql.conn.default</name> + <value>hiveconn</value> + <description>The default connection profile</description> +</property> +<property> + <name>hplsql.conn.hiveconn</name> + <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://</value> + <description>HiveServer2 JDBC connection (embedded mode)</description> +</property> +<property> + <name>hplsql.conn.init.hiveconn</name> + <value> + set mapred.job.queue.name=default; + set hive.execution.engine=mr; + use default; + </value> + <description>Statements for execute after connection to the database</description> +</property> +<property> + <name>hplsql.conn.convert.hiveconn</name> + <value>true</value> + <description>Convert SQL statements before execution</description> +</property> +<property> + <name>hplsql.conn.hive1conn</name> + <value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value> + <description>Hive embedded JDBC (not requiring HiveServer)</description> +</property> +<property> + <name>hplsql.conn.hive2conn</name> + <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10000;hive;hive</value> + <description>HiveServer2 JDBC connection</description> +</property> +<property> + <name>hplsql.conn.init.hive2conn</name> + <value> + set mapred.job.queue.name=default; + set hive.execution.engine=mr; + use default; + </value> + <description>Statements for execute after connection to the database</description> +</property> +<property> + <name>hplsql.conn.convert.hive2conn</name> + <value>true</value> + <description>Convert SQL statements before execution</description> +</property> +<property> + <name>hplsql.conn.db2conn</name> + <value>com.ibm.db2.jcc.DB2Driver;jdbc:db2://localhost:50001/dbname;user;password</value> + <description>IBM DB2 connection</description> +</property> +<property> + <name>hplsql.conn.tdconn</name> + <value>com.teradata.jdbc.TeraDriver;jdbc:teradata://localhost/database=dbname,logmech=ldap;user;password</value> + <description>Teradata connection</description> +</property> +<property> + <name>hplsql.conn.mysqlconn</name> + <value>com.mysql.jdbc.Driver;jdbc:mysql://localhost/test;user;password</value> + <description>MySQL connection</description> +</property> +<property> + <name>hplsql.dual.table</name> + <value>default.dual</value> + <description>Single row, single column table for internal operations</description> +</property> +<property> + <name>hplsql.insert.values</name> + <value>native</value> + <description>How to execute INSERT VALUES statement: native (default) and select</description> +</property> +<property> + <name>hplsql.onerror</name> + <value>exception</value> + <description>Error handling behavior: exception (default), seterror and stop</description> +</property> +<property> + <name>hplsql.temp.tables</name> + <value>native</value> + <description>Temporary tables: native (default) and managed</description> +</property> +<property> + <name>hplsql.temp.tables.schema</name> + <value></value> + <description>Schema for managed temporary tables</description> +</property> +<property> + <name>hplsql.temp.tables.location</name> + <value>/tmp/plhql</value> + <description>LOcation for managed temporary tables in HDFS</description> +</property> +</configuration> \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java ---------------------------------------------------------------------- diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java index ee2be66..5ec91d9 100644 --- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java +++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java @@ -134,26 +134,6 @@ public class TestHplsqlLocal { } @Test - public void testException2() throws Exception { - run("exception2"); - } - - @Test - public void testException3() throws Exception { - run("exception2"); - } - - @Test - public void testException4() throws Exception { - run("exception2"); - } - - @Test - public void testException5() throws Exception { - run("exception2"); - } - - @Test public void testExit() throws Exception { run("exit"); } @@ -300,11 +280,7 @@ public class TestHplsqlLocal { System.setOut(new PrintStream(out)); Exec exec = new Exec(); String[] args = { "-f", "src/test/queries/local/" + testFile + ".sql", "-trace" }; - exec.init(args); - Var result = exec.run(); - if (result != null) { - System.out.println(result.toString()); - } + exec.run(args); String s = getTestOutput(out.toString()).trim(); FileUtils.writeStringToFile(new java.io.File("target/tmp/log/" + testFile + ".out.txt"), s); String t = FileUtils.readFileToString(new java.io.File("src/test/results/local/" + testFile + ".out.txt"), "utf-8").trim(); http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/db/create_procedure_return_cursor.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/db/create_procedure_return_cursor.sql b/hplsql/src/test/queries/db/create_procedure_return_cursor.sql new file mode 100644 index 0000000..d954863 --- /dev/null +++ b/hplsql/src/test/queries/db/create_procedure_return_cursor.sql @@ -0,0 +1,53 @@ +CREATE PROCEDURE spResultSet1 + DYNAMIC RESULT SETS 1 +BEGIN + DECLARE cur1 CURSOR WITH RETURN FOR + SELECT 'A', 'A1' FROM src LIMIT 3; + OPEN cur1; +END; + +CREATE PROCEDURE spResultSet2 + DYNAMIC RESULT SETS 2 +BEGIN + DECLARE cur1 CURSOR WITH RETURN FOR + SELECT 'B', 'B1' FROM src LIMIT 5; + DECLARE cur2 CURSOR WITH RETURN FOR + SELECT 'C', 'C1' FROM src LIMIT 7; + OPEN cur1; + OPEN cur2; +END; + +DECLARE v1 VARCHAR(10); +DECLARE v2 VARCHAR(10); + +CALL spResultSet1; +ALLOCATE c1 CURSOR FOR PROCEDURE spResultSet1; + +FETCH c1 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c1 INTO v1, v2; +END WHILE; +CLOSE c1; + +CALL spResultSet2; +ALLOCATE c2 CURSOR FOR PROCEDURE spResultSet2; + +FETCH c2 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c2 INTO v1, v2; +END WHILE; +CLOSE c2; + +ALLOCATE c3 CURSOR FOR PROCEDURE spResultSet2; + +FETCH c3 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c3 INTO v1, v2; +END WHILE; +CLOSE c3; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/db/create_procedure_return_cursor2.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/db/create_procedure_return_cursor2.sql b/hplsql/src/test/queries/db/create_procedure_return_cursor2.sql new file mode 100644 index 0000000..a9a1ffe --- /dev/null +++ b/hplsql/src/test/queries/db/create_procedure_return_cursor2.sql @@ -0,0 +1,59 @@ +CREATE PROCEDURE spResultSet1 + DYNAMIC RESULT SETS 1 +BEGIN + DECLARE cur1 CURSOR WITH RETURN FOR + SELECT 'A', 'A1' FROM src LIMIT 3; + OPEN cur1; +END; + +CREATE PROCEDURE spResultSet2 + DYNAMIC RESULT SETS 2 +BEGIN + DECLARE cur1 CURSOR WITH RETURN FOR + SELECT 'B', 'B1' FROM src LIMIT 5; + DECLARE cur2 CURSOR WITH RETURN FOR + SELECT 'C', 'C1' FROM src LIMIT 7; + OPEN cur1; + OPEN cur2; +END; + +DECLARE v1 VARCHAR(10); +DECLARE v2 VARCHAR(10); +DECLARE loc1 RESULT_SET_LOCATOR VARYING; +DECLARE loc2 RESULT_SET_LOCATOR VARYING; + +CALL spResultSet1; + +ASSOCIATE RESULT SET LOCATOR (loc1) WITH PROCEDURE spResultSet1; +ALLOCATE c1 CURSOR FOR RESULT SET loc1; + +FETCH c1 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c1 INTO v1, v2; +END WHILE; +CLOSE c1; + +CALL spResultSet2; + +ASSOCIATE RESULT SET LOCATOR (loc1, loc2) WITH PROCEDURE spResultSet2; +ALLOCATE c2 CURSOR FOR RESULT SET loc1; + +FETCH c2 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c2 INTO v1, v2; +END WHILE; +CLOSE c2; + +ALLOCATE c3 CURSOR FOR RESULT SET loc2; + +FETCH c3 INTO v1, v2; +WHILE (SQLCODE = 0) +DO + PRINT v1 || ' - ' || v2; + FETCH c3 INTO v1, v2; +END WHILE; +CLOSE c3; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/local/exception2.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/local/exception2.sql b/hplsql/src/test/queries/local/exception2.sql deleted file mode 100644 index 3394da8..0000000 --- a/hplsql/src/test/queries/local/exception2.sql +++ /dev/null @@ -1,10 +0,0 @@ -DECLARE - v VARCHAR(200); -BEGIN - OPEN cur FOR 'SELECT c1 FROM t1'; - FETCH cur INTO v; - CLOSE cur; -EXCEPTION WHEN OTHERS THEN - DBMS_OUTPUT.PUT_LINE('Error'); -END - http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/local/exception3.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/local/exception3.sql b/hplsql/src/test/queries/local/exception3.sql deleted file mode 100644 index a12b853..0000000 --- a/hplsql/src/test/queries/local/exception3.sql +++ /dev/null @@ -1,5 +0,0 @@ -PRINT 'Correct'; -WHILE 1=1 THEN -FETCH cur INTO v; -PRINT 'Incorrect - unreachable code, unknown cursor name, exception must be raised'; -END WHILE; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/local/exception4.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/local/exception4.sql b/hplsql/src/test/queries/local/exception4.sql deleted file mode 100644 index 38d89b5..0000000 --- a/hplsql/src/test/queries/local/exception4.sql +++ /dev/null @@ -1,7 +0,0 @@ -PRINT 'Correct'; -DECLARE EXIT HANDLER FOR SQLEXCEPTION - PRINT 'Correct - Exception raised'; -WHILE 1=1 THEN -FETCH cur INTO v; -PRINT 'Incorrect - unreachable code, unknown cursor name, exception must be raised'; -END WHILE; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/queries/local/exception5.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/local/exception5.sql b/hplsql/src/test/queries/local/exception5.sql deleted file mode 100644 index 6232984..0000000 --- a/hplsql/src/test/queries/local/exception5.sql +++ /dev/null @@ -1,10 +0,0 @@ -DECLARE cnt INT := 0; -PRINT 'Correct'; -DECLARE CONTINUE HANDLER FOR SQLEXCEPTION - PRINT 'Correct - Exception raised'; -WHILE cnt < 10 THEN -FETCH cur INTO v; -PRINT cnt; -PRINT 'Correct - exception handled'; -SET cnt = cnt + 1; -END WHILE; http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/results/db/create_procedure_return_cursor.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/db/create_procedure_return_cursor.out.txt b/hplsql/src/test/results/db/create_procedure_return_cursor.out.txt new file mode 100644 index 0000000..81aa6c8 --- /dev/null +++ b/hplsql/src/test/results/db/create_procedure_return_cursor.out.txt @@ -0,0 +1,135 @@ +Ln:1 CREATE PROCEDURE spResultSet1 +Ln:9 CREATE PROCEDURE spResultSet2 +Ln:20 DECLARE v1 VARCHAR +Ln:21 DECLARE v2 VARCHAR +EXEC PROCEDURE spResultSet1 +Ln:4 DECLARE CURSOR cur1 +Ln:6 OPEN +Ln:6 cur1: SELECT 'A', 'A1' FROM src LIMIT 3 +Ln:24 ALLOCATE CURSOR +Ln:26 FETCH +Ln:26 COLUMN: _c0, string +Ln:26 SET v1 = A +Ln:26 COLUMN: _c1, string +Ln:26 SET v2 = A1 +Ln:27 WHILE - ENTERED +Ln:29 PRINT +A - A1 +Ln:30 FETCH +Ln:30 COLUMN: _c0, string +Ln:30 SET v1 = A +Ln:30 COLUMN: _c1, string +Ln:30 SET v2 = A1 +Ln:29 PRINT +A - A1 +Ln:30 FETCH +Ln:30 COLUMN: _c0, string +Ln:30 SET v1 = A +Ln:30 COLUMN: _c1, string +Ln:30 SET v2 = A1 +Ln:29 PRINT +A - A1 +Ln:30 FETCH +Ln:27 WHILE - LEFT +Ln:32 CLOSE +EXEC PROCEDURE spResultSet2 +Ln:12 DECLARE CURSOR cur1 +Ln:14 DECLARE CURSOR cur2 +Ln:16 OPEN +Ln:16 cur1: SELECT 'B', 'B1' FROM src LIMIT 5 +Ln:17 OPEN +Ln:17 cur2: SELECT 'C', 'C1' FROM src LIMIT 7 +Ln:35 ALLOCATE CURSOR +Ln:37 FETCH +Ln:37 COLUMN: _c0, string +Ln:37 SET v1 = B +Ln:37 COLUMN: _c1, string +Ln:37 SET v2 = B1 +Ln:38 WHILE - ENTERED +Ln:40 PRINT +B - B1 +Ln:41 FETCH +Ln:41 COLUMN: _c0, string +Ln:41 SET v1 = B +Ln:41 COLUMN: _c1, string +Ln:41 SET v2 = B1 +Ln:40 PRINT +B - B1 +Ln:41 FETCH +Ln:41 COLUMN: _c0, string +Ln:41 SET v1 = B +Ln:41 COLUMN: _c1, string +Ln:41 SET v2 = B1 +Ln:40 PRINT +B - B1 +Ln:41 FETCH +Ln:41 COLUMN: _c0, string +Ln:41 SET v1 = B +Ln:41 COLUMN: _c1, string +Ln:41 SET v2 = B1 +Ln:40 PRINT +B - B1 +Ln:41 FETCH +Ln:41 COLUMN: _c0, string +Ln:41 SET v1 = B +Ln:41 COLUMN: _c1, string +Ln:41 SET v2 = B1 +Ln:40 PRINT +B - B1 +Ln:41 FETCH +Ln:38 WHILE - LEFT +Ln:43 CLOSE +Ln:45 ALLOCATE CURSOR +Ln:47 FETCH +Ln:47 COLUMN: _c0, string +Ln:47 SET v1 = C +Ln:47 COLUMN: _c1, string +Ln:47 SET v2 = C1 +Ln:48 WHILE - ENTERED +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:51 COLUMN: _c0, string +Ln:51 SET v1 = C +Ln:51 COLUMN: _c1, string +Ln:51 SET v2 = C1 +Ln:50 PRINT +C - C1 +Ln:51 FETCH +Ln:48 WHILE - LEFT +Ln:53 CLOSE \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/2240dbd6/hplsql/src/test/results/db/create_procedure_return_cursor2.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/db/create_procedure_return_cursor2.out.txt b/hplsql/src/test/results/db/create_procedure_return_cursor2.out.txt new file mode 100644 index 0000000..40f2c33 --- /dev/null +++ b/hplsql/src/test/results/db/create_procedure_return_cursor2.out.txt @@ -0,0 +1,139 @@ +Ln:1 CREATE PROCEDURE spResultSet1 +Ln:9 CREATE PROCEDURE spResultSet2 +Ln:20 DECLARE v1 VARCHAR +Ln:21 DECLARE v2 VARCHAR +Ln:22 DECLARE loc1 RESULT_SET_LOCATOR VARYING +Ln:23 DECLARE loc2 RESULT_SET_LOCATOR VARYING +EXEC PROCEDURE spResultSet1 +Ln:4 DECLARE CURSOR cur1 +Ln:6 OPEN +Ln:6 cur1: SELECT 'A', 'A1' FROM src LIMIT 3 +Ln:27 ASSOCIATE LOCATOR +Ln:28 ALLOCATE CURSOR +Ln:30 FETCH +Ln:30 COLUMN: _c0, string +Ln:30 SET v1 = A +Ln:30 COLUMN: _c1, string +Ln:30 SET v2 = A1 +Ln:31 WHILE - ENTERED +Ln:33 PRINT +A - A1 +Ln:34 FETCH +Ln:34 COLUMN: _c0, string +Ln:34 SET v1 = A +Ln:34 COLUMN: _c1, string +Ln:34 SET v2 = A1 +Ln:33 PRINT +A - A1 +Ln:34 FETCH +Ln:34 COLUMN: _c0, string +Ln:34 SET v1 = A +Ln:34 COLUMN: _c1, string +Ln:34 SET v2 = A1 +Ln:33 PRINT +A - A1 +Ln:34 FETCH +Ln:31 WHILE - LEFT +Ln:36 CLOSE +EXEC PROCEDURE spResultSet2 +Ln:12 DECLARE CURSOR cur1 +Ln:14 DECLARE CURSOR cur2 +Ln:16 OPEN +Ln:16 cur1: SELECT 'B', 'B1' FROM src LIMIT 5 +Ln:17 OPEN +Ln:17 cur2: SELECT 'C', 'C1' FROM src LIMIT 7 +Ln:40 ASSOCIATE LOCATOR +Ln:41 ALLOCATE CURSOR +Ln:43 FETCH +Ln:43 COLUMN: _c0, string +Ln:43 SET v1 = B +Ln:43 COLUMN: _c1, string +Ln:43 SET v2 = B1 +Ln:44 WHILE - ENTERED +Ln:46 PRINT +B - B1 +Ln:47 FETCH +Ln:47 COLUMN: _c0, string +Ln:47 SET v1 = B +Ln:47 COLUMN: _c1, string +Ln:47 SET v2 = B1 +Ln:46 PRINT +B - B1 +Ln:47 FETCH +Ln:47 COLUMN: _c0, string +Ln:47 SET v1 = B +Ln:47 COLUMN: _c1, string +Ln:47 SET v2 = B1 +Ln:46 PRINT +B - B1 +Ln:47 FETCH +Ln:47 COLUMN: _c0, string +Ln:47 SET v1 = B +Ln:47 COLUMN: _c1, string +Ln:47 SET v2 = B1 +Ln:46 PRINT +B - B1 +Ln:47 FETCH +Ln:47 COLUMN: _c0, string +Ln:47 SET v1 = B +Ln:47 COLUMN: _c1, string +Ln:47 SET v2 = B1 +Ln:46 PRINT +B - B1 +Ln:47 FETCH +Ln:44 WHILE - LEFT +Ln:49 CLOSE +Ln:51 ALLOCATE CURSOR +Ln:53 FETCH +Ln:53 COLUMN: _c0, string +Ln:53 SET v1 = C +Ln:53 COLUMN: _c1, string +Ln:53 SET v2 = C1 +Ln:54 WHILE - ENTERED +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:57 COLUMN: _c0, string +Ln:57 SET v1 = C +Ln:57 COLUMN: _c1, string +Ln:57 SET v2 = C1 +Ln:56 PRINT +C - C1 +Ln:57 FETCH +Ln:54 WHILE - LEFT +Ln:59 CLOSE \ No newline at end of file