Solved

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

Posted on 2009-04-08
26
963 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:rbhargaw
  • 11
  • 7
  • 4
  • +3
26 Comments
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24099433
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
 
LVL 19

Expert Comment

by:grant300
ID: 24099500
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
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24099694
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
 

Author Comment

by:rbhargaw
ID: 24100950
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
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24101105
>>>> 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
 
LVL 19

Expert Comment

by:grant300
ID: 24102938
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
 
LVL 13

Expert Comment

by:alpmoon
ID: 24103182
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
 

Author Comment

by:rbhargaw
ID: 24103567
>>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
 
LVL 19

Expert Comment

by:grant300
ID: 24104150
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
 

Author Comment

by:rbhargaw
ID: 24107627
>>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
 
LVL 19

Expert Comment

by:grant300
ID: 24107661
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
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 24114063
>>>> 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
 

Author Comment

by:rbhargaw
ID: 24149641
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Accepted Solution

by:
DanRollins earned 250 total points
ID: 24289609
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
 

Author Comment

by:rbhargaw
ID: 24298517
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
 

Author Comment

by:rbhargaw
ID: 24327183
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
 

Author Comment

by:rbhargaw
ID: 24327718
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
 
LVL 19

Expert Comment

by:grant300
ID: 24328301
Well, now we are debugging the stored procedure code.

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

Regards,
Bill
0
 

Author Comment

by:rbhargaw
ID: 24328429
I have attached the Stored procedure.
StoredProcedure.txt
0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 250 total points
ID: 24329599
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
 

Author Comment

by:rbhargaw
ID: 24330110
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
 
LVL 19

Expert Comment

by:grant300
ID: 24330573
Glad I could help.

Regards,
Bill
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 24330727
Ah Ha!  I suspected that NULL handling was at the root of the problem.  I'm glad I could help.
-- Dan
0
 

Author Comment

by:rbhargaw
ID: 24330764
Yeah Dan..You were right on money :)
0
 

Author Closing Comment

by:rbhargaw
ID: 31568131
Thanks Dan and Bill for the help!
0
 

Expert Comment

by:ModernMatt
ID: 24405190
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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.
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.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now