1001 lines
22 KiB
HTML
1001 lines
22 KiB
HTML
<HTML
|
|
><HEAD
|
|
><TITLE
|
|
>Creating, Running and Connecting to Databases</TITLE
|
|
><META
|
|
NAME="GENERATOR"
|
|
CONTENT="Modular DocBook HTML Stylesheet Version 1.63
|
|
"><LINK
|
|
REL="HOME"
|
|
TITLE="Sybase Adaptive Server Anywhere for Linux HOWTO"
|
|
HREF="index.html"><LINK
|
|
REL="PREVIOUS"
|
|
TITLE="Installation"
|
|
HREF="install.html"><LINK
|
|
REL="NEXT"
|
|
TITLE="Backing up and Restoring a Database"
|
|
HREF="backup.html"></HEAD
|
|
><BODY
|
|
CLASS="SECT1"
|
|
BGCOLOR="#FFFFFF"
|
|
TEXT="#000000"
|
|
LINK="#0000FF"
|
|
VLINK="#840084"
|
|
ALINK="#0000FF"
|
|
><DIV
|
|
CLASS="NAVHEADER"
|
|
><TABLE
|
|
WIDTH="100%"
|
|
BORDER="0"
|
|
CELLPADDING="0"
|
|
CELLSPACING="0"
|
|
><TR
|
|
><TH
|
|
COLSPAN="3"
|
|
ALIGN="center"
|
|
>Sybase Adaptive Server Anywhere for Linux HOWTO</TH
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="10%"
|
|
ALIGN="left"
|
|
VALIGN="bottom"
|
|
><A
|
|
HREF="install.html"
|
|
>Prev</A
|
|
></TD
|
|
><TD
|
|
WIDTH="80%"
|
|
ALIGN="center"
|
|
VALIGN="bottom"
|
|
></TD
|
|
><TD
|
|
WIDTH="10%"
|
|
ALIGN="right"
|
|
VALIGN="bottom"
|
|
><A
|
|
HREF="backup.html"
|
|
>Next</A
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><HR
|
|
ALIGN="LEFT"
|
|
WIDTH="100%"></DIV
|
|
><DIV
|
|
CLASS="SECT1"
|
|
><H1
|
|
CLASS="SECT1"
|
|
><A
|
|
NAME="AEN428"
|
|
>4. Creating, Running and Connecting to Databases</A
|
|
></H1
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN430"
|
|
>4.1. Creating a database</A
|
|
></H2
|
|
><P
|
|
>When you ask Adaptive Server Anywhere to create a database,
|
|
it creates the main database file, which contains the following
|
|
objects, among others: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>user tables </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>indexes </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>views </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>system tables </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>The maximum size of a database file depends on your file system
|
|
and the page size you choose. Database files are limited to 256
|
|
million database pages or the filesize limit, whichever is reached
|
|
first. UNIX files can be as large as 1 Tb, in some cases-see the
|
|
Physical Limitations chapter of the Adaptive Server Anywhere Reference
|
|
Manual or your Linux documentation for more information. You can
|
|
set pages to be 1, 2, 4, 8, 16, or 32 kb in size, but it is not
|
|
recommended that you use a page size of 1 kb. The default page size
|
|
is 2 kb. </P
|
|
><P
|
|
>By default, Adaptive Server Anywhere also creates a file called
|
|
the <EM
|
|
>transaction log</EM
|
|
>. Besides improving performance,
|
|
the transaction log is vital to Adaptive Server Anywhere replication
|
|
systems and database recovery in event of system failures. When possible,
|
|
it is recommended that the transaction log be placed on a physical
|
|
device (in most cases, a disk drive) separate from the main database
|
|
file, to reduce the chances of both the main database file and transaction
|
|
log being affected in the event of a media failure. You can specify
|
|
the name and location of the transaction log when you create the
|
|
database. </P
|
|
><P
|
|
>This section shows you how to create databases at either the
|
|
command prompt or in Interactive SQL. You can also create databases
|
|
through Sybase Central, if you prefer, by opening the Utilities
|
|
folder under Adaptive Server Anywhere 7. </P
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="ASA-41HTMLTOC511556197"
|
|
>4.1.1. Creating a database
|
|
from the command prompt</A
|
|
></H3
|
|
><P
|
|
>The command line utility for creating a database is <EM
|
|
>dbinit</EM
|
|
>.</P
|
|
><P
|
|
>Syntax: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbinit [switches] db-file-name</B
|
|
></P
|
|
><P
|
|
>db-file-name is the name you would like to give to your database
|
|
file, for example, mydb.db. If you issue the command "dbinit -?"
|
|
you'll be shown the above syntax, along with a list of options you
|
|
can use. </P
|
|
><P
|
|
>To create your first Adaptive Server Anywhere database on
|
|
Linux, enter the following command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbinit -t './logs/mydb.log' p 4096 mydb.db</B
|
|
></P
|
|
><P
|
|
>This command creates a database in the current working directory
|
|
called mydb.db with a page size of 4096 bytes, specified by the
|
|
-p switch. Assuming the directory exists, it also creates the transaction
|
|
log mydb.log in the subdirectory "logs," specified by the -t switch.
|
|
Adaptive Server Anywhere databases carry the extension ".db" . </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="ASA-41HTMLTOC511556198"
|
|
>4.1.2. Creating a database
|
|
from Sybase Central</A
|
|
></H3
|
|
><P
|
|
>To create a database in Sybase Central, open the Adaptive
|
|
Server Anywhere section of the left pane, and select Utilities.
|
|
Double-click Create Database in the right pane, and follow the on-screen
|
|
instructions. </P
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN461"
|
|
>4.2. Running a database server and starting databases</A
|
|
></H2
|
|
><P
|
|
>There are two versions of the database server installed on
|
|
your machine. If you are just using Adaptive Server Anywhere locally,
|
|
use the personal database server (dbeng7). If you are going to connect
|
|
to the Adaptive Server Anywhere database over a network, however,
|
|
you should use the network database server (dbsrv7). Examples in
|
|
this document use dbeng7, but the two commands are, for the most
|
|
part, interchangeable. See the table below for specific differences. </P
|
|
><DIV
|
|
CLASS="TABLE"
|
|
><A
|
|
NAME="AEN464"
|
|
></A
|
|
><P
|
|
><B
|
|
>Table 7. Differences
|
|
between the Personal and Network database servers</B
|
|
></P
|
|
><TABLE
|
|
BORDER="1"
|
|
CLASS="CALSTABLE"
|
|
><TBODY
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
> </TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
><EM
|
|
>Personal database server </EM
|
|
></TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
><EM
|
|
>Network database server </EM
|
|
></TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Name of executable</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>dbeng7</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>dbsrv7</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Local connections</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Yes</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Yes</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Network connections</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>No</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Yes</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Maximum number of connections</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>10</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Depends on license</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Available communications protocols</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Shared memory, TCP/IP</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Shared memory, TCP/IP</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Maximum number of CPUs for request
|
|
processing</TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>2</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Unlimited</TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="368"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>Default/Maximum number of internal
|
|
threads </TD
|
|
><TD
|
|
WIDTH="240"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>10/10</TD
|
|
><TD
|
|
WIDTH="233"
|
|
ALIGN="LEFT"
|
|
VALIGN="TOP"
|
|
>20/Unlimited</TD
|
|
></TR
|
|
></TBODY
|
|
></TABLE
|
|
></DIV
|
|
><P
|
|
>Syntax: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>(dbeng7 | dbsrv7) [server-switches] [database-file [database-switches],
|
|
] </B
|
|
></P
|
|
><P
|
|
>database-file specifies the path and filename to the database.
|
|
You aren't actually required to specify a database file when you
|
|
start up the database server, but if you don't, you must specify a
|
|
name for the server using the -n switch. By default, if you do not
|
|
specify a name for the database, it takes on the name of the database
|
|
file, minus the path and extension. Similarly, if you do not specify
|
|
a name for the database server (which you can do in server-switches),
|
|
it takes on the name of the first database that was started on it. </P
|
|
><P
|
|
>For full details on the usage of dbeng7 and dbsrv7, see "The
|
|
database server" in the Adaptive Server Anywhere Reference. </P
|
|
><P
|
|
>To start up the Adaptive Server Anywhere personal database
|
|
server, but not a database, and name it MyServer, issue the following
|
|
command at a prompt: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbeng7 -n MyServer </B
|
|
></P
|
|
><P
|
|
>To start up the Adaptive Server Anywhere personal database
|
|
server and name it MyServer, then start a database on MyServer from
|
|
mydb.db, naming it MyDatabase, issue the following command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbeng7 -n MyServer mydb.db -n MyDatabase </B
|
|
></P
|
|
><P
|
|
>In the latter case, if you don't name the database server
|
|
MyServer, it would be named MyDatabase instead. </P
|
|
><P
|
|
>There's a plethora of other switches available for the server.
|
|
You can get a full listing of them by typing "dbeng7 -?" at a command
|
|
prompt. A few important switches include the following: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>-c, for specifying Adaptive Server
|
|
Anywhere's cache size </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>-x allows you to specify the communications protocols </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>-gt allows you to specify the number of processors
|
|
to be used </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>-ud tells the server to run as a daemon in UNIX
|
|
(explained below) </P
|
|
></LI
|
|
></UL
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN527"
|
|
>4.2.1. Running the server as a daemon</A
|
|
></H3
|
|
><P
|
|
>Sometimes it's necessary for the server to run outside of
|
|
the current session (that is, regardless of who, if anyone, is logged
|
|
in). To do so, use the -ud switch at the command line when starting
|
|
the server to run it as a daemon. </P
|
|
><P
|
|
>The following command would start up a database server as
|
|
a daemon, using the database we created before: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbsrv7 -ud -n MyDatabase mydb.db</B
|
|
> </P
|
|
><P
|
|
>NOTE: Using "&" to run the database server in the background
|
|
does not work. </P
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN534"
|
|
>4.3. Stopping the database server</A
|
|
></H2
|
|
><P
|
|
>Assuming you have the appropriate authority, you can stop
|
|
the database server using any of the following methods: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>the dbstop command line utility </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>using the STOP ENGINE SQL statement </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>pressing the Q key when the server display window
|
|
has the focus </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>NOTE: While the term <EM
|
|
>engine</EM
|
|
> is part of
|
|
the SQL statement's name, <EM
|
|
>server</EM
|
|
> is the common
|
|
term now used. This document will use the term <EM
|
|
>server</EM
|
|
> unless
|
|
referring explicitly to the STOP ENGINE SQL statement. </P
|
|
><P
|
|
>By default, any user can stop a personal database server,
|
|
but only a user with the DBA authority can stop a network database
|
|
server. (This default can be changed by using the -gk switch when
|
|
starting the server-see the Adaptive Server Anywhere Reference for
|
|
details.) </P
|
|
><P
|
|
>The command line utility syntax is as follows: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbstop [switches] {name}</B
|
|
></P
|
|
><P
|
|
>If you are issuing dbstop to stop a locally-running server,
|
|
you can simply specify the name of the database server in {name}.
|
|
If the server is not running locally, you need to create a connection
|
|
to the server before you can tell it to stop. The -c switch allows
|
|
you to specify a connection string for the database running on the
|
|
server that you would like to stop. To stop MyServer, execute the
|
|
following command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbstop -c "uid=DBA;pwd=SQL;eng=MyServer;dbn=MyDatabase"</B
|
|
></P
|
|
><P
|
|
>In this instance, you could also just give the server name,
|
|
since the server is running locally: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbstop MyServer </B
|
|
></P
|
|
><P
|
|
>The first command connects to the database named MyDatabase
|
|
on the server MyServer, then stops the server named MyServer. In
|
|
the case that no databases are active on the server, you have to
|
|
add "dbn=utility_db" to the connection string.</P
|
|
><P
|
|
>Let's say "Club" is the name of one of the databases running
|
|
on a server named "Goliath," and you want to stop all the databases
|
|
running on Goliath, including Club. The following command accomplishes
|
|
that, as well as shutting down the database server: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbstop -c "uid=DBA;pwd=SQL;eng=Goliath;dbn=Club"</B
|
|
></P
|
|
><P
|
|
>If you have a database server named "David" running without
|
|
any databases started on it, you can stop the server using the following
|
|
command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbstop -c "uid=DBA;pwd=SQL;eng=David;dbn=utility_db"</B
|
|
></P
|
|
><P
|
|
>The syntax for the STOP ENGINE statement is as follows: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>STOP ENGINE [ server-name ] [ UNCONDITIONALLY ] </B
|
|
></P
|
|
><P
|
|
>The server named server-name is stopped. If server-name is
|
|
omitted, the currently running database server is stopped. If UNCONDITIONALLY
|
|
is specified, the database server is stopped whether or not there
|
|
are still connections to the server. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN569"
|
|
>4.4. Stopping databases</A
|
|
></H2
|
|
><P
|
|
>It's also possible to stop individual databases without stopping
|
|
the server, or any of the other databases that might be running
|
|
on it. To do so, use the STOP DATABASE SQL statement.</P
|
|
><P
|
|
>Syntax: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>STOP DATABASE database-name [ON engine-name] [UNCONDITIONALLY] </B
|
|
></P
|
|
><P
|
|
>You specify the name of the database that you would like to
|
|
stop in database-name, with the restriction that the database specified
|
|
cannot be the currently connected one. The "ON engine-name" clause
|
|
can be used only in Interactive SQL. You use it to specify the server
|
|
that the database is running on. Outside of Interactive SQL, the
|
|
database can only be stopped if it is on the current server. The
|
|
UNCONDITIONALLY keyword forces databases to be stopped, even if
|
|
there are connections to it. By default, you can't stop a database
|
|
if there are connections active. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT2"
|
|
><H2
|
|
CLASS="SECT2"
|
|
><A
|
|
NAME="AEN576"
|
|
>4.5. Connecting to a database</A
|
|
></H2
|
|
><P
|
|
>You can connect to an Adaptive Server Anywhere database via
|
|
any of the following interfaces: </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>ODBC </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>OLE DB or ADO </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>Embedded SQL </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>Sybase Open Client </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>JDBC </P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>Regardless of how you connect, you must specify some parameters,
|
|
such as a username and password, to establish a connection to the
|
|
database. These can be specified in a connection string, the SQLCONNECT
|
|
environment variable, an ODBC data source configuration, or the fields
|
|
of a dialog box. </P
|
|
><P
|
|
>In this section, you'll find explanations on how to connect
|
|
via SQL and ODBC. </P
|
|
><P
|
|
>As the Adaptive Server Anywhere network server is a client/server
|
|
database, you may connect to a Linux-hosted database from Windows-based
|
|
PCs and other non-Linux devices, as well as Linux applications.
|
|
Programming interfaces such as OLE DB or ADO are available ony on
|
|
Windows, but can still be used against a Linux-hosted database. </P
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN593"
|
|
>4.5.1. Connection strings</A
|
|
></H3
|
|
><P
|
|
>Connection strings are frequently used when performing actions
|
|
on a database. They consist of a list of parameter settings, delimited
|
|
by semicolons and enclosed in double quotes. There should be no
|
|
extra spaces in a connection string. </P
|
|
><P
|
|
>Example: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>"uid=DBA;pwd=SQL"</B
|
|
></P
|
|
><P
|
|
>The short strings of letters just before each equal sign (in
|
|
this example, uid, pwd, and dbf) are called <EM
|
|
>keywords</EM
|
|
>,
|
|
which each correspond to a connection parameter. There are many
|
|
connection parameters available, and they are listed in the Connecting
|
|
to a Database chapter of the Adaptive Server Anywhere User's Guide.
|
|
They are also described in detail in the Connection and Communication
|
|
Parameters chapter of the Adaptive Server Anywhere Reference. </P
|
|
><P
|
|
>When Adaptive Server Anywhere utilities are looking for connection
|
|
parameters, they check the SQLCONNECT environment variable for any
|
|
parameters that were left out of the connection string. If you're
|
|
putting connection parameters into the SQLCONNECT environment variable,
|
|
replace the equal signs with number (#) signs. In bash you would
|
|
use the following command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>SQLCONNECT='uid#DBA;pwd#SQL'</B
|
|
></P
|
|
><P
|
|
>The single quotes are necessary in the above command because
|
|
semicolons can be used to separate bash commands. You can also use
|
|
double quotes. </P
|
|
><P
|
|
>To make SQLCONNECT available in subsequent shells, you'd need
|
|
to use "export SQLCONNECT" to export the SQLCONNECT variable to
|
|
the environment. You may also want to put these commands into your
|
|
.bash_profile (or .profile, if you're using another shell) if you
|
|
want the same connection parameters to be available each time you
|
|
log in. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN606"
|
|
>4.5.2. Connecting from Interactive SQL</A
|
|
></H3
|
|
><P
|
|
>To connect to a database from Interactive SQL, go to the Command
|
|
menu, and choose "Connect...", then fill in the dialog box as appropriate. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT3"
|
|
><H3
|
|
CLASS="SECT3"
|
|
><A
|
|
NAME="AEN609"
|
|
>4.5.3. Connecting via ODBC</A
|
|
></H3
|
|
><P
|
|
>ODBC (which stands for Open Database Connectivity) is an industry-standard
|
|
interface for connecting client applications to relational and non-relational
|
|
DBMSes. When you create an ODBC data source, it encapsulates the
|
|
data and any other information required to get the data, including
|
|
connection parameters. </P
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN612"
|
|
>4.5.3.1. Setting up ODBC with Adaptive Server Anywhere</A
|
|
></H4
|
|
><P
|
|
>To connect to Adaptive Server Anywhere from ODBC applications
|
|
on Linux, you can either use Sybase's ODBC driver as a driver manager,
|
|
or use a third-party ODBC driver manager such as iODBC or unixODBC.
|
|
If you choose the latter route, follow the installation instructions for
|
|
the driver manager you've chosen and choose dbodbc7.so (which resides
|
|
in the sybase/SYBSsa7/lib directory) as the ODBC driver for Adaptive
|
|
Server Anywhere. </P
|
|
><P
|
|
>If you choose the former route, you can use Adaptive Server
|
|
Anywhere's ODBC driver as a driver manager if you will only be connecting
|
|
to Adaptive Server Anywhere databases. To do so, you need to create
|
|
a few symbolic links so that ODBC driver manager requests get routed
|
|
to the Sybase ODBC driver. From the sybase/SYBSsa7/lib subdirectory,
|
|
enter the following commands: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>$ ln -s dbodbc7.so libodbc.so</B
|
|
></P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>$ ln -s dbodbc7.so libodbc.so.1</B
|
|
></P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>$ ln -s dbodbc7.so libodbcinst.so</B
|
|
></P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>$ ln -s dbodbc7.so libodbcinst.so.1</B
|
|
></P
|
|
><P
|
|
>That's it! </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN625"
|
|
>4.5.3.2. About ODBC data sources</A
|
|
></H4
|
|
><P
|
|
>Data sources exist on the client computer, with at least one
|
|
for each database accessible via ODBC. They reside in the .odbc.ini
|
|
file or in a separate .dsn file. </P
|
|
><P
|
|
>If the client computer is running Linux or another UNIX operating
|
|
system, ODBC data sources can be used both for ODBC applications
|
|
as well as for the Interactive SQL and Sybase Central utilities. </P
|
|
><P
|
|
>NOTE: The database server looks for <EM
|
|
>.odbc.ini</EM
|
|
> in
|
|
the following locations, among several others: </P
|
|
><P
|
|
></P
|
|
><OL
|
|
TYPE="1"
|
|
><LI
|
|
><P
|
|
>ODBCINI environment variable </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>ODBCHOME and HOME environment variables </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>The user's home directory </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>The current directory </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>The path </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>The root directory</P
|
|
></LI
|
|
></OL
|
|
><P
|
|
>If no .odbc.ini file exists in your home directory, you'll
|
|
have to create one in your home directory. You can check if one
|
|
exists by using the command "ls -a ~/.odbc.ini".</P
|
|
><P
|
|
>You manage ODBC data sources using the <EM
|
|
>dbdsn </EM
|
|
>command
|
|
line utility. </P
|
|
><P
|
|
>Syntax: </P
|
|
><TABLE
|
|
BORDER="0"
|
|
BGCOLOR="#E0E0E0"
|
|
WIDTH="100%"
|
|
><TR
|
|
><TD
|
|
><PRE
|
|
CLASS="PROGRAMLISTING"
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbdsn [ modifier-switches ]
|
|
{ -l
|
|
| -d dsn
|
|
| -g dsn
|
|
| -w dsn [details-switches]
|
|
| -cl }</B
|
|
></PRE
|
|
></TD
|
|
></TR
|
|
></TABLE
|
|
><P
|
|
>dbdsn has four main modes of operation, and its behaviour
|
|
depends on whether you choose the -l, -d, -g, or -w switch. Where
|
|
applicable, the name of the data source to be operated on is specified
|
|
by dsn. </P
|
|
><P
|
|
></P
|
|
><UL
|
|
><LI
|
|
><P
|
|
>the -l switch lists the data sources
|
|
that have been defined </P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>the -d switch deletes the specified data source</P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>the -g switch gives you the details of the specified
|
|
data source</P
|
|
></LI
|
|
><LI
|
|
><P
|
|
>the -w switch creates a new DSN using parameters
|
|
specified in details-switches</P
|
|
></LI
|
|
></UL
|
|
><P
|
|
>The most important details-switch is the -c switch, which
|
|
allows you to specify the usual database connection parameters.
|
|
You can also specify the name of a database server as a details-switch.
|
|
Type "dbdsn -cl" to display a list of available connection parameters. </P
|
|
><P
|
|
>To create a new data source named MyNewDSN for the server
|
|
MyServer, execute the following command at a shell prompt: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbdsn -w MyNewDSN -c "uid=dba;pwd=sql;eng=MyServer" </B
|
|
></P
|
|
><P
|
|
>If there is a data source named MyNewDSN already existing,
|
|
dbdsn asks if you would like to overwrite it. </P
|
|
><P
|
|
>Conversely, to delete MyNewDSN, execute the following command: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>dbdsn -d MyNewDSN </B
|
|
></P
|
|
><P
|
|
>The modifier-switches control how dbdsn outputs its messages
|
|
to screen, and whether or not data sources can be overwritten without
|
|
confirmation. For more information on other dbdsn options, see "The
|
|
Data Source utility" under the Database Administration Utilities
|
|
chapter of the Adaptive Server Anywhere Reference. </P
|
|
></DIV
|
|
><DIV
|
|
CLASS="SECT4"
|
|
><H4
|
|
CLASS="SECT4"
|
|
><A
|
|
NAME="AEN669"
|
|
>4.5.3.3. Connecting to an ODBC data source</A
|
|
></H4
|
|
><P
|
|
>Once you've created an ODBC data source, you can access it
|
|
through the DSN (DataSourceName) connection string keyword. </P
|
|
><P
|
|
>For an ODBC data source called mydatasrc, for example, use
|
|
the following connection string to connect to the database associated
|
|
with it: </P
|
|
><P
|
|
><B
|
|
CLASS="COMMAND"
|
|
>"dsn=mydatasrc"</B
|
|
></P
|
|
><P
|
|
>NOTE: Explicitly-provided connection parameters and SQLCONNECT
|
|
override any parameters provided in the ODBC data source, in that
|
|
order. </P
|
|
><P
|
|
>NOTE: The FileDSN connection parameter is not yet available
|
|
in version 7.0.2 of Adaptive Server Anywhere. Future versions of
|
|
Adaptive Server Anywhere should support File DSNs. </P
|
|
></DIV
|
|
></DIV
|
|
></DIV
|
|
></DIV
|
|
><DIV
|
|
CLASS="NAVFOOTER"
|
|
><HR
|
|
ALIGN="LEFT"
|
|
WIDTH="100%"><TABLE
|
|
WIDTH="100%"
|
|
BORDER="0"
|
|
CELLPADDING="0"
|
|
CELLSPACING="0"
|
|
><TR
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="left"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="install.html"
|
|
>Prev</A
|
|
></TD
|
|
><TD
|
|
WIDTH="34%"
|
|
ALIGN="center"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="index.html"
|
|
>Home</A
|
|
></TD
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="right"
|
|
VALIGN="top"
|
|
><A
|
|
HREF="backup.html"
|
|
>Next</A
|
|
></TD
|
|
></TR
|
|
><TR
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="left"
|
|
VALIGN="top"
|
|
>Installation</TD
|
|
><TD
|
|
WIDTH="34%"
|
|
ALIGN="center"
|
|
VALIGN="top"
|
|
> </TD
|
|
><TD
|
|
WIDTH="33%"
|
|
ALIGN="right"
|
|
VALIGN="top"
|
|
>Backing up and Restoring a Database</TD
|
|
></TR
|
|
></TABLE
|
|
></DIV
|
|
></BODY
|
|
></HTML
|
|
> |