It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes inserted data lost! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "inserted data lost!" Watch "inserted data lost!" New topic

inserted data lost!

Li Shangqiang

Joined: Mar 25, 2001
Posts: 22

I have encountered a very strange problem: I've inserted n lines into a table, but i can just get n-1 lines in table.

The database is Access, and driver is JDBC-ODBC Bridge, code is like this:
in = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
String line, supplierId = "test";
StringTokenizer parser;
int count = 0;
while ( (line = in.readLine()) != null) {
parser = new StringTokenizer(line, ",");
// MedId, MedCName, DemandAmount, DemandUnit, DemandPrice, ValidTime, Description
count = parser.countTokens();
if (count != 6) continue;
System.out.println("insert: " + line);
StringBuffer buf = new StringBuffer(sqlStr);
buf.append(supplierId); buf.append("','");
buf.append((String)parser.nextToken().trim()); buf.append("','"); //Medid
buf.append((String)parser.nextToken().trim()); buf.append("','"); //MedCName
buf.append((String)parser.nextToken().trim()); buf.append("','"); //SupplyAmount
buf.append((String)parser.nextToken().trim()); buf.append("','");//SupplyPrice
buf.append((String)parser.nextToken().trim()); buf.append("','");//SupplyUnit
buf.append(" ','");
buf.append(date); buf.append("',");
buf.append((String)parser.nextToken().trim()); //ValidTime
System.out.println("insert: " + stmt.executeUpdate(buf.toString())); //return 1 to every line
Who can give me answer?
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Can you give the output from your println statements? And the file line that is not being inserted?
By the way, did you consider using a PreparedStatement for this?
- Peter
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I have seen this problem extensively with certain combinations of the jdbc dbc bridge and MSAccess. Stop pulling your hair out, there are 2 workarounds to force this last record to insert:
1) Close the connection each time afterwards.
2) Do a dummy select each time afterwards. That seems to be the better one.
That means: after your last insert (not after each record) do this "trick".(after "insert into emp..." execute a query "select something from emp...) Not sure if the select has to be from the same table as the insert, but you can try it both ways
Li Shangqiang

Joined: Mar 25, 2001
Posts: 22
It's very strange now no problem at all. If I encountered this problem again, I'll do like what you said.
Joe Fisher

Joined: Nov 05, 2001
Posts: 12
I have experienced the same problem if I fail to close the connection when the program terminates.
I agree. Here's the link:
subject: inserted data lost!
jQuery in Action, 3rd edition