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.
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:
would be replaced by
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.
posted 5 years ago
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.