dbAnywhere : Java Glossary
I have left this tombstone entry for historical interest.
This product is now defunct. Symantec’s software to allow a client program to
run with a single set of JDBC (Java Data Base Connectivity)
drivers and simultaneously access SQL (Standard Query Language)
databases from several different vendors. dbAnywhere also allows ordinary
Java-enabled web browsers with no installed JDBC
drivers to access an SQL
database. The dbAnywhere client JDBC
drivers are pure Java, and thus can be downloaded without violating Applet security.
It is a bitch to install because there are so many errors in the readme files.
Symantec is phasing out dbAnywhere. They do longer ship it with Visual Café
database edition. They suggest using plain JDBC
instead. Unfortunately for me, I built my business SQL
classes around it. Some day I will have to sort over.
How To Install
I spent 7 days trying install dbAnywhere under Windows-95
and JDK (Java Development Kit) 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
Unfortunatey dbAnywhere has been discontinued. Visual Cafe now uses the standard
less intelligent JDBC
The 21 Steps to Bliss
- Edit (or create) C:\Windows\hosts.
Add a line
127.0.0.1 localhost # dummy address for local machineSee C:\Windows\hosts.
for more information on the tab-delimited HOSTS file. Under NT it stored in
Also create entries for any computers on your local LAN (Local Area Network)
don’t have permanent IP (Internet Protocol) addresses like this:
A file like this should be in each machine on the
# Other computers 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
- Install Visual Café, Sybase
Anywhere, dbAnywhere in that order. Optionally install
the other goodies. You will only need the Win32 drivers for
Anywhere, none of the C++ stuff, and only the
ODBC (Open Data Base Connectivity) 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\
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 (Compact Disc)
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.
- There should be no need to modify your
file, the way you had to previously. There are now
fewer files needed on the CLASSPATH, and the
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]If Café crashes immediately after loading, chances are the
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
directory. I renamed the
kl group directory to klg
to avoid complications with embedded spaces in the name.
version=1.00 Build 4
; CLASSPATH must actually be all on one long
unreadable line. Phht!
- Optionally get the latest version of the Microsoft
ODBC documenatation. The SDK (Software Development Kit)
contains documentation that will help you understand ODBC.INI, ODBCINST.INI and
api. This will come in handy later when you try to understand the
api which is modelled on ODBC.
- Run the Sybase 32-bit ODBC
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:
You can optionally leave the entire connection section as the
Data Source Name: osademo
description: Sybase SQL
Anywhere Sample Data
user ID: dba
server name: sademo
database name: sademo
database file: C:\sqlany\sademo.db
start command: C:\sqlany\win32\dbeng50
database switches: __
translator name: <no translator>
- Optionally run C:\Windows\RegEdit.exe
to have a peek at the registry to make sure all is set up correctly with
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]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
osademo = Sybase SQL
AutoStop = yes
Databasefile = C:\sqlany\sademo.db
DatabaseName = sademo
Description = Sybase SQL
Anywhere Sample Database
Driver = C:\sqlany\win32\wod50t.dll
EngineName = sademo
(Print Working Directory
) = sql
Start = C:\sqlany\win32\dbeng50
UID = dba
- Optionally, use C:\Windows\RegEdit.exe
to check that under HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI you
[ODBC Drivers]Some of this information will also be echoed in the vestigial
[Sybase SQL Anywhere 5.0]
[Sybase SQL Anywhere 5.0 Translator]
- Configure dbAnywhere (
) 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
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
. The dbawdsn.ini file now
lives in C:\dbAny\bin\
. You no longer have to tweak
DSNTOOL.BAT to get it to work. Run
to create the
file. Click NEW. Fill in:
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
Selected Data Source:
Description: Sybase SQL
Anywhere Sample Database
Engine: Sybase SQL
AnyWhere 5+ via ODBC32
(second) datasource field: osademo
dbAnywhere Server URL
(Uniform Resource Locator
- Click Save. The generated
file should look like this:
[dbANYWHERE Data Sources]It is often easier just to compose the
file with a text editor.
Anywhere Sample Database
- In Win95, click Control Panel ⇒ Network ⇒
Configuration ⇒ TCP/IP (Transmission Control Protocol/Internet Protocol) ⇒ Properties ⇒
DNS (Domain Name Service) Configuration ⇒ Disable
DNS . This
does not turn off DNS totally, just Domain Name Service lookup for the
local machine. In NT, click Control Panel ⇒ Network ⇒
protocols ⇒ TCP/IP
⇒ properties ⇒ DNS ⇒ turn off DNS
you have a domain name server operating all the time your
LAN apps are
- In Win95, click 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
- Start the Sybase SQL Anywhere sample database server (
). I have seen Visual Café indirectly fire up SQL
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
engine is functioning by running SQL
and checking out the table structure of the sample database. It works independently
of dbAnywhere, ODBC
- Start dbAnywhere (
- 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.
- 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 (Data Set Name)
Anywhere. Alternatively you can use a name of the form form xxx for your
sqlAnywhere database, oxxx for the
to it, jxxx for the JDBC
hook to it, and cxxx for the Java connection to
- How do you configure things when the database is on a
Then, the machine hosting the dbAnywhere software will have a permanent
(192.168.0.x) address. You use that IP
in your jdbc :dbaw://192.168.0.1:8889/
use names, add these IP
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
Name Service). You might use dbAnywhere by itself, or combine it with the
HTTP (Hypertext Transfer Protocol) Netscape
Server packed with Visual Café. You could use either the dotted quad (e.g.
j dbc:dbaw://22.214.171.124:8889/) or the name (
jdbc:dbaw://www.mindprod.com:8889/) in your accessing
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.
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
- For NT you start
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 10000Kthe -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
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
|CREATE TABLE PEOPLE|
||integer NOT NULL,|
||char(30) NOT NULL,|
||char(30) NOT NULL,|
|PRIMARY KEY (ACCT));|
- You can then check the structure of your database by connecting with
or by manually entering an
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
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;
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
JSQL (Java Standard Query Language). There is considerable
detailed documentation, but no overview of how it all fits together.
- You create a Session object that contains the
- You create a ConnectionInfo object that contains the
name of the database, the userid, and password.
- You create a Request object that contains the
- 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
- 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
- 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 into the database, though you cannot see the results
- 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
- 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
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
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.