logo
vbRad Home
Source Code
Book Reviews
Forum
Links
About Us
Contribute

Compare Databases with SQL Effects Clarity
 
 Sybase, VB and ADO

Posted on
3/4/2001
Author:
Robert Gelb
Email:
Not Shown
Applies To OS:
All
Product:
5, 6



Originally written on 03-05-01
Updated on 05-17-01

Quite a bit of confusion reigns out there today on what are the options for connecting to Sybase from Visual Basic using ADO. Most of the blame goes to Sybase which has a truly confusing marketing message and a completely useless website, full of marketing fluff. It seems that they rename the products and their goals at will. One minute a product is called Sybase Studio Anywhere, the next minute Sybase Lite or Adaptive Server Anywhere and then back. Adaptive Server Enterprise (the high end product) is on occasion called Sybase SQL Server. You get the picture. Same happens with the marketing priorities. Depending on when you talk to them, you'll hear a different song on where the company is headed. Sometimes they are all about replication with mobile devices, other times they are the database provider, then XML champions...

So there is no surprise that one of the most asked questions about Sybase on the newsgroups is the simplest one of all: how to connect with ADO. Well, there are several options. I've broken down information based on the version.

Sybase Adaptive Server Enterprise 12.x

  • Version 12.x ships with its own OLE DB Provider. It will show up in the list of drivers as Sybase ASE Provider. The performance is not too bad, definitely better than connecting to the ODBC driver through the OLEDB Provider for ODBC Drivers. There were a couple of lingering bugs, which should be stumped out by March 2001. The driver requires that OpenClient be installed on user's machine.
  • Second option for drivers is to request an unreleased, but solid, version of what is known as "TDS" (Tabular Data Stream) driver. It sheds the dependence on the OpenClient, thus offering better performance. Besides that's one less installation headache you have to deal with. As of writing (March 2001), you have to call their tech support and ask for it. They'll make you sign an agreement if you want to use it in a production environment. This driver will ship with Sybase 12.5 when it comes out in August 2001
  • The last option is to purchase drivers from Merant (formerly Intersolv). I would never purchase drivers because they should be provided for free with the database. However, with this one there is a catch. Sybase doesn't write its own drivers. They are outsourced to Merant. So Merant, sells, what I would call, either a value-added driver or a driver that is one version ahead. It is up to you to determine whether what they sell is worth your money.

Sybase Adaptive Server Enterprise 11.x

  • You are using Sybase 11.x on the backend, but there is no OLEDB provider for it. What do you do? One option is to use OLEDB Provider for ODBC Drivers. I know it is slower than other options, but it is usable. It slows down if you have a lot of queries returning little amounts of data. If you have few queries returning large amounts of data, you probably won't notice the difference. I've been using this method for a year now and the users are not complaining. There are actually several ODBC drivers for the 11.x series. There is one for 11.1.x, 11.5 and 11.9.x versions. The most widely used driver is from Sybase version 11.1.x. One drawback to this driver is that it is not thread-safe, thus it will refuse to connect in an MTS environment. In other words, if you are making a connection from a COM object that requires an MTS transaction, the ODBC driver will refuse to connect. If you must have an MTS transaction, upgrade to at least the ODBC driver for Sybase 11.5, which is thread-safe and thus able to participate in transactions.
  • Another alternative is to install OpenClient 12 on your users' machines (if in fact you have OpenClient 12, since it comes with Sybase ASE 12.x). This way you can use the OLEDB Provider for Sybase 12 with your Sybase 11.x backend. The provider is backwards compatible, just don't use any Sybase 12 specific commands (like dynamic execute) and you'll be fine.
  • Merant sells an OLEDB provider for ASE 11.x as well. Again, weigh your costs against the perceived performance improvement.
  • Last, but not least in my mind and definitely no performance slouch is to use the ODBC driver but bypass ADO's default mechanism for connecting to ODBC drivers. Basically if you connect with standard syntax ODBC syntax, like: "Driver={Sybase System 11};PWD=vbguru;UID=gelbro;DB=dictionary;servername=SYBPRD_DEV" and so on, you'll be speeding along.

