CODEWITHSUNDEEP
javamysql
zoit
zoit

Reputation: 667

Mistake in a SQL statement

I'm trying to find this mistake:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% ORDER BY t1.nombre_cuenta ASC' at line 1

Where is it?. Thanks so much:

public Tdatoscuentas[] consultarCuentas(int id_tipo_cuenta){  //Acabar método para login

    Tdatoscuentas[] params = null; 

    try {
        this.conexion=conectar();
        System.out.println("Vamos a usar el id tipo cuenta:"+id_tipo_cuenta);
        int id_tipo_cuenta_padre=id_tipo_cuenta-1;
        //System.out.println();

        SQL="SELECT t1.id_cuenta as id_cuenta,"+     
                "t1.n_cuenta as n_cuenta,"+
           "SUBSTRING_INDEX(t1.n_cuenta,'.',"+id_tipo_cuenta_padre+") as n_cuenta_padre,"+
           "t1.nombre_cuenta as nombre,"+         
           "(SELECT tt1.cif"+
           "  FROM cuentas_rs tt1"+
            " WHERE tt1.id_cuenta = t1.id_cuenta"+
            " ORDER BY tt1.f_inicio DESC "+
            " LIMIT 1) as cif,"+                          
            "(SELECT tt2.porcentaje "+
            " FROM impuestos tt2"+
            " WHERE tt2.id_pais = (SELECT tt1.cif"+
            "                     FROM cuentas_rs tt1"+
            "                     WHERE tt1.id_cuenta = t1.id_cuenta"+
            "                     ORDER BY tt1.f_inicio DESC"+
            "                     LIMIT 1)"+
            " ORDER BY tt2.f_inicio DESC "+
            "LIMIT 1)  as impuesto,"+
           "t1.descuento as descuento,"+      
           "t1.id_tipo_cuenta as nivel,"+          
           "t1.borrado  as borrado,"+        
           "t1.cod_cliente as cod_cliente "+     
           "FROM cuentas t1 "+
           "LEFT OUTER JOIN cuentas_rs t2 ON t2.id_cuenta = t1.id_cuenta"+ 
           " WHERE t1.id_esquema_asociado = 1"+  
           " AND t1.id_proveedor_cloud = 1"+
           " AND SUBSTRING(t1.n_cuenta,1,LENGTH(001.00001))"+
           " AND t1.id_tipo_cuenta =("+id_tipo_cuenta+")"+
           " AND t1.borrado = false"+         
           " AND t1.nombre_cuenta LIKE %" +         
           " ORDER BY t1.nombre_cuenta ASC";

        System.out.println("La consulta de SQL para consultar Cuentas es:"+SQL);
        this.pstm     = this.conexion.prepareStatement(SQL);

        this.rs =  pstm.executeQuery();

        this.rs.last(); 
        int numRows = this.rs.getRow(); 
        this.rs.beforeFirst();
        System.out.println("Vamos a hacer:"+numRows);
        params = new Tdatoscuentas[numRows];

        int i=0;
         while(this.rs.next()){
             params[i] = new Tdatoscuentas();
                params[i].setId_cuenta(this.rs.getInt("id_cuenta"));
                params[i].setN_cuenta(this.rs.getString("n_cuenta"));
                params[i].setN_cuenta_padre(this.rs.getString("n_cuenta_padre"));
                params[i].setNombre_cuenta(this.rs.getString("nombre"));
                params[i].setCif(this.rs.getString("cif"));
                params[i].setImpuesto(this.rs.getDouble("impuesto"));
                params[i].setDescuento(this.rs.getDouble("descuento"));
                params[i].setNivel(this.rs.getInt("nivel"));
                params[i].setBorrado(this.rs.getBoolean("borrado"));
                params[i].setCod_cliente(this.rs.getString("cod_cliente"));

            i++;        

            }
         System.out.println("params tiene una long de"+params.length);
         return params;  
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return params;  

}

Upvotes: 1

Views: 163

Answers (4)

alfredaday
alfredaday

Reputation: 2068

Take a look at the documentation on pattern matching.

Change

" AND t1.nombre_cuenta LIKE %" +

into

" AND t1.nombre_cuenta LIKE '%'" +

I also second @podiluska's suggestion to look into using bind variables in your queries.

Upvotes: 0

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

first your % should be like this '%'

" AND t1.nombre_cuenta LIKE '%'" +

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

It is here:

...
AND t1.nombre_cuenta LIKE %" +  <---------- It is here       
           " ORDER BY t1.nombre_cuenta ASC
...

You have to add the string to match after the % and it must be quoted something like: Like '%somevalue'.

Upvotes: 1

podiluska
podiluska

Reputation: 51494

It's in the line " AND t1.nombre_cuenta LIKE %" +

I would also suggest reading up on how preparestatement is designed to work before you release this into the wild.

Upvotes: 3

Related Questions