Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to insert cachedrowset data into MS Access Database?

 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

i was wondering if there is any one here who can help me figure out how to insert data in a cachedrowset into MS Access 2010 database table??? The code below does not generate any errors when using eclipse v1.7. The method below creates the SQL INSERT statement and loops through the data in the cachedrowset and stores it into MS Access database. The code below was able to insert data but not in the desired output.

If I remove the series of preparedStatements below in hopes of looping all the cachedrowset data and placed into MS Access, then I'll receive an error message: "Message:Parameter-Set has missing values."







The below code generates the SQL INSERT statement like shown below:

INSERT INTO book (TITLE_ID, TITLE_NAME, TYPE, PUB_ID, PAGES, PRICE, SALES, PUBDATE, CONTRACT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)




The sample of the results after running the code above:




The desire output that I would like to have is like below:




 
Campbell Ritchie
Sheriff
Posts: 51468
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch
I shall move you to our databases fora where we usually discuss such questions. Please tell is if you have asked the same question elsewhere (and tell the other site).
 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks you for the reply.

I am new to java forums and currently have posted the same topic at the following website and waiting for anyone to assist.

http://www.java-forums.org/new-java/94202-how-insert-cachedrowset-data-into-ms-access-database.html
 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Removing those "junk" prepared statement parameters you have before IS the right thing to do.
You need to deal with that error message properly. "Message:Parameter-Set has missing values."
Why would it have missing values?
Surely you set all parameter values before you execute the sql/ add a batch to the set right? Right?

If you notice, you get one row in your result set, with a new column value written in each time.
T01
T01 Java
T01 Java history
...

Does that maybe give you a hint?
 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for the quick reply,

I know that the data values are stored and exist. They are able to be printed on to the console.

I'm guessing it is the way the data is being looped around with the "while loop" trying to move to the next row and the "for loop" looping to the next column is causing the incorrect data layout.

Plus I'm guessing that the values in the table has blank spaces that the "preparedStatement.setObject" can't handle???
 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. IF you set all the parameters correctly, it will store it correctly.
However you have your "addBatch" command inside the loop for your columns.

So you are effectively doing



hence you get the output that you do.
 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wow thanks everyone for the help.

I modified the code to look like below and the test table that I created called "book" inserted into MS Access correctly. Now testing other tables to see if it works or if other errors appear.




 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After I tested my code with other tables that have null vales at random places in the table. I get an NullPointerException error message like below. This method seems to work when there are no null values in it. I have attempted to add in cachedrowset.updateString commands as well as IF THEN statements but they do not appear to work when inserting data to the MS Access database. Does anyone have any ideas that I can try to use to help me insert data into MS Access if the result set contains null values in it???


Exception in thread "main" java.lang.NullPointerException
at package_project.testAccess.insertAccessTable(testAccess.java:46)
at package_project.testAccess.createAccessTable(testAccess.java:120)
at package_project.testAccess.main(testAccess.java:23)


 
Stefan Evans
Bartender
Posts: 1807
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Testing an object for null is the case where you should use == rather than the equals method.


CRS_insertAccess.getString(i).equalsIgnoreCase(null) --> CRS_insertAccess.getString(i) == null

 
johnny ridden
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have changed the following line to use the "==" but I still get the nullpointerexception

CRS_insertAccess.getString(i).equalsIgnoreCase(null) --> CRS_insertAccess.getString(i) == null

Exception in thread "main" java.lang.NullPointerException
at package_project.testAccess.insertAccessTable(testAccess.java:49)
at package_project.testAccess.createAccessTable(testAccess.java:122)
at package_project.testAccess.main(testAccess.java:23)

The stack trace above points to the 4th line in my code below, which puts me at a loss as to what I can do to at least change the NULL value to something else (maybe like a blank space) which would then allow the INSERT statement to insert the cachedrowset data into MS Access Database correctly.

String columnValue = (CRS_insertAccess.getString(i).equalsIgnoreCase(null)
|| CRS_insertAccess.getString(i).matches("#*")
|| CRS_insertAccess.getString(i).matches(" *"))
? CRS_insertAccess.getString(i).replace(null, "").toString()
: CRS_insertAccess.getString(i).toString().trim();


The below code I have attempted to use in hopes that it will update the Null value to a blank space, but I got an unusual error like below.

javax.sql.rowset.spi.SyncProviderException: 13conflicts while synchronizing




 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic