Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|[Home]||[Back to Tip 84]||[Forward to Tip 86]||[Archives]|
Question: Can Java code running in one database connect to a different database?
Yes, via JDBC and jConnect as the following crude "cross-server query" illustrates. Figure 1 shows a result set where product id and unit sales were retrieved from one database, and the matching title was retrieved from another database on a different computer, all with a single SELECT statement run via DBISQL.
Figure 1: Cross-Server Query
Let's not kid ourselves, it's not a simple SELECT by any stretch of the imagination. It looks weird, and there's some very strange code running under the covers. But it works, and works quite quickly, so even though no one in their right mind would write production code that looks exactly like this it's worth examining to see how it works.
Figure 2 shows the pseudo-code for the SELECT in Figure 1. Here's the situation:
Figure 2: Simple Pseudo-Code For The Cross-Database Query
Figure 3 shows the same pseudo-code, expanded with what looks like a correlated subquery.
Figure 3: Expanded Pseudo-Code For The Cross-Database Query
Unlike Adaptive Server Enterprise, ASA SQL always executes within the context of a single database. You cannot code "FROM database2.DBA.product" in ASA, and that means no cross-database joins and no cross-database subqueries...
...unless... unless we code the subquery as a call to a Java method, and let the Java code make the connection and execute the subquery. That's what the ASAConnection class is for, with its method selectSingleString as described in Figure 4.
Figure 4: selectSingleString Method [ASAConnection]
The ASAConnection class also encapsulates the connection to the other database, plus a method that actually makes the connection via jConnect. This allows the user to make a single connection followed by repeated queries rather than having to endure the delay caused by repeated connections.
Figure 5 shows the SQL required to create an object of type ASAConnection and actually make the connection to the other database. This is actual SQL, not pseudo-code. You run it once via DBISQL before running the SELECT shown in Figure 1.
Figure 5: Making A Cross-Database Connection
The first statement in Figure 5 creates an SQL variable of type ASAConnection called asaConnection. This variable will last as long as the connection to the current database lasts. Note that there are two connections being discussed here: DBISQL's connection to the current database, and the Java code's connection to the other database.
The second statement in Figure 5 creates a new object of type ASAConnection and assigns it to the variable asaConnection. The default constructor ASAConnection() is used to do this.
The last statement in Figure 5 makes a call to the setConnection method, passing it the machine id, port number, user id and password. This call will make the actual connection to the other server.
Figure 6 shows the complete Java code for the ASAConnection class. Each section of code is numbered 1, 2, 3, .., and explained in the text that follows Figure 6. You can click on the highlighted links to jump back and forth between Figure 6 and the explanations.
Figure 6: ASAConnection.java
A key feature of the ASAConnection class is the private variable connection of type java.sql.Connection. When an object of type ASAConnection is created, the encapsulated connection variable is also created, and it will last as long as the ASAConnection object. That's why only one connection to the other server is required.
The default constructor initializes the private variable connection to null, and then loads the Sybase jConnect driver. Most of this code for making a connection has been copied from Tip 84 - JDBC and jConnect.
The setConnection method is called when a connection to the other server is required. It's called "setConnection" because that's what it does: sets the private variable called connection to point to the other server.
For a description of the machine, port and other parameters, see Tip 84 - JDBC and jConnect.
The call to getConnection makes the actual connection, and setAutoCommit( false ) lets the Java code control COMMIT and ROLLBACK logic.
For debugging purposes the setConnection method displays the connection parameters for the other computer. This output will appear in the DBSRV6 console window for the server that DBISQL's connected to directly; i.e., this computer, not the other one.
The selectSingleString method calls createStatement to create an object of type java.sql.Statement. It then calls executeQuery to fill in the SQL SELECT for the subquery, execute the statement and return a result set.
The call to next positions the result set at the first row, and the getString call extracts the value for the named column from that row.
For debugging purposes the selectSingleString method displays the parameters and column value. It then returns that value to the caller which is the outer SELECT statement in Figure 1.
Figure 7 shows the SELECT from Figure 1, in a nice big font. Here are the key points:
Figure 7: The Cross-Server Query
Figure 8 shows the diagnostic output produced by setConnection and selectSingleString. The blue highlighted portion shows the subquery SELECT and return value for one row.
Figure 8: DBSRV6 Console Display
|[Home]||[Back to Tip 84]||[Forward to Tip 86]||[Archives]||[mail to: firstname.lastname@example.org]|