File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes strange problem...help!!!!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "strange problem...help!!!!!" Watch "strange problem...help!!!!!" New topic
Author

strange problem...help!!!!!

kriti sharma
Ranch Hand

Joined: Feb 16, 2001
Posts: 160
the table transfer has a text field which stores sql queries. There are three queries in the table.
example-- insert into parul values('neeraj','27','male')
When the program runs only first two rows are entered in the table parul.why???

import java.sql.*;
class forest
{
Connection con=null;
forest()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc dbc:ForestDev","forestdev","forestdev");
String sql="select * from transfer";
Statement s=con.createStatement();
ResultSet rs=s.executeQuery(sql);
String query=null;

while(rs.next())
{
query=rs.getString(2);
Statement s1=con.createStatement();
s1.executeUpdate(query);
}
}
catch(Exception e)
{
e.printStackTrace();
}

}
public static void main(String str[])
{
forest f=new forest();
}
}
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Kriti,
I'm not sure if this will solve your problem, but you should close the "Statement" objects, for example:

Good Luck,
Avi.
Sanjeev Kaushik
Ranch Hand

Joined: Aug 01, 2002
Posts: 105
This is a good practice to close the Statement, ResultSet and Connection object. In large applications, connection leakage is a big problem if you don't close all these objects.


Sanjeev Kaushik
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Kriti,
Does the sql in all three rows have a different value for the primary key of your other table?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
kriti sharma
Ranch Hand

Joined: Feb 16, 2001
Posts: 160
my other table does not have a primary key.
after closing statement,connection and resultset i got all three entries inserted.i tested,actually the closing of connection mattered.
I have another doubt.both of my tables(the one that holds the sql and the one into which values have to be inserted) have a primary key which is autonumber type (in MS Access).now i am not inserting the value of primary key,i want it to increment on its own.how can i do it?currently SQLException is being thrown that the number of values and destination fields are not same.will i have to explicitly write the value of key as well?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Kriti,
No, you don't have to explicitly write the value of the key. Just take its field name out of the list.
kriti sharma
Ranch Hand

Joined: Feb 16, 2001
Posts: 160
to Jeanne: i inserted only those values which i wanted to and skipped the autonumber field it says...
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query
values and destination fields are not the same.
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
It's probably because it looks like you are writing the insert like this: When you do not provide a column list immediately following the table name, then values for ALL columns must be present in the "values" clause. But in your case, you do not want to provide a value for the id column since Access will auto-generate it. When that is the case, you must explicitly specify the columns you are providing values for, like this:. By the way, I think it is always a good idea to explicity add the columns that you are providing values for (e.g. (col1,col2)) in your insert statement. Otherwise, if you add a new column to your table later, your code will not work without modification. Of course, you may want to modify the code when you add the new column, but you may not have to if the new column allows nulls or has a default value and it is not critical to provide a value at the time of your insert.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
kriti sharma
Ranch Hand

Joined: Feb 16, 2001
Posts: 160
thank you,my problem is solved!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: strange problem...help!!!!!