Error in Android SQLite query

Question

Error in Android SQLite query

Navigation

#1 by (1 votes)

4

I want to make a query of the id_empresa of the table TABLE_CLIENTE , where the client_name is equal to what is inserted in the textView called et_searchClient , making the query INSIDE the activity.

I already tried several methods like doing the search directly from the database and it does not work out well, this is my current code:

Activity:

public class ListaEmpresas extends AppCompatActivity implements View.OnClickListener{

    ArrayList<Notas> listaNotas;
    RecyclerAdaptador recyclerAdaptador;
    RecyclerView recyclerView;
    private Activity mActivity;
    private Context mContext;
    private ImageView logoImage;
    private ListView clientsList;
    private Button btnInspeccion;
    private EditText et_searchClient;
    private TextView textView;
    ArrayList<String> clientList;
    ArrayAdapter adaptador;
    DatabaseHelper dbconeccion;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_lista_empresas);
        mContext = getApplicationContext();
        mActivity = ListaEmpresas.this;
        clientsList = (ListView) findViewById(R.id.clientsList);
        logoImage = (ImageView) findViewById(R.id.profileLogo);
        btnInspeccion = (Button)findViewById(R.id.btnInspeccion);
        //TextView para la impresión de los campos de la base de datos
        textView = (TextView)findViewById(R.id.textView);
        et_searchClient = (EditText)findViewById(R.id.et_searchClient);
        dbconeccion = new DatabaseHelper(getApplicationContext());

        if(!SaveSharedPreference.getStringKeyValue(this, SaveSharedPreference.KEY_ORGANIZATION_IMAGE_PATH).equals("")){
            Bitmap b = SaveSharedPreference.loadImageFromStorage(SaveSharedPreference.getStringKeyValue(this, SaveSharedPreference.KEY_ORGANIZATION_IMAGE_PATH), SaveSharedPreference.ORGANIZATION_NAME);
            if(b != null){
                logoImage.setImageBitmap(b);
            }
        }
    }
    @Override
    public void onClick(View v) {
        switch (v.getId()) {

            case R.id.btnInspeccion:
                SQLiteDatabase search = dbconeccion.getWritableDatabase();
                String codigo = et_searchClient.getText().toString();
                //Búsqueda RAWQUERY, quitarle el comentario a las lineas 109, 110, 116, 118, 120122, 123
                Cursor cursor = search.rawQuery("select id_cliente from TABLE_CLIENTE where nombre_cliente ='" +codigo+"'", null);
                try {
                    if (!codigo.isEmpty()) {
                        if (cursor.moveToFirst()) {

                            textView.setText(cursor.getString(1));

                            //INTENT QUE FUNCIONA

                            Intent intent = new Intent(this, select_form.class);

                            intent.putExtra("textView", textView.getText().toString());

                            startActivity(intent);

                        } else
                            Toast.makeText(this, "No ha elegido ningún cliente", Toast.LENGTH_SHORT).show();
                    } else {
                        Toast.makeText(this, "No existe ningún usuario con ese nombre", Toast.LENGTH_SHORT).show();
                    }
                }catch (Exception ex)
                {
                    Log.e("BD", "ERROR AL CONECTAR CON LA BD");
                }
                search.close();
                break;
            default:
        }
    }
}

Database:

public class DatabaseHelper  extends SQLiteOpenHelper {


