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

Hello,

I have made a simple VC++ application to update the records in a table.

The Server database server is in Linux host and the Sybase 15.0.3 ODBC drivers are used to connect to database. The update of the records is failing at the following location in dbcore.cpp

CRecordset::ExecuteSetPosUpdate()
{
.......................
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);
      }
}

The update works fine with Sybase server in Unix host and with Sybase 15.0.3 ODBC drivers.

Has any one faced this kind of issue?Are there any differences relative to Server database server in Linux or Unix host?

Thanks
Roop


Sample Application code:
----------------------------------------------
	m_database.OpenEx("DSN, USER,Password")
	TableClass m_Test(&m_database);
	m_Test.Open(CRecordset::snapshot,_T("select * from <Table> where m_TestID = 1234"));
	m_Test.MoveFirst();
	m_Test.Edit();
	m_Test.m_TestID = 1234;
	m_Test.m_Desc = "hello";
	m_Test.m_EntNo = 0;
 
	m_Test.Update();
	m_Test.Close();
	m_database.Close();

Open in new window

rbhargawFounderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DanRollinsConnect With a Mentor Commented:
Are you still struggling with this?
I wonder about those NULL parameter values.  If the SP expects strings, try passing an empty string.  If it expects a number try passing NULL (the word).
The fact that DBArtisan and ASE run it without error could just mean that they are handling the NULLs internally.
What does the SP look like?  What parameterrs does it expect?
0
 
itsmeandnobodyelseCommented:
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,&amp;sdwNativeError,szErrorMsg,
              sizeof(szErrorMsg),&amp;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,&amp;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.
0
 
grant300Commented:
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
itsmeandnobodyelseCommented:
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?)
0
 
rbhargawFounderAuthor Commented:
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.
0
 
itsmeandnobodyelseCommented:
>>>> 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.



0
 
grant300Commented:
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
0
 
alpmoonCommented:
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?
0
 
rbhargawFounderAuthor Commented:
>>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



0
 
grant300Commented:
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
0
 
rbhargawFounderAuthor Commented:
>>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.


0
 
grant300Commented:
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
0
 
itsmeandnobodyelseCommented:
>>>> 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);

0
 
rbhargawFounderAuthor Commented:
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
0
 
rbhargawFounderAuthor Commented:
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.
0
 
rbhargawFounderAuthor Commented:
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 

Open in new window

0
 
rbhargawFounderAuthor Commented:
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();
--------------------------------------------------------
0
 
grant300Commented:
Well, now we are debugging the stored procedure code.

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

Regards,
Bill
0
 
rbhargawFounderAuthor Commented:
I have attached the Stored procedure.
StoredProcedure.txt
0
 
grant300Connect With a Mentor Commented:
Comments on the code:
  • Some of this code is annotated in 1997.  This stuff has been around awhile
  • "With Recompile" is probably neither necessary or desireable
  • The line commented with "check input parm !!! see MikeM has a problem.  You cannot compare a NULL to a string literal or a numeric value.  You must explicitly test for a NULL.
  • If you need to check a string for both NULL and zero-length, you can use the following: ISNULL(@param,'') != ''   Those are two single quotes.
  • SET ansinull OFF is probably neither necessary or desireable.  I am guessing it is a holdover from a much older version of Sybase ASE
  • In the line comment by "Select By ClaimNum", the test for a zero length string is incorrect.  Double Quotes are reserved for identifiers only and should never be used for a string literal.  Again, use the INSULL(@claimNum,'') != '' to test for both conditions
Make some changes and try it again.

Regards,
Bill
0
 
rbhargawFounderAuthor Commented:
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



0
 
grant300Commented:
Glad I could help.

Regards,
Bill
0
 
DanRollinsCommented:
Ah Ha!  I suspected that NULL handling was at the root of the problem.  I'm glad I could help.
-- Dan
0
 
rbhargawFounderAuthor Commented:
Yeah Dan..You were right on money :)
0
 
rbhargawFounderAuthor Commented:
Thanks Dan and Bill for the help!
0
 
ModernMattCommented:
Mods: CS request to remove code snippets at http://www.experts-exchange.com/Q_24413545.html. This request was closed with no action taken because the Author failed to respond.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.