Solved

error handling for oracle sql

Posted on 2006-06-25
30
309 Views
Last Modified: 2013-11-20
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
Comment
Question by:turbot_yu
  • 18
  • 10
  • 2
30 Comments
 
LVL 11

Assisted Solution

by:Jase-Coder
Jase-Coder earned 500 total points
ID: 16981720
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
 

Author Comment

by:turbot_yu
ID: 16981743
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
 

Author Comment

by:turbot_yu
ID: 16981747
Is it the API?
0
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 

Author Comment

by:turbot_yu
ID: 16981767
After I can the catch(Oracle exception), what should I do?
0
 

Author Comment

by:turbot_yu
ID: 16981778
what should be put inside catch( ). thanks
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16981821
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16981840
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
 

Author Comment

by:turbot_yu
ID: 16981888

thx, i am trying, when set up the connection is ok, after uploaded some data, the connection seem will have problem.
0
 
LVL 11

Accepted Solution

by:
Jase-Coder earned 500 total points
ID: 16981902
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
 

Author Comment

by:turbot_yu
ID: 16981967
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982008
have you checked return code when it fails ?

-MAHESH
0
 

Author Comment

by:turbot_yu
ID: 16982032
It fails randomly, I am trying to catch it.
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982043
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
 

Author Comment

by:turbot_yu
ID: 16982674
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
 

Author Comment

by:turbot_yu
ID: 16982688
The MessageText is            204 'Ì'
The SqlState is             4 ''

0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982694
>>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
 

Author Comment

by:turbot_yu
ID: 16982703
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982732
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
 

Author Comment

by:turbot_yu
ID: 16982790
there is one compile error when follow the sample code:
'SQLGetDiagRec' : cannot convert parameter 4 from 'char [10]' to 'unsigned char *'
0
 

Author Comment

by:turbot_yu
ID: 16982858
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982869
how you are tracing string and error code when you get values 204 'Ì' and  4 ''....

-MAHESH
0
 

Author Comment

by:turbot_yu
ID: 16982878
that time i use
 SQLCHAR        Sqlstate;            
...
rc9=SQLGetDiagRec(
            SQL_HANDLE_DBC,
            hStmt,
            1,
            &Sqlstate,
            &NativeErrorPtr,
            &MessageText,
            600,
            &TextLengthPtr);
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16982891
then what contents you received in NativeErrorPtr and MessageText, ? same as above ??
0
 

Author Comment

by:turbot_yu
ID: 16982897
Now compile error, cannot run any more.
0
 

Author Comment

by:turbot_yu
ID: 16982899
                 SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));
                  SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,Sqlstate,&SQLerr,pMsg,100,&SQLmsglen);
0
 

Author Comment

by:turbot_yu
ID: 16982952
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
 

Author Comment

by:turbot_yu
ID: 16982965
      SQLCHAR        Sqlstate;
       SQLCHAR        MessageText;
      SQLINTEGER SQLerr;
      SQLSMALLINT SQLmsglen;
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16989592
turbot_yu ,
 so how you solved your problem ?

-MAHESH
0
 

Author Comment

by:turbot_yu
ID: 16992175
just do what you said then found many error most are 'oracle max cusor reached'
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 16993617
>>just do what you said then found many error

then you solved your problem or not ?

-MAHESH
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
WinWaitActive parameters 12 31
fizzArray  challenge 1 83
Is there a simple front-end menu system. 9 89
"Black Box" Testing of Control System Software 2 51
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question