How To Install
Symantec dbAnywhere Database Development Edition JDK 1.1
Last updated 1998 June 16 Roedy Green ©
1997 Canadian Mind Products.
I spent 7 days trying install dbAnywhere under Windows-95 and JDK
1.0.2, and another afternoon redoing it for JDK 1.1.3, and about an hour
for database development edition. In the interest of saving you a little
time, here are the magic incantations I discovered. I can't guarantee all
these steps are necessary, but they are sufficient. These instructions
were written for the version of dbAnywhere that comes with Visual
Café Pro 1.1 or the Database Development Edition 2.0. (Visual
Café Pro is the same code as Database Development Edition with some
of the new features left out.). I have updated them slightly for Version
2.5 and use under NT, though that work is in progress.
The 20 Steps to Bliss
-
Edit (or create) C:\WINDOWS\HOSTS. Add a line
127.0.0.1 localhost # dummy address for local
machine
See C:\WINDOWS\HOSTS.SAM for more information on the tab-delimited HOSTS file.
Under NT it stored in System32\Drivers\Etc . Also create
entries for any computers on your local LAN that don't have permanent IP
addresses like this:
# Other computers on the LAN
192.168.0.1 Gloria
192.168.0.2 Ramona
A file like this should be in each machine on the LAN.
- Uninstall any previous versions before you start. Don't install
over top into the same directories or you will have old files lying
about. You may have to manually edit the registry to remove all
vestiges of earlier versions. I found dbAnywhere would die with an
error 10049 pipe failed if there was even a trace of an
earlier version in the registry. It may even be a remnant of an early
version of Visual Cafe. Use the registry editor to clean out any old
Visual Cafe or bBbAnywhere entries.
- Install Visual Cafe Pro, Sybase SQL Anywhere,
dbAnywhere in that order. Optionally install the other goodies. You
will only need the Win32 drivers for SQL Anywhere, none of the C++ stuff,
and only the ODBC and Watcom SQL drivers. Don't UNZIP any of the ZIP or
JAR files! Uninstall any previous versions before you start. Don't install
over top into the same directories or you will have old files lying
about.
Reboot.
- Use the Live Update feature of dbAnywhere
and Visual Cafe to get the latest versions. Make sure SQLAnywhere
is up to date as well. Don't UNZIP any of the ZIP or JAR files! The live
update will also download improved readme files. I suggest using directory
names vcp, sqlany and dbany rather than the
long and constantly changing default directory names.
Reboot.
- There is no need to modify your
C:\VCP\bin\SC.INI file, the way you had to previously. There are
now fewer files needed on the CLASSPATH, and the JDBC files are
automatically included. You might, just for clarity, or to add the KL
Group components, clean it up to look something like this:
[Version]
version=1.00 Build 4
[Environment]
PATH=c:\vcp\bin;c:\vcp\java\bin;%path%
BIN=c:\vcp\bin
INCLUDE=c:\vcp\include
LIB=c:\vcp\lib
HELP=c:\vcp\help
JAVAINC=c:\vcp\java\src
JAVA_HOME=c:\vcp\java
; CLASSPATH must actually be all on one line
; For versions prior to 2.5
CLASSPATH=.;
c:\MyStuff;
c:\vcp\bin\components\symbeans.jar;
c:\vcp\java\lib;
c:\vcp\java\lib\symclass.zip;
c:\vcp\java\lib\classes.zip;
c:\vcp\java\lib\dbaw.zip;
c:\vcp\bin\components\dbaw_awt.jar;
c:\vcp\kl group\jcbwt205.jar
; For versions 2.5 and later
CLASSAPATH=.;
c:\MyStuff;
c:\vcp\bin\components\symbeans.jar;
c:\vcp\java\lib;
c:\vcp\java\lib\symclass.zip;
c:\vcp\java\lib\classes.zip;
c:\vcp\java\lib\dbaw.zip;
c:\vcp\bin\components\dbaw_awt.jar;
c:\vcp\bin\components\databind.jar;
c:\vcp\jfc\swingall.jar;
c:\vcp\kl group\klg.jar;
Select either JCDWT205.jar or klg.jar, whichever you have in your version.
-
Optionally get the latest version of the ODBC configuring software from
Microsoft by downloading their ODBC
SDK. The SDK contains documentation that will help you understand ODBC.INI,
ODBCINST.INI and the ODBC api. This will come in handy later when you try
to understand the JDBC api which is modelled on ODBC.
Reboot.
-
Run the Sybase 32 bit ODBC Administrator C:\WINDOWS\odbcad32.exe
to configure SQL Anywhere ODBC access for the current user. You can create
a system wide or only-for-this-user entry. Enter the following
values:
Data Source Name: osademo
description: Sybase SQL Anywhere Sample Data
user ID: dba
password: sql
server name: sademo
database name: sademo
database file: C:\sqlany\sademo.db
custom
start command: C:\sqlany\win32\dbeng50
database switches: __
autostop: on
translator name: <no translator>
You can optionally leave the entire connection section as the defaults/blanks.
-
Optionally run C:\WINDOWS\RegEdit.exe to have a peek at the registry
to make sure all is set up correctly with ODBC. You should see under HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
[ODBC Data Sources]
osademo = "Sybase SQL Anywhere 5.0"
[osademo]
AutoStop = "yes"
Databasefile = "C:\sqlany\sademo.db"
DatabaseName = "sademo"
Description = "Sybase SQL Anywhere Sample
Database"
Driver = "C:\sqlany\win32\wod50t.dll"
EngineName = "sademo"
PWD = "sql"
Start = "C:\sqlany\win32\dbeng50"
UID = "dba"
Patch any errors, (sometimes it screws up) particularly the Start command
and the password, to make sure it is lower case. Use the search command
to check any duplicates. Some of this information will also be echoed in
the vestigial C:\WINDOWS\ODBC.INI.
-
Optionally, use C:\WINDOWS\RegEdit.exe to check that under HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI
you see:
[ODBC Drivers]
Sybase SQL Anywhere 5.0="installed"
[Sybase SQL Anywhere 5.0]
Driver="C:\sqlany\win32\wod50t.dll"
Setup="C:\sqlany\win32\wod50t.dll"
C:\sqlany\win32\wod50t.dll"
[Sybase SQL Anywhere 5.0 Translator]
Setup="C:\sqlany\win32\wtr50t.dll"
Translator="C:\sqlany\win32\wtr50t.dll"
Some of this information will also be echoed in the vestigial C:\WINDOWS\ODBCINST.INI.
-
Configure dbAnywhere (C:\dbAny\bin\dbawsrvr.exe) with options,
properties, network to use host 127.0.0.1. and port 8889.
127.0.0.1 is the dummy dotted quad address of the local host. Note that
C:\dbAny\dbaw.exe has been renamed and moved to C:\dbAny\bin\dbawsrvr.exe.
You have to shut it down and restart for any changes to take effect.
-
DSNTOOL.BAT now automatically installs in C:\dbAny. The dbawdsn.ini
file now lives in C:\dbAny\bin. You no longer have to tweak DSNTOOL.BAT
to get it to work.. Run C:\dbAny\DSNTOOL.bat to create the
C:\dbAny\bin\dbawdsn.ini file. Click NEW. Fill in:
Selected Data Source:
Name: jsademo
Description: Sybase SQL Anywhere Sample Database
Engine: Sybase SQL AnyWhere 5.x via ODBC32
(second) datasource field: osademo
Username: dba
password: sql
dbAnywhere Server URL: dbaw://127.0.0.1:8889/
You can use the word localhost in place of 127.0.0.1 if you like. In
theory you should not have to add ODBC databases to dbawdsn.ini since dbAnywhere
checks ODBC.INI directly, but it won't hurt.
-
Click Save. The generated C:\dbAny\bin\dbawdsn.ini file
should look like this:
[dbANYWHERE Data Sources]
jsademo=Sybase SQL Anywhere Sample Database
[jsademo]
ENGINE=SYBASE_SQLANY
SERVER=osademo
DB=osademo
USERNAME=dba
PASSWORD=sql
It is often easier just to compose the C:\dbAny\bin\dbawdsn.ini
file with a text editor.
-
In Control Panel / Network / Configuration / TCP/IP / Properties / DNS
Configuration, click Disable DNS. This does not turn off DNS totally,
just Domain Name Service lookup for the local machine.
-
In Control Panel / Internet / Connection, uncheck the "connect
to Internet as needed" option. Though "Internet" appears to be settings
only for MS Internet Explorer, this setting has a broader effect.
-
To propitiate the installation gods, optionally sacrifice a small mammal
and
reboot.
-
Start the Sybase SQL Anywhere sample database server (C:\sqlany\win32\dbeng50.exe
C:\sqlany\sademo.db). I have seen Visual Cafe Pro indirectly fire up
SQL engines automatically by grabbing the name of the start sqlany.exe
from the registry. However, I think it wise to start the SQL engine manually.
If you start multiple databases, there will be a separate sqlany.exe
running for each one. You can check that the SQLAnywhere SQL engine is
functioning by running SQL Central and checking out the table structure
of the sample database. It works independently of dbAnywhere, ODBC and
JDBC.
-
Start dbAnywhere (C:\dbAny\bin\dbawsrvr.exe).
- Run C:\dbAny\DSNTOOL.bat. Run the two tests. They should open
and close the server without error messages (other than complaints that
some procedures are not callable and some tables are not selectable). The
most likely error is mistyping the "sql" password since you have to type
it blind in DSNTOOL. There is also a database test function hidden under HELP all
places in the dbAnywhere server menu. You can also test your database with
Visual Café's dbNavigator or by using a wizard to write a simple
app to view your database.
- When you use a wizard in Visual Cafe, you need both Sybase and
dbAnywhere running. It will ask you for a dbAnywhere connection name. Give
it csademo. This is just a name used for the connection variable.
It must be different from the other names.
-
The first time you start Visual Cafe, just shut it down again without doing
anything, not even closing the default project. It seems to need
this first shutdown cycle to get itself properly initialised.
Further Mysteries
-
This poor database has more names that a cheque forger. The Sybase database
engine calls it sademo. Sybase SQL anywhere makes it available as
an ODBC database under the name osademo. dbAnywhere then makes it
available via JDBC as jsademo. The Java source code uses the name
csademo as the name of the dbAnywhere connection. For simplicity,
I'd suggest using the same DSN in JDBC, ODBC and Sybase SQL Anywhere.
Alternatively you can use a name of the form form xxx for your sqlAnywhere
database, oxxx for the ODBC hook to it, jxxx for the JDBC
hook to it, and cxxx for the Java connection to it.
-
How do you configure things when the database is on a LAN? Then,
the machine hosting the dbAnywhere software will have a permanent IP lan-style
(10.x.x.x) address. You use that IP in your jdbc:dbaw://10.x.x.x:8889/
URL. To use names, add these IPs to your HOSTS file.
-
How do you configure things when the dbAnywhere server is accessed over
the Internet? Then your host machine will have a name, e.g. www.mindprod.ca,
a permanent dotted quad IP address and a permanent internet presence, and
will be registered with the global DNS (Domain Name Service). You might
use dbAnywhere by itself, or combine it with the http Netscape Server packed
with Visual Cafe Pro. You could use either the the dotted quad (jdbc:dbaw://x.x.x.x:8889/)
or the name (jdbc:dbaw://jdbc:dbaw://www.mindprod.ca:8889/) in your accessing
URL.
Creating Your Own Database
How do you create your own database and hook it up to ODBC and JDBC?
It depends on the SQL vendor.
-
For SQL Anywhere, you create an empty database with:
C:\sqlany\win32\dbinit.exe -p1024 myDatabase.db
None of the documented Sybase techniques for creating a database work:
-
The Create wizard does not exist.
-
The CREATE DATABASE SQL command is not supported.
-
The ISQL create/init tool is not on the menu.
-
There is no initialise command in SQL Central.
-
You then have to build your tables etc. and put them in a *.SQL file something
like this:
CREATE TABLE PEOPLE
(ACCT integer NOT NULL,
SURNAME char(30) NOT NULL,
GIVEN_NAME char(30) NOT NULL,
CITY char(18),
PROV char(2),
POSTAL char(6),
PRIMARY KEY (ACCT));
The help files in ISQL document the subset of SQL that Sybase SQLAnywhere
supports. The main things I noticed missing were domains -- aliases for
data types, and BIGINT, 64-bit long integers.
-
Then you can feed the SQL transactions in en masse with ISQL.
-
You can then check the structure of your database by connecting with SQL
Central, or by manually entering an SQL command into ISQL like this:
SELECT table_name from SYS.SYSTABLE
-
Then add your database to ODBC, giving it an arbitrary DSN (or follow my
pattern of using the database name as the DSN), then optionally add it
to dbawdsn.ini.
-
For non-ODBC databases, you go directly to dbawdsn.ini. Be warned, each
vendor uses the various fields in different ways.
How It Works
In Version 2.5 I think there appears to be a major change. However, prior
to that here is how it all worked:
In a simple case, here is how Symantec's proprietary dbAnywhere/SQL interface
works. The interface is quite a bit more advanced than JDBC or JSQL. There
is considerable detailed documentation, but no overview of how it all fits
together.
-
You create a Session object that
contains the URL to access dbAnywhere.
-
You create a ConnectionInfo object
that contains the name of the database, the userid, and password.
-
You create a Request object that
contains the SQL retrieval statement.
-
You create a RelationView object
that represents the current row in the database query result set.
-
On each dbAware field, you invoke its setBinding
method to bind it to the RelationView
object using a string representing the corresponding SQL column name.
-
dbAnywhere calls the getData and SetData
methods of your component as necessary to keep the screen and database
in sync. Normally only Strings and binary StringBufferInputStreams are
passed as Objects since the generic BeanHelper
code can only deal with those two types. BeanHelper just asks for any integer
values to be handed over in String format.
-
Your component contains a pointer to a ProjBinder
or ListBinder object which ties the dbAware
component and a column (projection) of the RelationView (row) together.
- It appears not to be necessary for the dbAware component to
inform the database every time the value on the screen changes.
Behind the scenes, the BeanHelper
compares database and screen values to detect changes, and calls
getData and setData as needed. It looks as if some data
changes are detected by hooking up to various event listeners on the
dbAware component.
- Unfortunately, if you want to change the WHERE or ORDER BY
clause, you have to start over almost from scratch, setting the SQL
of the Request, creating a new RelationView and Arggh! rebinding all
the dbAware components.
- You can learn quite a bit about how the dbAware components work
by turning on logging in dbAnywhere. They you can watch the SQL
queries and updates going in to the database, though you cannot see
the results coming back.
-
You use methods like RelationView.prev
and next. Automagically the screen
fields are updated and any keyed changes are recorded. You can scroll back
and forth over the result set making changes to various records. The changes
are not committed until you call RelationView.saveMultiView.
-
There is a special screen variable called a RecordStateLabel
that displays whether the current record has been modified, and a RecordNumberLabel
that displays the index of the record in the result set.
-
SQL lets you either commit or rollback the database changes done during
a transaction. All the RelationViews involved in a transaction are lumped
together under a MultiView. This way if you decide to rollback the transaction,
all the corresponding screen fields can be found and updated. In Symantec
terminology, commit is called saveMultiview and rollback is called undoRecord.
-
dbAware components work quite differently than they are described in the
Visual Cafe Pro help. dbAware components are derived from their non-dbAware
versions, and contain a ProjectionBeanHelper
m_Helper field to handle communication with dbAnywhere. The dbAware component
needs to implement the ProjectionBean interface,
which typically does the bulk of its work by invoking the methods of m_Helper.
The ProjectionBean interface defines only
a few methods, yet a great many standard methods need to be implemented
for the component to be dbAware.The two main methods of the interface as
are getData and setData.
The component itself is no longer responsible for notifying dbAnywhere
when the value changes or even of tracking if the screen value has changed
via keystroke activity. dbAnywhere will invoke theComponent.getData or
setData when it wants to using the ProjectionBeanHelper object which has
a pointer to the component itself. This is quite a simplication for dbAware
components over JDK 1.0.2.
Credits
I would particularly like to thank Ron Emrick of Symantec Internet tools
for his various emails and posts. My buddies on BIX.com helped a lot too:
Bruce Stewart, Andrew Langmead, Bob Friesenhahn, Dana Hudes, Bill Clardy,
Michael Pepplar and Roy Harvey.