[erlang-bugs] ODBC enhancement: support for out & inout parameters for stored procedures
Scott Lystig Fritchie
fritchie@REDACTED
Mon Oct 29 23:36:16 CET 2007
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}).
+
More information about the erlang-bugs
mailing list