Set - 5

Question 6 :

What is the difference between client and server database cursors?

Answer :

What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.


Question 7 :

How can I pool my database connections so I don't have to keep reconnecting to the database?

Answer :

There are plenty of connection pool implementations described in books or availalble on the net. Most of them implement the same model. The process is always the same :
* you gets a reference to the pool
* you gets a free connection from the pool
* you performs your different tasks
* you frees the connection to the pool

Since your application retrieves a pooled connection, you don't consume your time to connect / disconnect from your data source. You can find some implementation of pooled connection over the net, for example:
* Db Connection Broker (http://www.javaexchange.com/), a package quite stable ( I used it in the past to pool an ORACLE database on VMS system)
You can look at the JDBC 2.0 standard extension API specification from SUN which defines a number of additional concepts.


Question 8 :

How can I connect to an Excel spreadsheet file using jdbc?

Answer :

Let's say you have created the following Excel spreadsheet in a worksheet called Sheet1 (the default sheet name). And you've saved the file in c:\users.xls.
USERID FIRST_NAME LAST_NAME
pkua Peter Kua
jlsmith John Smith
gh2312 Everett Johnson
chimera Faiz Abdullah
roy6943 Roy Sudirman

Since Excel comes with an ODBC driver, we'll use the JDBC-ODBC bridge driver that comes packaged with Sun's JDK to connect to our spreadsheet.
In Excel, the name of the worksheet is the equivalent of the database table name, while the header names found on the first row of the worksheet is the equivalent of the table field names. Therefore, when accessing Excel via jdbc, it is very important to place your data with the headers starting at row 1.
1. Create a new ODBC Data Source using the Microsoft Excel Driver. Name the DSN "excel", and have it point to c:\users.xls.
2. Type in the following code:

package classes;
import java.sql.*;

public class TestServer
{
static
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (Exception e) {
System.err.println(e);
}
}

public static void main(String args[]) {
Connection conn=null;
Statement stmt=null;
String sql="";
ResultSet rs=null;

try {
conn=DriverManager.getConnection("jdbc:odbc:excel","","");
stmt=conn.createStatement();
sql="select * from [Sheet1$]";
rs=stmt.executeQuery(sql);

while(rs.next()){
System.out.println(rs.getString("USERID")+
" "+ rs.getString("FIRST_NAME")+" "+
rs.getString("LAST_NAME"));
}
}
catch (Exception e){
System.err.println(e);
}
finally {
try{
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch(Exception e){}
}
}
}

Notice that we have connected to the Excel ODBC Data Source the same way we would connect to any normal database server.
The only significant difference is in the SELECT statement. Although your data is residing in the worksheet called "Sheet1", you'll have to refer to the sheet as Sheet1$ in your SQL statements. And because the dollar sign symbol is a reserved character in SQL, you'll have to encapsulate the word Sheet1$ in brackets, as shown in the code.