## RemoteDB Gateway Version 2.3 ### Table Of Contents
| Overview Features Usage Scenarios Installation User Guide
| Using the Server Using the JDBC Driver Using the ODBC Drivers
| DAO Example ADO Example ODBC API Example
Registration Distribution Contacting Nogginware
### Overview RemoteDB Gateway is a SHAREWARE program that lets Java and native Windows applications access ODBC data sources configured on a remote computer. The package consists of a JDBC driver and 16-bit and 32-bit ODBC drivers that communicate with a 32-bit server process running under Windows NT/95/98. The server process provides access to the full array of ODBC data sources that have been configured on the server machine. The architecture is depicted by the following diagram. The RemoteDB Gateway JDBC driver is a Type 3 driver written using "pure" Java allowing it to run on virtually any platform. Java applications or applets that utilize JDBC for database access can now exploit the capabilities of RemoteDB Gateway to access remote ODBC data sources. In addition, Windows applications that have been designed to work with a particular data source can now access that same data source from a remote computer. It is as simple as configuring a data source on the remote computer to use either the REMOTE16 or REMOTE32 ODBC driver. Only three configuration options are required: 1) a local data source name, 2) the remote host name or IP address of the machine where the RemoteDB Gateway server process is running, and 3) the remote data source name. ### Features RemoteDB Gateway includes the following set of major features. 32-bit multithreaded server capable of running on Windows NT/95/98. JDBC driver fully compliant with JavaSoft JDBC 1.20 specification. 16-bit and 32-bit ODBC drivers capable of running on Windows 3.x/NT/95/98. Intelligent caching improves performance by minimizing network delays. Server can optionally be run as a service on Windows NT. Support for ODBC 1.0, 2.x, and 3.x drivers. Various licensing options. Unlimited support. Free upgrades. ### Usage Scenarios RemoteDB Gateway can be utilized in any circumstance where remote access to ODBC data sources is required. The following outlines two possible scenarios where RemoteDB Gateway can be employed. Consider accessing a single-tier data source (e.g., Microsoft Access, dBase IV, Btrieve, Excel, Text, etc.) across a slow link such as a 28.8 kbps Remote Access Service (RAS) connection. Certain database operations can chew up unnecessary bandwidth by performing multiple seeks and reads of the underlying database files. Why pay these enormous performance penalties when you can use RemoteDB Gateway to perform lengthy searches remotely and limit the amount of traffic on the underlying network link? Consider accessing a two-tier data source (e.g., Oracle, Informix, Sybase, Microsoft SQL Server, etc.). Accessing these data sources requires the licensing, installation and configuration of client-side software which provides access to the target data source. Examples include licensing an ODBC driver from a vendor such as Intersolv, installing and configuring SQL*Net to access an Oracle database, installing and configuring Open Client to access a Sybase database, and installing and configuring I-Net to access an Informix database. Why be faced with the daunting challenge of administering each of these components on every client machine when you can use RemoteDB Gateway and limit administration to the server machine? The possibilities for RemoteDB Gateway are by no means limited to the scenarios described above. In general, RemoteDB Gateway makes very efficient use of the underlying network. As a result, RemoteDB Gateway provides outstanding performance across networks that suffer from significant round-trip delays (e.g., the internet) as well as local area networks. ### Installation RemoteDB Gateway is distributed in a single ZIP file. This archive contains the following files:
| INSTALL.EXE | - Master installation program.
| LICENSE.HTM | - Software license agreement.
| REGISTER.HTM | - Information on registering RemoteDB Gateway.
| REMOTEDB.HTM | - This file - general information.
| IMAGES\ | - Directory containing images referenced by HTML files.
| ODBC16\ | - Directory containing setup for 16-bit ODBC driver.
| ODBC32\ | - Directory containing setup for 32-bit ODBC driver.
| SERVER\ | - Directory containing setup for RemoteDB Gateway server.
The master installation program, INSTALL.EXE, provides a convenient way to launch the setup programs for each of the individual RemoteDB Gateway components. See the following paragraphs for more information on the setup for each component. The RemoteDB 16-bit ODBC driver can be installed on Windows 3.x, 95, 98, or NT. To install the driver, run the SETUP.EXE program in the ODBC16\ directory. The RemoteDB 32-bit ODBC driver can be installed on Windows 95, 98 or NT. To install the driver, run the SETUP.EXE program in the ODBC32\ directory. The RemoteDB Gateway server can be installed on Windows 95, 98 or NT. To install the server, run the SETUP.EXE program in the SERVER\ directory. The setup program copies files onto your hard disk and creates a group icon that allows you to run the server software. Clicking on the icon will bring up a graphical user interface that allows you to monitor current activity on the RemoteDB Gateway server. When the RemoteDB Gateway server is installed for the first time, a license key will automatically be generated that grants the user a 30-day evaluation license to use the software for a single client. To enable additional concurrent clients, the software must be registered and a license must be purchased from the author. Please see the section entitled "Registration" for further details. When installing the server software on Windows NT, the setup program will provide you with the option of installing the server as an NT service. The default startup type for the service is set to Manual. To make the NT service automatically start when the system boots, you will need to select the Services option in the Control Panel and set the startup type for the RemoteDB Gateway Server service to Automatic. ### User Guide #### Using the Server The RemoteDB Gateway server will run on Windows NT, 95 or 98. The server includes a graphical user interface that allows you to configure the server and view the status of current connections. The graphical user interface can be launched by clicking on the RemoteDB Gateway icon that is created during installation. To configure the RemoteDB Gateway Server, select the "File|Options..." menu item. This brings up a dialog that contains the available program options. These include: Port Number - The TCP port number that the server will listen on for incoming client connections. The default is 1800. License Key - The license key that controls the total number of concurrent client connections. Please see the section entitled "Registration" for licensing options. Log Server Activity - A check box that controls logging activity by the server. Check this box to diagnose any problems that might be occurring at the server. Log Filename - The name of the file to be used for logging. Refresh Delay - The delay (in seconds) between successive status window refreshes. The graphical user interface also permits viewing the status of current connections to the server. The connections are displayed in a list view containing the following columns. Data Source Name - The data source name that is currently being utilized by this client on the server. Remote Host - The IP address of the client's machine. Elapsed Time - The total time that has elapsed during the client's connection to the server. Bytes Rcvd - The total number of bytes received from the client. Bytes Sent - The total number of bytes sent to the client. A specific connection can be terminated by highlighting the connection in the list and selecting the "Disconnect..." menu item from either the "User" menu or the context menu that appears when the right mouse button is held down. The RemoteDB Gateway server is capable of using any ODBC data source that has been configured on the server machine. To configure an ODBC data source on the server machine, run the Control Panel application and select the ODBC icon. Follow these steps to configure a new data source. Select either the "User DSN" or "System DSN" tab from the ODBC Data Source Administrator window. IMPORTANT: If you are running the RemoteDB Gateway server as an NT service, you must select the "System DSN" tab. Choose the button labelled "Add..." to bring up a dialog box that allows you to create a new data source. Select a driver from the list of available ODBC drivers. This should bring up a setup dialog for the selected driver that allows you to configure the data source name and driver parameters. Enter the driver parameters and click "OK" to complete. See your ODBC driver vendor's documentation for more information. Once one or more ODBC drivers have been setup on the server, a client application utilizing either the RemoteDB Gateway JDBC or ODBC driver can now access the data source via the server. #### Using the JDBC Driver The JDBC driver classes are installed as part of the RemoteDB Gateway Server installation. The classes that make up the JDBC driver are part of a Java package "com.nogginware.remotedb". To use the JDBC driver, the class files must be referenced by the CLASSPATH environment variable. This can be acheived by either adding the installation directory path
| C:\Program Files\Nogginware\RemoteDB Gateway\java
to the CLASSPATH environment variable or physically copying the class files from their installation directory to a directory being referenced by the CLASSPATH. The main driver class which implements the java.sql.Driver interface is found in the "com.nogginware.remotedb.Driver" class. Your Java program must explicitly load this class by executing the following Java statement.
| Class.forName("com.nogginware.remotedb.Driver");
This statement will cause the JDBC driver to be registered with the JDBC DriverManager. Once the driver has been loaded, connections can be made to the database by invoking the DriverManager.getConnection() method. The URL that is passed to DriverManager.getConnection() must conform to the following syntax.
| jdbc:remotedb://host[:port]/dsn[/parameter-settings]
Items enclosed in square brackets ([]) are optional. In its simplest form, a URL of the form "jdbc:remotedb://marvin/test" instructs the JDBC driver to connect to the RemoteDB Gateway server running on the host "marvin" and use the ODBC data source name "test" that has been configured on that server. If a port is not specified, it is assumed to be port 1800. In a more complex form, it is possible to set various parameters associated with the target ODBC data source. For example, the URL "jdbc:remotedb://wizard:2371/accounting/TIMEOUT=30" will instruct the JDBC driver to connect to the host "wizard" at port 2371, use the ODBC data source name "accounting", and pass the parameter "TIMEOUT=30" to the specified data source. An abbreviated example of the statements needed to connect to a database is presented below.
| String url = "jdbc:remotedb://localhost/test"; String username = "my-username"; String password = "my-password"; Class.forName("com.nogginware.remotedb.Driver"); Connection c = DriverManager.getConnection(url, username, password);
The "C:\Program Files\Nogginware\RemoteDB Gateway\java\samples" directory contains sample code for the following two Java applications.
| ConnectTest | - Attempts a connection to a specified URL.
| SimpleTest | - Connects to a database and issues simple statements.
4. - These samples demonstrate the use of JDBC and, more importantly, how connections to the RemoteDB Gateway server are made. They can also be used to test whether or not the software has been properly installed. #### Using the ODBC Drivers To have a Windows application remotely connect to an ODBC data source via the RemoteDB Gateway server, the appropriate RemoteDB Gateway ODBC driver (16-bit or 32-bit) must be installed and configured on the client machine (i.e., the same computer on which the application is to be run). To configure the RemoteDB Gateway ODBC driver on the client machine, run the Control Panel application and select the ODBC icon. Follow these steps to configure a new data source. Select either the "User DSN" or "System DSN" tab from the ODBC Data Source Administrator window. Choose the button labelled "Add..." to bring up a dialog box that allows you to create a new data source. Select the RemoteDB Gateway Driver from the list of available ODBC drivers. This will bring up the RemoteDB Gateway Setup dialog that allows you to configure the driver parameters. Enter the following parameters and click "OK" to complete. Data Source Name - The name of the data source to be used by the Windows application. Description - Arbitrary text that describes the data source. Remote Host Name - A string of the form "host:port" which contains 1) the host name of the computer that is running the RemoteDB Gateway server and 2) an optional port number that specifies the TCP port on which the server is listening for client connections. The port number defaults to 1800 if not specified. If the server is running on the same machine as the client application, specify "localhost" for the host name. Otherwise, specify the host name or the IP address (e.g., 198.98.0.62) of the server machine. Examples of valid entries for this field include the following:
     localhost | - connects to local host port 1800
     localhost:2417 | - connects to local host port 2417
     wizard | - connects to host "wizard" port 1800
     wizard:3712 | - connects to host "wizard" port 3712
     198.98.0.62 | - connects to 198.98.0.62 port 1800
     198.98.0.62:1317 | - connects to 198.98.0.62 port 1317
