JDBC/Java - Data Type issue from database to Java

[Jeremy McNally]

I am connecting to two databases ultimately. (SQL Server and Oracle). Inside SQL Server I have one staging table where I hold multi-million records. My job is to read from it through a Java program. Inside the Java program, I will call a stored procedure in Oracle which will validate the data record by record (per hourly batch (continuous)) and insert the records that I need.
To read data from SQL Server hourly have the first script below which is on the Java side. The method below that is the getMaxLastHour() which selects the last hour proceessed from a Marker table I have in Oracle.

ISSUE: DATE/TIME --- I am not sure which data types to use in Java and also Oracle. I chose DATE. The problem here is that I can't get it to work. I return the last hour from Oracle and insert it into my SQL Server script so it knows where to resume processing. The Datatypes aren't matching up somewhere and I have no idea how to fix this.

Thank you for your help.


Go through a SQL Server table and grab data from it in hourly batches until there isn't any more data left to retrieve.

   public void readSqlServer_Stage() throws SQLException {
        Connection conn1 = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        List<String> testList = new ArrayList<String>();
        String result = null;
        
        
        try {
        	//Call the method that connects to the SQL Server Database - returns a connection object.  
              conn1 = getDbConnect_SqlServer();
               
                
              //String sql1 = "";
              
             //INSTEAD OF SELECTING  ------  MAX(DateEnd) FROM Stage Table - Select MAX date from my new marker table.  Run Script there. 
             //String - Holds the Script that will be executed             
              String sql1 = "DECLARE @vEndTime DateTime"
                      + " DECLARE @vStartTime DateTime"
                      + " set @vEndTime = '05/30/2015 12:00:00:00'"
                      + " set @vStartTime = " +  getMaxLastHour_Oracle()  //THIS IS WHERE DATE IS SUPPOSED TO INSERT - The format of the date is correct but I keep getting err
              
                      + " WHILE @vStartTime < '05/30/2015 12:00:00:00'"

                      + " BEGIN"

                      + " SET @vEndTime = DATEADD (minute , 60 , @vStartTime )"
+ " SELECT"
+ " stage.LineName"
+ " ,SUBSTRING(stage.BoardBarcode, 1, 5) AS BoardBarcode"
+ " ,stage.MachineProductName"
+ " ,convert(varchar,stage.DateStart,120) AS DateStart"
+ " ,convert(varchar,stage.DateEnd,120) AS DateEnd"
+ " ,stage.MachineName"
+ " ,convert(varchar,stage.PlacementTime,120) AS PlacementTime"
+ " ,stage.RefID"
+ " ,stage.CircuitNumber"
+ " ,stage.FeederSlot"
+ " ,stage.FeederTrack"
+ " ,stage.Component"
+ " ,stage.LotNumber"
+ " ,stage.ReelID"
+ " FROM [UICTransfer_Archive].[dbo].[StageLotLineage] as stage"
+ " where stage.DateEnd >= @vStartTime" 
+ " and stage.DateEnd <= @vEndTime"
+ " and stage.CircuitNumber IS NOT NULL"

+ " SET @vStartTime = DATEADD (minute , 60 , @vEndTime )"
+ " END";
             
              
            //Prepares the SQL Statement.  The argument being passed 'is' the query itself.
              ps = conn1.prepareStatement(sql1);

            //Execute the 'PreparedStatement' 
              rs = ps.executeQuery();
             
            /*
             * Now we have the result set - held in the ResultSet variable (rs).  Iterate through RS and store each column from the script above inside a variable in each iteration.  
             * Once each column is stored inside a variable... pass the variable names as arguments to the parameters needed inside the method called *** processRecord_Oracle(...) *** 
             */
              while (rs.next()){
            	  String lineName = rs.getString(1);
            	  String boardBarcode = rs.getString(2);
            	  String machineProductName = rs.getString(3);
            	  String dateStart = rs.getString(4);
          		  String dateEnd = rs.getString(5);
          		  String machineName = rs.getString(6);
          		  String placementTime = rs.getString(7);
          		  String refId = rs.getString(8);
          		  int circuitNumber =rs.getInt(9);
          		  int feederSlot = rs.getInt(10);
          		  int feederTrack = rs.getInt(11);
          		  String component = rs.getString(12);
          		  String lotNumber = rs.getString(13);
          		  String reelId = rs.getString(14);
            	  
            	  System.out.println("LineName Result: " + lineName + ", \nBoardBarcode Result: " + boardBarcode + ", \nMachineProductName Result: " + machineProductName
            			  + ", \nDateStart Result: " + dateStart + ", \nDateEnd Result: " + dateEnd + ", \nMachineName Result: " + machineName 
            			  + ", \nPlacementTime Result: " + placementTime + ", \nRefId Result: " + refId + ", \nCircuitNumber Result: " + circuitNumber 
            			  + ", \nFeederSlot Result: " + feederSlot + ", \nFeederTrack Result: " + feederTrack + ", \nComponent Result: " + component
            			  + ", \nLotNumber Result: " + lotNumber + ", \nReelId Result: " + reelId);
            	 
            	
            	  //This method will call the stored procedure in Oracle (already completed for the most part AND was written by Wenlong on the Whiteboard. 
            	  //processRecord_Oracle(lineName, boardBarcode, machineProductName, dateStart, dateEnd, machineName, placementTime, refId, circuitNumber, feederSlot, 
            	  //	  feederTrack, component, lotNumber, reelId);	  
       
              }
              

              
        } catch (SQLException sqlExc) {
            sqlExc.printStackTrace();
            sqlExc.getLocalizedMessage();
            
        } catch (Exception ex) {
            ex.printStackTrace();
            ex.getLocalizedMessage();
            
        } finally {    	
            closeConnect(conn1, ps); 
        }

    }    

