Connecting to a Database
The syntax for connecting to a database depends on whether the target database server is local or remote.
Connecting to a Local Server
The following connection examples assume that the configuration parameter dbms_authentication=on for the server.
A user logged in locally (on the server machine) can connect as follows:
sql dbname
This assumes that the user is defined WITH DBMS_AUTHENTICATION='OPTIONAL'. Even if the user has a DBMS password, neither password prompt nor DBMS password validation occurs. If the user is defined WITH DBMS_AUTHENTICATION='REQUIRED', the connection attempt fails because no user name or password was supplied.
A user can also connect, either as himself or as another Vector user, using DBMS authentication. He can use the +user option or the local-vnode syntax:
sql +user dbname
The user is prompted for the password and connects as the logged-in OS user.
sql +user=fred dbname
The user is prompted for the password and connects as user fred. Fred need not be an OS user.
sql '@[fred,secret]::dbname'
The user enters the password on the command line. Fred need not be an OS user.
Note: If dbms_authentication is not enabled on the server, the last two examples will work, except that fred would have to be an OS user, and the password would have to be fred's OS password. If fred has a DBMS password defined, with dbms_authentication off, the server requests a password prompt for the DBMS password (thus, the +user=fred example might get two password prompts).
The +user option does not conflict with the effective user option (‑u). For example, if user fred has DB_ADMIN privileges for the database, he can connect and authenticate as himself, but then run the session with a different effective user:
sql +user=fred -u ralph dbname
The user (fred) is prompted for his password, and fred and password are authenticated by the DBMS. After the session is established, the effective user (ralph) is set.
Connecting to a Remote Server
If connecting to a remote server where dbms_authentication=on, a user can use either a vnode or dynamic vnode syntax:
sql vnode::dbname
The user name and password in the vnode definition are used.
sql '@host,tcp_ip,VW[fred,secret]::dbname'
The user name and password are entered on the command line.
sql +user=fred @host,tcp_ip,VW::dbname
Prompts for password. Because the +user flag is used, the [username, password] syntax cannot be specified on the dynamic vnode.
Connections established through API-based applications such as ODBC, JDBC, and .NET Data Provider directly specify a user name and password. A DBMS password sent by such an application to a dbms_authentication enabled server is meaningless and ignored.
Connecting to a Non-UTF8 Server that Does Not Support Transliteration
When establishing a connection from a Vector instance to a non-UTF8 server that does not support transliteration (such as an Ingres instance that uses a native character set), you must declare the character set used for the connection.
The character set specified must match or be compatible with the character set of the target server instance. So if you are connecting from a Vector client on Linux to an Ingres instance on Windows, you can specify WIN1252, which matches the character set on the Ingres for Windows installation.
The character set can be declared in a vnode or in the connection string by using the connection attribute "character_set" (or the shorter "charset"), using the following format:
@host,port;character_set=charset[user,pwd]::dbname
where port is the listen address of the instance on the remote node.
For example, the following dynamic vnode specification starts the terminal monitor and uses the WIN1252 character set to connect to the inventorydb database:
sql @hosta,VW;character_set=win1252[Susan,mypassword]::inventorydb