dbAnywhere : Java Glossary

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 2.5a.

Unfortunatey dbAnywhere has been discontinued. Visual Cafe now uses the standard less intelligent JDBC interface.

The 21 Steps to Bliss

1. Edit (or create) C:\Windows\hosts..
   Add a line
   127.0.0.1 localhost # dummy address for local machine
   See C:\Windows\hosts. 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 (Local Area Network) that don’t have permanent IP (Internet Protocol) 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.
2. 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.
3. 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.
4. 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 (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\, 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.
5. 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. Reboot.
6. 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.
7. 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 the ODBC api. This will come in handy later when you try to understand the JDBC api which is modelled on ODBC. Reboot.
8. 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.
9. 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 (Print Working Directory) = 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.
10. 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.
11. 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.
12. 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+ via ODBC32
    (second) datasource field: osademo
    Username: dba
    password: sql
    dbAnywhere Server URL (Uniform Resource Locator) : 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.
13. 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.
14. 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 unless you have a domain name server operating all the time your LAN apps are running.
15. 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.
16. To propitiate the installation gods, optionally sacrifice a small mammal and reboot.
17. 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.
18. Start dbAnywhere ( C:\dbAny\bin\dbawsrvr.exe).
19. 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.
20. 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.
21. 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

1. 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) 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.
2. 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 IP to your HOSTS file. If you are developing on a single machine you can use the localhost IP, 127.0.0.1.
3. 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 (Hypertext Transfer Protocol) Netscape Server packed with Visual Café. You could use either the dotted quad (e.g. j dbc:dbaw://209.153.248.8:8889/) or the name ( jdbc:dbaw://www.mindprod.com:8889/) in your accessing URL.

Creating Your Own Database

1. For

   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.
2. 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 10000K
   the -b option turns off rollback and recovery providing a faster database load.
3. 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.
4. You can then check the structure of your database by connecting with SQL Central, or by manually entering an

   SELECT table_name from SYS.SYSTABLE

5. Then you can feed the SQL transactions in en masse with ISQL. You would use an ISQL command something like this

   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;

6. 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.
7. 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 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 (Java Standard Query Language). There is considerable detailed documentation, but no overview of how it all fits together.

1. You create a Session object that contains the URL to access dbAnywhere.
2. You create a ConnectionInfo object that contains the name of the database, the userid and password.
3. You create a Request object that contains the SQL retrieval statement.
4. You create a RelationView object that represents the current row in the database query result set.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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.
10. 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 coming back.
11. 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.
12. 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.
13. 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.
14. 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