I can not record from Java in SQL Server

0

Good, I have a Web application in which the front end is javaScript and the back java, which connects to SQL Server, I have an SP which queries the data on a table, which if not generated a function, the question is that the web screen shows me the generated data, that is, the sp works

But it does not record the data in the database, it generates them, it queries them about the table in modified theory but it does not save them, but if I run from the sql console it goes perfectly. The SQL code is as follows:

ALTER PROCEDURE [dbo].[SPC_PeriodoTasaObtener]
(
    @idInmueble int,
    @anio int,
    @idContribuyente int
)
AS BEGIN
declare @valores int
set @valores = (select COUNT(idDeuda) from ValoresPeriodoTasa where 
                 idInmueble=@idInmueble and anio = @anio)

if @valores = 0 begin
    insert into ValoresPeriodoTasa(idDeuda,idInmueble,anio,periodo,montoPeriodo,primerVencimiento,segundoVencimiento,estado,codigoPeriodo)
    select codigoDeduda,idInmueble,anioDeuda,periodo,montoperiodo,primerVence,segundoVence,estadoDeuda,codigoPeriodo 
    from dbo.GeneraDeuda(@idContribuyente,@idInmueble,@anio)
end 

select idDeuda,idInmueble,anio,periodo,montoPeriodo,primerVencimiento,segundoVencimiento,estado,codigoPeriodo
from ValoresPeriodoTasa 
where idInmueble=@idInmueble and anio=@anio
end 

I hope you can guide me to solve my problem. thanks

public List<EstadoPeriodosTasa> obtenerPeriodos(int idInmueble, int anio, int idContribuyente) {
        List<EstadoPeriodosTasa> tasa = new ArrayList<EstadoPeriodosTasa>();
        Connection          connection = DBConection.getConnection();
        PreparedStatement   st          = null;
        ResultSet           resultSet   = null;

        try{
            DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
            st = connection.prepareStatement("SPC_PeriodoTasaObtener " + idInmueble + ", " + anio + ", " + idContribuyente);
            resultSet = st.executeQuery();
            while (resultSet.next()) {
                EstadoPeriodosTasa elem = new EstadoPeriodosTasa();
                elem.setIdDeuda(resultSet.getInt(1));
                elem.setIdInmueble(resultSet.getInt(2));
                elem.setAnio(resultSet.getInt(3));
                elem.setPeriodo(resultSet.getString(4));
                elem.setMontoPeriodo(resultSet.getDouble(5));
                elem.setPrimerVence(dateFormat.format(resultSet.getDate(6)));
                elem.setSegundoVence(dateFormat.format(resultSet.getDate(7)));
                elem.setEstadoPeriodo(resultSet.getString(8));
                elem.setCodigoPeriodo(resultSet.getInt(9));
                tasa.add(elem);
            }
            return tasa;
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                DBConection.cerrarConexion(resultSet,st,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tasa;
    }

this is the java code that calls the sp, I already have other sp that I pass the parameters and they record me perfect, this case is very particular xq step the parameters a function is executed, generates the new fields, returns them to me but it does not record them, but if I execute from sql it records them and also returns them

    
asked by moloyoni 04.09.2017 в 16:38
source

2 answers

0

To make an entry in a database from java would be like this

try{
  Connection conexion = new Connection();
  PreparedStatement pstm = conexion.preparedStatement("INSERT INTO 
  nombre_tabla(campo1,campo2,campo3) VALUES (?,?,?)");
  pstm.setInt(1,campo1);
  pstm.setString(2,campo2);
  pstm.setString(3,campo3);
  pstm.executeUpdate();

  }catch(SQLException ex){

System.out.println(ex);
} 

In this part "VALUES (?,?,?)" are so many "?" as fields you are going to enter into the database. I hope it helps you.

    
answered by 04.09.2017 в 22:15
0

solved the problem was the Java code as I execute a transaction that returns a list and not a simple query I missed the commit the new java code stayed that way

public List<EstadoPeriodosTasa> obtenerPeriodos(int idInmueble, int anio, int idContribuyente) {
        List<EstadoPeriodosTasa> tasa = new ArrayList<EstadoPeriodosTasa>();
        Connection          connection = DBConection.getConnection();
        PreparedStatement   st          = null;
        ResultSet           resultSet   = null;
        try{
            DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
            st = connection.prepareStatement("SPC_PeriodoTasaObtener ?,?,?");
            st.setInt(1, idInmueble);
            st.setInt(2, anio);
            st.setInt(3, idContribuyente);
            resultSet = st.executeQuery();
            while (resultSet.next()) {
                EstadoPeriodosTasa elem = new EstadoPeriodosTasa();
                elem.setIdDeuda(resultSet.getInt(1));
                elem.setIdInmueble(resultSet.getInt(2));
                elem.setAnio(resultSet.getInt(3));
                elem.setPeriodo(resultSet.getString(4));
                elem.setMontoPeriodo(resultSet.getDouble(5));
                elem.setPrimerVence(dateFormat.format(resultSet.getDate(6)));
                elem.setSegundoVence(dateFormat.format(resultSet.getDate(7)));
                elem.setEstadoPeriodo(resultSet.getString(8));
                elem.setCodigoPeriodo(resultSet.getInt(9));
                tasa.add(elem);
            }
            connection.commit();
            return tasa;
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                DBConection.cerrarConexion(resultSet,st,connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tasa;
    }

not only returns the list but also records the problem was definitely in java

    
answered by 05.09.2017 в 06:51