This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes resultset problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "resultset problem" Watch "resultset problem" New topic
Author

resultset problem

Uma, Mula
Greenhorn

Joined: Aug 07, 2001
Posts: 6
Hello
I am trying to insert data from oracle database to a table in mysql through java. I could able to do that if atleast one row is there in mysql table. But if the table is empty it is giving me a run time error that "Before start of resultset". I gave if count(*) is equal to zero insert thedata. How to solve my error now. please help me.
with regards
uma
Chad McGowan
Ranch Hand

Joined: May 10, 2001
Posts: 265
Can you post your code? It would make it easier to figure out what is happening.
Chad
Uma, Mula
Greenhorn

Joined: Aug 07, 2001
Posts: 6
/* HERE IS MY CODE */
import java.sql.*;
import java.io.*;
public class oracle_mysql_demo
{
public static void main(String args[])
{
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con1 = DriverManager.getConnection ("jdbc racle:thin:@64.240.98.211:1521:cricket","boli","boli");
Statement st1=con1.createStatement();
Statement st3=con1.createStatement();
Statement st5=con1.createStatement();
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Connection con2 = DriverManager.getConnection("jdbc:mysql://localhost/Team");
Statement st2 = con2.createStatement();
Statement st4 = con2.createStatement();
Statement st6 = con2.createStatement();
ResultSet r1 = st1.executeQuery("Select * from uma_example");
ResultSet r2 = st2.executeQuery("Select * from uma_demo");

ResultSet r3 = st3.executeQuery("SELECT COUNT(*) FROM uma_example");
r3.next();
int rowcount1 = r3.getInt("COUNT(*)");
System.out.println("\nThe number of rows in table uma_example are :"+rowcount1);
ResultSet r4 = st4.executeQuery("SELECT COUNT(*) FROM uma_demo");
r4.next();
int rowcount2 = r4.getInt("COUNT(*)");
System.out.println("\nThe number of rows in table uma_demo are :"+rowcount2);
while(r1.next())
{
r2.next();
if(r1.getInt("id_no")==r2.getInt("id_no"))
{
int j=st2.executeUpdate("UPDATE uma_demo SET name ='"+r1.getString("name")+"',salary ="+r1.getInt("salary")+" WHERE id_no="+r1.getInt("id_no")+"");
System.out.println("The row which has been checked or updated is:"+r1.getInt("id_no"));
}
else
{
if(r1.getInt("id_no") != r2.getInt("id_no") | | rowcount2==0)
{
int i=st2.executeUpdate("INSERT INTO uma_demo VALUES("+r1.getInt("id_no")+",'"+r1.getString("name")+"',"+r1.getInt("salary")+")");
System.out.println("The record inserted was : "+r1.getInt("id_no"));
}
}
/*if((rowcount1<rowcount2))>
{
int k=st2.executeUpdate("DELETE FROM uma_demo WHERE id_no != "+r1.getInt("id_no")+"");
System.out.println("The deleted rows were :"+r1.getInt("id_no"));
}*/
}

}
catch(Exception e)
{
System.out.println("\nThe message is !!"+e);
}
}
}
/**************************************************************/
My problem is if the table in mysql is empty i have to insert all the rows from oracle table. But it is giving me error that "Before start of ResultSet". If atleast one row is existing it is inserting and updating the table. In delete also if we happen to delete a row from oracle table it has to delete in mysql also. these are my doubts. I will be really thankful if you clear my doubts.
thankyou
uma
Chad McGowan
Ranch Hand

Joined: May 10, 2001
Posts: 265


Hoping this will be easier to read...
Chad McGowan
Ranch Hand

Joined: May 10, 2001
Posts: 265

I think that you should check to make sure r2 has data, use if (r2.hasNext()). If r2 has no data in the result set, the r2.get call should not happen. Try that and let me know what happens.
Hope this helps.
Chad
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

tried to resolve some of the errors:
example:
if(r1.getInt("id_no") != r2.getInt("id_no") | | rowcount2==0)
the above is interpreted as "if the r1 value is not equal to the r2 value OR r2 returned nothing"...how can you read the first value if of r2 before you check to see if it has any rows??
anyways try the code I've tried to help you with below. I beleive it does the same thing as you tried but in a little bit of a different way(no counts are necessary). In a nutshell-->
while (r1 has records)
{
if (r2 also has records)
{
process the update
}
else //only r1 has records
{
process the update
}
}


hope this helps,
Jamie
[This message has been edited by Jamie Robertson (edited August 08, 2001).]
[This message has been edited by Jamie Robertson (edited August 08, 2001).]
Uma, Mula
Greenhorn

Joined: Aug 07, 2001
Posts: 6
Hello sir
I gave r2.hasNext() in if condition for inserting rows. But it is giving that "cannot resolve symbol". How to rectify this error?
thankyou
uma
Uma, Mula
Greenhorn

Joined: Aug 07, 2001
Posts: 6
hi Jamie,
I got the result with the help of your code. I am really thankfull to you. I understood where I went wrong. Thanks once again.
thankyou
uma
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Uma, Mula:
hi Jamie,
I got the result with the help of your code. I am really thankfull to you. I understood where I went wrong. Thanks once again.
thankyou
uma

your welcome.
I just made a one more refinement--> I changed

to

no need to make an unnecessary call to the database in your if statement. In fact I would store the values of the ints as a local variable instead of calling to the database everytime you need the value;
int id_no1 = r1.getInt("id_no");
int id_no2 = r2.getInt("id_no");
then use id_no1 and id_no2 for the rest of the time.

Cheers,
Jamie
Uma, Mula
Greenhorn

Joined: Aug 07, 2001
Posts: 6
thankyou Jamie
Chad McGowan
Ranch Hand

Joined: May 10, 2001
Posts: 265
Originally posted by Uma, Mula:
Hello sir
I gave r2.hasNext() in if condition for inserting rows. But it is giving that "cannot resolve symbol". How to rectify this error?
thankyou
uma

Sorry, meant if (r2.next()).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: resultset problem
 
Similar Threads
error in database
mysql help
inserting values from a class to a mysql database
Insert the current date
Inserting the Date in to MySQL