java.sql.SQLException: ORA-01006: bind variable does not exist

[lunas]

hi
i am trying to update a table selecting a value from another table and ve written the following codes for it..

import java.sql.*;
import java.io.*;
import java.util.*;

public class AF_updateAlleleTmp
{
Connection m_alleleCon;

public void con() throws SQLException, java.lang.ClassNotFoundException
{
Class.forName("oracle.jdbc.driver.OracleDriver"); // oracle driver
m_alleleCon = (Connection)DriverManager.getConnection("jdbc:orac le:thin:@192.168.2.222:1521:SNPDB","g2p","g2p");

}

public void updateAlleleTemp() throws Exception
{
Statement my_updateStmt=m_alleleCon.createStatement(); //:1<int>,:2<char[32]>,:3<double>,:4<char[128]>)
PreparedStatement my_SelMTStmt=m_alleleCon.prepareStatement("select distinct(AutoMarkerID) from marker_temp where marker_temp.dbSNPrsID=?");
System.out.println("after 1");
PreparedStatement my_updateATPStmt=m_alleleCon.prepareStatement("upd ate allele_temp set allele_temp.AutoMarkerID =? where dbsnprsid=? and automarkerid is null");
System.out.println("after 2");
ResultSet AT_amid=my_updateStmt.executeQuery("select * from missingautomarkerid");
System.out.println("after 3");
while (AT_amid.next())
{

System.out.println("inside while");
String dbsnpStr=AT_amid.getString("dbsnprsid");
System.out.println("got dbsnprsid\t"+dbsnpStr);

my_SelMTStmt.setString(1,dbsnpStr);
System.out.println("sel stmt");
//ResultSet rs=my_SelMTStmt.executeQuery();

System.out.println("result set...");
while (rs.next())
{
System.out.println("inside 2 while");
int MT_amid=rs.getInt("automarkerid");
System.out.println("got automarkerid");
my_updateATPStmt.setInt(1,MT_amid);
System.out.println("set 1 done");
my_updateATPStmt.setString(2,dbsnpStr);
System.out.println("set 2 done");
my_updateATPStmt.executeQuery();
System.out.println("update done");
}
System.out.println("one update done !!!!");
my_updateATPStmt.executeQuery("commit");

}

my_updateATPStmt.executeQuery("commit");
my_updateStmt.close();
System.out.println("Update Allele temp done");
}
public static void main(String[] args)
{
try
{
AF_updateAlleleTmp up =new AF_updateAlleleTmp();
up.con();
up.updateAlleleTemp();
System.out.println("Hello World!");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}


after one update the i get the following error
java.sql.SQLException: ORA-01006: bind variable does not exist

can anyone plitz suggest something...
thanx

[madhoriya22]

hi
i am trying to update a table selecting a value from another table and ve written the following codes for it..

import java.sql.*;
import java.io.*;
import java.util.*;

public class AF_updateAlleleTmp
{
Connection m_alleleCon;

public void con() throws SQLException, java.lang.ClassNotFoundException
{
Class.forName("oracle.jdbc.driver.OracleDriver"); // oracle driver
m_alleleCon = (Connection)DriverManager.getConnection("jdbc:orac le:thin:@192.168.2.222:1521:SNPDB","g2p","g2p");

}

public void updateAlleleTemp() throws Exception
{
Statement my_updateStmt=m_alleleCon.createStatement(); //:1<int>,:2<char[32]>,:3<double>,:4<char[128]>)
PreparedStatement my_SelMTStmt=m_alleleCon.prepareStatement("select distinct(AutoMarkerID) from marker_temp where marker_temp.dbSNPrsID=?");
System.out.println("after 1");
PreparedStatement my_updateATPStmt=m_alleleCon.prepareStatement("upd ate allele_temp set allele_temp.AutoMarkerID =? where dbsnprsid=? and automarkerid is null");
System.out.println("after 2");
ResultSet AT_amid=my_updateStmt.executeQuery("select * from missingautomarkerid");
System.out.println("after 3");
while (AT_amid.next())
{

System.out.println("inside while");
String dbsnpStr=AT_amid.getString("dbsnprsid");
System.out.println("got dbsnprsid\t"+dbsnpStr);

my_SelMTStmt.setString(1,dbsnpStr);
System.out.println("sel stmt");
//ResultSet rs=my_SelMTStmt.executeQuery();

System.out.println("result set...");
while (rs.next())
{
System.out.println("inside 2 while");
int MT_amid=rs.getInt("automarkerid");
System.out.println("got automarkerid");
my_updateATPStmt.setInt(1,MT_amid);
System.out.println("set 1 done");
my_updateATPStmt.setString(2,dbsnpStr);
System.out.println("set 2 done");
my_updateATPStmt.executeQuery();
System.out.println("update done");
}
System.out.println("one update done !!!!");
my_updateATPStmt.executeQuery("commit");

}

my_updateATPStmt.executeQuery("commit");
my_updateStmt.close();
System.out.println("Update Allele temp done");
}
public static void main(String[] args)
{
try
{
AF_updateAlleleTmp up =new AF_updateAlleleTmp();
up.con();
up.updateAlleleTemp();
System.out.println("Hello World!");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}


after one update the i get the following error
java.sql.SQLException: ORA-01006: bind variable does not exist

can anyone plitz suggest something...
thanx

Hi,
For updating a table u should use pStatement.executeUpdate();

thanks and regards,
madhoriya22.

[praveen2gupta]

Hi
1. In the second while loop you are using rs , ResultSet from which you are not getting any value. Use AT_amid.next() in place of rs.next().

2. In place of my_updateATPStmt.executeQuery(); use my_updateATPStmt.executeUpdate();

try and post your results and Explain your program working more clearly.