Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

error handling for oracle sql

Posted on 2006-06-25

System Programming

2 solutions

Medium Priority

315 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);

Comment

Question by:turbot_yu

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

30 Comments

LVL 11

Assisted Solution

by:Jase-Coder

Jase-Coder earned 2000 total points

ID: 169817202006-06-25

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.

Author Comment

by:turbot_yu

ID: 169817432006-06-25

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 );

Author Comment

by:turbot_yu

ID: 169817472006-06-26

Is it the API?

Author Comment

by:turbot_yu

ID: 169817672006-06-26

After I can the catch(Oracle exception), what should I do?

Author Comment

by:turbot_yu

ID: 169817782006-06-26

what should be put inside catch( ). thanks

LVL 22

Expert Comment

by:mahesh1402

ID: 169818212006-06-26

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

LVL 22

Expert Comment

by:mahesh1402

ID: 169818402006-06-26

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

Author Comment

by:turbot_yu

ID: 169818882006-06-26

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

LVL 11

Accepted Solution

by:Jase-Coder

Jase-Coder earned 2000 total points

ID: 169819022006-06-26

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

Author Comment

by:turbot_yu

ID: 169819672006-06-26

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.

LVL 22

Expert Comment

by:mahesh1402

ID: 169820082006-06-26

have you checked return code when it fails ?

-MAHESH

Author Comment

by:turbot_yu

ID: 169820322006-06-26

It fails randomly, I am trying to catch it.

LVL 22

Expert Comment

by:mahesh1402

ID: 169820432006-06-26

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

Author Comment

by:turbot_yu

ID: 169826742006-06-26

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);

Author Comment

by:turbot_yu

ID: 169826882006-06-26

The MessageText is 204 'Ì'
The SqlState is 4 ''

LVL 22

Expert Comment

by:mahesh1402

ID: 169826942006-06-26

>>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

Author Comment

by:turbot_yu

ID: 169827032006-06-26

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.

LVL 22

Expert Comment

by:mahesh1402

ID: 169827322006-06-26

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

Author Comment

by:turbot_yu

ID: 169827902006-06-26

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

Author Comment

by:turbot_yu

ID: 169828582006-06-26

I follow the define as 'char pStatus[10], pMsg[101];'

the line is SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,pStatus,&SQLerr,pMsg,100,&SQLmsglen);

LVL 22

Expert Comment

by:mahesh1402

ID: 169828692006-06-26

how you are tracing string and error code when you get values 204 'Ì' and 4 ''....

-MAHESH

Author Comment

by:turbot_yu

ID: 169828782006-06-26

that time i use
SQLCHAR Sqlstate;
...
rc9=SQLGetDiagRec(
            SQL_HANDLE_DBC,
            hStmt,
            1,
            &Sqlstate,
            &NativeErrorPtr,
            &MessageText,
            600,
            &TextLengthPtr);

LVL 22

Expert Comment

by:mahesh1402

ID: 169828912006-06-26

then what contents you received in NativeErrorPtr and MessageText, ? same as above ??

Author Comment

by:turbot_yu

ID: 169828972006-06-26

Now compile error, cannot run any more.

Author Comment

by:turbot_yu

ID: 169828992006-06-26

SQLExecDirect(hStmt, SqlStmt, lstrlen((char *)SqlStmt));
SQLGetDiagRec(SQL_HANDLE_DBC,hStmt,1,Sqlstate,&SQLerr,pMsg,100,&SQLmsglen);

Author Comment

by:turbot_yu

ID: 169829522006-06-26

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
      }
}

Author Comment

by:turbot_yu

ID: 169829652006-06-26

      SQLCHAR Sqlstate;
       SQLCHAR MessageText;
      SQLINTEGER SQLerr;
      SQLSMALLINT SQLmsglen;

LVL 22

Expert Comment

by:mahesh1402

ID: 169895922006-06-26

turbot_yu ,
so how you solved your problem ?

-MAHESH

Author Comment

by:turbot_yu

ID: 169921752006-06-27

just do what you said then found many error most are 'oracle max cusor reached'

LVL 22

Expert Comment

by:mahesh1402

ID: 169936172006-06-27

>>just do what you said then found many error

then you solved your problem or not ?

-MAHESH

Featured Post

Question has a verified solution.

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

Sudoku, a complete MFC application. Part 1

Article by: AndyAinscow

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes. I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet. Introductio…

System Programming

DRY Exception Handling with PostSharp

Article by: Alexandre

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.

Installing EGit on Eclipse

Video by: Jeffrey

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.

Some Basic Differences Between MySQL and MongoDB

Video by: Percona

In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…