Boa Tarde
Estou tendo um problema e não consigo perceber o que está errado :?
Já tentei o código de duas maneiras, mas o erro surge igual em ambas.
package msi;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
public class DAP {
public static void dosemetria()throws SQLException{
String caminho = "C:/user.mdb";
try {
Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + caminho);
Statement stmt = con.createStatement();
Statement stmt1 = con.createStatement();
String strSql = "SELECT Id FROM PatientData";
ResultSet rs = null;
rs = stmt.executeQuery(strSql);
String [] vector = new String [16];
ArrayList<String> resultados = new ArrayList<String>();
while (rs.next()) {
String resid = rs.getString("Id");
if (resid != null){
resultados.add(resid);
//System.out.println("1 "+resultados.add(resid));
}
for (String id : resultados) {
//Statement stm = con.createStatement();
String DAP1 = "SELECT MAX(D1) AS V1 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d1 = stmt1.executeQuery(DAP1);
if (d1.next()){
String rd1 = d1.getString("V1");
System.out.println("M1 "+rd1);
vector[0] = rd1;
System.out.println("1 "+vector[0]);
}
String DAP2 = "SELECT MAX(D2) AS V2 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d2 = stmt1.executeQuery(DAP2);
if (d2.next()){
String rd2 = d2.getString("V2");
System.out.println("M2 "+rd2);
vector[1] = rd2;
System.out.println("2 "+vector[1]);
}
String DAP3 = "SELECT MAX(D3) AS V3 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d3 = stmt1.executeQuery(DAP3);
if (d3.next()){
String rd3 = d3.getString("V3");
System.out.println("M3 "+rd3);
vector[2] = rd3;
System.out.println("3 "+vector[2]);
}
.....
String DAP16 = "SELECT MAX(D16) AS V16 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))))";
ResultSet d16 = stmt1.executeQuery(DAP16);
if (d16.next()){
String rd16 = d16.getString("V16");
System.out.println("M16 "+rd16);
vector[15] = rd16;
System.out.println("16 "+vector[15]);
}
}
Arrays.sort(vector);
System.out.println("MAXIMO "+vector[15]);
stmt1.executeUpdate("UPDATE Parameter SET DAP = ("+vector [15]+") WHERE (ParamNr = (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"'))))");
System.out.println("UPDATE Parameter SET DAP = ("+vector [15]+") WHERE (ParamNr = (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"'))))");
ResultSet df = stmt1.executeQuery("SELECT DAP FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '"+resid+"')))");
if (df.next()){
String dfim = df.getString("DAP");
System.out.println("DAP "+dfim);
}
stmt1.executeUpdate("UPDATE PatientData SET PDose = ("+vector[15]+") WHERE Id = '"+resid+"'");
//System.out.println("UPDATE PatientData SET PDose = ("+dfim+") WHERE Id = '"+resid+"'");
System.out.println("Actualização concluída");
}
}
public static void main(String[] args) {
try {
dosemetria();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}neste código que coloquei acima está dando o erro, mas consigo fazer o calculo e inserção do valor nas duas tabelas, mas apenas para o primeiro id.
erro:
java.sql.SQLException: [Microsoft][Controlador Microsoft Access de ODBC] Não existe nenhum registo actual.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at msi.DAP.dosemetria(DAP.java:57)
at msi.DAP.main(DAP.java:225)
[code]
package msi;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
public class TestDos {
public static void main(String[] args) {
TestDos app = new TestDos();
app.dosimetria("C:/user.mdb");
}
public void dosimetria (String caminho){
String temp = null;
ResultSet rs = null;
String [] vector = new String[16];
ArrayList<String> resultados = new ArrayList<String>();
try{
Driver d = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + caminho);
Statement stmt = con.createStatement();
rs = stmt.executeQuery("SELECT Id FROM PatientData");
while (rs.next()){
temp = rs.getString("Id");
if (temp != null){
resultados.add(temp);
}
}
stmt.close();
PreparedStatement prepStm1 = con.prepareStatement("SELECT MAX(D1) AS V1 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = ?)))))");
PreparedStatement prepStm2 = con.prepareStatement("SELECT MAX(D2) AS V2 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = ?)))))");
....
PreparedStatement prepStm16 = con.prepareStatement("SELECT MAX(D16) AS V16 FROM MeasData WHERE (ParamNr = (SELECT ParamNr FROM Parameter WHERE ResumeNr IN (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = ?)))))");
for(String id : resultados){
prepStm1.setString(1, id);
rs = prepStm1.executeQuery();
if (rs.next()){
temp = rs.getString("V1");
vector[0] = temp;
System.out.println("M1: " + temp + "\n1: " + vector[0]);
}
prepStm2.setString(1, id);
rs = prepStm2.executeQuery();
if (rs.next()){
temp = rs.getString("V2");
vector[1] = temp;
System.out.println("M2: " + temp + "\n2: " + vector[1]);
}
.....
prepStm16.setString(1, id);
rs = prepStm16.executeQuery();
if (rs.next()){
temp = rs.getString("V16");
vector[15] = temp;
System.out.println("M16: " + temp + "\n16: " + vector[15]);
}
Arrays.sort(vector);
System.out.println("MAXIMO " + vector[15]);
/*PreparedStatement prestatuppar = con.prepareStatement("UPDATE Parameter SET DAP = (" + vector[15] +") WHERE (ParamNr = (SELECT ResumeNr FROM ResumeData WHERE InfoNr IN (SELECT InfoNr FROM MeasInfo WHERE PatNr IN (SELECT PatNr FROM PatientData WHERE Id = '" +temp+"'))))");
for(String dos : resultados){
prepStm1.setString(1, dos);
rs = prestatuppar.executeQuery();
if (rs.next()){
temp = rs.getString("DAP");
System.out.println("DAP: " + temp);
}
}*/
}
}catch (InstantiationException ex){
ex.printStackTrace();
}catch (IllegalAccessException ex){
ex.printStackTrace();
}catch (ClassNotFoundException ex){
ex.printStackTrace();
}catch (SQLException ex){
ex.printStackTrace();
}
}
}[/code]
Aqui está fazendo o calculo também apenas para o primeiro id. Mas neste código não estou a conseguir fazer a inserção para as tabelas, nem sei como fazer. Mas continua a dar o erro de que não existe nenhum registo actual.
Alguém consegue ajudar-me???
Obrigada