java.sql.SQLSyntaxErrorException: Syntax error: Encountered "St\u00f6rungen"

Question

I'm trying to fill my Derby database with some information but i keep getting this error..

I already encoded my project to UTF-8 but my Derby Database seem to not understand those -> "Ä,Ö,Ü"

How can i fix this ? I don't want to put "ae,oe,ue"

Here is the example where things start to happen:

st.executeUpdate("Insert into Scoarboard(Name,Wirkung,Ursprung,Stoffklasse,Schadenspot,Risiken,Legalitaet,NutzungMedi,NutzungGenuss,Konsum) "
                + "values (- Cannabis (Marihuana),- Sedativa/Hypnotika,- biogenpflanzlich,- Cannabinoide,"
                + "- 20/100,- Schwindel/übelkeit\r\n- Müdigkeit\r\n- Herzrasen\r\n- psychische Störungen,"
                + "- legal (verschreibungspflichtig),- Schmerzlindernd\r\n- Angstlösend\r\n- Reduziert Wachstum von Tumorzellen\r\n"
                + "- Entkrampfend\r\n- Antipsychotisch\r\n,- Appetitanregend\r\n- Euphorisierend\r\n- Entspannend\r\n,"  
                + "- Lunge (Joint)\r\n- Magen-Darm-Trakt (Gebäck)\r\n- Mundschleimhäute (Tinktur)\r\n)");

Answer 1

The first, i recommend to look up what the ANSI'S format, if the ansi format is not correct that you want, you should change its

AnSI FORMAT

Also i recommend to check the default encoding

default encoding and change

Also I recommend to use procedures, because it helps to save data, also possibly you don't need to change its

It is an example about an procedure in SQL SERVER

USE prueba;  
GO  
CREATE PROCEDURE dbo.aaaa

    @be_state int,   
    @resultado int OUTPUT

AS   

    BEGIN TRY


              INSERT INTO prueba.dbo.aaaa(be_state) VALUES (
              @be_state); 

         SET @resultado=1

    END TRY
    BEGIN CATCH
       SELECT  
           ERROR_NUMBER() AS ErrorNumber  
          ,ERROR_SEVERITY() AS ErrorSeverity  
          ,ERROR_STATE() AS ErrorState  
          ,ERROR_PROCEDURE() AS ErrorProcedure  
          ,ERROR_LINE() AS ErrorLine  
          ,ERROR_MESSAGE() AS ErrorMessage; 
       SET @resultado=2;
       RETURN @resultado;
    END CATCH; 




GO  

Example to connect a database

package com.prueba.conexiones;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.log4j.Logger;

public class Conexion implements Serializable {

    private static final long serialVersionUID = -3723843422114279249L;
    static Logger logger = Logger.getLogger(Conexion.class);

    public Connection conectar() throws ClassNotFoundException, SQLException {

        StringBuilder cadenaConexion;
        Connection conexion;

        cadenaConexion = new StringBuilder();
        cadenaConexion.append("jdbc:sqlserver://");
        cadenaConexion.append("127.0.0.1");
        cadenaConexion.append(":");
        cadenaConexion.append(9990);
        cadenaConexion.append(";");
        cadenaConexion.append("databaseName=");
        cadenaConexion.append("PRUEBA");
        cadenaConexion.append(";");
        cadenaConexion.append("user=");
        cadenaConexion.append("PRUEBA");
        cadenaConexion.append(";");
        cadenaConexion.append("password=");
        cadenaConexion.append("12345678");
        cadenaConexion.append(";");
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        conexion = DriverManager.getConnection(cadenaConexion.toString());

        return conexion;

    }

    public void Desconectar(Connection conexion) throws ClassNotFoundException {
        try {
            logger.debug("Comenzo el metodo desconectar() de la clase conexion ");

            if (conexion != null) {
                conexion.close();
            }

            logger.debug("Termino el metodo desconectar() de la clase conexion ");
        } catch (SQLException e) {
            logger.error("Error inexperado 0xC02643 CT" + e.getMessage(), e);
        }
    }

}

Finally to insert the data is the next

package com.prueba.insert;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.log4j.Logger;

public class ProcedimientoPrueba{

    static Logger logger = Logger.getLogger(ProcedimientoPrueba.class);
    private Connection conexion;
    private CallableStatement procedimiento;
    private boolean estado;

    public void conectarProcedimiento() throws SQLException {
        procedimiento = conexion.prepareCall("{call dbo.Prueba(?, ?)}");
    }

    public synchronized void insertarToBaseDatos() throws SQLException {
        estado = false;
        procedimiento.setInt(1, 1);
        procedimiento.registerOutParameter(2, java.sql.Types.INTEGER);
        procedimiento.execute();
        if (procedimiento.getInt(2) == 1) {
            estado = true;
        } else {
          System.out.println("Errro :(");
        }

    }


}

Answer 2

First use a PreparedStatement. That allows typed fields, like int for an integer number. Also it escapes strings containing apostrophe, backslash or line breaks (\r\n), and it helps with special characters.

String sql = "INSERT INTO Scoarboard(Name,Wirkung,Ursprung,Stoffklasse,Schadenspot,Risiken,"
                + "Legalitaet,NutzungMedi,NutzungGenuss,Konsum) "
                + "VALUES(?,?,?,?,?,?,?,?,?,?)";
try (PreparedStatement st = connection.prepareStatement(sql)) {
    st.setString(1, "Cannabis (Marihuana)");
    st.setString(2, "Sedativa/Hypnotika");
    st.setString(3, "biogenpflanzlich");    
    st.setString(4, "Cannabinoide");
    st.setString(5, "20/100");
    st.setString(6, "Schwindel/übelkeit\r\nMüdigkeit\r\nHerzrasen\r\npsychische Störungen");
    st.setString(7, "legal (verschreibungspflichtig)");
    st.setString(8, "Schmerzlindernd\r\nAngstlösend\r\n"
                + "Reduziert Wachstum von Tumorzellen\r\n"
                + "Entkrampfend\r\n- Antipsychotisch\r\n");
    st.setString(9, "Appetitanregend\r\nEuphorisierend\r\nEntspannend\r\n");
    st.setString(10, "Lunge (Joint)\r\nMagen-Darm-Trakt (Gebäck)\r\n"
                + "Mundschleimhäute (Tinktur)\r\n");

    st.executeUpdate();
}

The database table columns must be able to hold the special characters, and also the java compiler could erroneously use an other encoding as the editor. But let's assume everything is okay. Now it should work.

The try-with-resources syntax try(X x = open()) { ... } ensures that x is closed even on return or exception inside.

(String constants in SQL are normally given with an apostrophe WHERE Name LIKE 'Can%'.)