Sybase Anywhere Studio 7.x

  • Just like ASE 12.x brethen, this product also comes with its own OLEDB Provider, which makes it a no-brainer to select your connection strategy. The provider has no requirement for OpenClient, but can work through it if necessary

Sybase Anywhere Studio 6.x

  • The only option here is to use the provided ODBC driver either through explicit ODBC connection syntax or through OLEDB Provider for ODBC driver. For this case, I recommend dumping ADO and using DAO's excellent ODBC Direct feature. The provided ODBC driver can also work either with or without the OpenClient.

Where to get all these drivers

If you ever looked to Sybase's website to download drivers or providers, you were probably frustrated. They are difficult to find. Below are the four ways, I gotten drivers:
  • Sybase expects you to get drivers from the product CD-ROM and that is the first place you should look. If you go to the Downloads section of their website, you will NOT find any driver downloads.
  • Another way to get a driver is through an EBF (emergency bug fix) - basically a variation on Microsoft's service pack, which is really a variation of a bug fix. Find the latest EBF for your product, read the manifest (it's sybase-speak for readme.txt), make sure it includes the drivers, then download and install.
  • Call Sybase tech support and ask for drivers. They are pretty friendly and will give you some FTP site to download from.
  • Download the NT evaluation of Sybase System 12. Keep in mind that the download is about 300 MB
  • Go to the beta download section of the Linux 11.9.2 version and download the Sybase PC Client (35MB). At the time of writing this download is directly available here.
  • If all else fails, go to Merant's web site and download their evaluation drivers.
Thanks go out to Robert Glckner from Frth, Germany for suggesting extra ways to get drivers.

Add Your Comment  

Name: Email Address: all fields optional
Notify me via email when someone responds to this message (valid email required).

Enter the word:
 



Comments
#1. By shakil. Posted on 11/18/2006 4:06:37 AM
how to use vb coneect to sybase database

#2. By Vipin Bhardwaj. Posted on 2/16/2007 4:14:07 AM
Dear Sir,
I am connecting to sybase 11 using ADO connection wid VB6.0. I am able to connect but while fatching values from table its giving me value 1 while value in database is 10000. Means its truncation all 0000 . Either before or after value. Means I am getting 1 only for 10,100,1000...etc. I think now u r able to understand my problem.
I m using "Driver={Sybase System 11}". Plz help me out regarding this problem.

#3. By Anonymous. Posted on 2/17/2007 12:11:37 AM
Yes, there was a bug like that many years ago. The fix now escapes my memory, however, it involved placing something like WorkArounds2=64 (or something like that) into the ODBC entry in the registry. If I were you, I'd stop messing around with such an old driver and get the latest ODBC driver.

#4. By RameshPaladi. Posted on 3/26/2007 1:20:04 PM
how to bind list of sybase Environments to DropDownList and the sybase version is 12.5

#5. By Cleo. Posted on 10/30/2007 10:57:22 AM
"Necessito Fdesenvolver uma aplicação para com umbanco Sybase ja existente, mecessitaria de um exemplo. para conexao e exportar os dados para o Access

#6. By Sushil. Posted on 12/26/2007 12:07:46 PM
Hi,

I want to have Sybase OLE DB PRovider in my system to create connection string in VB.NET application. How to get the Sybase OLE DB provider. IS there any websites so that I can download?

Anand.S.Sushil Kumar

#7. By Anonymous. Posted on 12/26/2007 5:08:54 PM
To #6. Did you not read the section on how to get drivers?

#8. By Anonymous. Posted on 5/9/2009 4:50:15 AM
You can download the Sybase oledb driver from the Sybase site for free from http://www.sybase.com/ase_1500devel.
After registering, download the setup file.

Steps
1. Run setup and choose Sybase Developer version and select "Custom" setup.
2. In next screen, uncheck all other components, and choose ASE OLEDB Driver.

#9. By rent. Posted on 1/13/2010 11:04:13 AM
try going to w3school.com it is much easier to understand...!

#10. By test. Posted on 2/26/2010 6:36:33 PM
test

#11. By vinod john. Posted on 11/29/2011 9:47:30 AM
sir,
i am using visual basic & sybase central 5.0 database. after inserting around 15000 records in the table the programe working very slow. wths the reason??