How To Install
Symantec dbAnywhere
with
Visual Café Database Development Edition 2.5a
Last updated by Roedy Green
©1997-1999 Canadian Mind Products.
Stuck in a frame? Click here to break out.
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é Database Development Edition 2.5a.
The 21 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 C:\Winnt\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 Carol
A file like this should be in each machine on the LAN.
- Save a copy of your current C:\vcp\bin\sc.ini and
c:\dbany\bin\dbawdsn.ini. When you reinstall, these files
will be lost. You can then tweak these backups and reinstall rather than
starting your customizations from scratch.
- 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
Café. Use the registry editor to clean out any old Visual
Café or bBbAnywhere entries. Search for string of the form
C:\dbany, C:\sqlany, C:\vcp or wherever you put the files last time. The
uninstall does not get them all, and reinstall will trip over them if you
use a different name than last time.
- Install Visual Café, 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 SQLAnywhere drivers. Don't UNZIP any of the
ZIP or JAR files! Don't install over top of previous installs into the
same directories or you will have old files lying about. I suggest using
directories C:\vcp, C:\sqlany, C:\dbany. Watch
out! Symantec periodically changes the names of its directories. All your
bat file will stop working if you go with the defaults. Watch the
installs. They are sneaky and will change the names on you or put them on
the wrong drives if you are not careful. Reboot.
- Use the Live Update feature of dbAnywhere and Visual
Café to get the latest versions. Alternatively you may need to
get the latest by going to
cafe.symantec.com (not
www.symantec.com) and looking under downloads. You will need your CD
keys to get in. They make you type the second line blind as a sort of
competence test to see if you have sufficient Darwinian fitness to be
granted the update. Hint: watch the case! To save yourself frustration, I
suggest cut and paste from a document where you keep all your CD keys.
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. There
are different updates for Sybase SQLAnywhere depending on whether
you are using Win95 or NT. The latest Sybase updates are at www.sybase.com (not cafe.symantec.com). Sybase posts
various old versions as well, so be sure to get the latest, currently
5.5.04. Reboot.
- There should be 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 long unreadable line. Phht!
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\bin\components\databind.jar;
c:\vcp\jfc\swingall.jar;
c:\vcp\klg\klg.jar;
If Café crashes immediately after loading, chances are the
C:\vcp\bin\SC.INI has a bad directory on the classpath, or
the SET classpath has an invalid entry. The other cause is a damaged
project file. To clear Café's mind of a damaged project that delete
any *.rps, *.vws or *.reg files in the C:\vcp\bin directory. I renamed the
"kl group" directory to "klg" to avoid complications with embedded spaces
in the name.
- 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. Alternatively, you can fire it up from the control
panel. 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 or
HKEY_CURRENT_MACHINE\Software\ODBC\ODBC.INI if you made a system wide entry.
[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 Win95, 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 NT, in Control Panel / Network / protocols / TCP/IP /
properties / DNS turn off DNS unless you have a domain name server
operating all the time your LAN apps are running.
- In Win95, 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 Café 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 Café, 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 Café, 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
(192.168.0.x) address. You use that IP in your jdbc:dbaw://192.168.0.1:8889/
URL. To use names, add these IPs to your HOSTS file. If you are developing on
a single machine you can use the localhost IP, 127.0.0.1.
- 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.com,
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 Café. You could use either the the dotted quad (e.g. jdbc:dbaw://209.153.248.8:8889/)
or the name (jdbc:dbaw://jdbc:dbaw://www.mindprod.com: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.
- For NT you start the database like this:
start "sqlany" dbeng50.exe C:\MyDir\mydatabase.db -b -c 17000
For Win95 you start the database engine like this:
dbeng50.exe C:\MyDir\mydatabase.db -b -c 10000K
The -b option turns off rollback and recovery providing a faster database
load.
- 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.
- 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 you can feed the SQL transactions in en masse with ISQL. You would use an ISQL command something like this to load
a comma delimited file into your database.
LOAD TABLE PEOPLE FROM 'C:\MyOld\PEOPLE.LOD';
You can then check that the data actually loaded with statements like this:
SELECT COUNT(*) FROM PEOPLE;
or
SELECT * FROM PEOPLE;
- 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 there was a minor 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 Café 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.