SQL server ODBC doesn't throw error message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL server ODBC doesn't throw error message

Author

Topic

Awal Junanto
Starting Member

2 Posts

Posted - 2010-02-22 : 11:13:36

Dear all,

I came across a case where when cursor side is set to SQL_CURSOR_STATIC, under certain circumstances SQL server doesn’t throw any error message although it failed to execute the SQL statement.

I use ODBC and C++ to access a remote SQL server.

This is the client side code to reproduce this problem (you must compile this using the multi-threaded CRT libraries):

#include <stdio.h>
#include <process.h>

#include <windows.h>
#include <sql.h>
#include <sqlext.h>

SQLHENV henv = SQL_NULL_HENV;

bool sql_error(const char* func, SQLRETURN rc, SQLHDBC hdbc=SQL_NULL_HDBC, SQLHSTMT hstmt=SQL_NULL_HSTMT);
bool exec_sql(SQLHDBC hdbc, SQLCHAR* sql);
void sql_thread(void* arg);

/**********/
void main()
{
SQLRETURN rc;

// allocate environment handle
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (sql_error("SQLAllocHandle(SQL_HANDLE_ENV)", rc))
return;

// set ODBC version
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if (sql_error("SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", rc))
return;

// run two concurent threads to access the database simultaneously
for (int i=0; i<2; i++) {
_beginthread(sql_thread, 0, (void*)i);
}

// wait until user presses return
getchar();

return;
}

/**********/
void sql_thread(void* arg)
{
int thread_num = (int)arg;
SQLRETURN rc;
SQLHDBC hdbc;

// allocate database connection handle
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (sql_error("SQLAllocHandle(SQL_HANDLE_DBC)", rc))
return;

// establish connection to the database
SQLCHAR *inp_dsn = (SQLCHAR*)"DRIVER=SQL Server;SERVER=127.0.0.1;DATABASE=foo;UID=sa;PWD=;";
SQLCHAR out_dsn[1024+1];
SQLSMALLINT out_dsn_len;
rc = SQLDriverConnect(hdbc, 0, inp_dsn, SQL_NTS, out_dsn, sizeof(out_dsn), &out_dsn_len, SQL_DRIVER_NOPROMPT);
if (sql_error("SQLDriverConnect", rc, hdbc))
return;

for (unsigned iter = 1; ; iter++) {
char sql[128];
sprintf(sql, "exec poor_sql %d, %d", thread_num, iter);
printf("%s\n", sql);
if (!exec_sql(hdbc, (SQLCHAR*)sql)) {
return;
}

Sleep(100);
}
}

/**********/
bool exec_sql(SQLHDBC hdbc, SQLCHAR* sql)
{
SQLRETURN rc;
SQLHSTMT hstmt;

// allocate a statement handle
rc = SQLAllocStmt(hdbc, &hstmt);
if (sql_error("SQLAllocStmt", rc, hdbc, hstmt))
return false;

// set cursor type
rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);
if (sql_error("SQLSetStmtAttr(SQL_ATTR_CURSOR_TYPE)", rc, hdbc, hstmt))
return false;

// execute SQL statement
rc = SQLExecDirect(hstmt, sql, SQL_NTS);
if (sql_error("SQLExecDirect", rc, hdbc, hstmt))
return false;

// close the handle
rc = SQLFreeStmt(hstmt, SQL_DROP);
if (sql_error("SQLFreeStmt(SQL_DROP)", rc, hdbc, hstmt))
return false;

return true;
}

/**********/
bool sql_error(const char* func, SQLRETURN rc, SQLHDBC hdbc, SQLHSTMT hstmt)
{
if (rc == SQL_SUCCESS)
return false;

SQLCHAR err_msg[SQL_MAX_MESSAGE_LENGTH+1] = {0};
SQLCHAR sql_state

= {0};
SQLINTEGER native_err = 0;
SQLSMALLINT msg_len = 0;
SQLError(henv, hdbc, hstmt, sql_state, &native_err, err_msg, sizeof(err_msg), &msg_len);
printf("%s: [%s][%d]%s\n", func, sql_state, native_err, err_msg);

if (rc == SQL_SUCCESS_WITH_INFO)
return false;

return true;
}

And the server side is as follow:

CREATE TABLE visit_log(thread INT, iter INT);
GO

CREATE PROCEDURE poor_sql
@thread INT,
@iter INT
AS
BEGIN
IF object_id('tempdb..#foo') > 0
DROP TABLE #foo;

CREATE TABLE #foo(val INT,
CONSTRAINT foo_pk PRIMARY KEY(val));

WAITFOR DELAY '000:00:01' -- simulate processing delay

DROP TABLE #foo;

INSERT INTO visit_log VALUES(@thread, @iter);
END

Theoritically if there is no error, the table visit_log should contain all successful iterations for each thread. However, that is not the case. It looks more like this

thread iter
1 1
0 11
1 11

If I don’t set the cursor type to SQL_CURSOR_STATIC then the following correct error message is displayed: [42S01][Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'foo_pk' in the database.

I have tested this on the following platform:

SQL 2000 client & SQL 2000 server:
odbc32.dll 3.525.1117.0
sqlsrv32.dll 3.85.1117

and SQL 2008 client & SQL 2008 server:
odbc32.dll 3.526.3959.0
sqlsrv32.dll 3.86.3959

Please advise.

Thanks in advance
AJ

Awal Junanto
Starting Member

2 Posts

Posted - 2010-02-23 : 03:16:06

Hi again,

Maybe I was not very clear with my previous post.

My department is responsible for the database client part (C++ part) while another department is responsible for customizing the SQL part, therefore, I cannot control the validity of each written SQL statement. The real deliverable has a lot of different SQL stored procedures and each of them is usually very long and complex. So when there is a problem which goes silently without any error message like in this case, it is very difficult to track down. The above sample is only a simplified version which contains just enough code to reproduce the issue.

So, I am hoping to get some guidance for the experts here about how I can build a robust client which will throw proper error message when it encounters such issue.

Or, am I asking in the wrong forum?

Thanks again.
Awal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources