[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

error handling for oracle sql

my sql running well in oracle directly, but has error when connect from remote internet.

Want to add error handling code to see what happened, May I know how to do so. Thanks

      SQLAllocHandle(SQL_HANDLE_STMT, dbSession.hDbc, &hStmt);
      lstrcpy( (char *) SqlStmt, "SELECT ");
      lstrcat( (char *) SqlStmt, viType);
      lstrcat( (char *) SqlStmt, " FROM instance WHERE instid=");
      lstrcat( (char *) SqlStmt, db2_instid);
      lstrcat( (char *) SqlStmt, ";");
      SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));
      SQLBindCol( hStmt,1,SQL_C_CHAR,Timestamp,sizeof(Timestamp), &DataLen);
      RetCode = SQLFetch( hStmt );
      SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
0
turbot_yu
Asked:
turbot_yu
  • 18
  • 10
  • 2
2 Solutions
 
Jase-CoderCommented:
You need to tell us which APi your using. For error handling you just need to check the return values of the functions you call. Or you should check if they throw exceptions.


for example:

if(SQLAllocHandle(SQL_HANDLE_STMT, dbSession.hDbc, &hStmt) == 0)
{
     failed
     some error message
}


or

try
{
   SQLAllocHandle(SQL_HANDLE_STMT, dbSession.hDbc, &hStmt);
     lstrcpy( (char *) SqlStmt, "SELECT ");
     lstrcat( (char *) SqlStmt, viType);
     lstrcat( (char *) SqlStmt, " FROM instance WHERE instid=");
     lstrcat( (char *) SqlStmt, db2_instid);
     lstrcat( (char *) SqlStmt, ";");
     SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));
     SQLBindCol( hStmt,1,SQL_C_CHAR,Timestamp,sizeof(Timestamp), &DataLen);
     RetCode = SQLFetch( hStmt );
     SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
catch(Oracle exception)

The exception and the return value I haved used are not real values I just made them up to show you how to handle the error. Check your documentation for function return values and exceptions.
0
 
turbot_yuAuthor Commented:
I am using the connection as:
      SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
      SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER );
      SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc);
      SQLinfo = SQLConnect( hDbc,
            (SQLCHAR*)DefServerName,SQL_NTS,
            (SQLCHAR*)"h1",SQL_NTS,
            (SQLCHAR*)"h1",SQL_NTS );
0
 
turbot_yuAuthor Commented:
Is it the API?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
turbot_yuAuthor Commented:
After I can the catch(Oracle exception), what should I do?
0
 
turbot_yuAuthor Commented:
what should be put inside catch( ). thanks
0
 
mahesh1402Commented:
When SQLAllocHandle returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLGetDiagRec with the appropriate HandleType and Handle set to the value of InputHandle. SQL_SUCCESS_WITH_INFO (but not SQL_ERROR) can be returned for the OutputHandle argument.

You may trap return value of  SQLAllocHandle() function..refer error code and description table gievn on folowing link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlallochandle.asp

e.g return code of SQLSTATE  is
08003 Connection does not exist
HY001 Memory allocation  
IM001 Driver does not support this function
...
....

-MAHESH
0
 
mahesh1402Commented:
its good to go like this

/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
     
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set the ODBC version environment attribute */
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
         
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
                             
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)5, SQL_IS_UINTEGER);

/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLCHAR*) "MyODBCName", SQL_NTS,(SQLCHAR*) "", SQL_NTS,(SQLCHAR*) "", SQL_NTS);

//if (retcode !=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO){
AfxMessageBox( _T(":No ODBC Connection") );

NOTE you may trap errors by retcode of appropriate functions individually to locate which function fails.

-MAHESH
0
 
turbot_yuAuthor Commented:

thx, i am trying, when set up the connection is ok, after uploaded some data, the connection seem will have problem.
0
 
Jase-CoderCommented:
do something like:

    SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
     SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER );
     SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc);
     
      SQLinfo = SQLConnect( hDbc,
          (SQLCHAR*)DefServerName,SQL_NTS,
          (SQLCHAR*)"h1",SQL_NTS,
          (SQLCHAR*)"h1",SQL_NTS );

       if(SQLinfo == SQL_ERROR)
       {
           // call SQLGetDiagRec;


       }


Once you have called SQLGetDiagRec() you can then check the return value and compare it to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlconnect.asp
           
0
 
turbot_yuAuthor Commented:
I am trying to upload the data by a while loop,
every time fetch one recorder from msSQL, then write to oracle.  but some time cannot write into.
0
 
mahesh1402Commented:
have you checked return code when it fails ?

-MAHESH
0
 
turbot_yuAuthor Commented:
It fails randomly, I am trying to catch it.
0
 
mahesh1402Commented:
As I said in my first statement "When SQLAllocHandle returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLGetDiagRec with the appropriate HandleType and Handle set to the value of InputHandle."

e.g
               SQLCHAR pszSqlState[SQL_MAX_MESSAGE_LENGTH] = "";
      SQLCHAR pszErrorMsg[SQL_MAX_MESSAGE_LENGTH] = "";

      SQLINTEGER      nNativeError = 0L;
      SQLSMALLINT nErrorMsg = 0;

    SQLGetDiagRec(SQL_HANDLE_DBC, hEnv, 1,  pszSqlState, &nNativeError,pszErrorMsg, SQL_MAX_MESSAGE_LENGTH - 1, &nErrorMsg);
   // str.Format("Error %s (%d)\n State %s \n", pszErrorMsg, nErrorMsg, pszSqlState); <====  print error

