public void executeSqlBatch(
String[] queryarr, JSONArray[] jsonparams, String[] queryIDs, String tx_id) {
try {
this.myDb.beginTransaction();
String query = "";
String query_id = "";
int len = queryarr.length;
for (int i = 0; i < len; i++) {
query = queryarr[i];
query_id = queryIDs[i];
if (query.toLowerCase().startsWith("insert") && jsonparams != null) {
SQLiteStatement myStatement = this.myDb.compileStatement(query);
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
} else if (jsonparams[i].get(j) instanceof Number) {
myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
} else {
myStatement.bindString(j + 1, jsonparams[i].getString(j));
}
}
long insertId = myStatement.executeInsert();
String result = "{'insertId':'" + insertId + "'}";
this.sendJavascript(
"SQLitePluginTransaction.queryCompleteCallback('"
+ tx_id
+ "','"
+ query_id
+ "', "
+ result
+ ");");
} else {
String[] params = null;
if (jsonparams != null) {
params = new String[jsonparams[i].length()];
for (int j = 0; j < jsonparams[i].length(); j++) {
params[j] = jsonparams[i].getString(j);
if (params[j] == "null") // XXX better check
params[j] = "";
}
}
Cursor myCursor = this.myDb.rawQuery(query, params);
this.processResults(myCursor, query_id, tx_id);
myCursor.close();
}
}
this.myDb.setTransactionSuccessful();
} catch (SQLiteException ex) {
ex.printStackTrace();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql(): Error=" + ex.getMessage());
this.sendJavascript(
"SQLitePluginTransaction.txErrorCallback('" + tx_id + "', '" + ex.getMessage() + "');");
} catch (JSONException ex) {
ex.printStackTrace();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql(): Error=" + ex.getMessage());
this.sendJavascript(
"SQLitePluginTransaction.txErrorCallback('" + tx_id + "', '" + ex.getMessage() + "');");
} finally {
this.myDb.endTransaction();
Log.v("executeSqlBatch", tx_id);
this.sendJavascript("SQLitePluginTransaction.txCompleteCallback('" + tx_id + "');");
}
}
/**
* Get rows results from query cursor.
*
* @param cur Cursor into query results
* @return results in string form
*/
private JSONObject executeSqlStatementNDK(
String query, JSONArray paramsAsJson, CallbackContext cbc) throws Exception {
JSONObject rowsResult = new JSONObject();
boolean hasRows = false;
SQLiteStatement myStatement = mydb.prepareStatement(query);
try {
String[] params = null;
params = new String[paramsAsJson.length()];
for (int i = 0; i < paramsAsJson.length(); ++i) {
if (paramsAsJson.isNull(i)) {
myStatement.bindNull(i + 1);
} else {
Object p = paramsAsJson.get(i);
if (p instanceof Float || p instanceof Double)
myStatement.bindDouble(i + 1, paramsAsJson.getDouble(i));
else if (p instanceof Number) myStatement.bindLong(i + 1, paramsAsJson.getLong(i));
else myStatement.bindTextNativeString(i + 1, paramsAsJson.getString(i));
}
}
hasRows = myStatement.step();
} catch (Exception ex) {
ex.printStackTrace();
String errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql[Batch](): Error=" + errorMessage);
// cleanup statement and throw the exception:
myStatement.dispose();
throw ex;
}
// If query result has rows
if (hasRows) {
JSONArray rowsArrayResult = new JSONArray();
String key = "";
int colCount = myStatement.getColumnCount();
// Build up JSON result object for each row
do {
JSONObject row = new JSONObject();
try {
for (int i = 0; i < colCount; ++i) {
key = myStatement.getColumnName(i);
switch (myStatement.getColumnType(i)) {
case SQLColumnType.NULL:
row.put(key, JSONObject.NULL);
break;
case SQLColumnType.REAL:
row.put(key, myStatement.getColumnDouble(i));
break;
case SQLColumnType.INTEGER:
row.put(key, myStatement.getColumnLong(i));
break;
case SQLColumnType.BLOB:
case SQLColumnType.TEXT:
default: // (just in case)
row.put(key, myStatement.getColumnTextNativeString(i));
}
}
rowsArrayResult.put(row);
} catch (JSONException e) {
e.printStackTrace();
}
} while (myStatement.step());
try {
rowsResult.put("rows", rowsArrayResult);
} catch (JSONException e) {
e.printStackTrace();
}
}
myStatement.dispose();
return rowsResult;
}
/**
* Executes a batch request and sends the results via sendJavascriptCB().
*
* @param dbname The name of the database.
* @param queryarr Array of query strings
* @param jsonparams Array of JSON query parameters
* @param queryIDs Array of query ids
* @param cbc Callback context from Cordova API
*/
private void executeSqlBatch(
String dbname,
String[] queryarr,
JSONArray[] jsonparams,
String[] queryIDs,
CallbackContext cbc) {
SQLiteDatabase mydb = this.getDatabase(dbname);
if (mydb == null) return;
String query = "";
String query_id = "";
int len = queryarr.length;
JSONArray batchResults = new JSONArray();
for (int i = 0; i < len; i++) {
query_id = queryIDs[i];
JSONObject queryResult = null;
String errorMessage = "unknown";
try {
boolean needRawQuery = true;
query = queryarr[i];
// UPDATE or DELETE:
// NOTE: this code should be safe to RUN with old Android SDK.
// To BUILD with old Android SDK remove lines from HERE: {{
if (android.os.Build.VERSION.SDK_INT >= 11
&& (query.toLowerCase().startsWith("update")
|| query.toLowerCase().startsWith("delete"))) {
SQLiteStatement myStatement = mydb.compileStatement(query);
if (jsonparams != null) {
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
} else if (jsonparams[i].get(j) instanceof Number) {
myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
} else if (jsonparams[i].isNull(j)) {
myStatement.bindNull(j + 1);
} else {
myStatement.bindString(j + 1, jsonparams[i].getString(j));
}
}
}
int rowsAffected = -1; // (assuming invalid)
// Use try & catch just in case android.os.Build.VERSION.SDK_INT >= 11 is lying:
try {
rowsAffected = myStatement.executeUpdateDelete();
// Indicate valid results:
needRawQuery = false;
} catch (SQLiteException ex) {
// Indicate problem & stop this query:
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v(
"executeSqlBatch", "SQLiteStatement.executeUpdateDelete(): Error=" + errorMessage);
needRawQuery = false;
} catch (Exception ex) {
// Assuming SDK_INT was lying & method not found:
// do nothing here & try again with raw query.
}
if (rowsAffected != -1) {
queryResult = new JSONObject();
queryResult.put("rowsAffected", rowsAffected);
}
} // to HERE. }}
// INSERT:
if (query.toLowerCase().startsWith("insert") && jsonparams != null) {
needRawQuery = false;
SQLiteStatement myStatement = mydb.compileStatement(query);
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
} else if (jsonparams[i].get(j) instanceof Number) {
myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
} else if (jsonparams[i].isNull(j)) {
myStatement.bindNull(j + 1);
} else {
myStatement.bindString(j + 1, jsonparams[i].getString(j));
}
}
long insertId = -1; // (invalid)
try {
insertId = myStatement.executeInsert();
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.executeInsert(): Error=" + errorMessage);
}
if (insertId != -1) {
queryResult = new JSONObject();
queryResult.put("insertId", insertId);
queryResult.put("rowsAffected", 1);
}
}
if (query.toLowerCase().startsWith("begin")) {
needRawQuery = false;
try {
mydb.beginTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.beginTransaction(): Error=" + errorMessage);
}
}
if (query.toLowerCase().startsWith("commit")) {
needRawQuery = false;
try {
mydb.setTransactionSuccessful();
mydb.endTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v(
"executeSqlBatch",
"SQLiteDatabase.setTransactionSuccessful/endTransaction(): Error=" + errorMessage);
}
}
if (query.toLowerCase().startsWith("rollback")) {
needRawQuery = false;
try {
mydb.endTransaction();
queryResult = new JSONObject();
queryResult.put("rowsAffected", 0);
} catch (SQLiteException ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLiteDatabase.endTransaction(): Error=" + errorMessage);
}
}
// raw query for other statements:
if (needRawQuery) {
String[] params = null;
if (jsonparams != null) {
params = new String[jsonparams[i].length()];
for (int j = 0; j < jsonparams[i].length(); j++) {
if (jsonparams[i].isNull(j)) params[j] = "";
else params[j] = jsonparams[i].getString(j);
}
}
Cursor myCursor = mydb.rawQuery(query, params);
if (query_id.length() > 0) {
queryResult = this.getRowsResultFromQuery(myCursor);
}
myCursor.close();
}
} catch (Exception ex) {
ex.printStackTrace();
errorMessage = ex.getMessage();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql[Batch](): Error=" + errorMessage);
}
try {
if (queryResult != null) {
JSONObject r = new JSONObject();
r.put("qid", query_id);
r.put("type", "success");
r.put("result", queryResult);
batchResults.put(r);
} else {
JSONObject r = new JSONObject();
r.put("qid", query_id);
r.put("type", "error");
JSONObject er = new JSONObject();
er.put("message", errorMessage);
r.put("result", er);
batchResults.put(r);
}
} catch (JSONException ex) {
ex.printStackTrace();
Log.v("executeSqlBatch", "SQLitePlugin.executeSql[Batch](): Error=" + ex.getMessage());
// TODO what to do?
}
}
cbc.success(batchResults);
}