Search code examples
javaandroidsqlsqliteandroid-sqlite

How to read value with get readable and insert into another table with set writeable in the same function

I need to read value with get readable and insert into another table with set writeable in the same function.

public void Check(String msg){

    //getdata from database
    String query="SELECT "+ CUL_ID +" FROM  " + STUDENT_TABLE + " WHERE "+ CUL_QRCODE +" like "+ msg +" ";
    SQLiteDatabase db1=this.getReadableDatabase();
    db1.execSQL(query);
    db1.close();
// I need to return this query in string variable value and put it into the queryString

    String value = "";
    String queryString ="INSERT INTO " + TABLE_PRESENCE_TABLE + " VALUES (" + value + " ,( SELECT count( "+CUL_ID_PRESENCE+" ) FROM  " + TABLE_PRESENCE_TABLE + " WHERE "+ CUL_ID_PRESENCE+ " = ( SELECT "+ CUL_ID +" FROM "+ STUDENT_TABLE +" WHERE "+ CUL_QRCODE + " = "+ msg +"))+"+1+" , datetime('now'));";
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL(queryString);
    db.close();

}
Solution

  • I need to read value with get readable and insert into another table with set writeable in the same function.

    You don't need to. As much as you have used/coded a subquery for the 2nd column as per :-

    ( SELECT count( "+CUL_ID_PRESENCE+" ) FROM  " + TABLE_PRESENCE_TABLE + " WHERE "+ CUL_ID_PRESENCE+ " = ( SELECT "+ CUL_ID +" FROM "+ STUDENT_TABLE +" WHERE "+ CUL_QRCODE + " = "+ msg +"))+"+1+"

    You can code a subquery for the 1st value i.e. replace value with the query that obtained the value.

    e.g. you could have a single insert without the preceding query, along the lines of :-

    INSERT INTO presence VALUES
    (
        coalesce((SELECT cul_id FROM student WHERE cul_qrcode = 'message'),-99),
        (SELECT count(cul_id_presence) FROM  presence WHERE CUL_ID_PRESENCE = (SELECT CUL_ID FROM STUDENT WHERE CUL_QRCODE = 'message')+1),
        datetime('now')
    )
;

    • note that the coalesce function will convert a NULL (if there is no cul_qrcode LIKE msg) to another value (-99 in the case of the above).

    • table and column names have been guessed so they may not match what you actually have.

    • Otherwise to actually receive the result of the query you capture the result in a Cursor BUT you do not use execSQL, you use rawQuery or the query convenience method.

    • execSQL Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

    How can i Receive the value of getReadableDatabase() in variable string that's it

    To use the query followed by the insert you would have something like :-

    Cursor csr = db1.rawQuery(query,null); 
String value = "-99"; /* assume that if nothing is returned then use -99 */
if(csr.moveToFirst) {
    value = csr.getString(0);
}
csr.close(); /* SHOULD ALWAYS CLOSE CURSORS WHEN FINISHED WITH THEM */

    However, it is BAD practice to concatenate parameters to SQL as this is considered a potential for SQL injection. Instead you should bind parameters (which will over come a MAJOR flaw as you appear to probably NOT be enclosing the msg in single quotes, binding a parameter will enclose the parameter in single quotes). So the above should be :-

    public void Check(String msg){

    //getdata from database
    String query="SELECT "+ CUL_ID +" FROM  " + STUDENT_TABLE + " WHERE "+ CUL_QRCODE +" like ?"; //<<<<< use ? to allow bind of msg
    SQLiteDatabase db1=this.getReadableDatabase();
    Cursor csr = db1.rawQuery(query,new String[]{msg}); 
    String value = "-99"; /* assume that if nothing is returned then use -99 */
    if(csr.moveToFirst) {
        value = csr.getString(0);
    }
    csr.close(); /* SHOULD ALWAYS CLOSE CURSORS WHEN FINISHED WITH THEM */
    ....

    Furthermore

    There is no need to close the database and and then reopen it to apparently switch between readable and writable. That is getReadableDatabase will if it's possible return a writeable database except if there is some reason why the database cannot be opened as writable. If a read only database is returned then the subsequent getWritableDatabase would very likely fail unless somehow the underlying issue is fixed.

    as per:-

    Create and/or open a database. This will be the same object returned by getWritableDatabase() unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase() may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future.