[erlang-bugs] ODBC enhancement: support for out & inout parameters for stored procedures
Ingela Anderton Andin
ingela@REDACTED
Fri Nov 2 15:12:50 CET 2007
Hi!
We can absolutely add this patch to odbc if it will pass our
test-suite. However it will probably not make it in time for R12B but
maybe R12B-1. It would also be good to have some new test case for
this functionality. If you could provide some test data it would speed
things up. Preferably the test should be able to run on all database
backends. If it can not be made genericly we already use callbacks
for some special cases. The currently tested backends are sql-server
and postgres. The test case should create a data base table, do operations
on the table to verify the functionality and then drop the table.
Regards Ingela - OTP team
Scott Lystig Fritchie wrote:
> Howdy. We've been using the patch below for months without any ill
> effect and are wondering if it'd be useful for the community at
> large. IIRC, it supports out & inout parameters for stored
> procedures ... er, or something else that the ODBC driver can't quite
> do. :-)
>
> -Scott
>
> --- snip --- snip --- snip --- snip --- snip --- snip ---
>
> --- ./otp_src_R11B-5/lib/odbc/c_src/odbcserver.c.org 2007-01-29 05:17:56.000000000 -0800
> +++ ./otp_src_R11B-5/lib/odbc/c_src/odbcserver.c 2007-07-24 19:03:43.000000000 -0700
> @@ -81,7 +81,8 @@
> N - integer
> Binary - binary encodede tuple of {SQLQuery, NoRows, Parameters}
> NoRows - integer
> - Parameters - [{Datatype, Value}]
> + Parameters - [{Datatype, InOrOut, Value}]
> + InOrOut = [IN | OUT | INOUT]
> Datatype - USER_INT | USER_SMALL_INT | {USER_DECIMAL, Precision, Scale} |
> {USER_NMERIC, Precision, Scale} | {USER_CHAR, Max} | {USER_VARCHAR, Max} |
> {USER_FLOAT, Precision} | USER_REAL | USER_DOUBLE
> @@ -144,6 +145,10 @@
> static db_result_msg encode_result(db_state *state);
> static db_result_msg encode_result_set(SQLSMALLINT num_of_columns,
> db_state *state);
> +static db_result_msg encode_out_params(db_state *state,
> + int cols,
> + param_array *params,
> + int num_param_values);
> static db_result_msg encode_column_name_list(SQLSMALLINT num_of_columns,
> db_state *state);
> static db_result_msg encode_value_list(SQLSMALLINT num_of_columns,
> @@ -203,7 +208,7 @@
> static void init_driver(int erl_auto_commit_mode, int erl_trace_driver,
> db_state *state);
> static void init_param_column(param_array *params, byte *buffer, int *index,
> - int num_param_values);
> + int num_param_values, db_state* state);
>
> static void init_param_statement(int cols,
> int num_param_values,
> @@ -225,6 +230,7 @@
> db_state *state);
> static db_result_msg retrive_scrollable_cursor_support_info(db_state
> *state);
> +static int num_out_params(int num_of_params, param_array* params);
> /* ------------- Error handling functions --------------------------------*/
>
> static diagnos get_diagnos(SQLSMALLINT handleType, SQLHANDLE handle);
> @@ -240,7 +246,7 @@
> # define DO_EXIT(code) do { ExitProcess((code)); exit((code));} while (0)
> /* exit() called only to avoid a warning */
> #else
> -# define DO_EXIT(code) exit((code))
> +# define DO_EXIT(code) _exit((code))
> #endif
>
> /* ----------------- Main functions --------------------------------------*/
> @@ -327,7 +333,7 @@
> byte *request_buffer = NULL;
> db_state state =
> {NULL, NULL, NULL, NULL, 0, {NULL, 0, 0},
> - FALSE, FALSE, FALSE, FALSE, FALSE};
> + FALSE, FALSE, FALSE, FALSE, FALSE, FALSE};
> byte request_id;
> #ifdef WIN32
> SOCKET socket;
> @@ -771,7 +777,11 @@
> }
> if(msg.length == 0) {
> ei_x_new_with_version(&dynamic_buffer(state));
> - msg = encode_result(state);
> + if(out_params(state)){
> + msg = encode_out_params(state, cols, params, num_param_values);
> + }else{
> + msg = encode_result(state);
> + }
> if(msg.length == 0) {
> msg.buffer = dynamic_buffer(state).buff;
> msg.length = dynamic_buffer(state).index;
> @@ -951,9 +961,9 @@
> }
>
> if (num_of_columns == 0) {
> - elements = 2;
> - atom = "updated";
> - update = TRUE;
> + elements = 2;
> + atom = "updated";
> + update = TRUE;
> } else {
> elements = 3;
> atom = "selected";
> @@ -1011,6 +1021,85 @@
> return msg;
> }
>
> +static db_result_msg encode_out_params(db_state *state,
> + int num_of_params,
> + param_array* params,
> + int num_param_values)
> +{
> + int num_of_columns = 0;
> + int i = 0;
> + int j = 0;
> + param_array column;
> + db_result_msg msg;
> + msg = encode_empty_message();
> +
> + ei_x_encode_tuple_header(&dynamic_buffer(state), 3);
> + ei_x_encode_atom(&dynamic_buffer(state), "executed");
> +
> + num_of_columns = num_out_params(num_of_params, params);
> + ei_x_encode_long(&dynamic_buffer(state), num_of_columns);
> +
> + ei_x_encode_list_header(&dynamic_buffer(state), num_param_values);
> + for(j =0; j < num_param_values; j ++){
> +
> + if(tuple_row(state)) {
> + ei_x_encode_tuple_header(&dynamic_buffer(state), num_of_columns);
> +
> + } else {
> + ei_x_encode_list_header(&dynamic_buffer(state), num_of_columns);
> + }
> +
> + for (i = 0; i< num_of_params; i++) {
> + if(params[i].input_output_type==SQL_PARAM_INPUT){
> + continue;
> + }
> + column = params[i];
> + if (column.type.len == 0 ||
> + column.type.strlen_or_indptr == SQL_NULL_DATA) {
> + ei_x_encode_atom(&dynamic_buffer(state), "null");
> + } else {
> + void* values = retrive_param_values(&column);
> + switch(column.type.c) {
> + case SQL_C_CHAR:
> + ei_x_encode_string(&dynamic_buffer(state), ((char*)values)+j*column.type.len);
> + break;
> + case SQL_C_SLONG:
> + ei_x_encode_long(&dynamic_buffer(state), ((long*)values)[j]);
> + break;
> + case SQL_C_DOUBLE:
> + ei_x_encode_double(&dynamic_buffer(state),
> + ((double*)values)[j]);
> + break;
> + case SQL_C_BIT:
> + ei_x_encode_atom(&dynamic_buffer(state),
> + ((Boolean*)values)[j]==TRUE?"true":"false");
> + break;
> + default:
> + ei_x_encode_atom(&dynamic_buffer(state), "error");
> + break;
> + }
> + }
> + }
> + if(!tuple_row(state)) {
> + ei_x_encode_empty_list(&dynamic_buffer(state));
> + }
> + }
> + ei_x_encode_empty_list(&dynamic_buffer(state));
> + return msg;
> +}
> +
> +static int num_out_params(int num_of_params, param_array* params)
> +{
> + int ret = 0;
> + int i = 0;
> + for(i=0; i < num_of_params; i++){
> + if(params[i].input_output_type==SQL_PARAM_INPUT_OUTPUT ||
> + params[i].input_output_type==SQL_PARAM_OUTPUT)
> + ret++;
> + }
> + return ret;
> +}
> +
> /* Description: Encodes the result set into the "ei_x" - dynamic_buffer
> held by the state variable */
> static db_result_msg encode_result_set(SQLSMALLINT num_of_columns,
> @@ -1514,7 +1603,9 @@
> }
>
> /* ------------- Socket communication functions --------------------------*/
> -#ifdef WIN32
> +#define USE_IPV4
> +#define SOCKET int
> +#if defined WIN32 || defined USE_IPV4
> /* Currently only an old windows compiler is supported so we do not have ipv6
> capabilities */
> static SOCKET connect_to_erlang(const char *port)
> @@ -1856,10 +1947,11 @@
> }
>
> static void init_param_column(param_array *params, byte *buffer, int *index,
> - int num_param_values)
> + int num_param_values, db_state* state)
> {
> int size, erl_type;
> long user_type, precision, scale, length, dummy;
> + long in_or_out;
>
> ei_decode_long(buffer, index, &user_type);
>
> @@ -1972,6 +2064,20 @@
> break;
> }
> params->offset = 0;
> +
> + ei_decode_long(buffer, index, &in_or_out);
> + switch((in_or_out_type)in_or_out){
> + case(OUT):
> + out_params(state) = TRUE;
> + params->input_output_type = SQL_PARAM_OUTPUT; break;
> + case(INOUT):
> + out_params(state) = TRUE;
> + params->input_output_type = SQL_PARAM_INPUT_OUTPUT; break;
> + case(IN):
> + default:
> + params->input_output_type = SQL_PARAM_INPUT; break;
> + }
> +
> }
>
> static void init_param_statement(int cols, int num_param_values,
> @@ -1987,7 +2093,8 @@
> DO_EXIT(EXIT_ALLOC);
> }
>
> -
> + if(num_param_values <= 1) return;
> +
> if(!sql_success(SQLSetStmtAttr(statement_handle(state),
> SQL_ATTR_PARAM_BIND_TYPE,
> SQL_PARAM_BIND_BY_COLUMN, 0))) {
> @@ -2129,7 +2236,7 @@
>
> ei_decode_tuple_header(buffer, index, &size);
>
> - init_param_column(¶ms[i], buffer, index, num_param_values);
> + init_param_column(¶ms[i], buffer, index, num_param_values, state);
>
> ei_decode_list_header(buffer, index, &size);
>
> @@ -2153,7 +2260,7 @@
>
> if(!sql_success(
> SQLBindParameter(statement_handle(state), i + 1,
> - SQL_PARAM_INPUT,
> + params[i].input_output_type,
> params[i].type.c,
> params[i].type.sql,
> params[i].type.col_size,
> --- ./otp_src_R11B-5/lib/odbc/c_src/odbcserver.h.org 2007-01-29 05:18:02.000000000 -0800
> +++ ./otp_src_R11B-5/lib/odbc/c_src/odbcserver.h 2007-07-24 19:01:23.000000000 -0700
> @@ -142,6 +142,7 @@
> typedef struct {
> col_type type;
> int offset;
> + SQLUSMALLINT input_output_type;
> union {
> byte *string;
> long *integer;
> @@ -167,8 +168,15 @@
> Boolean tuple_row;
> Boolean exists_more_result_sets;
> Boolean param_query;
> + Boolean out_params;
> } db_state;
>
> +typedef enum {
> + IN = 0,
> + OUT=1,
> + INOUT=2
> +} in_or_out_type;
> +
> #define connection_handle(db_state) (db_state -> connection_handle)
> #define environment_handle(db_state) (db_state -> environment_handle)
> #define statement_handle(db_state) (db_state -> statement_handle)
> @@ -180,3 +188,4 @@
> #define tuple_row(db_state) (db_state -> tuple_row)
> #define exists_more_result_sets(db_state) (db_state -> exists_more_result_sets)
> #define param_query(db_state) (db_state -> param_query)
> +#define out_params(db_state) (db_state -> out_params)
> --- ./otp_src_R11B-5/lib/odbc/src/odbc.erl.org 2007-01-29 05:17:55.000000000 -0800
> +++ ./otp_src_R11B-5/lib/odbc/src/odbc.erl 2007-07-24 19:01:23.000000000 -0700
> @@ -864,23 +864,31 @@
> end.
>
> %%-------------------------------------------------------------------------
> -fix_params({sql_integer, Values}) ->
> - {?USER_INT, [256 | Values]};
> -fix_params({sql_smallint, Values}) ->
> - {?USER_SMALL_INT, [256 | Values]};
> -fix_params({sql_tinyint, Values}) ->
> - {?USER_TINY_INT, [256 | Values]};
> -fix_params({{sql_decimal, Precision, 0},
> +%% integer values(0, 1, 2) are defined in odbcserver.h - in_or_out_type
> +fix_inout(in) ->
> + 0;
> +fix_inout(out) ->
> + 1;
> +fix_inout(inout) ->
> + 2.
> +
> +fix_params({sql_integer, InOut, Values}) ->
> + {?USER_INT, fix_inout(InOut), [256 | Values]};
> +fix_params({sql_smallint, InOut, Values}) ->
> + {?USER_SMALL_INT, fix_inout(InOut), [256 | Values]};
> +fix_params({sql_tinyint, InOut, Values}) ->
> + {?USER_TINY_INT, fix_inout(InOut), [256 | Values]};
> +fix_params({{sql_decimal, InOut, Precision, 0},
> Values}) when Precision >= 0, Precision =< 9 ->
> - {?USER_DECIMAL, Precision, 0, [256 | Values]};
> -fix_params({{sql_decimal, Precision, Scale}, Values}) ->
> - {?USER_DECIMAL, Precision, Scale, Values};
> -fix_params({{sql_numeric, Precision, 0},
> + {?USER_DECIMAL, Precision, 0, fix_inout(InOut), [256 | Values]};
> +fix_params({{sql_decimal, InOut, Precision, Scale}, Values}) ->
> + {?USER_DECIMAL, Precision, Scale, fix_inout(InOut), Values};
> +fix_params({{sql_numeric, InOut, Precision, 0},
> Values}) when Precision >= 0, Precision =< 9 ->
> - {?USER_NUMERIC, Precision, 0, [256 | Values]};
> -fix_params({{sql_numeric, Precision, Scale}, Values}) ->
> - {?USER_NUMERIC, Precision, Scale, Values};
> -fix_params({{sql_char, Max}, Values}) ->
> + {?USER_NUMERIC, Precision, 0, fix_inout(InOut), [256 | Values]};
> +fix_params({{sql_numeric, InOut, Precision, Scale}, Values}) ->
> + {?USER_NUMERIC, Precision, Scale, fix_inout(InOut), Values};
> +fix_params({{sql_char, InOut, Max}, Values}) ->
> NewValues =
> case (catch
> lists:map(fun(Str) -> Str ++ [?STR_TERMINATOR] end, Values)) of
> @@ -889,8 +897,8 @@
> Result ->
> Result
> end,
> - {?USER_CHAR, Max, NewValues};
> -fix_params({{sql_varchar, Max}, Values}) ->
> + {?USER_CHAR, Max, fix_inout(InOut), NewValues};
> +fix_params({{sql_varchar, InOut, Max}, Values}) ->
> NewValues =
> case (catch
> lists:map(fun(Str) -> Str ++ [?STR_TERMINATOR] end, Values)) of
> @@ -899,12 +907,36 @@
> Result ->
> Result
> end,
> - {?USER_VARCHAR, Max, NewValues};
> + {?USER_VARCHAR, Max, fix_inout(InOut), NewValues};
> +fix_params({{sql_float, InOut, Precision}, Values}) ->
> + {?USER_FLOAT, Precision, fix_inout(InOut), Values};
> +fix_params({sql_real, InOut, Values}) ->
> + {?USER_REAL, fix_inout(InOut), Values};
> +fix_params({sql_double, InOut, Values}) ->
> + {?USER_DOUBLE, fix_inout(InOut), Values};
> +fix_params({sql_bit, InOut, Values}) ->
> + {?USER_BOOLEAN, fix_inout(InOut), Values};
> +%% default is IN %%%
> +fix_params({sql_integer, Values}) ->
> + fix_params({sql_integer, in, Values});
> +fix_params({sql_smallint, Values}) ->
> + fix_params({sql_smallint, in, Values});
> +fix_params({sql_tinyint, Values}) ->
> + fix_params({sql_tinyint, in, Values});
> +fix_params({{sql_decimal, Precision, Scale}, Values}) ->
> + fix_params({{sql_decimal, in, Precision, Scale}, Values});
> +fix_params({{sql_numeric, Precision, Scale}, Values}) ->
> + fix_params({{sql_numeric, in, Precision, Scale}, Values});
> +fix_params({{sql_char, Max}, Values}) ->
> + fix_params({{sql_char, in, Max}, Values});
> +fix_params({{sql_varchar, Max}, Values}) ->
> + fix_params({{sql_varchar, in, Max}, Values});
> fix_params({{sql_float, Precision}, Values}) ->
> - {?USER_FLOAT, Precision, Values};
> + fix_params({{sql_float, in, Precision}, Values});
> fix_params({sql_real, Values}) ->
> - {?USER_REAL, Values};
> + fix_params({sql_real, in, Values});
> fix_params({sql_double, Values}) ->
> - {?USER_DOUBLE, Values};
> + fix_params({sql_double, in, Values});
> fix_params({sql_bit, Values}) ->
> - {?USER_BOOLEAN, Values}.
> + fix_params({sql_bit, in, Values}).
> +
> _______________________________________________
> erlang-bugs mailing list
> erlang-bugs@REDACTED
> http://www.erlang.org/mailman/listinfo/erlang-bugs
>
>
More information about the erlang-bugs
mailing list