Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inserting multiple record into a database

 
Claude Nichols
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello ranchers,

I got a problem.
I have a servlet that uses the JDBC facilities to connect to a database. The servlet recieves several records at any one time and must access the database and insert the new records into the database. Each record contains info for 6 fields.
I am able to insert a single record at a time but i need to insert multiple records to releave servlet overhead.
Is there a way to achieve multiple record insertion without coding a insert statements for each record or creating a large insert statement encompassing all records.

Thanks in advance
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have seen a number of postings about "batching inserts", so I would do some searches on this forum with "batch" or "batching"
Dan
 
Thomas Suer
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the only way is to create a 'large' insert statement of the following form:

Tom
 
Claude Nichols
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thanks for you responces
I have heard of batch inserts but never come across it before. If you find anything on how to send me the link and how it works.
I'll however try to use the format for a large insert statement given and see how that goes.

later then

thanks
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html
Jamie
...by the way, I've never heard of large insert statements. I don't think they are standard SQL because it doesn't work with Oracle. I would stick to Batches because they are very efficient and are a portable(standard) solution.
 
Claude Nichols
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Unfortunetly I have to use an existing access database and it does not support batch processing.
Throws a UnsupportedOperationException
The suggestion of a large string is still on the table the only problem is that insert values are determined dynamicaly and amount are not previously known.
I'll keep trying
thanks
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a note:
It's not the database that doesn't support batch statements, it's the JDBC driver that doesn't implement it.
another note:
I can use batch statements with the jdbc dbc bridge to an access database, which is as bare bones as it can get. Unless you are using some obscure database, there is a driver that will support batch updates.
Which database/driver/jdk combination are you using? may be you are using an old jdbc driver or an older jdk
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic