Difference between Sybase12.5 and Sybase15 ODBC version

If anyone is using sybase 15.0.2  and tell me what version of ODBC are  used there , I can ask our IT support team to take the new version.

Saying version "12.5" is a bit ambiguous.  There were some substantial differences between 12.5.0.3 and 12.5.4 say.

The real issue you are running up against is that most (or all, I don't remember) of the 12.5.x version of ASE used a third party ODBC driver that Sybase had licensed.  Sybase decided that was not a good strategy so ASE 15 uses Sybase-developed ODBC drivers.  In fact, someplace along the line you should have been notified of this change and the fact that they will (have not) be able to provide patches and bug fixes any longer.

The ODBC version number thing is unfortunate because Sybase version numbers are lower than the third party drivers numbers that they supersede so, no, you can't "increment" the v15 drivers.

More importantly, are you having a problem with your application when you try and use the new ODBC drivers?  ODBC is a pretty neutral interface so unless you are doing something pretty hinkey, your app should run just fine.  Worst case is you'll have to recompile/link it.

One of the issues you may be having is that they changed the name of some of the DLLs for the CT-LIB.  That can cause havoc if you are trying to use the old drivers and/or your paths are not setup correctly.

Regards,
Bill

The application which is used is quite and I am quite new in this company :)
The problem is if you opened a CRecordset (and not yet closed it),  it is using a "cursor" to step from record to record.  Rather than reject the request,  the Sybase 12.5 4.2 ODBC driver was doing favor of opening a new connection and also closing them while exiting the screens:)

But Sybase 15 ODBC drivers does not do that. I open the application , it opens 3 locks on one table and when I update, it gives "Multiple Rows updated", where is an error.

I am sure of the bad connection model in the Application, but wanted to "avoid" doing changes. so wanted to confirm whether Sybase 4.2 ODBC drivers does provide this feature for the cursors?

Application is developed in VC++(Client-server)

There are a bunch of substantial new cursor features in ASE 15.  Specifically, v15 supports scrollable cursors.  It also supports both sensitive and insensitive cursor behavior.

I would pick up a copy of The Official New Features Guide to Sybase ASE 15 by Taylor, Adurty, Bradley, and Taylor.  There are 20 plus pages devoted to the topic and you may well find that there are session level setting you can add that would give you back the old functionality your application is dependent upon.

ASE 12.5 only supported Non-Scrollable/Insensitive cursors.  This is also the default for ASE 15.  The behavior you are seeing is indicative of what happens in v15 with a Scrollable/Insensitive cursor.  Scrollable cursors do not automatically close when you get to the end.  In addition, scrollable cursor are not updateable which is forcing the language environment to setup yet another connection on which updates will be performed.

My guess is that the ODBC drivers are taking advantage of the new scrollable cursor feature under the covers.  I would look both for attributes of the CRecordset object (in whatever language you are using) as well as in the advanced features setup of the ODBC data source.  Look for some way to tell it not to use scrollable cursors or to use "client-side" cursors instead.

You are correct about the application being poorly behaved.  Only in a Sybase world can you create connections willy nilly without taking a performance hit.  Were this Oracle, each connection takes the better part of 4MB on the server, creates a new heavyweight process, and usually takes at least a second or two to startup.  We have been spoiled.


Let us know what works for you.

Bill

Bill,

I have attached the snap shot of "All Locks" as shown in DBArtisan.

In the application if I open the recordset say m_recContract.Open(), I will see 3 locks as shown in the snap shot(  connecting to Sybase 15.0.0). Can you gather anything why the driver is opening 3 locks on a table?.

I tried with with the simple application instead of our main VC++ app and it showed the same behaviour.

So the issue here is when I do an Update, the application says "Multiple rows Updated" even when the rows present in the table is only one.
SybaseConnection.bmp

Please ignore this message

"So the issue here is when I do an Update, the application says "Multiple rows Updated" even when the rows present in the table is only one."

mmm.. I will ask the error message any way :)

My application is giving "Multiple rows were updated" error when I am calling an Update function of the CRecordset class. m_recContract is an CRecordset class.

e.g m_recContract.Update()

This is following query which is the cause of problem:
UPDATE contract SET [statusId]=?,[sitCatID]=?,[sitCodeID]=?  WHERE CURRENT OF SQL_CUR_1 (This works in sybase 12.5 ODBC 4.2 drivers)

But in sybase 15.0, it gives the error "Multiple rows were updated"
Do I need to add atleast one item to make the update unique or is there any other way?

Thanks
Roop

I don't see a problem with the locks.  The ODBC driver that is doing the locking; it is the result of theopening a cursor.  It is also not creating multiple connections, at least not in the snapshot you provided.

The first lock is a Shared Intent on the table which is a byproduct of opening the cursor.  The second and third locks, also shared, are on individual pages.  You might expect to see a shared lock on the current page on which the cursor sits and, I am guessing the cursor window simply spans two pages.  This is more likely to happen if you have small pages and large rows rather than large pages and small rows.

BTW, I have found that an 8KB pages size is a good all around starting point.  2KB pages in this day and age are just too small.

Regards,
Bill

There is only one possibility I can think of assuming that your table has a valid primary key.

It may be that the error message is bogus and that the real error is that you have a scrollable cursor which is simply not updateable.  You can try and fix this a couple different ways.  The first is to, as I suggested before, look for a way to configure/open the recordset object so it does not use scrollable server cursors or look for a way to force the issue in the ODBC driver advanced setup.  You may be able to force this is you can define the cursor as "FOR UPDATE" in the first place.  The second way is to change the "WHERE CURRENT OF CURSOR...." to use the primary key or some other unique key instead.

If you can force/trick/coerce the recordset object into using a non-scrollable cursor, I know the problem with the cursors staying open will be fixed and I am pretty sure the "multiple rows..." will go away as well.

Regards,
Bill

Bill,

There was a Update trigger on the table . I dropped the trigger on that table and the error "Multiple rows were updated" was gone. But I don't know the reason why the error is not coming NOW??Can you gather anything from this information?

Thanks
Roop

P.S. It happens on Sybase 15.0.0 only

This is the pseudo code of the trigger which I dropped 
------------------------------------------------------------------------------
 
CREATE TRIGGER <Triggername>
   ON <Table>
   FOR UPDATE
AS
  BEGIN
 
DECLARE 
        variables
 
INSERT INTO <Table2>
SELECT ,,,,,,,,               
FROM deleted   
 
 
END

Select allOpen in new window

It may be that the ASE 15 client is reporting more information than the 12.5.x client was.  The message you are getting is definitely not a SYBASE error.  It is undoubtedly a recordset object error since it expects to update one record and is getting information from the server that says something different.  You removed the trigger which, I can only assume since you did not give us any information about it, was doing additional work resulting in rowcount message back through the client.

Try adding a statement just inside the BEGIN of your trigger and put it back on the table.  The statement should be:
 SET NOCOUNT ON

This should suppress the reporting of the number of rows affected so that the recordset object does not know what is going on under the covers.


 Regards,
Bill

You are Awesome Bill!!!