|
7.1 What is Open Client?
7.2 What is the difference between DB-lib and CT-lib?
7.3 What is this TDS protocol?
7.4 I have upgraded to MS SQL Server 7.0 and can no
longer connect from Sybase's isql.
7.5 The Basics of Connecting to Sybase
7.6 Connecting to ASE using ODBC
7.7 Which version of Open Client works with which ASE?
7.8 How do I tell the version of Open Client I am running?
7.9 What is FreeTDS?
Open Client is the interface (API) between client systems and Sybase servers.
Fundamentally, it comes in two forms:
Runtime
The runtime version is a set of dynamic libraries (dlls on W32
platforms) that allow client applications to connect to Sybase and
Microsoft servers, or, in fact, any server that implements the Tabular Data
Streams (TDS) protocol. You need some form of Open
Client in order to be able to connect to ASE in any way, shape or form.
Even if you are running isql on exactly the same
machine as ASE itself, communication will still be via Open Client.
That is not to say that client to server communication on the same machine
will go via the physical network, that decision is left entirely to the
protocol implementation on the machine in question.
Development
The development version contains all of the libraries from the runtime
version, plus the header files and other files, library files etc,
that enable developers to build client apps that are able to connect to
Sybase servers.
Back to top
Both DB-lib and CT-lib are libraries that implement the TDS protocol from the client side.
DB-lib
DB-lib was Sybase's first version. It was a good first attempt,
but has/had a number of inconsistencies. There are, or possibly were,
a lot of applications written using DB-lib. If you are about to start
a new Open Client development, consider using CT-lib, it is the preferred
choice. (What version of TDS does DB-lib, is it only 4.2?)
Having said that you should use CT-lib for new developments, there is
one case that this may not be true for and that is 2 phase commit. 2
phase commit is supported directly by DB-lib but is not supported
directly by CT-lib.
CT-lib
CT-lib is a completely re-written version of Open Client that was
released in the early '90s. The API is totally different from DB-lib,
and is much more consistent. Applications written using DB-lib cannot
simply be compiled using CT-lib, they need a significant amount of porting
effort. CT-lib is newer, more consistent and, in several people's
opinions, including mine, slightly longer winded. Having said that,
the future of DB-lib is uncertain and is certainly not being developed any
more, as a result all new apps should be written using CT-lib.
Back to top
Tabular Data Streams or TDS is the name given to the protocol that is
used to connect Sybase clients with Sybase servers. A specification
for the protocol can be obtained from Sybase, I had a copy but cannot seem
to find it now.
The is a project that is reverse engineering the protocol and building a
set of libraries independent of either Sybase or Microsoft, but able to
connect to either of their servers.
FreeTDS is a considerable way down the
line, although I do not believe that it is production ready
yet!
As part of the project, they have started to document the protocol, and
a view of TDS 5.0 can be seen
here.
Back to top
The following is courtesy of James Lowden and Brian Bruns of the
FreeTDS project.
With SQL Server 7.0, Microsoft changed the TDS protocol; they themselves call it
"TDS 7.0". They didn't actually change it in any fundamental way.
They added some tokens and stretched the use of others.
Microsoft's marketing sometimes utters stupidities like "ODBC is the native
protocol for SQL Server", but ODBC isn't a protocol. If you dig, you'll
find some references to TDS in their technical docs, a sort of grudging
admission that TDS still undergirds everything.
What Microsoft dropped was TDS 5.0 support, which is the version Sybase
developed after they licensed SQL Server to Microsoft. There is a patch
that will provide that support for SQL Server 7.0, but no such patch exists
for SQL Server 2000. You can still connect to 2000 with TDS 4.2, which
is what is used by pre-System 10 tools and by Microsoft's current
isql command line tool.
Actually, to say MS SQL ever supported 5.0 is a bit misguided. The
extent of support was basically, it would accept a TDS 5.0 login packet
and then tell the client to fall back to 4.2. This was basically because
the 4.2 and 5.0 login packets were largely the same. I frankly would
avoid even attempting to send 5.0 to MS SQL as it will likely re-break at
some point in the future (like when they remove 4.2 support down the
road).
The following table gives you an idea of what is supported.
+---------------------------------------------------------------+
| | MS SQL | Sybase ASE | Sybase ASA |
+---------------------------------------------------------------+
|ver |6.5 |7.0 |2000| <10.x | >11.0 |<5.5.03|5.5*|>6.0|
+-----+----+----+----+-----------+------------+-------+----+----+
| 4.2 | X | X | X | X | X | | | |
| 5.0 | | | | | X | | X | X |
| 7.0 | | X | X | | | | | |
| 8.0 | | | X | | | | | |
+-----+----+----+----+-----------+------------+-------+----+----+
* Sybase ASA 5.5 requires 5.5.03 or better with the OpenServer Gateway
(OSG).
Back to top
The following describes how to connect to Sybase ASE on a UNIX machine
from a windows client with isql etc. The specific example is Sybase
ASE 11.9 on Redhat Linux 6.1, using Windows 95 and NT. (Have both on
partitions and the process was the same... This is not a technical review
or an in-depth discussion (there are people far more qualified than me for
that ;-) ). Rather it is more along the lines of "This is how I
managed it, it should work for you". As always there are no guarantees, so
it if goes wrong, it's your fault [<g>].
The starting point for this discussion has to be, you've downloaded (or
whatever means you used to acquire it) both Sybase ASE for Linux and the PC
Client software (a big zip file) and are ready to install. I'm not going
to discuss the install process as Sybase managed to do a good job of that,
so
I'm leaving well alone. The bit you have to take notice of is when
you run srvbuild. This should happen the first time you log
on as the user sybase after the install. If it doesn't
then you can run it by hand after, it line in the $SYBASE
directory under bin. The reason why I'm mentioning this is that
srvbuild defaults to installing your database using the name
"localhost". Now the problem with localhost is that it is kind
of a special case and would mean that you could not connect to your
database from anywhere other that the server itself. This would defeat the
object of this
discussion, so simply name it something else, bob,
george, albert, mydatabase, whatever, the choice is yours.
Having done this (it takes a while to complete) you should now have a
running database. so try to connect to it on the local machine with
something like isql -SServerName -Usa, (where ServerName is
whatever you called it when you ran srvbuild) when it asks
for a password, just press enter and you should be greeted by the
monumentous welcome
1>
Not a lot for all the work you have done to get to this point, but
you've connected to your database and that's the main thing. This is very
important as not only does this mean that your database is working, but it
also means that the server half of Open Client is working. This is because
even isql on the server connects to the database using Open
Client and you've just proved it works, cool. Next run
dsedit on the server and make a note of the following 3 things:
1: The server name
2: The IP address
3: The port
Your going to need these to get connected from windows.
Now switch to you windows machine, did I remember to tell you to shut
down dsedit on the server?, consider it said ;-). Unpack the
PC Client software zip file and install it using the instructions that came
with it. They worked fine for me and I'm an idiot, so they should work for
you. When you've finished, go to the start menu and start
dsedit (on my machine it's under programs -> sybase). When it
runs, it begins with a dialog asking you which Interface driver to open,
I've done this 3 times and went with the default every time, so it should
be a safe bet. At this point you can now add your Linux based server.
Select the menu item serverobject->add. Then enter the name of the
server you just got from your Linux box, in the field labeled "server". It
is probably a good idea that it is the same name you got from your Linux
based dsedit to ensure that everyone is referring to the same
server with the same name. Prevents confusion. This then opens
a new window with several fields, one of which is the server name you just
entered. The bottom field is the bit where you enter the "nitty gritty",
the server IP address and port. To do this right click on the field and
select "modify attribute" to open the server address dialog. When this new
dialog opens click add to open yet another dialog (is there an award for
the most gratuitous use of the word dialog???). OK, this is the last one,
honest. Leave the drop down list where it is (hopefully showing TCP/IP or
something similar). Instead move straight to the address field and enter
the following: the Linux servers IP address followed by the port number
(the one from the server dsedit), separated by a comma. On my machine it
looks like this.
192.0.0.2,2501
Now you can "OK" your way back out of the dialogs, back up to
where you started from and exit dsedit. Then launch
isql on the windows box and log in. Personally I did this from a
DOS prompt, using exactly the same syntax I did on the Linux box, but
that's just because I like it that way. Now you should be happily
querying you Linux (or other UNIX for that matter) based Sybase ASE
database. What you do with it now, is covered elsewhere in this FAQ from
people able to tell you, unlike me. Now just one more time for good
measure, I'm going to type the word, wait for it.... Dialog.
Back to top
To begin with you need to be certain that you can connect to your Linux
hosted Sybase ASE database from your windows based machine. Do this by
running isql from your Linux box and connect to the database,
if this works, then your all set (See Q7.5). You will
need the Sybase ODBC driver, this came with the PC Client package. If you
got your Windows Open Client software through some other means, then you
may need to down load the ODBC driver, this will become apparent
later. Right, begin by launching the 32 bit ODBC administrator, either from
the Sybase menu under start -> programs or the control panel. Ensure that
you are displaying the "user DSN" section (by clicking on the appropriate
tab).
You can then click on the button labeled add to move to the
driver selection dialog. Select Sybase System 11 and click on
finish. You will by now have noticed that this is Microsoft's way
of taunting you and you haven't actually finished yet, you're actually at
the next dialog. What you have actually done is told windows that you are
now about to configure your Sybase ODBC driver. There are 4 boxes on the
dialog with which you are now presented, and they are:
Data Source Name
Description
Server Name
Database Name
The data source name is the Server name from your
interfaces file on your Linux server. If you are uncertain of any
of these values, then log onto your Linux box, run dsedit and
take a look. It will only take you 2 minutes and much easier than
debugging it later. The description field is irrelevant and you can put
anything in there that is meaningful to you. Server name is the IP
address of the Linux server, that is hosting your database. Database name
is the name of a database to which you want to connect, once your Sybase
connection has been established. If in doubt, you can stick master in
there for now, at least you'll get a connection. Now you can click on
OK to get back to the starting screen, followed by another OK to exit ODBC
administrator. We will now test the connection by running Sybase Central.
I chosen this because I figure that if you downloaded the PC Client
package, then I know you've got it (at least I'm fairly sure). When you
launch Sybase administrator from start->programs->Sybase, you are presented
with a connection dialog. There are 3 fields in this box
User ID
Password
Server Name
In the field labeled UserID, you can type in sa. If you've been doing
some work on Sybase through other means and you have already created a
valid user, then you can use him (her, it, whatever). In the password
field, type in the appropriate password. Assuming you have changed nothing
from the
original Sybase install and you are using sa, then you will
leave this blank. The final field is a dropdown list box containing all
the Sybase remote connections you have. Assuming you only have the one,
then you can leave this alone. If you have more than one, stick to the one
that you know works for now and that allows access to the user you've used.
In simple English (and if you don't speak English, then I hope somebody has
translated it :-) ). If this is a clean install and you have altered
nothing after following the instruction earlier to establish an Open
Client, then the top box should contain simply "sa", the middle box should
be blank, and the bottom list-box should contain whatever the servername is
in your Linux based interfaces file. Clicking on OK will now
connect Sybase Central to the database and "away you go"... Hope
this is of some assistance to you, but if you run into problems then I
suggest you post to the newsgroup, which is where the real experts hang
out. I am unlikely to be able to help you, as I have simply noted down my
experiences as I encountered them, in the hope they may help somebody
out.
I take no responsibility for anything, including any result of
following the instructions in this text.
Good luck...
Jim
Back to top
The TDS protocol that *is* Open Client is built so that either the
client or server will fallback to a common dialect. I suppose that
it is theoretically possible that both would fallback for some reason, but
it seems unlikely. I was recently working with a client that was using
Open/Client 4.2 to speak to a version 11.5 ASE using Powerbuilder 3 and 4!
Amazing, it all worked! The main problem that you will encounter is not
lack of communication but lack of features. The facility to bcp out of
views was added to the 11.1.1 release. You will still be able to connect
to servers with old copies of Open/Client, you just won't have all of the
features.
There is also another fairly neat feature of the later releases of
Open/Client, it has a very good compatibility mode for working with old
applications. The client that was running Open/Client 4.2 with
Powerbuilder 3 is now connecting to the database using version 11.1.1.
Which is not bad when you remember that Powerbuilder 3 only talked
4.2 DBLib!
Back to top
Run
isql -v
from the command line, will return a string like:
Sybase CTISQL Utility/11.1.1/P-EBF7729/PC Intel/1/
OPT/Thu Dec 18 01:05:29 1997
The 11.1.1 part represents the version number.
Back to top
The following is lifted directly from the FreeTDS FAQ.
FreeTDS is a free re-implementation of the TDS (Tabular Data Stream) protocol that is
used by Sybase and Microsoft for their database products. It currently implements TDS 4.2,
5.0, 7.0 and 8.0, and can communicate with any Sybase product as well as Microsoft SQL
Server.
FreeTDS comes with a low level library (the TDS layer) along with a number of APIs (Application
Programming Interfaces). The current APIs are DB-Lib, CT-Lib, and ODBC.
A JDBC driver has also been contributed under a BSDish license and is available from the
download page. It does not require the FreeTDS C libraries.
It can be located at http://www.freetds.org.
Back to top
|
You are here: The International Sybase User Group > Knowledge Center > Sybase FAQ > ASE > Open Client