Remote Data Source - The data source name on the server that will ultimately be used. As an alternative, the Remote Host Name and Remote Data Source can be specified at runtime via the RemoteHost and RemoteDSN keywords in a connection string. For example, let's suppose we have defined a data source name called "TestDB" which is configured to use the RemoteDB Gateway ODBC driver and that the Remote Host Name and Remote Data Source are not known until runtime. A connection string such as the following can be specified.
| DSN=TestDB;RemoteHost=wizard:3712;RemoteDSN=MyDB
This connection string instructs the RemoteDB Gateway ODBC driver to connect to the host named "wizard" at TCP port number 3712 and open the data source name "MyDB" on the specified host. Note that if the remote data source requires additional parameters such as a username password (e.g., specified via the UID and PWD keywords), these keywords can be included on the connection string. As an example, consider the following connection string.
| DSN=TestDB;RemoteHost=wizard:3712;RemoteDSN=MyDB;UID=admin;PWD=barney
This connection string causes a connection to be made to the remote data source named "MyDB" providing a username of "admin" and a password of "barney". ##### DAO Example The most general and widely used out-of-the-box database access method in Visual Basic 5.0 and Visual C++ 5.0 is Data Access Objects (DAO). DAO is simple and provides a convenient means to access databases via ODBC. The following Visual Basic example demonstrates the use of DAO to open an ODBC data source named TestDB and perform a query on a table residing in the database.
| Dim db As Database Dim rs As Recordset ' Open the database. Set db = OpenDatabase("TestDB", dbDriverNoPrompt, False, "ODBC;DSN=TestDB") ' Execute a query on the database. Set rs = db.OpenRecordset("select * from test", dbOpenDynamic, 0, dbOptimistic) ' Fetch each row in the Recordset. rs.MoveFirst While Not rs.EOF
| rs.MoveNext
Wend ' Close the Recordset. rs.Close ' Close the Database. db.Close
Since a Workspace object was not utilized in the previous example, the call to OpenDatabase opens the specified data source using the default DAO Workspace. This is equivalent to executing the following code.
| Dim ws As Workspace Dim db As Database ' Open the database. Set ws = Workspaces(0) Set db = ws.OpenDatabase("TestDB", dbDriverNoPrompt, False, "ODBC;DSN=TestDB")
The default workspace makes use of the Microsoft Jet database engine. Using the Microsoft Jet database engine can have a serious impact on application performance. In addition to its size, the Microsoft Jet Engine has a drawback of opening multiple connections to the underlying database. This introduces a great deal of inefficiencies and its use is not recommended with RemoteDB Gateway. Instead of the default workspace, we recommend the use of ODBCDirect workspaces. With an ODBCDirect workspace, your application has complete control over the number of connections that can be made to the underlying data source. The following example illustrates the creation and utilization of an ODBCDirect workspace.
| Dim ws As Workspace Dim dc As Connection Dim rs As Recordset ' Create an ODBCDirect workspace. Set ws = CreateWorkspace("MyWorkspace", "", "", dbUseOdbc) ' Open a connection to the database. Set dc = ws.OpenConnection("TestDB", dbDriverNoPrompt, False, "ODBC;DSN=TestDB") ' Execute a query on the database. Set rs = dc.OpenRecordset("select * from test", dbOpenDynamic, 0, dbOptimistic) ' Fetch each row in the Recordset. rs.MoveFirst While Not rs.EOF
| rs.MoveNext
Wend ' Close the Recordset. rs.Close ' Close the connection to the database. dc.Close ws.Close
In this example, note the usage of a DAO Connection object. The Connection object provides the application with the means to explicitly control the number of database connections being made. For more information on ODBCDirect workspaces, please consult the Visual Basic 5.0 online help. ##### ADO Example ActiveX Data Objects (ADO) provide yet another means to access databases from application programs written in Visual Basic, Visual C++, VBScript, or JavaScript. The following Visual Basic example demonstrates the usage of ADO.
| Dim con As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset ' Open a connection to the database. Set con = New ADODB.Connection con.ConnectionString = "DSN=TestDB" con.Open ' Create a Command object. Set cmd = New ADODB.Command Set cmd.ActiveConnection = con ' Execute a query on the database. Set rs = New ADODB.Recordset rs.Open "select * from test", con, adOpenDynamic, adLockOptimistic ' Fetch each row in the Recordset. rs.MoveFirst While Not rs.EOF
| rs.MoveNext
Wend ' Close the Recordset. rs.Close ' Close the connection to the database. con.Close
For additional information on programming with ADO, please consult the help files included with the ADO installation. ##### ODBC API Example The ODBC API provides another means by which to access databases. In general, the ODBC API is used primarily by applications written in C/C++. The following example demonstrates a simple program written in C++.
| static HENV henv; static HDBC hdbc; static HSTMT hstmt; static char szConnStrIn[] = "DSN=TestDB;UID=admin;PWD=barney"; static char szConnStrOut[256]; void main(int argc, char **argv) {
| RETCODE rc; // Connect to the database. rc = SQLAllocEnv(&henv); HandleRetcode(rc, "SQLAllocEnv"); rc = SQLAllocConnect(henv, &hdbc); HandleRetcode(rc, "SQLAllocConnect"); rc = SQLDriverConnect(hdbc, NULL, szConnStrIn, SQL_NTS, szConnStrOut,
| sizeof(szConnStrOut), NULL, SQL_DRIVER_COMPLETE);
HandleRetcode(rc, "SQLDriverConnect"); // Execute a query on the database. rc = SQLAllocStmt(hdbc, &hstmt); HandleRetcode(rc, "SQLAllocStmt"); rc = SQLExecDirect(hstmt, "select * from test", SQL_NTS); HandleRetcode(rc, "SQLExecDirect"); for (;;) {
| rc = SQLFetch(hstmt); if (rc == SQL_NO_DATA_FOUND) break; HandleRetcode(rc, "SQLFetch");
} SQLFreeStmt(hstmt, SQL_DROP); HandleRetcode(rc, "SQLFreeStmt"); hstmt = SQL_NULL_HSTMT; // Disconnect from the database. rc = SQLDisconnect(hdbc); HandleRetcode(rc, "SQLDisconnect"); rc = SQLFreeConnect(hdbc); HandleRetcode(rc, "SQLFreeConnect"); hdbc = SQL_NULL_HDBC; rc = SQLFreeEnv(henv); HandleRetcode(rc, "SQLFreeEnv"); henv = SQL_NULL_HENV;
} void HandleRetcode(RETCODE rc, CHAR *szFunc) {
| UCHAR szSqlState[6]; SDWORD fNativeError; UCHAR szErrorMsg[256]; SWORD cbErrorMsg; RETCODE retcode; if (rc == SQL_SUCCESS || rc == SQL_NO_DATA_FOUND) return; for (;;) {
| // Fetch the next error message from the data source. retcode = SQLError(henv, hdbc, hstmt, szSqlState, &fNativeError,
| szErrorMsg, sizeof(szErrorMsg), &cbErrorMsg);
if (retcode != SQL_SUCCESS) break; printf("SQLException (%s)\n", szFunc); printf(" State: %s\n", szSqlState); printf(" Error Code: %ld\n", fNativeError); printf(" Error Msg: %s\n", szErrorMsg);
} if (rc == SQL_ERROR) exit(-1);
}
This example does not address issues such as column or parameter binding. For more details on the ODBC API, please consult the ODBC API Reference which ships with the Platform SDK. ### Registration Various licensing options are available. Licenses are sold based on a concurrent usage model and range anywhere from a single user license to an unlimited user license. Each client that establishes a connection to the server counts as a user. Therefore if a client machine establishes three connections to the server, this will count as three users against the license. To purchase a license, please complete the Registration Form and mail the completed form along with payment directly to Nogginware Corporation. For some of our larger customers, purchase orders will be accepted. Once a completed registration form and accompanying payment have been received, a license key will be distributed that will enable the software for the specified number of concurrent users. By purchasing a license, you agree to abide by the terms of the License Agreement. Registering the software entitles you to unlimited support and free upgrades to the product as they become available. As a registered user, you will be notified via E-mail when upgrades are available for download. Your software license key will continue to work with subsequent releases of RemoteDB Gateway. For those interested in reselling RemoteDB Gateway, OEM and reseller arrangements will be evaluated on a case-by-case basis. Please contact us for more details. ### Distribution RemoteDB Gateway may be freely distributed with the following conditions: All files in the ZIP file should be included in distributions. The software must be identified as SHAREWARE in any distribution. ### Contacting Nogginware Address questions, problems, or praise to:
E-mail: |  | info@nogginware.com
U.S. mail: |  | Nogginware Corporation 2506 Branding Iron Ct. Herndon, VA 20171
(c) 1999 Nogginware Corporation. All rights reserved.