From 16ea5fc0048d121d9bd09e9389142cc3a09ad047 Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Wed, 9 Jan 2013 17:27:56 +0900 Subject: test case for stored procedure with (32bit) integer out params on 64bit platform --- lib/odbc/test/odbc_query_SUITE.erl | 20 +++++++++++++++++++- lib/odbc/test/oracle.erl | 23 +++++++++++++++++++++++ 2 files changed, 42 insertions(+), 1 deletion(-) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/odbc_query_SUITE.erl b/lib/odbc/test/odbc_query_SUITE.erl index 062373afa0..e70a9d2eef 100644 --- a/lib/odbc/test/odbc_query_SUITE.erl +++ b/lib/odbc/test/odbc_query_SUITE.erl @@ -43,7 +43,7 @@ suite() -> [{ct_hooks,[ts_install_cth]}]. all() -> case odbc_test_lib:odbc_check() of ok -> - [sql_query, next, {group, scrollable_cursors}, select_count, + [stored_proc, sql_query, next, {group, scrollable_cursors}, select_count, select_next, select_relative, select_absolute, create_table_twice, delete_table_twice, duplicate_key, not_connection_owner, no_result_set, query_error, @@ -172,6 +172,24 @@ end_per_testcase(_Case, Config) -> %%------------------------------------------------------------------------- %% Test cases starts here. %%------------------------------------------------------------------------- +stored_proc(doc)-> + ["Test stored proc with OUT param"]; +stored_proc(suite) -> []; +stored_proc(Config) when is_list(Config) -> + case ?RDBMS of + oracle -> + Ref = ?config(connection_ref, Config), + {updated, _} = + odbc:sql_query(Ref, + ?RDBMS:stored_proc_integer_out()), + Result = ?RDBMS:query_result(), + Result = + ?RDBMS:param_query(Ref), + ok; + _ -> + {skip, "stored proc not yet supported"} + end. + sql_query(doc)-> ["Test the common cases"]; sql_query(suite) -> []; diff --git a/lib/odbc/test/oracle.erl b/lib/odbc/test/oracle.erl index d74863d8c1..3e5fd244a0 100644 --- a/lib/odbc/test/oracle.erl +++ b/lib/odbc/test/oracle.erl @@ -240,3 +240,26 @@ describe_floating() -> {ok,[{"F",sql_double},{"R",sql_double},{"D",sql_double}]}. describe_dec_num() -> {ok,[{"MYDEC",{sql_decimal,9,3}},{"MYNUM",{sql_decimal,9,2}}]}. + +stored_proc_integer_out() -> + "create or replace PROCEDURE test_proc1(" ++ + "int_a OUT NUMBER, " ++ + "int_b OUT NUMBER) " ++ + "is " ++ + "begin " ++ + " int_a := 123; " ++ + " int_b := 456; " ++ + "exception " ++ + "WHEN NO_DATA_FOUND THEN " ++ + " int_a := 0; " ++ + " int_b := 0; " ++ + "end;". + +param_query(Ref) -> + odbc:param_query(Ref, "call test_proc1(?,?)", + [{sql_integer, out, [0]}, + {sql_integer, out, [0]}]). + + +query_result() -> + {executed, 2, [{123, 456}]}. -- cgit v1.2.3 From de56e0943aa79723c45647722eef209d5061849a Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Thu, 10 Jan 2013 11:23:41 +0900 Subject: drop procedure after testing --- lib/odbc/test/oracle.erl | 3 +++ 1 file changed, 3 insertions(+) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/oracle.erl b/lib/odbc/test/oracle.erl index 3e5fd244a0..052461ba4f 100644 --- a/lib/odbc/test/oracle.erl +++ b/lib/odbc/test/oracle.erl @@ -241,6 +241,9 @@ describe_floating() -> describe_dec_num() -> {ok,[{"MYDEC",{sql_decimal,9,3}},{"MYNUM",{sql_decimal,9,2}}]}. +drop_proc() -> + "drop procedure test_proc1;". + stored_proc_integer_out() -> "create or replace PROCEDURE test_proc1(" ++ "int_a OUT NUMBER, " ++ -- cgit v1.2.3 From 3b53663da11d423a866cca3f7993be252210988d Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Thu, 10 Jan 2013 11:35:46 +0900 Subject: set scrollable_cursors to off for oracle driver --- lib/odbc/test/odbc_test_lib.erl | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/odbc_test_lib.erl b/lib/odbc/test/odbc_test_lib.erl index e814cd2aca..cf1f523657 100644 --- a/lib/odbc/test/odbc_test_lib.erl +++ b/lib/odbc/test/odbc_test_lib.erl @@ -79,7 +79,12 @@ strict(_,_) -> ok. platform_options() -> - []. + case ?RDBMS of + oracle -> + [{scrollable_cursors, off}]; + _ -> + [] + end. skip() -> case os:type() of -- cgit v1.2.3 From 9fae5efae6ea1350058a1ee5393d114278860b74 Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Thu, 10 Jan 2013 11:44:17 +0900 Subject: drop procedure after testing --- lib/odbc/test/odbc_query_SUITE.erl | 2 ++ 1 file changed, 2 insertions(+) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/odbc_query_SUITE.erl b/lib/odbc/test/odbc_query_SUITE.erl index e70a9d2eef..2f8f364304 100644 --- a/lib/odbc/test/odbc_query_SUITE.erl +++ b/lib/odbc/test/odbc_query_SUITE.erl @@ -185,6 +185,8 @@ stored_proc(Config) when is_list(Config) -> Result = ?RDBMS:query_result(), Result = ?RDBMS:param_query(Ref), + {updated, _} = + odbc:sql_query(Ref, ?RDBMS:drop_proc()), ok; _ -> {skip, "stored proc not yet supported"} -- cgit v1.2.3 From 2a19b42b91ba3521b4ee5f19910d90870b02b084 Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Thu, 10 Jan 2013 11:57:05 +0900 Subject: rollback disabling scrollable_cursors for oracle --- lib/odbc/test/odbc_test_lib.erl | 7 +------ 1 file changed, 1 insertion(+), 6 deletions(-) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/odbc_test_lib.erl b/lib/odbc/test/odbc_test_lib.erl index cf1f523657..e814cd2aca 100644 --- a/lib/odbc/test/odbc_test_lib.erl +++ b/lib/odbc/test/odbc_test_lib.erl @@ -79,12 +79,7 @@ strict(_,_) -> ok. platform_options() -> - case ?RDBMS of - oracle -> - [{scrollable_cursors, off}]; - _ -> - [] - end. + []. skip() -> case os:type() of -- cgit v1.2.3 From 16ea8d79a1df685acf92d338d6843144b6a85d1d Mon Sep 17 00:00:00 2001 From: Satoshi Kinoshita Date: Thu, 10 Jan 2013 15:23:37 +0900 Subject: postgresql test case for 64bit bug for param_query --- lib/odbc/test/odbc_query_SUITE.erl | 2 +- lib/odbc/test/postgres.erl | 22 ++++++++++++++++++++++ 2 files changed, 23 insertions(+), 1 deletion(-) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/odbc_query_SUITE.erl b/lib/odbc/test/odbc_query_SUITE.erl index 2f8f364304..56550bfaa6 100644 --- a/lib/odbc/test/odbc_query_SUITE.erl +++ b/lib/odbc/test/odbc_query_SUITE.erl @@ -177,7 +177,7 @@ stored_proc(doc)-> stored_proc(suite) -> []; stored_proc(Config) when is_list(Config) -> case ?RDBMS of - oracle -> + X when X == oracle; X == postgres-> Ref = ?config(connection_ref, Config), {updated, _} = odbc:sql_query(Ref, diff --git a/lib/odbc/test/postgres.erl b/lib/odbc/test/postgres.erl index d564dbd5ff..811d5ebaed 100644 --- a/lib/odbc/test/postgres.erl +++ b/lib/odbc/test/postgres.erl @@ -293,3 +293,25 @@ describe_dec_num() -> describe_timestamp() -> {ok, [{"field", sql_timestamp}]}. + +drop_proc() -> + "drop function test_proc1(OUT integer, OUT integer);". + +stored_proc_integer_out() -> + "create or replace FUNCTION test_proc1(" ++ + "OUT int_a INTEGER, " ++ + "OUT int_b INTEGER) " ++ + "AS $$ " ++ + "BEGIN " ++ + " int_a := 123; " ++ + " int_b := 456; " ++ + "END " ++ + "$$ LANGUAGE plpgsql ". + +param_query(Ref) -> + odbc:param_query(Ref, "select * from test_proc1(?, ?)", + [{sql_integer, out, [123]}, + {sql_integer, out, [456]}]). + +query_result() -> + {executed, 2, [{123, 456}]}. -- cgit v1.2.3 From a8ca64098870e420f9f28f1d70433f45dc4ba3f9 Mon Sep 17 00:00:00 2001 From: Raimo Niskanen Date: Thu, 17 Jan 2013 11:18:11 +0100 Subject: explain postgres pecularity for param query out parameters --- lib/odbc/test/oracle.erl | 1 + lib/odbc/test/postgres.erl | 25 +++++++++++++++++++++---- 2 files changed, 22 insertions(+), 4 deletions(-) (limited to 'lib/odbc/test') diff --git a/lib/odbc/test/oracle.erl b/lib/odbc/test/oracle.erl index 052461ba4f..95cf7155dc 100644 --- a/lib/odbc/test/oracle.erl +++ b/lib/odbc/test/oracle.erl @@ -241,6 +241,7 @@ describe_floating() -> describe_dec_num() -> {ok,[{"MYDEC",{sql_decimal,9,3}},{"MYNUM",{sql_decimal,9,2}}]}. +%------------------------------------------------------------------------- drop_proc() -> "drop procedure test_proc1;". diff --git a/lib/odbc/test/postgres.erl b/lib/odbc/test/postgres.erl index 811d5ebaed..0c1761b835 100644 --- a/lib/odbc/test/postgres.erl +++ b/lib/odbc/test/postgres.erl @@ -294,6 +294,7 @@ describe_dec_num() -> describe_timestamp() -> {ok, [{"field", sql_timestamp}]}. +%------------------------------------------------------------------------- drop_proc() -> "drop function test_proc1(OUT integer, OUT integer);". @@ -308,10 +309,26 @@ stored_proc_integer_out() -> "END " ++ "$$ LANGUAGE plpgsql ". +%% This does not test what you might think it is supposed to test. +%% Since the stored procedure has got 2 out parameters and no +%% in parameters it is of arity 0 as called below. +%% +%% The port program odbcserver.c will marshal these out parameters +%% and hand them to ODBC. The ODBC driver for postgres will +%% apparently not give a hoot about these out parameters and instead +%% return the result in a regular result select set. The port program +%% will assume it has the result in the out parameters and marshal +%% these as they are i.e as it itself had packed them, so they +%% come back unchanged. +%% +%% The real function result goes into the void but the code in odbcserver.c +%% that marshals out parameters returned from ODBC will be run +%% so that is what this test tests... +%% param_query(Ref) -> - odbc:param_query(Ref, "select * from test_proc1(?, ?)", - [{sql_integer, out, [123]}, - {sql_integer, out, [456]}]). + odbc:param_query(Ref, "select * from test_proc1()", + [{sql_integer, out, [111]}, + {sql_integer, out, [444]}]). query_result() -> - {executed, 2, [{123, 456}]}. + {executed, 2, [{111, 444}]}. -- cgit v1.2.3