-MAHESH


0
 
turbot_yuAuthor Commented:
Thx MAHESH, I got ur idea, but when trying, cannot get the return value, is there any thing wrong,

      SQLHSTMT      hStmt;
       SQLCHAR        Sqlstate;
       SQLINTEGER     NativeErrorPtr;
       SQLCHAR         MessageText;
       SQLSMALLINT    TextLengthPtr;
       SQLRETURN            rc9;
........
      rc9=SQLGetDiagRec(
                  SQL_HANDLE_STMT,
                   hStmt,
                  1,
                  &Sqlstate,
                  &NativeErrorPtr,
                  &MessageText,
                  600,
                  &TextLengthPtr);

      SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
0
 
turbot_yuAuthor Commented:
The MessageText is            204 'Ì'
The SqlState is             4 ''

0
 
mahesh1402Commented:
>>cannot get the return value

what problems you are facing ? look at my last post about how to format error string to display purpose...

btw have you tried to trace error in individual functions by trapping return error code by each function ?

-MAHESH
0
 
turbot_yuAuthor Commented:
I use the SQLGetDiagRec, but cannot return the error text. Any defination error.
I also tried your sample, still not work.

When use the trapping return code, it only gave the value of -1.
0
 
mahesh1402Commented:
Note return code you will get in like

retcode = SQLAllocHandle(..)

retcode = SQLSetEnvAttr(...)

retcode = SQLConnect(...)

e.g
               SQLHENV hEnv;
      SQLHDBC hOdbc;
      char pStatus[10], pMsg[101];
      long erg;
      SQLINTEGER SQLerr;
      SQLSMALLINT SQLmsglen;
...
...
retcode = SQLConnect(hOdbc, (SQLCHAR*) "myserver", SQL_NTS,(SQLCHAR*) "user", SQL_NTS,(SQLCHAR*) "pass", SQL_NTS);

if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) // NOTE
      {
            SQLGetDiagRec(SQL_HANDLE_DBC, hOdbc,1,pStatus,&SQLerr,pMsg,100,&SQLmsglen);
            // format string and print("%s (%d)\n",pMsg,(int)SQLerr);
            SQLFreeHandle(SQL_HANDLE_DBC, &hOdbc);
            SQLFreeHandle(SQL_HANDLE_ENV, &hEnv);
            return 0;
      }

-MAHESH
0
 
turbot_yuAuthor Commented:
there is one compile error when follow the sample code:
'SQLGetDiagRec' : cannot convert parameter 4 from 'char [10]' to 'unsigned char *'
0
 
turbot_yuAuthor Commented:
I follow the define as 'char pStatus[10], pMsg[101];'


the line is SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,pStatus,&SQLerr,pMsg,100,&SQLmsglen);
0
 
mahesh1402Commented:
how you are tracing string and error code when you get values 204 'Ì' and  4 ''....

-MAHESH
0
 
turbot_yuAuthor Commented:
that time i use
 SQLCHAR        Sqlstate;            
...
rc9=SQLGetDiagRec(
            SQL_HANDLE_DBC,
            hStmt,
            1,
            &Sqlstate,
            &NativeErrorPtr,
            &MessageText,
            600,
            &TextLengthPtr);
0
 
mahesh1402Commented:
then what contents you received in NativeErrorPtr and MessageText, ? same as above ??
0
 
turbot_yuAuthor Commented:
Now compile error, cannot run any more.
0
 
turbot_yuAuthor Commented:
                 SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));
                  SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,Sqlstate,&SQLerr,pMsg,100,&SQLmsglen);
0
 
turbot_yuAuthor Commented:
If retrieve back, then can run, but the text can not return correctly.

all code:
{
dbSession = OpenDatabase(defServerName);
.....
WriteToEvent(dbSession, MeterName, strTimestampDT, strTimestampMS, iMS, "V1 Waveform", strinstid, "0", strType, &BLOBnum);
....
}
void WriteToEvent(DataStruct dbSession, CString MName, CString tsDT, CString tsMS, int mSec, CString qty, CString db2_instid, CString channel, CString instanceCol, int* BLOBnum)
{
....
      while( RetCode01 == SQL_SUCCESS || RetCode01 == SQL_SUCCESS_WITH_INFO )
      {      
                  SQLAllocHandle(SQL_HANDLE_STMT, dbSession.hDbc, &hStmt);

                  lstrcpy( (char *) SqlStmt, "BEGIN\n");
                  lstrcat( (char *) SqlStmt, "poc_pmlevent(");      
......
                  lstrcat( (char *) SqlStmt, "END;\n");            
                  SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));

                  SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,&Sqlstate,&SQLerr,&MessageText,100,&SQLmsglen);
......
                  RetCode01 = SQLFetch( hStmt01 );            //Get another record
      }
}      
0
 
turbot_yuAuthor Commented:
      SQLCHAR        Sqlstate;
       SQLCHAR        MessageText;
      SQLINTEGER SQLerr;
      SQLSMALLINT SQLmsglen;
0
 
mahesh1402Commented:
turbot_yu ,
 so how you solved your problem ?

-MAHESH
0
 
turbot_yuAuthor Commented:
just do what you said then found many error most are 'oracle max cusor reached'
0
 
mahesh1402Commented:
>>just do what you said then found many error

then you solved your problem or not ?

-MAHESH
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 18
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now