    public DatabaseHelper(Context context) {super(context, Util.DATABASE_NAME, null, 1); }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_USUARIO   + "  (_id INTEGER PRIMARY KEY AUTOINCREMENT,id_user STRING, nombre_user STRING, direccion STRING, telefono STRING, email STRING, id_empresa STRING, nombre_empresa STRING, rol_user STRING)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_ZONA      + "  (_id INTEGER PRIMARY KEY AUTOINCREMENT,id_zona STRING, nombre_zona STRING, id_user STRING, desc_zona STRING)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_CLIENTE   + "  (_id INTEGER PRIMARY KEY AUTOINCREMENT,id_cliente STRING, id_empresa STRING, id_zona STRING, nombre_cliente STRING, direccion STRING, telefono STRING, correo STRING, latitud STRING, longitud STRING)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_FORMS + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, id_form STRING, id_user STRING, id_empresa STRING, form STRING)");// campos para almacenar los formularios
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_QUESTIONS + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, id_question STRING, id_form STRING, question STRING, required STRING)");//tabla de almacenamiento de preguntas de los formularios
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_ANSWERS + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, id_answer STRING, id_question STRING, answer STRING, id_answer_type STRING)");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + Util.TABLE_ATRIBUTES + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, id_attribute STRING, attribute STRING, type NUMBER, value STRING)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_USUARIO);
        db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_ZONA);
        db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_CLIENTE);
        onCreate(db);
    }

    public boolean resetAllDataWithNoInfoSync() {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_USUARIO);
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_ZONA);
            db.execSQL("DROP TABLE IF EXISTS "  + Util.TABLE_CLIENTE);
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_ATRIBUTES);
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_ANSWERS);
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_QUESTIONS);
            db.execSQL("DROP TABLE IF EXISTS " + Util.TABLE_FORMS);
            onCreate(db);
        }
        finally {
            db.close();
        }
        return true;
    }

    public boolean insertUsuario(  ArrayList<Usuario> dats) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            for (final Usuario user : dats) {
                String a =  "'"+user.getId_usuario() + "', '"+user.getNombre()+"' ,'"+user.getDireccion()+"', '"+ user.getTelefono() +"' , '"+ user.getCorreo()+"' , '"+ user.getId_empresa()+"' , '"+ user.getNombre_empresa()+"' , '"+ user.getRolUser()+"'" ;
                String values = " values ("+ a +" )";
                String g = "insert into " + Util.TABLE_USUARIO + " (id_user, nombre_user, direccion, telefono, email, id_empresa, nombre_empresa, rol_user)" + values;
                // Log.i("INSERTSQuiz",g);
                db.execSQL(g);
            }
        }catch ( Exception ex   ){
            Log.d("SAG ERROR",ex.getMessage() );
        }
        finally {
            db.close();
        }
        return  true;
    }

    public Cursor readZonaData() {
        SQLiteDatabase db = this.getWritableDatabase();
        String[] nameDescZona = new String[] {
                "_id",
                "nombre_zona",
                "desc_zona"
        };
        Cursor c = db.query(Util.TABLE_ZONA, nameDescZona, null,
                null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

}

Useful Class:

public class Util {
    public static String DATABASE_NAME = "data.db";
    public static String TABLE_USUARIO = "usuario";
    public static String TABLE_ZONA = "zona";
    public static String TABLE_CLIENTE = "cliente";
    public static String TABLE_FORMS = "form";//Tabla de los formularios
    public static String TABLE_QUESTIONS = "questions";// tabla de los cuestionarios
    public static String TABLE_ANSWERS = "answers";// tabla de respuestas
    public static String TABLE_ATRIBUTES = "atributes";

    //public static final String URL_TARGET = "http://192.168.100.34/oauth/"; ESTE YA ESTABACOMENTADO
    public static final String URL_TARGET = "http://ec2-13-58-155-97.us-east-2.compute.amazonaws.com/oauth/";
    //public static final String URL_TARGET = "http://ec2-13-58-155-97.us-east-2.compute.amazonaws.com/administration/organizations/clients";  YO PUSE ESTA, ESTÁ MAL
}

And these are the errors he sends me, which apparently is in the query:

08-27 11:31:52.008 14721-14721/mx.com.stesso.appstesso D/ERROR: near ",": syntax error (code 1): , while compiling: insert into answersid_answers, id_question, answer, id_answer_typevalues (11', '11', 'q11respuesta11(hora)', '14)
08-27 11:31:52.015 14721-14721/mx.com.stesso.appstesso E/SQLiteLog: (1) near ",": syntax error
08-27 11:31:52.015 14721-14721/mx.com.stesso.appstesso D/ERROR: near ",": syntax error (code 1): , while compiling: insert into answersid_answers, id_question, answer, id_answer_typevalues (12', '12', 'q12respuesta12(fechaHora)', '15)

I would appreciate your help.

android android-sqlite consulta-sql

asked by DarkMifnight 27.08.2018 в 19:21

source

1 answer

How to delete a repeated character in C? PyQT - Switch between windows with StackedWidget

score 1 · Accepted Answer

You should check the construction of the query string, it seems that the error would be in the absence of parentheses, so it gives you the error with the comma, because the parser expects to find the name of a table, first finding a comma:

insert into answersid_answers , id_question, answer, id_answer_typevalues ('12', '12', 'q12 reply12 (date)', '15')

Your query is not being constructed correctly. A correct query would be:

INSERT INTO table1 (
 column1,
 column2 ,..)
VALUES
(
 value1,
 value2 ,...);

In your case it should be:

 insert into answers (id_answers, id_question, answer, id_answer_type ) values (12', '12', 'q12respuesta12(fechaHora)', '15)

I add

Also watch the quotes in the data, in the error it is also observed that you are missing quotes in the data. If you do not correct this, it will also give you an error. Review the query constructor well.