• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

resultset problem

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post your code? It would make it easier to figure out what is happening.
Chad
 
Uma, Mula
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
/* 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
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Hoping this will be easier to read...
 
Chad McGowan
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thankyou Jamie
 
Chad McGowan
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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()).
 
reply
    Bookmark Topic Watch Topic
  • New Topic