Update of table not working via VC++ application, Sybase linux host, MFC, VC++ 2005

You may call the SQLError function in a loop to retrieve more information.

Sample code from MSDN
---------------------------------
   // The Error function.
   void ErrorMsg( HSTMT hstmt, RETCODE retcode )
   {
      RETCODE rc;
      UCHAR szSqlState[6];
      SDWORD sdwNativeError;
      UCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
      char   errMsg[4096];
      SWORD cbErrorMsg;

      if (retcode != SQL_SUCCESS)
      {
         rc = SQLError(SQL_NULL_HENV,SQL_NULL_HDBC,hstmt,
              szSqlState,&sdwNativeError,szErrorMsg,
              sizeof(szErrorMsg),&cbErrorMsg);
         if (rc == SQL_INVALID_HANDLE) return;
         while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
         {
            sprintf(errMsg,"Error Message:%s\n\n",(LPCSTR)szErrorMsg);
            MessageBox(NULL,errMsg,"Error",MB_OK);
            rc = SQLError(NULL,NULL,hstmt,szSqlState,&sdwNativeError,
                 szErrorMsg,sizeof(szErrorMsg),NULL);
         }
      }
   }



---------------------------------

Did you check the output window of Visual Studio IDE. In debug mode most ODBC drivers output error information there.

First, the chances that ASE 15.0.3 behaves differently on Linux than on Unix for such a fundamentally simple operation are vanishingly small.

Try issuing the update statement with ISQL or some database IDE.  There is a good chance you have an index or integrity constraint violation that is causing the update to fail.  You should get an meaningful error message.

There could be a difference in the way the database objects where built between the two platforms but, more likely, there is a difference in the data in the two different databases.

Finally, your code does not appear to be handling errors at all.  You pretty much have to have error handling after each database operation or you will never (as you are quickly finding out) figure out why your program fails.

Regards,
Bill

What actually is the UNIX host?

Are you sure that both the DBMS' were identically configured, i. e. have the same DB Users and access rights on all tables (and of course the same data?)

1. The update statement in Db Artisan is working fine
2. Output window of Visual Studio IDE shows "Error: failure updating record"

which comes from dbcore.cpp and the nRetCode is coming as -1 when the Check function is called.

RETCODE nRetCode;
AFX_ODBC_CALL(::SQLSetPos(m_hstmt, 1, wPosOption, SQL_LOCK_NO_CHANGE));
      if (!Check(nRetCode))
      {
            TRACE(traceDatabase, 0, _T("Error: failure updating record.\n"));
            AfxThrowDBException(nRetCode, m_pDatabase, m_hstmt);
      }
3. The Errormsg is blank.

4.The DBMS' are configured by DBA but he is on vacation this week, I wanted to figure out
whether the issue is "really" from database side.I can only ask the DBA next week to check the table consistency with previous database but looks quite ok regarding data and access.

5. As the DBA are planning to migrate Sybase server from unix to linux environment,
they have asked me to test the VC++ application. The application cribbed on new environment so
I tried to see if this is really something Application can handle or not.So I have made the sample application
as the updates of the table were not working in the actual application too.

8. I tried the following code

      try{
            if (!m_Test.Update())
            {
                  AfxMessageBox ("Record Cannot be updated");
                  
            }
      }
      catch (CDBException* e)      
            {
                  AfxMessageBox(e->m_nRetCode);
                  AfxMessageBox(e->m_strError);
                  e->ReportError();
                  e->Delete();
            }

Both the message boxes are blank.

9. There is an trigger on the update of table. Don't know if that could be the issue.
I do have "SET NOCOUNT ON" on the trigger so that it does not send additional messages.

10. If I change only the DSN settings and connect to server hosted on Unix, it works
but if I connect to server hosted on linux , it does not work. So this could be database issue but I wanted to be sure from my side.

Let me know the directions to proceed.

>>>> Both the message boxes are blank.

You should use the ErrorMsg function I posted above and use the m_Test.m_hStmt as statement handle .

>>>> I do have "SET NOCOUNT ON" on the trigger

Looks like it was a counter trigger which was suppressed when updating.

Precisely what is the UNIX Sybase server, hardware, O/S and version, Sybase version.

Please do a "select @@version" against the UNIX db server and post the results.

If you are simply migrating from ASE 15.0.3 on UNIX to 15.0.3 on LINUX, it should be completely transparent to the application code.  If you are changing ASE versions at the same time as I suspect you are, you could have a whole host of issues starting with the installation and working up from there.

Regards,
Bill

Given that update works through DBArtisan and also with the ASE on UNIX through Sybase 15.0.3 ODBC drivers. I suspect that your Open Client libraries are not compatible with the version on Linux. Are you using DB-Lib or any other library?

>>Bill

Unix server:
Adaptive Server Enterprise/15.0.1/EBF 14201 ESD#2/P/Sun_svr4/OS 5.8/ase1501/2400/32-bit/FBO/Sat Jan 13 04:11:18 2007

New Linux server:
Adaptive Server Enterprise/15.0.3/EBF 16380/P/Linux Intel/Linux 2.6.9-42.ELsmp i686/ase1503/2669/32-bit/FBO/Wed Nov 12 04:10:10 2008

>>Alpmoon

ODBC drivers used is ASE 15.0.3 Client , when I install them ,it do come with db-lib..I hope if this answers your question

