Another issue…

August 7, 2006

When attempting to map LONGVARBINARY(hsql) to BLOB(derby)…

An attempt was made to put a data value of type ‘java.lang.String’ into a data value of type ‘BLOB’.

Tried to convert the String to Byte Array. But clueless about inserting ByteArray into the table…      😦 😦 

Selfish, selfish, selfish. What an insulting thing to say to someone: “You’re selfish.”

Of course they’re selfish! Everyone’s selfish. So are you. So am I. So why is it so degrading to admit? People are finely tuned to whatever might hurt or help them. We can’t help it: it’s the way we’re built. Any creature that neglects itself lives a short life.

I remember a kid’s game where I’d try to make my friend blink. I’d flick a hand at his face, and if he blinked he lost. It was fun to play because it was hard to win. We can’t help protecting ourselves.

“But it’s bad to be selfish! We should think of the other person first.” Well, if only one of us is going to be happy, what’s the difference if it’s you or me? “You won’t go to heaven if you’re selfish.” Oh, we’re being good so we can get a big payoff? Sounds selfish to me.

“But what about all the great, selfless people of history? You know, Gandhi, Albert Schweitzer, those people.” Ah, now we’re getting somewhere. These people were very selfish. They insisted on having things work out their way. They just happened to be busy with one of the deepest selfish pleasures of all: making a difference to others. What greater satisfactions are there than nurturing your kids, doing a fine job at work, helping a friend through a tough time? Or removing a beer can from a garden path, or planting a tree, or – sometimes – helping your nation be a better place?

President Gorbachov is well fed, has a beautiful wife, lots of friends, a dacha in the south, every wealth available. But his real interest is his reform program for the Soviet Union. After all, he’s already head of state: he’s got what most leaders crave. Now he can concentrate on doing some good. I’ll bet he wants to be remembered as a great reformer. Okay, so it’s a selfish desire. Got any complaints?

Jesus said, “Love thy neighbor as thyself.” As thyself. He didn’t expect you to drop your own interests. He wanted you to expand them to include others. There is a deep joy in doing for others. I think it’s designed that way. Besides, how far can you get if the people you live and work with aren’t happy?

When someone says, “You’re being selfish,” what they mean is, “You’re not doing it the way I want.” If they can guilt-trip you into doing it their way, fine. What can you do when you’re confronted this way? Be ingenious! Think up a way to solve the whole problem so both sides are happy. “Would you pick up Sis at five today?” “Sorry, I gotta be at the library then.” “Thanks a lot. You’re always just thinking about yourself, aren’t you?” At this point it’s tempting to tell them where to get off. Instead, try diplomacy. “I can see you’re upset about this,” you say. “So, how can we work this out so everybody’s happy?” They’re still grouchy and not much use, so you fill in. “How about I pick her up at five-twenty? We’ll warn her now so she can do some extra shopping,” or some such. That was easy! And you avoid all the moral lectures and arguments.

Let’s put away the insults and see what we can do to solve problems. Sometimes that’s hard, but we humans are an ingenious bunch. Every problem you solve serves everyone, including yourself.

Pretty selfish, eh?

Here is my actual code for migration.. dont get confused with ConnectionString object, its from proprietory API.  Instead, you can use the Connection from java.sql.

This program has the following spec:

. This program must read all the tables from the source database and create them in the target database. After creation, data from each table in the source must be copied into the corresponding table in the target.

  • Source and Target databases are accessible and the drivers are in the classpath.
  • The input parameters will be a source com.rli.util.ConnectionString object and a target com.rli.util.ConnectionString object.  (See javadoc on how to use the ConnectionString class)
  • The connection information like driver, url, ursername and password must be extracted from this object to make the connection to the respective databases.
  • All constraints must be recreated.

The actual Code goes here:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.rli.util.ConnectionString;

public class Test {

