|
회사에서 DB응용을 개발하고 있는 연구원입니다.
현재 Sybase 12.0 or 12.5를 사용할 예정이고요. 개발은 12.0에서 진행중입니다.
개발 중에 문제가 발생하여 도움을 청하기 위해 이렇게 글을 올립니다.
현재 개발 중 응용의 구조는 아래와 같습니다.
+-----------+
| | -> (DBMgr 스레드 생성) -> DBMgrT(0) <=> (ESQL) <=> +---------+
| 응용 | -> DBMgrT(1) <=> (ESQL) <=> | Sybase |
| | -> DBMgrT(2) <=> (ESQL) <=> +---------+
+-----------+ -> .
.
.
즉, ESQL로 작성한 함수 몇개를 가지고 여러개의 스레드가 각각 새로 컨넥션하여 질의를 처리하는 구조입니다.
이 과정에서 아래와 같은 에러가 발생하며 정상적으로 처리되지 않네요. ㅠㅠ
SQLERROR> This routine cannot be called because another command structure has results pending
제가 작성한 코드는 아래와 같으며 C 코드로 되어 있어 DBMgr에는 함수를 첨부해 사용하고 있습니다.
아래는 해당 코드입니다.
/*
* Include the SQL Communications Area.
* You can use #include or EXEC SQL INCLUDE.
*/
/*EXEC SQL INCLUDE SQLCA;*/
/* INCLUDE FILES */
#include
#include
#include
/*
* Defines ESQL action flags.
*/
/* ESQLFreeStmt() Options */
#define ESQL_CLOSE 0
#define ESQL_DROP 1
#define ESQL_UNBIND 2
#define ESQL_RESET_PARAM 3
/* ESQLEndTran() Options */
#define ESQL_COMMIT 0
#define ESQL_ROLLBACK 1
/* SQL data type codes */
#define ESQL_UNKNOWN_TYPE 0
#define ESQL_CHAR 1
#define ESQL_NUMERIC 2
#define ESQL_DECIMAL 3
#define ESQL_INTEGER 4
#define ESQL_SMALLINT 5
#define ESQL_FLOAT 6
#define ESQL_REAL 7
#define ESQL_DOUBLE 8
#define ESQL_DATETIME 9
#define ESQL_VARCHAR 12
#define ESQL_BIT 14
#define ESQL_MONEY (-10)
/* Return values from functions */
#define ESQL_SUCCESS 0
#define ESQL_SUCCESS_WITH_INFO 1
#define ESQL_NO_DATA 100
#define ESQL_ERROR (-1)
#define ESQL_INVALID_HANDLE (-2)
#define ESQL_STILL_EXECUTING 2
#define ESQL_NEED_DATA 99
/* Defines ESQL max/min values */
#define MAX_COLUMN_SIZE 256
/*
* SyncESQLMgr Structure
*/
struct SyncESQLMgr
{
/***************************************************************
* Member attributes implementation.
**************************************************************/
/* A member variables */
char dyn_stmt[1024 * 1024];
char* data_ptr[MAX_COLUMN_SIZE];
short* indic_ptr[MAX_COLUMN_SIZE];
short type_ptr[MAX_COLUMN_SIZE];
/* A member flag for the error routine. */
int does_prepare;
int does_alloc_desc;
int conn_idx;
/***************************************************************
* Member functions implementation.
**************************************************************/
void (*esqlc_cleanup_func)(struct SyncESQLMgr* obj);
int (*esqlc_dealloc_desc_func)(struct SyncESQLMgr* obj);
int (*esqlc_alloc_desc_func)(struct SyncESQLMgr* obj);
int (*esqlc_parsing_query_func)(struct SyncESQLMgr* obj, const char* query);
int (*esqlc_prepare_statement_func)(struct SyncESQLMgr* obj);
int (*esqlc_get_error_func)(struct SyncESQLMgr* obj, int* code, char* msg, short* len);
int (*esqlc_connect_func)(struct SyncESQLMgr* obj, char* url);
int (*esqlc_disconnect_func)(struct SyncESQLMgr* obj);
int (*esqlc_exec_direct_func)(struct SyncESQLMgr* obj, char* query);
int (*esqlc_num_result_cols_func)(struct SyncESQLMgr* obj, short* columns);
int (*esqlc_describe_col_func)(struct SyncESQLMgr* obj, short col_num,
char* col_name, short buf_len, short* name_len,
short* data_type, unsigned int* data_size, short* decimal_digit, short* nullability);
int (*esqlc_bind_col_func)(struct SyncESQLMgr* obj, short col_num,
short data_type, void* data_value, int data_size, int* indic);
int (*esqlc_fetch_func)(struct SyncESQLMgr* obj);
int (*esqlc_end_tran_func)(struct SyncESQLMgr* obj, short com_type);
int (*esqlc_free_stmt_func)(struct SyncESQLMgr* obj, short com_type);
};
/*
* Global attributes implementations.
*/
static int conn_idx = 0;
/*
* Member functions implementations.
*/
void com_cleanup_func(struct SyncESQLMgr* obj);
int com_dealloc_desc_func(struct SyncESQLMgr* obj);
int com_alloc_desc_func(struct SyncESQLMgr* obj);
int com_parsing_query_func(struct SyncESQLMgr* obj, const char* query);
int com_prepare_statement_func(struct SyncESQLMgr* obj);
int com_get_error_func(struct SyncESQLMgr* obj, int* code, char* msg, short* len);
int com_connect_func(struct SyncESQLMgr* obj, char* url);
int com_disconnect_func(struct SyncESQLMgr* obj);
int com_exec_direct_func(struct SyncESQLMgr* obj, char* query);
int com_num_result_cols_func(struct SyncESQLMgr* obj, short* columns);
int com_describe_col_func(struct SyncESQLMgr* obj, short col_num,
char* col_name, short buf_len, short* name_len,
short* data_type, unsigned int* data_size, short* decimal_digit, short* nullability);
int com_bind_col_func(struct SyncESQLMgr* obj, short col_num,
short data_type, void* data_value, int data_size, int* indic);
int com_fetch_func(struct SyncESQLMgr* obj);
int com_end_tran_func(struct SyncESQLMgr* obj, short com_type);
int com_free_stmt_func(struct SyncESQLMgr* obj, short com_type);
/*
* Constructor SyncESQLMgr structure.
*/
void ESQLInit(struct SyncESQLMgr* obj)
{
obj->esqlc_cleanup_func = com_cleanup_func;
obj->esqlc_dealloc_desc_func = com_dealloc_desc_func;
obj->esqlc_alloc_desc_func = com_alloc_desc_func;
obj->esqlc_parsing_query_func = com_parsing_query_func;
obj->esqlc_prepare_statement_func = com_prepare_statement_func;
obj->esqlc_get_error_func = com_get_error_func;
obj->esqlc_connect_func = com_connect_func;
obj->esqlc_disconnect_func = com_disconnect_func;
obj->esqlc_exec_direct_func = com_exec_direct_func;
obj->esqlc_num_result_cols_func = com_num_result_cols_func;
obj->esqlc_describe_col_func = com_describe_col_func;
obj->esqlc_bind_col_func = com_bind_col_func;
obj->esqlc_fetch_func = com_fetch_func;
obj->esqlc_end_tran_func = com_end_tran_func;
obj->esqlc_free_stmt_func = com_free_stmt_func;
obj->does_prepare = 0;
obj->does_alloc_desc = 0;
obj->conn_idx = conn_idx;
conn_idx++;
}
/*
* Cleanup all objects.
*/
void com_cleanup_func(struct SyncESQLMgr* obj)
{
memset(obj->dyn_stmt, 0, sizeof(obj->dyn_stmt));
memset(obj->data_ptr, 0, sizeof(obj->data_ptr));
memset(obj->type_ptr, 0, sizeof(obj->type_ptr));
memset(obj->indic_ptr, 0, sizeof(obj->indic_ptr));
}
/*
* Deallocate input and output descriptor.
*/
int com_dealloc_desc_func(struct SyncESQLMgr* obj)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char stmt_name[32];
char input_desc_name[32], output_desc_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(stmt_name, "STMT%d", obj->conn_idx);
sprintf(input_desc_name, "INDESC%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
if (obj->does_alloc_desc)
{
EXEC SQL AT :conn_name DEALLOCATE PREPARE :stmt_name;
EXEC SQL DEALLOCATE DESCRIPTOR :input_desc_name;
EXEC SQL DEALLOCATE DESCRIPTOR :output_desc_name;
obj->does_alloc_desc = 0;
}
return sqlca.sqlcode;
}
/*
* Allocate input/output and fetch descriptor.
*/
int com_alloc_desc_func(struct SyncESQLMgr* obj)
{
EXEC SQL BEGIN DECLARE SECTION;
char input_desc_name[32], output_desc_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(input_desc_name, "INDESC%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
if (obj->does_alloc_desc) obj->esqlc_dealloc_desc_func(obj);
EXEC SQL ALLOCATE DESCRIPTOR :input_desc_name WITH MAX 256;
EXEC SQL ALLOCATE DESCRIPTOR :output_desc_name WITH MAX 256;
obj->does_alloc_desc = 1;
return sqlca.sqlcode;
}
/*
* Parsing prepare query.
*/
int com_parsing_query_func(struct SyncESQLMgr* obj, const char* query)
{
int flag = 0, i = 0, j = 0;
while (query[i] != '\0')
{
if (query[i] == '\'')
{
flag = (flag == 0) ? 1 : 0;
}
if (flag == 0 && (query[i] == ';' || query[i] == '\n'))
{
obj->dyn_stmt[j] = ' ';
j++;
}
else
{
obj->dyn_stmt[j] = query[i];
j++;
}
i++;
}
while ((j - 1) >= 0)
{
if (obj->dyn_stmt[j - 1] != ' ')
{
break;
}
j--;
}
obj->dyn_stmt[j] = '\0';
if (toupper(obj->dyn_stmt[0]) == 'S' &&
toupper(obj->dyn_stmt[1]) == 'E' &&
toupper(obj->dyn_stmt[2]) == 'L' &&
toupper(obj->dyn_stmt[3]) == 'E' &&
toupper(obj->dyn_stmt[4]) == 'C' &&
toupper(obj->dyn_stmt[5]) == 'T')
{
return 1;
}
return 0;
}
/*
* Prepare the statement.
*/
int com_prepare_statement_func(struct SyncESQLMgr* obj)
{
EXEC SQL BEGIN DECLARE SECTION;
char stmt[1024 * 1024];
char conn_name[32];
char cursor_name[32], stmt_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(cursor_name, "CURSOR%d", obj->conn_idx);
sprintf(stmt_name, "STMT%d", obj->conn_idx);
strcpy(stmt, obj->dyn_stmt);
EXEC SQL AT :conn_name PREPARE :stmt_name FROM :stmt;
/*
* The declare a cursor, giving it a name and associating it
* with a SQL statement or a PL/SQL block.
*/
EXEC SQL AT :conn_name DECLARE :cursor_name CURSOR FOR :stmt_name;
obj->does_prepare = 1;
return sqlca.sqlcode;
}
/*
* Get error message for ESQL.
*/
int com_get_error_func(struct SyncESQLMgr* obj, int* code, char* msg, short* len)
{
EXEC SQL INCLUDE SQLCA;
*code = sqlca.sqlcode;
if (sqlca.sqlerrm.sqlerrml > 0)
{
int n = 0;
n += sprintf(msg + n, "\n** SQL Server Error ");
n += sprintf(msg + n, "\n** %s", sqlca.sqlerrm.sqlerrmc);
*(msg + n) = '\0';
*len = n;
}
else
{
*len = 0;
}
return sqlca.sqlcode;
}
/*
* Connect to DBMS.
* If an error occurs when connection to the default database.
*/
int com_connect_func(struct SyncESQLMgr* obj, char* url)
{
EXEC SQL BEGIN DECLARE SECTION;
char servername[32];
char dbname[32];
char username[32];
char password[32];
char connname[32];
EXEC SQL END DECLARE SECTION;
int i = 0, j = 0;
char str[32];
char *ptr = url, *dest = 0;
EXEC SQL INCLUDE SQLCA;
memset(servername, 0, sizeof(char) * 32);
memset(dbname, 0, sizeof(char) * 32);
memset(username, 0, sizeof(char) * 32);
memset(password, 0, sizeof(char) * 32);
memset(connname, 0, sizeof(char) * 32);
/*
* Delete empty space.
*/
while (url[i] != '\0')
{
if (url[i] != ' ')
{
ptr[j] = url[i];
j++;
}
i++;
}
ptr[j] = '\0';
/*
* Parsing the connection string.
*/
while (ptr != 0)
{
if ((dest = strchr(ptr, ';')) != 0)
{
strncpy(str, ptr, dest - ptr);
*(str + (dest - ptr)) = '\0';
ptr = dest + 1;
}
else if (strlen(ptr) > 0)
{
strcpy(str, ptr);
ptr = 0;
}
else
{
*str = '\0';
ptr = 0;
}
if (strlen(str) > 4)
{
if (*str == 'D' && *(str + 1) == 'S' && *(str + 2) == 'N')
{
if ((dest = strchr(str + 4, ':')) != 0)
{
strncpy(servername, str + 4, dest - str - 4);
*(servername + (dest - str - 4)) = '\0';
strncpy(dbname, dest + 1, strlen(dest) - 1);
*(dbname + (strlen(dest) - 1)) = '\0';
}
else
{
strcpy(servername, str + 4);
}
}
else if (*str == 'U' && *(str + 1) == 'I' && *(str + 2) == 'D')
{
strcpy(username, str + 4);
}
else if (*str == 'P' && *(str + 1) == 'W' && *(str + 2) == 'D')
{
strcpy(password, str + 4);
}
}
}
EXEC SQL WHENEVER SQLERROR GO TO ESQL_CONNECT_FAIL;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
sprintf(connname, "CONN%d", obj->conn_idx);
EXEC SQL CONNECT :username IDENTIFIED BY :password AT :connname USING :servername;
if (strlen(dbname) > 0)
{
/*
* Set using database name.
*/
EXEC SQL AT :connname USE :dbname;
}
ESQL_CONNECT_FAIL:
return sqlca.sqlcode;
}
/*
* Disconnect from DBMS.
*/
int com_disconnect_func(struct SyncESQLMgr* obj)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR CONTINUE;
sprintf(conn_name, "CONN%d", obj->conn_idx);
EXEC SQL DISCONNECT :conn_name;
return sqlca.sqlcode;
}
/*
* Execute Direct SQL-statement.
*/
int com_exec_direct_func(struct SyncESQLMgr* obj, char* query)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char cursor_name[32], stmt_name[32];
char input_desc_name[32], output_desc_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(cursor_name, "CURSOR%d", obj->conn_idx);
sprintf(stmt_name, "STMT%d", obj->conn_idx);
sprintf(input_desc_name, "INDESC%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
if (obj->esqlc_parsing_query_func(obj, query))
{
obj->esqlc_alloc_desc_func(obj);
obj->esqlc_prepare_statement_func(obj);
/* Open the cursor and execute the statement. */
EXEC SQL AT :conn_name OPEN :cursor_name USING SQL DESCRIPTOR :input_desc_name;
EXEC SQL AT :conn_name DESCRIBE OUTPUT :stmt_name USING SQL DESCRIPTOR :output_desc_name;
}
else
{
EXEC SQL BEGIN DECLARE SECTION;
char stmt[1024 * 1024];
EXEC SQL END DECLARE SECTION;
strcpy(stmt, obj->dyn_stmt);
EXEC SQL AT :conn_name EXECUTE IMMEDIATE :stmt;
}
return sqlca.sqlcode;
}
/*
* Get result column number.
*/
int com_num_result_cols_func(struct SyncESQLMgr* obj, short* columns)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char output_desc_name[32];
int count = 0;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
EXEC SQL AT :conn_name GET DESCRIPTOR :output_desc_name :count = COUNT;
*columns = count;
return sqlca.sqlcode;
}
/*
* Returns the name, type, precision, scale, and nullability of the given result column.
*/
int com_describe_col_func(struct SyncESQLMgr* obj, short col_num, char* col_name, short buf_len, short* name_
len,
short* data_type, unsigned int* data_size, short* decimal_digit, short* nullability)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char output_desc_name[32];
char name[32];
int occurs, type, length, scale, nullable;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
occurs = col_num;
EXEC SQL AT :conn_name GET DESCRIPTOR :output_desc_name VALUE :occurs
:name = NAME, :type = TYPE, :length = LENGTH, :scale = SCALE, :nullable = NULLABLE;
strcpy(col_name, name);
*name_len = strlen(col_name);
*data_type = type;
*data_size = length;
*decimal_digit = scale;
*nullability = nullable;
return sqlca.sqlcode;
}
/*
* Binds application data buffers to columns in the result set.
*/
int com_bind_col_func(struct SyncESQLMgr* obj, short col_num,
short data_type, void* data_value, int data_size, int* indic)
{
int index;
index = col_num - 1;
/*
* Binds application data buffers to columns in the result set.
*/
obj->data_ptr[index] = (char*)data_value;
obj->indic_ptr[index] = (short*)indic;
obj->type_ptr[index] = data_type;
return ESQL_SUCCESS;
}
/*
* To fetch a row of data, an application calls ESQLFetch.
* SQLFetch can be called with any kind of cursor, but it only
* moves the rowset cursor in a forward-only direction. ESQLFetch
* advances the cursor to the next row and returns the data for
* any columns that were bound with calls to ESQLBindCol.
* When the cursor reaches the end of the result set, ESQLFetch
* returns SQL_NO_DATA.
*/
int com_fetch_func(struct SyncESQLMgr* obj)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char cursor_name[32];
char output_desc_name[32];
int count, occurs, coltype, length, i;
short indic;
int t_integer;
char t_string[4096];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(cursor_name, "CURSOR%d", obj->conn_idx);
sprintf(output_desc_name, "OUTDESC%d", obj->conn_idx);
EXEC SQL AT :conn_name GET DESCRIPTOR :output_desc_name :count = COUNT;
/* FETCH each row selected and print the column values. */
EXEC SQL WHENEVER NOT FOUND STOP;
EXEC SQL AT :conn_name FETCH :cursor_name INTO SQL DESCRIPTOR :output_desc_name;
for (i = 0; i < count; i++)
{
if (!obj->data_ptr[i]) break;
occurs = i + 1;
switch (obj->type_ptr[i])
{
case ESQL_INTEGER:
EXEC SQL AT :conn_name GET DESCRIPTOR :output_desc_name VALUE :occurs
:t_integer = DATA, :indic = INDICATOR;
*(int*)obj->data_ptr[i] = t_integer;
break;
case ESQL_CHAR:
EXEC SQL AT :conn_name GET DESCRIPTOR :output_desc_name VALUE :occurs
:t_string = DATA, :indic = INDICATOR;
length = strlen(t_string);
while (length > 0 && *(t_string + length - 1) == ' ') length--;
*(t_string + length) = '\0';
if (length > 0) strcpy(obj->data_ptr[i], t_string);
else *(obj->data_ptr[i]) = '\0';
break;
}
*(obj->indic_ptr[i]) = indic;
}
return sqlca.sqlcode;
}
/*
* By default, the ESQL driver closes a statement's associated
* cursor when ESQLEndTran commits or rolls back an operation.
* Server cursors are closed unless they are static.
*/
int com_end_tran_func(struct SyncESQLMgr* obj, short com_type)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
switch (com_type)
{
case ESQL_COMMIT:
EXEC SQL AT :conn_name COMMIT WORK;
break;
case ESQL_ROLLBACK:
EXEC SQL AT :conn_name ROLLBACK WORK;
break;
}
return sqlca.sqlcode;
}
/*
* Stops processing associated with a specific statement, closes any open
* cursors associated with the statement, discards pending results, or,
* optionally, frees all resources associated with the statement handle.
*/
int com_free_stmt_func(struct SyncESQLMgr* obj, short com_type)
{
EXEC SQL BEGIN DECLARE SECTION;
char conn_name[32];
char cursor_name[32];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
sprintf(conn_name, "CONN%d", obj->conn_idx);
sprintf(cursor_name, "CURSOR%d", obj->conn_idx);
switch (com_type)
{
case ESQL_CLOSE:
if (obj->does_prepare)
{
/* Close the cursor. */
EXEC SQL AT :conn_name CLOSE :cursor_name;
obj->esqlc_dealloc_desc_func(obj);
obj->does_prepare = 0;
}
break;
case ESQL_UNBIND:
/* Cleanup of objects. */
obj->esqlc_cleanup_func(obj);
break;
}
return sqlca.sqlcode;
}
겨우겨우 어떻게 여기까지 오긴했는데.... 동시에 질의처리가 되지 않아 골머리를 앓고 있습니다. ㅠㅠ
어려우시겠지만 코드를 보시고 검토 부탁드립니다. ^^
|