Boy, now would be a great time to move to a 64 bit O/S version and 64 bit version of ASE....

As far as the ODBC drivers go, try installing the drivers from an earlier release and see if you don't get the same behavior from both servers.

Have you checked with Sybase tech support to see if there is anything flaky with the 15.0.3 ODBC drivers?

Regards,
Bill

>>Bill

Sybase 15.0.3, Sybase 12.5 ODBC drivers works well with Unix Host
Sybase 15.0.3 does not work with Linux host
Sybase 12.5 works with Linux host.

I checked the tech support but didn't find anything related with update of tables.

>>itsmeandnobody

I tried like the following code but nothing is coming up

RETCODE rc value is coming as 100.
--------------------------------------------------------------
            if (!m_Test.Update())
            {
                  ErrorMsg(m_Test.m_hstmt,-1);
                  AfxMessageBox ("Record Cannot be updated");
                  
            }
---------------------------------------------------------------


I think I will work with the DBA next week and come up with better details to get it resolved.

The problem is not with UPDATE.  Search for something with the 15.0.3 ODBC drivers.  ODBC is where the problem is.

In the mean time, you can use the 12.5 ODBC drivers indefinitely.

Regards,
Bill

>>>> rc value is coming as 100
As far as I remember 100 isn't an error for a general failing. It is SQL_NO_DATA which is an indication that there is more data to fetch/retrieve/supply than fits into one of the buffers you provided. We had some case where one of your data columns had changed from single char to wide char when exchanging the DBMS and if I remember right we had a similar error cause the buffers bound to that field did not fit.


>>>> ErrorMsg(m_Test.m_hstmt,-1);

You probably have to call

  ErrorMsg(m_Test.m_hstmt, SQL_NO_DATA);

The update is working fine now. The DBA restarted the database server and the update application worked fine.
Verifed with DBA about data columns and they told it is fine.

Still, my Client Server application is throwing error.

I have added the snapshots as to where it is failing. The application code is calling a stored procedure e.g. "Call <ProcName> (?,?,?,?,?). In this case first 3 parameters are NULL and last 2 parameters have the values.

Interesting thing is that when application calls stored procedure with first and 4th parameters with values and others as NULL, it works fine without any error.

If I execute the stored proc in Sybase Central, both the cases works fine.

WokingVersion.bmp :- Sybase 15.0.3 ODBC drivers with Database server in UNix
Non WorkingVersion.bmp :- Sybase 15.0.3 ODBC drivers with Database server in Linux.

Can anyone tell how do I approach?
NonWorking.bmp
NotWorking-.bmp
WorkingVersion.bmp

Dan,

We will be building 15.0.3 Sybase ASE Server on Solaris host( instead of Linux host) and
then will check whether or not the error is getting reproduced.

It will take a week or two for me to update any issues/questions.

I received the same errors with 15.0.3 Sybase ASE Server on Solaris host..

I am trying like this.. The VC++ does not come up with results
-----------------------------------------------------------------------------------------
            m_recSPfindHoldings.m_claimNum = "";
            m_recSPfindHoldings.m_contractNum = "";
            m_recSPfindHoldings.m_txnID = NULL;
            m_recSPfindHoldings.m_consEntNo = 1538747;
            m_recSPfindHoldings.m_purchID = 2060024;
            m_recSPfindHoldings.ReOpen();
---------------------------------------------------------------------------------------

in DBArtisan, it comes up with result
--------------------------------
exec  FindHoldings  "","",NULL,1538747,2060024.

Can anyone suggest me how to move forward? Let me know in case more details are needed.


Thanks
Roop

Create Proc FindHoldings ( 
     @claimNum varchar(22) =NULL ,
     @contractNum varchar(20) =NULL ,
     @txnID int =NULL ,
     @consEntNo int =NULL ,
     @purchID int =NULL 
                   ) 
 With Recompile 

Select allOpen in new window

One more thing:

It works with following
-----------------------------------------------------------------------------------------
            m_recSPfindHoldings.m_claimNum = "2019650010";
            m_recSPfindHoldings.m_contractNum = "";
            m_recSPfindHoldings.m_txnID = NULL;
            m_recSPfindHoldings.m_consEntNo = 1538747;
            m_recSPfindHoldings.m_purchID = 0;
            m_recSPfindHoldings.ReOpen();
--------------------------------------------------------

Well, now we are debugging the stored procedure code.

Post the code so we can see what the real issue is.

Regards,
Bill

I have attached the Stored procedure.
StoredProcedure.txt

Bill,

I could not believe my eyes :-)

After adding the below line in the stored procedure, the Application code started working fine!!!

ELSE IF (ISNULL(@purchID,0) != 0)

--ELSE IF (((@purchID != NULL) AND (@purchID !=0))) -- Earlier line of code

I will use ISNULL at others place too as recommended by you.

I have one more issue in my application with the new server but I will post a new question once I do my research.

Thanks
Roop

Glad I could help.

Regards,
Bill

Ah Ha!  I suspected that NULL handling was at the root of the problem.  I'm glad I could help.
-- Dan

Yeah Dan..You were right on money :)

Thanks Dan and Bill for the help!

Mods: CS request to remove code snippets at https://www.experts-exchange.com/questions/24413545/16-May-09-17-Automated-Request-for-Attention-Q-24306509.html. This request was closed with no action taken because the Author failed to respond.