Java Development

[cjlopez]

I'm having a little issue with my SQLite.

Turns out, i download data i store on my bb database, but then again, before storing said data, i want to check if its already on the table, so i dont have it repeated. This is the structure of my table

"CREATE TABLE 'LECGRAL'" + 
                "(" + 
                "'ID_LEC'           TEXT  NOT NULL," + 
                "'ID_LECTURISTA'    TEXT," + 
                "'COD_UNICOM'       TEXT  NOT NULL," + 
                "'RUTA'             TEXT  NOT NULL," +
                "'ITINERARIO'       TEXT  NOT NULL," + 
                "'CICLO'            TEXT  NOT NULL," + 
                "'ESTADO'           TEXT  NOT NULL" + 
                ")"

 Then is the insert i'm doing on the table

"INSERT INTO LECGRAL(ID_LEC, " + 
                                    "ID_LECTURISTA, " + 
                                    "COD_UNICOM, " + 
                                    "RUTA, " + 
                                    "ITINERARIO, " + 
                                    "CICLO, " + 
                                    "ESTADO) " + 
                                    "VALUES ('" + 
                                    (String) encabezado.get("Id_Lec") + "','" + 
                                    (String) encabezado.get("Id_Lecturista") + "','" + 
                                    (String) encabezado.get("Cod_Unicom") + "','" + 
                                    (String) encabezado.get("Ruta") + "','" + 
                                    (String) encabezado.get("Itinerario") + "','" + 
                                    (String) encabezado.get("Ciclo") + "','" + 
                                    (String) encabezado.get("Estado") + "')"

 I store the insert vuales on a hashtable then send the hashtable to my insert function

So far, everything works perfectly, until i want to check if the data is inserted. This is the function i use to see if its there

    private boolean exist(String query) throws Exception
    {
        boolean result = false;
        st = miDB.createStatement(query);
        st.prepare();
        c = st.getCursor();
        if(c.next())
            result = true;
        c.close();
        st.close();            
        return result;
    }    

 Here's the previously inserted data

ID_LEC          ID_LECturista Cod_Unicom Ruta Itinerario Ciclo  Estado
"2011080456"	"7401"	      "1105"	"3"	"6"	"8"	"ERI03"

 And here's the query i use to check if there's data

SELECT * FROM LECGRAL WHERE ID_LEC = '2011080456'
OR
SELECT * FROM LECGRAL WHERE ID_LEC = 2011080456

 But still, its always returning me a false, the cursor always return empty, any idea what i'm doing wrong? Is my syntax incorrect??

I'm using a firefox plugin to work with my querys syntax and info, is the SQLite Manager, this way i can open the created db file of my bb and run querys on it (to do this, just dont encrypt the database), and i run this query there, and whoala, there's the dataRow

[eicheled]

I recreated your database and populated it with your data, then ran the query. Everything worked for me, but I did it all on command line (not in Java). It might be a silly question, but have you checked the values in the debugger of things like your query string, and even your miDB variable? Are you catching the exception that this exist() method might throw? (It could be throwing an exception before it finishes, leaving your result variable as false).

One other thing, I don't know if this would work for your dataset but you could put a unique index on your table, and then just insert any row you want, and handle the duplicate error that would be thrown (rather than prechecking to see if your data exists before every insert).

[cjlopez]

The query string comes from the constructed string while debbuging theprogram, where was no exception thrown, and having the "exist" function throw an Exception on any kind of error, forces me to walys have it inside a try...catch, so i wont forget

Also, indeed, at some point i'll be turning ID_LEC inot a unique index, so duplicates cant exist, and ID_LEC is the FK on another table, meaning the data on another table will be in direct relation (1-n) so i can protect it, but still, that would throw an exception when trying to insert, and i'd like to refrain myself from using that method to check if the data its already there.

Anyway, i'm still seeing why this might be happening, i'm thinking on creating a stand alone program on which i can throw query to the database, directo from the phone and see the results