aspose file tools*
The moose likes JDBC and the fly likes to bulk or not to bulk?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "to bulk or not to bulk??" Watch "to bulk or not to bulk??" New topic
Author

to bulk or not to bulk??

Cathleen Meneses
Greenhorn

Joined: Mar 16, 2004
Posts: 21
Hi!
im making a java program which uses java.io to open a .txt file and update it to oracle database. any suggestions or ideas on whether i should "bulk" insert the entire .txt file or should i insert it individually? Some people said that it is safer to insert the data below individually not all at the same time...
a sample .txt file looks like this:

12345|08-24-2004|2000.00|16:50:56|8A25682|
12346|08-24-2004|3000.00|16:50:56|8B25682|
12347|08-24-2004|4000.00|16:50:56|8C25682|
12348|08-24-2004|5000.00|16:50:56|8D25682|
12349|08-24-2004|6000.00|16:50:56|8E25682|
12341|08-24-2004|7000.00|16:50:56|8F25682|
12342|08-24-2004|8000.00|16:50:56|8G25682|
12343|08-24-2004|9000.00|16:50:56|8H25682|

note that :each field is terminated using '|'

thanks!!!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Cathleen,

The answer is, as you will often find, that it depends.

I assume that by "bulk" insert you mean insert using a batch, rather than as a set of single insert statements in a loop. You could also use a bulk loading tool like Oracle's SQL*Loader (without Java).

The behaviour of java.sql.Statement.executeBatch() is database-dependent with regard to whether execution continues if an error is encountered whilst executing the batch. I've a feeling that the Oracle implementation executes the batch to completion regardless of failures, but you'll need to check that out. Check out the Javadoc for Statement.executeBatch() and BatchUpdateException for full details.

If you want your load operation to be atomic (i.e. all inserted or none inserted) then batch may be the best solution. If you want all valid records to be inserted, regardless of errors in the rest, then using a PreparedStatement in a loop might be the better option. It also depends somewhat on Oracle's batch behaviour as mentioned above.

Depending on the size of your data file you may need to split it into a number of batches, committed separately, to avoid running out of space in the log segment.

So that's not a definitive answer but I hope it gives you the info you need to make the right choice.

Jules
Cathleen Meneses
Greenhorn

Joined: Mar 16, 2004
Posts: 21
Hi Jules!
thanks for the big help! i really appreciate it! ^_^ i am now half way on my project because of that...
i have yet another question to ask you regarding that, see, i have decided to go forth on inserting my .txt file on a batch basis, meaning if there is a bad file on the .txt, it won't upload everything... i have made a sql loader and have also made a table in my oracle, i also made the method which connects to my server..can you enlighten me on how i should make the sql statement?
this is my method in mySql form... i have no idea on what to put in my sql variable... any suggestions? Many thanks!!
public void insertRecord(String strPath){
try{
String sql = null;
//String strFldTerminator = "|";
String strRowTerminator = "\\n"; Statement stm;

dbConn = BatchUploadConn.dbConnection;
sql = "BULK INSERT tbl_casa_txn_hist FROM '" + strPath + "' WITH (FIELDTERMINATOR = '"+ strFldTerminator +"',ROWTERMINATOR = '" + strRowTerminator + "')";
System.out.println(sql);
stm = dbConn.createStatement();
int intRes = stm.executeUpdate(sql);
stm.close();
System.out.println("okay");
}catch(SQLException sqlExcp){
System.out.println(sqlExcp.getMessage());
}
}
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Cathleen,

You seem to be a bit mixed up - please correct me if I'm wrong. As far as I know, BULK INSERT is an MS SQL Server syntax, not Oracle, however I'm a bit out of touch (last used 8i) so I'm prepared to be corrected.

If you want to use Oracle's SQL*Loader you don't need to use Java. You'll need to read the SQL*Loader documentation.

If you want to, or must, use Java then the method(s) I proposed require writing a simple INSERT...VALUES statement for use in a JDBC Batch. You will need to read in your file using java.io.FileReader and, probably, BufferedReader. Read each record using the readLine() method and split it into fields using String.split("|"). Add these fields as the appropriate parameters to your PreparedStatement, add it to the batch and then carry on round the loop. Get the idea?

If you want to use this approach I suggest you look at the Statement.addBatch() method plus those I've already suggested.

Jules
Cathleen Meneses
Greenhorn

Joined: Mar 16, 2004
Posts: 21
Hi juls! ^_^
you're right juls, it is ms sql server, kinda got it all wrong. ^_^ thanks a lot juls for the big help! i can move on now to what i am doing !
many thanks!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: to bulk or not to bulk??