Get Last Hour Processed

 /*
     * Will be used as the START_TIME for the script to read data from SQL Server.
     * Getting the time of the last hour processed / read from SQL Server from the Marker Table.  
     */
    public Date getMaxLastHour_Oracle() {
        
    	System.out.println("Entering getMaxLastHour_Oracle()");
    	
    	ResultSet rs = null;
    	PreparedStatement ps = null;
    	Connection conn1 = null;
    	Date lastHour = null;
    	
    	try{
    		conn1 = getDbConnect_Oracle();
    		
    		String sql1 = "SELECT MAX(LAST_HOUR) AS LAST_HOUR FROM JEREMY_MARKER ";
    		
    		//Prepares the SQL Statement.  The argument being passed 'is' the query itself.
            ps = conn1.prepareStatement(sql1);
        
          //Execute the 'PreparedStatement' 
            rs = ps.executeQuery();
           
          /*
           * Get the LastHour from the result set
           */
            while (rs.next()){
          	  lastHour = rs.getDate(1);          	 
          	  System.out.println("Last Hour Processed: " + lastHour);
            }
            
            
    	}catch(Exception e){
    		System.out.println("Exception occured inside getMaxLastHour_Oracle()");
    		System.out.println(e.getLocalizedMessage() + "\n");
    		e.printStackTrace();
    	}
    	
    	    return lastHour;
    	
    }  
    

[Greg Charles]

I'm not sure what error you're getting, so I can't be sure what's going wrong. However, I do have something for you to try. One of the useful features of PreparedStatements is you can use question marks as placeholders for parameters, and then use set statements to apply those parameters to the statement. One of the numerous benefits of that approach is that you don't have to figure out how to convert Java objects into SQL strings. In your case, you could take out some of the complicated "DECLARE" blocks and just go with the SELECT statement. The line:

where stage.DateEnd >= @vStartTime

would be replaced by

where stage.DateEnd >= ?

followed by:

ps.setTimestamp(1, endTimestamp);

after you create the PreparedStatement, but before you execute it.

The "1" is the index of the placeholder, so 1 for the first ?, 2 for the second, etc. You'll need a java.sql.Timestamp instead of a java.util.Date, but I'm sure you can work that part out.

[Jeremy McNally]

Thanks for the response. If I was to go with taking out the DECLARE block, would I just simply declare (let's say... @DateEnd) as a variable in my Java program and use it somehow? It would need to be dynamic based off the last hour processed instead of statically holding a date/time. The reason I ask is because those SQL Server variables are being used to read from the table and are incremented 60 minutes every time it loops through. If I was to insert a question mark and setTimeStamp(), how could I dynamically set it and have it incremented for each iteration if that were the case?

Your example is very interesting and will likely be my solution from how it sounds if certain things can happen by doing it like this.

Thanks again Greg.

[Greg Charles]

Hmm, maybe I don't understand what you're trying to accomplish with those declares. It's fine to keep them if they are still serving a purpose.