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();
}
CodePudding user response：
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.