ResultSet.getDate() retrives only Date Information but no Time Information

[Swapneel Killekar]

Hi,

I have a very generic Code to Copy the data from One Database to another Database with same schema.

String sql = " select * from " + srcDBName + "." + tableName + "";
			rs = st.executeQuery(sql);
			int columnCount = rs.getMetaData().getColumnCount();
			Date date = null;
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
			
			for (int currColumnIndex = 1; currColumnIndex <= columnCount-1; currColumnIndex++)
			{	
				if(rs.getMetaData().getColumnType(currColumnIndex) == Types.DATE)
				{
					totalPlaceHolderString += "to_date(?, 'dd-Mon-yyyy HH24:mi:ss'), ";
				}
				else
				{
					totalPlaceHolderString += "?, ";
				}
			}
			
			if(rs.getMetaData().getColumnType(columnCount) == Types.DATE)
			{
				totalPlaceHolderString += "to_date(?, 'dd-Mon-yyyy HH24:mi:ss') ";
			}
			else
			{
				totalPlaceHolderString += "? ";
			}
			
			insertStatement = String.format(insertStatement, totalPlaceHolderString);
			pStatementDest = distCon.prepareStatement(insertStatement);
			
			while(rs.next())
			{
				for (int currColumnIndex = 1; currColumnIndex <= columnCount; currColumnIndex++)
				{
					if(rs.getMetaData().getColumnType(currColumnIndex) == Types.DATE && rs.getDate(currColumnIndex) != null)
					{
						date = rs.getDate(currColumnIndex);
						pStatementDest.setString(currColumnIndex, sdf.format(date));
					}
					else
					{
						pStatementDest.setObject(currColumnIndex, rs.getObject(currColumnIndex));
					}
				}
				
				pStatementDest.addBatch();
				currBatchCount++;
				
				if(currBatchCount > 20000)
				{
					pStatementDest.executeBatch();
					currBatchCount=0;
				}
			}

Here on Line no 37 when i do ResultSet.getDate(currColumnIndex), it retrives only the date information but no time. which is then lost when i insert it into another database.

Kindly point out where i am going wrong.

Thanks in Advance.

[Sudheer Bhat]

If your database supports Database links, why don't you populate the target database using the INSERT statement?

You can probably run this query in the target DB.

INSERT INTO table SELECT * FROM table1@dblink to source db