Note: This answer gives generic information on the most common problems when
connecting to a SQL Anywhere server. It does not describe every connection
parameter, every TCP/IP option, or every problem you may run into. For more
details, look at the documentation for connection parameters or
Troubleshooting connections. If you are still unable
to connect, ask a new question with your specific connection string and server
details.
1 Connection Strings
When connecting to a SQL Anywhere server, you need to specify a connection
string containing a number of connection parameters. Each connection parameter is
a "key=value" pair, and parameters are separated by semicolons. Some parameter
values have options that can be specified in parens ( ) or braces { }.
There are well over 30 different connection parameters, but here are the ones
used most often (along with documentation links):
The only required connection parameters are UID and PWD, since you need to tell
the database server who you are (and if you're using integrated login you
don't even need those, but that's beyond the scope of this question). However, it
is generally good practice to specify the Server parameter as well, in order to
ensure that you connect to the correct server. If the server is hosting multiple
databases, the DBN parameter is also recommended.
2 Finding the server
2.1 Same machine
If you are connecting to a server running on the same machine then you can likely use shared memory to connect.
However there are situations when shared memory cannot be used:
If using Windows, see Session and terminal considerations;
On Unix both client and server processes must be using the same temporary directory.
To use shared memory, you can either use "LINKS=shmem" in your connection string or leave out the LINKS and HOST parameters entirely.
2.2 Using TCP/IP
If you cannot use shared memory, you must use TCP/IP. If your client is using SQL Anywhere version 12.0.0 or later, you can use either the HOST parameter or the LINKS parameter (but not both). For earlier clients, you must use the LINKS parameter.
In most cases, the HOST parameter is recommended over LINKS since it's much simpler.
2.2.1 HOST
The HOST parameter is the easiest way to tell the client library how to find the
server. The value of the HOST parameter is the IP address and, optionally, the
port on which the server is listening. You can list multiple addresses, separating
them with commas. The addresses are tried in the order listed.
The HOST parameter is not the same as the Host TCP/IP option.
Notes:
- If the port is not specified, it defaults to 2638.
- The IP address and port are separated with a colon (
:). If the IP address is
an IPv6 address and a port is specified, the address must be in square brackets,
eg. HOST=[1:2:3::7:8]:1234.
- If the HOST parameter is specified, the Server parameter is not required but
is still recommended.
- The HOST parameter does not support options like LINKS does. If you need to
specify options, you must use LINKS.
2.2.2 LINKS
The LINKS parameter tells the client library which protocol to use to find the
server. The only values for this parameter are "shmem" or "tcpip". For TCP/IP, you
can also use a number of options to help find the server. Options are appended to
the value in parens. Options are also "key=value" pairs, separated by semicolons
(;). These options are documented here (in the TCP/IP column), but the most
often used ones are:
| Parameter | Short form | Description |
|---|
| Host | IP | Host name or IP address of server |
| ServerPort | Port | Port number of server |
| DoBroadcast | DoBroad | How to use UDP broadcasts to find the server |
The DoBroadcast option takes the following values:
- all - The client sends a UDP broadcast to the local subnet looking for the
server. The server then responds with its IP address and port number. This is the
default if Host is not specified.
- direct - The Host option is required. The client sends UDP packets
to each of the hosts specified with the Host option. If the server is running on
one of those hosts, it will respond with its IP address and port number.
- none - The Host option is required. The client attempts to make
TCP connections to each of the hosts specified with the Host option. This is the
default if the Host option is specified.
3 Finding the database
When the server is started, the first database to be started on the server is called the
"default" database. Any connection to that server that does not specify a database
name or file is connected to the default database. If you know that the server
you're connecting to is only running one database then this is fine, but if you are not
sure, you may find yourself connecting to the wrong database. This could be simply
annoying (your userid and password won't work) or it could be disastrous (your
userid and password do work and you are accessing / updating / deleting the wrong
data).
To indicate which database you want to connect to, use the DatabaseName (DBN)
parameter with the name of the database you want to connect to. If the database
is not running, it may be possible to autostart it by specifying the
DatabaseFile (DBF) parameter, but that is beyond the scope of this question.
A future FAQ question will deal with autostarting databases and servers.
4 Examples
All examples assume you are connecting to the user "fred" with the password "secret".
4.1 Connecting to a local server
You want to connect to a local server (i.e. running on the same machine as
your client) called Frodo using the default database, then you can use:
- uid=fred;pwd=secret;Server=Frodo
4.2 Connecting to a remote server
You want to connect to a server called Frodo on the same subnet as your computer, but you don't know what machine it's running on, then you can use:
- uid=fred;pwd=secret;Server=Frodo;links=tcpip
You want to connect to a server called Frodo on a host called Shire, listening on
the default port. Any of the following connection strings will work:
- uid=fred;pwd=secret;Server=MyServer;host=Shire
- uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire)
- uid=fred;pwd=secret;Server=MyServer;links=tcpip
Note: The last one will only work if the server machine is on the same subnet as the client.
You want to connect to a server called Frodo on a host called Shire, listening on
port 50000. Any of the following will work:
- uid=fred;pwd=secret;Server=MyServer;host=Shire:50000
- uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire:50000)
- uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire;port=50000)
- uid=fred;pwd=secret;Server=MyServer;links=tcpip(port=50000)
Note: The last one will only work if the server machine is on the same subnet as the client.
5 Troubleshooting
Still having problems after you've read this answer? Here's how you can get some more information from both the server and the client library to figure out what's going wrong.
5.1 On the client
Add the LOG parameter to your connection string. This parameter takes a filename as a value, and then writes a bunch of connection diagnostic information to that file. After a failed connection, you can look at that file and see where the problem occurred.
For example, let's say you have "Server=Frodo;host=1.2.3.4" in your connection string and you see this in the log:
Attempting TCPIP connection (no sasrv.ini cached address)
Looking for server with name Frodo
Trying to find server at address 1.2.3.4:2638
TCP/IP link, function connect(), error code 10061
Error code 10061 (on Windows) is WSAECONNREFUSED (Windows error codes are listed here). On Unix, the equivalent code is ECONNREFUSED but the numeric value may differ on different flavours of Unix. This code tells you that there is no service listening on that port, i.e. the SQL Anywhere server is not running on that port on that machine. Perhaps you have the port number or IP address wrong, or perhaps the server is not actually running.
But let's say you see this instead:
Attempting TCPIP connection (no sasrv.ini cached address)
Looking for server with name Frodo
Trying to find server at address 1.2.3.4:2638
Found server, verifying server name
A HOST value was specified, skipping LDAP check
Sending broadcast to find server
This tells us that we did find a server but after attempting to verify the server name, the connection attempt continued, so obviously the verification failed. So there is a server running on that machine, but it has a different server name than the one we want to connect to.
5.2 On the server
To enable diagnostic information on the server, it needs to be restarted with some extra command line switches. Add the -z and -o <file> switches to the command line and then restart the server. This will include extra diagnostic information on the server console, and save the console output to a "console log" file called <file>. This file will tell you what IP addresses and ports the server will be listening on, and this may help diagnose connection problems.
You can also change these values on a running server without the need to stop and restart it using the sa_server_option system procedure using the 'DebuggingInformation' and 'ConsoleLogFile' option names. However, this doesn't help if you need the information from the server initialization. In that case, you will need to stop and restart using the command line switches.
For example, you may see this in the console log:
Trying to start TCPIP link ...
Could not bind to address (::):2638
Unable to start on default port; starting on port 49152 instead
This tells us that the server attempted to use port 2638 (the default port), but was unable to because something else was already using it - likely another SQL Anywhere server. If your connection string contains "HOST=Shire:2638", then the client will only try port 2638, but your server is listening on port 49152. Thus you will likely connect to the wrong server.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.