Set - 2

Question 11 :

How do I implement a RowSetReader? I want to populate a CachedRowSet myself and the documents specify that a RowSetReader should be used. The single method accepts a RowSetInternal caller and returns void. What can I do in the readData method?

Answer :

"It can be implemented in a wide variety of ways..." and is pretty vague about what can actually be done. In general, readData() would obtain or create the data to be loaded, then use CachedRowSet methods to do the actual loading. This would usually mean inserting rows, so the code would move to the insert row, set the column data and insert rows. Then the cursor must be set to to the appropriate position.


Question 12 :

How can I instantiate and load a new CachedRowSet object from a non-JDBC source?

Answer :

The basics are:
* Create an object that implements javax.sql.RowSetReader, which loads the data.
* Instantiate a CachedRowset object.
* Set the CachedRowset's reader to the reader object previously created.
* Invoke CachedRowset.execute().

Note that a RowSetMetaData object must be created, set up with a description of the data, and attached to the CachedRowset before loading the actual data.
The following code works with the Early Access JDBC RowSet download available from the Java Developer Connection and is an expansion of one of the examples:
// Independent data source CachedRowSet Example
import java.sql.*;
import javax.sql.*;
import sun.jdbc.rowset.*;


public class RowSetEx1 implements RowSetReader
{
CachedRowSet crs;
int iCol2;
RowSetMetaDataImpl rsmdi;
String sCol1,
sCol3;


public RowSetEx1()
{
try
{
crs = new CachedRowSet();
crs.setReader(this);
crs.execute(); // load from reader

System.out.println(
"Fetching from RowSet...");
while(crs.next())
{
showTheData();
} // end while next

if(crs.isAfterLast() == true)
{
System.out.println(
"We have reached the end");
System.out.println("crs row: " +
crs.getRow());
}

System.out.println(
"And now backwards...");

while(crs.previous())
{
showTheData();
} // end while previous

if(crs.isBeforeFirst() == true)
{ System.out.println(
"We have reached the start");
}

crs.first();
if(crs.isFirst() == true)
{ System.out.println(
"We have moved to first");
}

System.out.println("crs row: " +
crs.getRow());

if(crs.isBeforeFirst() == false)
{ System.out.println(
"We aren't before the first row."); }

crs.last();
if(crs.isLast() == true)
{ System.out.println(
"...and now we have moved to the last");
}

System.out.println("crs row: " +
crs.getRow());

if(crs.isAfterLast() == false)
{
System.out.println(
"we aren't after the last.");
}

} // end try
catch (SQLException ex)
{
System.err.println("SQLException: " +
ex.getMessage());
}

} // end constructor



public void showTheData() throws SQLException
{
sCol1 = crs.getString(1);
if(crs.wasNull() == false)
{ System.out.println("sCol1: " + sCol1); }
else { System.out.println("sCol1 is null"); }

iCol2 = crs.getInt(2);
if (crs.wasNull() == false)
{ System.out.println("iCol2: " + iCol2); }
else { System.out.println("iCol2 is null"); }

sCol3 = crs.getString(3);
if (crs.wasNull() == false)
{
System.out.println("sCol3: " +
sCol3 + "\n" );
}
else
{ System.out.println("sCol3 is null\n"); }

} // end showTheData



// RowSetReader implementation
public void readData(RowSetInternal caller)
throws SQLException
{
rsmdi = new RowSetMetaDataImpl();
rsmdi.setColumnCount(3);
rsmdi.setColumnType(1, Types.VARCHAR);
rsmdi.setColumnType(2, Types.INTEGER);
rsmdi.setColumnType(3, Types.VARCHAR);
crs.setMetaData( rsmdi );

crs.moveToInsertRow();

crs.updateString( 1, "StringCol11" );
crs.updateInt( 2, 1 );
crs.updateString( 3, "StringCol31" );
crs.insertRow();

crs.updateString( 1, "StringCol12" );
crs.updateInt( 2, 2 );
crs.updateString( 3, "StringCol32" );
crs.insertRow();

crs.moveToCurrentRow();
crs.beforeFirst();

} // end readData



public static void main(String args[])
{
new RowSetEx1();
}

} // end class RowSetEx1


Question 13 :

Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes problems when debugging the DBMS?

Answer :

Since the Connection interface ( and the underlying DBMS ) requires a specific user and password, there's not much of a way around this in a pool. While you could create a different Connection for each user, most of the rationale for a pool would then be gone. Debugging is only one of several issues that arise when using pools.
However, for debugging, at least a couple of other methods come to mind. One is to log executed statements and times, which should allow you to backtrack to the user. Another method that also maintains a trail of modifications is to include user and timestamp as standard columns in your tables. In this last case, you would collect a separate user value in your program.


Question 14 :

How can I protect my database password ? I'm writing a client-side java application that will access a database over the internet. I have concerns about the security of the database passwords. The client will have access in one way or another to the class files, where the connection string to the database, including user and password, is stored in as plain text. What can I do to protect my passwords?

Answer :

This is a very common question.
Conclusion: JAD decompiles things easily and obfuscation would not help you. But you'd have the same problem with C/C++ because the connect string would still be visible in the executable.
SSL JDBC network drivers fix the password sniffing problem (in MySQL 4.0), but not the decompile problem. If you have a servlet container on the web server, I would go that route (see other discussion above) then you could at least keep people from reading/destroying your mysql database.
Make sure you use database security to limit that app user to the minimum tables that they need, then at least hackers will not be able to reconfigure your DBMS engine.
Aside from encryption issues over the internet, it seems to me that it is bad practice to embed user ID and password into program code. One could generally see the text even without decompilation in almost any language. This would be appropriate only to a read-only database meant to be open to the world. Normally one would either force the user to enter the information or keep it in a properties file.


Question 15 :

Detecting Duplicate Keys I have a program that inserts rows in a table. My table has a column 'Name' that has a unique constraint. If the user attempts to insert a duplicate name into the table, I want to display an error message by processing the error code from the database. How can I capture this error code in a Java program?

Answer :

A solution that is perfectly portable to all databases, is to execute a query for checking if that unique value is present before inserting the row. The big advantage is that you can handle your error message in a very simple way, and the obvious downside is that you are going to use more time for inserting the record, but since you're working on a PK field, performance should not be so bad.
You can also get this information in a portable way, and potentially avoid another database access, by capturing SQLState messages. Some databases get more specific than others, but the general code portion is 23 - "Constraint Violations". UDB2, for example, gives a specific such as 23505, while others will only give 23000.