 public static void main(String[] args) throws Exception {

  com.rli.util.ConnectionString sourceCn = new ConnectionString(
    “org.hsqldb.jdbcDriver”, “jdbc:hsqldb:hsql://localhost:9001”,
    “sa”, “”);
  com.rli.util.ConnectionString destnCn = new ConnectionString(
    “org.apache.derby.jdbc.ClientDriver”,
    “jdbc:derby://localhost:1527/Test8”, “app”, “app”);
  migratesourceDest(sourceCn, destnCn);
 }

 private static Connection sourceConn() {
  Connection conn = null;
  com.rli.util.ConnectionString sourceCn = new ConnectionString(
    “org.hsqldb.jdbcDriver”, “jdbc:hsqldb:hsql://localhost:9001″,”sa”, “”);
  try {
   Class.forName(sourceCn.getDriverName());
   conn = DriverManager.getConnection(sourceCn.getURL(), sourceCn
     .getUser(), sourceCn.getPassword());
  } catch (Exception e) {
   e.printStackTrace();
  }

  return conn;
 }

 private static Connection destnConn() {
  Connection conn = null;
  com.rli.util.ConnectionString destnCn = new ConnectionString(
    “org.apache.derby.jdbc.ClientDriver”,
    “jdbc:derby://localhost:1527/Test8”, “app”, “app”);
  try {
   Class.forName(destnCn.getDriverName());
   conn = DriverManager.getConnection(destnCn.getURL(), destnCn
     .getUser(), destnCn.getPassword());
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 }
 

 private static void migratesourceDest(ConnectionString source,
   ConnectionString destn) throws Exception {

  try {
   Class.forName(source.getDriverName());
   Connection conn = DriverManager.getConnection(source.getURL(),
     source.getUser(), source.getPassword());
   Statement sql_stmt = conn.createStatement();
   String[] strarr = new String[5];

   try {
    DatabaseMetaData dbmd = conn.getMetaData();
   
    String pkField = null;

    String[] types = new String[]{“TABLE”};
    ResultSet resultSet = dbmd.getTables(null, null, “%”, types);
    
   

    while (resultSet.next()) {
     String tableType = resultSet.getString(“TABLE_TYPE”);
     System.out.println(“tableType: “+tableType);
     if(tableType.equals(“TABLE”)){
     
     String sourceTableName = resultSet.getString(“TABLE_NAME”);
     ResultSet results = dbmd.getPrimaryKeys(null, null,
       sourceTableName);
     boolean first = true;
     StringBuffer result = new StringBuffer();

     while (results.next()) {

      if (first) {
       first = false;
       result.append(‘,’);
       result.append(“PRIMARY KEY(“);
      } else
       result.append(“,”);
      result.append(results.getString(“COLUMN_NAME”));
      pkField = results.getString(“COLUMN_NAME”);
      System.out.println(“Primary Key : ” + pkField
        + “for table:” + sourceTableName);

      for (int i = 0; i < strarr.length; i++) {

       strarr[i] = results.getString(“COLUMN_NAME”);

      }

     }

     if (!first)
      result.append(‘)’);
     System.out.println(“result: ” + result);
     String tableDesc = getTableDesc(sourceTableName, result,
       pkField, conn);
     System.out.println(tableDesc);

     createDestTable(destn, sourceTableName, tableDesc);
     copyData(sourceTableName);
    }}
   } catch (SQLException e) {
    e.printStackTrace();
   }
   sql_stmt.close();
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }

 }

 private static void createDestTable(ConnectionString destn,
   String sourceTableName, String tableDesc) throws Exception {
  try {
   int exUpdate;
   Class.forName(destn.getDriverName());
   Connection connection =

   DriverManager.getConnection(destn.getURL(), destn.getUser(), destn
     .getPassword());
   Statement stmt = connection.createStatement();
   String sql = “CREATE TABLE ” + sourceTableName + “(” + tableDesc
     + “)”;
   System.out.println(“sql:” + sql);
   exUpdate = stmt.executeUpdate(sql);
   System.out.println(“UpdateCount” + exUpdate);
   connection.close();
  } catch (SQLException e) {
   // e.printStackTrace();
  }
 }

 private static String getTableDesc(String tableName, StringBuffer strarr,
   String pColName, Connection conn) throws Exception {

  String tableDesc = null;

  String typeName = null;
  final String sqlBase = “select * from “;
  String sql = sqlBase + tableName + ” where 1 = 2″;
  Statement stmt = null;
  ResultSet rslt = null;
  
  try {

   
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(sql);
   ResultSetMetaData meta = rslt.getMetaData();
   int numCols = meta.getColumnCount();
   StringBuffer strBuf = new StringBuffer();
   for (int i = 1; i <= numCols; i++) {
    int precision = meta.getPrecision(i);
    String colName = meta.getColumnName(i);

    if ((meta.getColumnTypeName(i).equals(“Counter”)) ||

    (meta.getColumnTypeName(i).equals(“counter”)) ||

    (meta.getColumnTypeName(i).equals(“COUNTER”))) {
     typeName = “INTEGER”;
    }

    else if

    (meta.getColumnTypeName(i).equals(“DATETIME”)) {
     typeName = “Date”;

    } else if

    (meta.getColumnTypeName(i).equals(“LONGCHAR”)) {
     typeName = “VARCHAR”;
    }

    else if

    (meta.getColumnTypeName(i).equals(“CURRENCY”)) {
     typeName = “NUMERIC”;
    }

    else if ((meta.getColumnTypeName(i).equals(“BIT”))) {
     typeName = “VARCHAR”;
    }
    
    /*
     else if ((meta.getColumnTypeName(i).equals(“LONGVARBINARY”))) {
     typeName = “VARCHAR(1) FOR BIT DATA”;
    }*/
    
    
   
    
    
    else if

    ((meta.getColumnTypeName(i).equals(“LONGBINARY”))|| (meta.getColumnTypeName(i).equals(“LONGVARBINARY”))) {
     
    typeName = “BLOB”;
    }

    else if (meta.getColumnTypeName(i).equals(“VARCHAR_IGNORECASE”)) {
     typeName = “VARCHAR”;
    } else if (meta.getColumnTypeName(i).equals(“LONGVARCHAR”)) {
     typeName = “VARCHAR”;
    }

    else {
     typeName = meta.getColumnTypeName(i);
    }

    if (!(colName.equals(pColName))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(“\””);
      strBuf.append(colName);
      strBuf.append(“\””);
      strBuf.append(” “);
      strBuf.append(typeName);

     } else {

      strBuf.append(colName);
      strBuf.append(” “);
      strBuf.append(typeName);
     }

    }

    else if ((colName.equals(pColName))
      && (!(typeName.equals(“VARCHAR”)))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(“\””);
      strBuf.append(colName);
      strBuf.append(“\” “);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      

     } else {

      strBuf.append(colName);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      
     }

    }

    else if ((colName.equals(pColName))
      && (typeName.equals(“VARCHAR”))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(colName);
      strBuf.append(” \” “);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(‘(‘);
      strBuf.append(“500″);
      strBuf.append(‘)’);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      

     } else {

      strBuf.append(colName);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(‘(‘);
      strBuf.append(“500″);
      strBuf.append(‘)’);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      
     }

    }

    

    if ((!(colName.equals(pColName)))
      && (typeName.equals(“VARCHAR”))) {
     if (precision > 255) {
      strBuf.append(‘(‘);
      strBuf.append(“500”);
      strBuf.append(‘)’);

     } else {
      strBuf.append(‘(‘);
      strBuf.append(“500”);
      strBuf.append(‘)’);
     }
    }
    if (typeName.equals(“INTEGER”)) {
     if (precision > 0) {
      strBuf.append(“”);
     }
    }
    if (typeName.equals(“NUMERIC”)) {
     if (precision > 0) {
      strBuf.append(“”);
     }
    }

    if (precision > 0) {
     // strBuf.append(‘(‘);
     // strBuf.append(precision);
     // strBuf.append(‘)’);
    }

    if ((colName.equals(pColName))) {

     strBuf.append(strarr);

    }

    if (i < numCols) {
     strBuf.append(‘,’);
     }
   }

   tableDesc = strBuf.toString();

  } catch (SQLException e) {

  } finally {
   if (stmt != null) {
    try {
     stmt.close();
    } catch (SQLException ignore) {
    }
   }
   if (rslt != null) {
    try {
     rslt.close();
    } catch (SQLException ignore) {
    }
   }
  }
  System.out.println(tableDesc);

  return tableDesc;

 }

 private static void copyData(String tableName) throws Exception {

  final String sqlBase = “select * from “;
  String sql = sqlBase + tableName + ” where 1 = 2″;
  Statement stmt = null;
  ResultSet rslt = null;
  String colName = null;
  PreparedStatement statement = null;
  PreparedStatement pst2 = null;
  ResultSet rs = null;

  StringBuffer selectSQL = new StringBuffer();
  StringBuffer insertSQL = new StringBuffer();
  StringBuffer values = new StringBuffer();

  System.out.println(“Begin copy: ” + tableName);

  selectSQL.append(“SELECT “);
  insertSQL.append(“INSERT INTO “);
  insertSQL.append(tableName);
  insertSQL.append(“(“);

  try {

   Connection conn = sourceConn();
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(sql);
   ResultSetMetaData meta = rslt.getMetaData();
   int numCols = meta.getColumnCount();
   StringBuffer strBuf = new StringBuffer();

   for (int i = 1; i <= numCols; i++) {
    boolean first = true;
    colName = meta.getColumnName(i);
    strBuf.append(colName);
    strBuf.append(”  “);
    if (!first) {
     selectSQL.append(“,”);
     insertSQL.append(“,”);
     values.append(“,”);
    } else
     first = false;

    selectSQL.append(colName);

    insertSQL.append(colName);

    values.append(“?”);

    if (i < numCols) {
     selectSQL.append(“,”);
     insertSQL.append(“,”);
     values.append(“,”);

    }
   }
   selectSQL.append(” FROM “);
   selectSQL.append(tableName);
   insertSQL.append(“) VALUES (“);
   insertSQL.append(values);
   insertSQL.append(“)”);

   Connection sConn = sourceConn();
   Connection dConn = destnConn();
  
   pst2 = sConn.prepareStatement(selectSQL.toString());
   statement = dConn.prepareStatement(insertSQL.toString());

   rs = pst2.executeQuery();

   int rows = 0;

   while (rs.next()) {
    rows++;
    for (int i = 1; i <= numCols; i++) {
     statement.setString(i, rs.getString(i));
     }
    statement.execute();
   }

   System.out.println(“Transferred ” + rows + ” rows.”);
   

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {

   if (rs != null) {
    try {
     rs.close();
    } catch (SQLException sqle) {
     sqle.printStackTrace();
    }
   }
   if (stmt != null) {
    try {
     stmt.close();
    } catch (SQLException sqle) {
     // sqle.printStackTrace();
    }
   }
   if (rslt != null) {
    try {
     rslt.close();
    } catch (SQLException sqle) {
     // sqle.printStackTrace();
    }
   }
  }
 }
}

My original Blog site…at Blogster has stopped opening for the reason best known to Google..  People say that Govt of India restricted the Blogster because of some anti-social guys.. but I hear many people still blogging in India with blogster.. so the possible reason would be the blockade from ISPs .

Keep commenting my blogs.. Happy Blogging !!!!!!!!!! 

Welcome world!

August 2, 2006

Thanks for peeping in. I will be glad if you can leave a comment before you leave my blog.