Issue when using SQLExecDirect against Oracle DSN

[joseoliv]

Hi all,
Im having an issue when executing SQLExecDirect function in the code below:

SQLHANDLE handle;
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR conn[256] = 
     "DSN=MY_DSN;UID=user;PWD=pwd;";
 //Allocate and environment handle
 SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
 //ODBC 3 support
 SQLSetEnvAttr (env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 //Allocate a connection handle
 SQLAllocHandle (SQL_HANDLE_DBC, env, &dbc);
 //Connect to the DSN 
 ret = SQLDriverConnect(dbc, NULL, conn, SQL_NTS, outstr, sizeof (outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
 if (SQL_SUCCEEDED(ret)){
  outfile1<<"SQLDriverConnect succeded"<<endl;
  //Allocate Statement handle
  ret = SQLAllocHandle (SQL_HANDLE_STMT, dbc, &stmt);
  if (SQL_SUCCEEDED(ret)){
   outfile1<<"Statement handle allocated"<<endl;
   //Execute Query
   ret = SQLExecDirect(stmt, (unsigned char*)"SELECT * FROM SIEBUTV2.S_USER", SQL_NTS);
   if (SQL_SUCCEEDED(ret)){
    outfile1<<"Execute query succeded"<<endl;
    outfile1.close();
    return (SecurityErrOK);
   } else{
    outfile1<<"Execute query failed"<<endl;
    outfile1.close();
    return (SecurityErrNoSuchUser);
   }
  } else{
   outfile1<<"Handle allocation failed"<<endl;
   outfile1.close();
   return (SecurityErrNoSuchUser);
  }
    } else{
  outfile1<<"SQLDriverConnect failed"<<endl;
  outfile1.close();
     return (SecurityErrNoSuchUser);
 }

I have no issues in connecting to the dsn and allocating the sql statement as shown in the logged file:

SQLDriverConnect succeded
Statement handle allocated

But for some reason the execution stops at SQLExecDirect. No line is written in the dump file and ret value cannot be accessed. I am connecting to an Oracle DB from an application than runs in Windows 2008 R2. I dont really know how I can debug this further.

Any comments or tips would be highly appreciated.

Thanks in advance,

[Paul McKenzie]

Hi all,
Im having an issue when executing SQLExecDirect function in the code below:

Why are you casting to unsigned char* in the SQLExecDirect?

That is a red flag right there. If you casted because you wanted to keep the compiler from telling you there is an error or warning, then that whole call is suspicious.

The reason why is that functions that take or return pointers to string data require you to ensure that the string data you pass is compatible. If you compiled with Unicode, and that function expects a Unicode string, then what you did is totally incorrect.

1) What is the actual prototype to SQLExecDirect? What are the actual parameter types for each argument?

2) What build type are you using, ANSI or Unicode?

3) You have "outstr" in your previous calls. What exactly is "outstr"? This is another red flag. If the build is UNICODE and that is a char array, then again, your code is using incompatible string types if the ODBC call expects wide strings (and returns wide strings).

Regards,

Paul McKenzie

[joseoliv]

Hi Paul,

Thanks very much for your response. I actually copied one of the "bad" tries I did. Here is the code I am currently working at:

#define SQL_STMT_LEN 500

ofstream outfile1;
outfile1.open("C:\\temp\\log.txt");
outfile1<<pUsername<<endl;
std::string u;
u = pUsername;
std::string select = "SELECT DISTINCT LOGIN FROM SIEBUTV2.S_USER WHERE LOGIN_DOMAIN ='" + u + "'";
SQLCHAR sqlstmt[SQL_STMT_LEN];
SQLCHAR *sql = sqlstmt;
strcpy((char*) sql, select.c_str());

SQLHANDLE handle;
//SQLSMALLINT type;
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLSMALLINT outstrlen;
SQLCHAR conn[256] =
"DSN=MY_DSN;UID=user;PWD=pwd;";
//Allocate and environment handle
SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
//ODBC 3 support
SQLSetEnvAttr (env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
//Allocate a connection handle
SQLAllocHandle (SQL_HANDLE_DBC, env, &dbc);
//Connect to the DSN
ret = SQLDriverConnect(dbc, NULL, conn, SQL_NTS, outstr, sizeof (outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(ret)){
outfile1<<"SQLDriverConnect succeded"<<endl;
//Allocate Statement handle
ret = SQLAllocHandle (SQL_HANDLE_STMT, dbc, &stmt);
if (SQL_SUCCEEDED(ret)){
outfile1<<"Statement handle allocated"<<endl;
//Execute Query
ret = SQLExecDirect(stmt, sql, strlen((char *)sql));
if (SQL_SUCCEEDED(ret)){
outfile1<<"Execute query succeded"<<endl;
outfile1.close();
return (SecurityErrOK);
} else{
outfile1<<"Execute query failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}
} else{
outfile1<<"Handle allocation failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}
} else{
outfile1<<"SQLDriverConnect failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}

I quite new at VS2010 and I just noticed that CharacterSet is not set to any value for the Project im working at. How this could affect the execution of the statement?

Thanks again for your help,

[joseoliv]

Sorry, I forgot to add code flags to my previuos message....

[Paul McKenzie]

Hi Paul,

Thanks very much for your response. I actually copied one of the "bad" tries I did. Here is the code I am currently working at:

I still don't see what the definition of outstr is iin all of that code.

Also, the question was simple, but you didn't answer -- what is the prototype to SQLExecDirect. What are the exact parameter types expected?

I quite new at VS2010 and I just noticed that CharacterSet is not set to any value for the Project im working at. How this could affect the execution of the statement?

It will effect everything if those ODBC calls are sensitive to string types. That entire code block would need to be rewritten. If you don't believe me, set the character set to Unicode and have fun fixing all the compiler errors.

Regards,

Paul McKenzie

[joseoliv]

Hi Paul,

Sorry, I did not paste line with outstr definition:

#define SQL_STMT_LEN 500

ofstream outfile1;
outfile1.open("C:\\temp\\log.txt");
outfile1<<pUsername<<endl;
std::string u;
u = pUsername;
std::string select = "SELECT DISTINCT LOGIN FROM SIEBUTV2.S_USER WHERE LOGIN_DOMAIN ='" + u + "'";
SQLCHAR sqlstmt[SQL_STMT_LEN];
SQLCHAR *sql = sqlstmt;
strcpy((char*) sql, select.c_str());

SQLHANDLE handle;
//SQLSMALLINT type;
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret;
SQLCHAR outstr[255]; --> outstr
SQLSMALLINT outstrlen;
SQLSMALLINT outstrlen;
SQLCHAR conn[256] = 
"DSN=MY_DSN;UID=user;PWD=pwd;";
//Allocate and environment handle
SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
//ODBC 3 support
SQLSetEnvAttr (env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
//Allocate a connection handle
SQLAllocHandle (SQL_HANDLE_DBC, env, &dbc);
//Connect to the DSN 
ret = SQLDriverConnect(dbc, NULL, conn, SQL_NTS, outstr, sizeof (outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(ret)){
outfile1<<"SQLDriverConnect succeded"<<endl;
//Allocate Statement handle
ret = SQLAllocHandle (SQL_HANDLE_STMT, dbc, &stmt);
if (SQL_SUCCEEDED(ret)){
outfile1<<"Statement handle allocated"<<endl;
//Execute Query
ret = SQLExecDirect(stmt, sql, strlen((char *)sql));
if (SQL_SUCCEEDED(ret)){
outfile1<<"Execute query succeded"<<endl;
outfile1.close();
return (SecurityErrOK);
} else{
outfile1<<"Execute query failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}
} else{
outfile1<<"Handle allocation failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}
} else{
outfile1<<"SQLDriverConnect failed"<<endl;
outfile1.close();
return (SecurityErrNoSuchUser);
}

Once the SQL is executed I just need to save the content of the unique column retrieved (DISTINCT LOGIN) into a const char* variable. So I think the scope of the function is pretty simple.

I did some tests with Unicode charset and got some errors in SQLCHAR->SQLWCHAR. After redefining those variable I could compile with no issues. But the result is still bad, the dll crashes at SQLExecDirect.....

Thanks

[Paul McKenzie]

Hi Paul,
I did some tests with Unicode charset and got some errors in SQLCHAR->SQLWCHAR. After redefining those variables I could compile with no issues.

First, can you format the code a little better, i.e. less cramped and better indented? It is hard to read your code.

Here is the problem:

std::string select = "SELECT DISTINCT LOGIN FROM SIEBUTV2.S_USER WHERE LOGIN_DOMAIN ='" + u + "'";

If you really did compile with Unicode, then that line is wrong. The std::string works only with 8-bit character strings. The correct string type if the compile is Unicode is std::wstring, not std::string.

Second:

SQLCHAR outstr[255]; --> outstr
//...
outstr, sizeof (outstr)

This is incorrect, as the type is probably SQLTCHAR, not SQLCHAR. Secondly, Unicode strings are 16-bit chars. This means that using sizeof() is totally wrong. The correct way to state this is:

sizeof(outstr) / sizeof(outstr[0])

That code determines the number of characters (again, since characters are 16-bit in Unicode, not 8 bit).

Here is the overall problem -- you're string types are screwed up. You are assuming things are 8-bit when they could be 16-bit, and you're using hard-coded char arrays instead of build-neutral string types.

Start slowly, and recode that entire function using string types that are neutral to whatever build you're using. This means usage of TCHAR, usage of the _T() macros to specify string literals, using SQLTCHAR instead of specifically stating SQLCHAR or SQLWCHAR, using the correct functions or code snippets to get the number of characters in an array, etc.

In other words, if that code is using char, char*, std::string in any way, it's wrong, even if it compiles. If you do that, then regardless of the build type, the code would work.

Regards,

Paul McKenzie

[Paul McKenzie]

Why are you doing this?

SQLCHAR sqlstmt[SQL_STMT_LEN];
SQLCHAR *sql = sqlstmt;

What is the reason to declare a pointer, when the array itself can be used?

Then you do this:

ret = SQLExecDirect(stmt, sql, strlen((char *)sql));

The strlen() function works on NULL terminated strings. There is no guarantee that sqlstmt is NULL terminated. That last parameter could be 0, or 234432, 56, or any integer you can think of.

Second, what does the third parameter denote? Is it a length()? Or does it mean something else?

But all of this is a moot point -- the probable reason your code crashes is a basic mishandling and misunderstanding of string data in the C++ language, and string handling for ANSI/Unicode Windows apps in general.

Regards,

Paul McKenzie

[joseoliv]

Hi,

You are right. I think Ill have to start over again readpting my code to 16 bits. Ill keep you posted.

Thanks for your advices,
Jose

[joseoliv]

Hi Paul,

I tried to keep it as simple as possible, using 16bits code and compiling in Unicode mode:

>>> José Luis Oliveros 6/25/2012 1:16 >>>

//initialize handles
   *ppErrMessage = (SSchar16*) 0;
   *ppUser = new CSSSecurityUser;
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLRETURN ret;
   //Open Log File
   ofstream outfile;
   outfile.open("E:\\temp\\log_sec_adapter.txt");
   //Allocate env
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

 

   //ODBC Version 3
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);

 

   //Allocate connection
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

 

   //Conection to Database
   ret = SQLDriverConnect (dbc, NULL, (SQLTCHAR*)TEXT("DSN=MY_DSN;UID=user;PWD=pwd;"), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
   if (SQL_SUCCEEDED(ret))
   {
    outfile<<"Connected"<<endl;
    ret = SQLAllocHandle (SQL_HANDLE_STMT, dbc, &stmt);
    if (SQL_SUCCEEDED(ret))
    {   
     outfile<<"Handle Allocated"<<endl;
     ret = SQLExecDirect (stmt, (SQLTCHAR*)TEXT("SELECT * FROM SIEBUTV2.S_USER"), SQL_NTS);
     if (SQL_SUCCEEDED(ret))
     {
      outfile<<"SQL Success"<<endl;    
      outfile.close();
         return (SecurityErrOK);
     }
     else
     {
      outfile<<"SQL Error"<<endl;    
      outfile.close();
         return (SecurityErrNoSuchUser);
     }
    }   
    else
    {
     outfile<<"Handle Error"<<endl;
     outfile.close();
     return (SecurityErrNoSuchUser);
    }
    
   } else
   {
    outfile<<"Connection failed"<<endl;
    outfile.close();
    return (SecurityErrNoSuchUser);
   }

Output:
Connected
Handle Allocated

Everything seems fine connecting to the source and allocating the sql statement, but again I cannot go thru the SQLExecDirect statement. I dont know exactly what is going wrong, can you see something "suspicious" within the ODBC Connection function SQLDriverConnect that might affect the execution of the SQL?

Thanks a lot in advance,

[Igor Vartanov]

First of all, are you able to get any (non-void) resultset for the query using the credentials user/pwd in any other tool, e.g. sqlplus console?

Second, what is behind SQL_SUCCEEDED? Are you sure SQL_SUCCESS_WITH_INFO is not considered an error? What is ret value?

Third, in case ret really is SQL_ERROR, you need to call SqlGetDiagRec to find out the real reason of the failure. Which processing you obviously miss in your code. BTW, that way you could obtain not only ODBC layer error status but as well an Oracle native error code that could clue you up about what exactly happens there with your query.

[Paul McKenzie]

but again I cannot go thru the SQLExecDirect statement.

Is the issue still a crash, or is it that SQLExecDirect gets called and returns a value for ret?

If it no longer crashes, then Igor is right in that you do not check further for the SQL diagnostic messages, which will tell you why the call returned with a non-success code. If you planned to run your application on other machines besides the one you're running on, then it's imperative you have full error checking, as you don't know what those other machines may have running or what ODBC version of the driver they're using.

Besides, you don't check at all for the return values for SQLAllocHandle, SQLSetEnvAttr, etc. All of these functions return values that you should be checking for, otherwise you will have an app that assumes these functions just "work", when they could also report a failure.

If on the other hand SQLExecDirect does crash, you should put together a complete but small "Hello World" type program (not a function, an entire program). Then you take that program to Oracle for them to resolve. The program shouldn't do anything else except 4 or 5 lines of calls to ODBC.

Regards,

Paul McKenzie