0

I'm programming a method insertOrUpdate in my android application and I can't do this:

database.execSQL('IF (select count(*) from RegNascimento where codigoAnimal = ?) > 0 then begin update RegNascimento set cgnRegNascimento = ? where codigoAnimal = ?; end else begin insert into RegNascimento(cgnRegNascimento, dataInspecaoRegNascimento) values (?,?); end;');

I'm getting this error:

06-26 09:24:58.835: E/SQLiteLog(3924): (1) near "if": syntax error
06-26 09:24:58.835: W/System.err(3924): android.database.sqlite.SQLiteException: near "if": syntax error (code 1): , while compiling: if (select count(*) from RegDefinitivo where codigoAnimal = ?) > 0 then begin update RegDefinitivo set cgdRegDefinitivo = ?, seloRegDefinitivo = ? where codigoAnimal = ?; end else begin insert into RegDefinitivo(cgdRegDefinitivo, seloRegDefinitivo, dataInspecaoRegDefinitivo) values (?,?,?); end;
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1013)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:624)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
06-26 09:24:58.835: W/System.err(3924):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
06-26 09:24:58.840: W/System.err(3924):     at org.apache.cordova.Storage.executeSql(Storage.java:173)
06-26 09:24:58.840: W/System.err(3924):     at org.apache.cordova.Storage.execute(Storage.java:83)
06-26 09:24:58.840: W/System.err(3924):     at org.apache.cordova.api.CordovaPlugin.execute(CordovaPlugin.java:66)
06-26 09:24:58.840: W/System.err(3924):     at org.apache.cordova.api.PluginManager.exec(PluginManager.java:224)
06-26 09:24:58.840: W/System.err(3924):     at org.apache.cordova.ExposedJsApi.exec(ExposedJsApi.java:51)
06-26 09:24:58.840: W/System.err(3924):     at android.webkit.JWebCoreJavaBridge.sharedTimerFired(Native Method)
06-26 09:24:58.840: W/System.err(3924):     at android.webkit.JWebCoreJavaBridge.sharedTimerFired(Native Method)
06-26 09:24:58.840: W/System.err(3924):     at android.webkit.JWebCoreJavaBridge.fireSharedTimer(JWebCoreJavaBridge.java:92)
06-26 09:24:58.840: W/System.err(3924):     at android.webkit.JWebCoreJavaBridge.handleMessage(JWebCoreJavaBridge.java:108)
06-26 09:24:58.840: W/System.err(3924):     at android.os.Handler.dispatchMessage(Handler.java:99)
06-26 09:24:58.840: W/System.err(3924):     at android.os.Looper.loop(Looper.java:137)
06-26 09:24:58.840: W/System.err(3924):     at android.webkit.WebViewCore$WebCoreThread.run(WebViewCore.java:1064)
06-26 09:24:58.840: W/System.err(3924):     at java.lang.Thread.run(Thread.java:856)

PS: I'm using sencha touch, but the sql is being executed in android via plugin. Thanks

Anderson Silva
  • 709
  • 1
  • 7
  • 31
  • You're not setting a value for `codigoAnimal` in your insert, so there will be no duplicate the next time it's run. Is that intentional? – Joachim Isaksson Jun 26 '13 at 12:34

3 Answers3

0

These three questions might help you figure out the syntax problem.

IF-Statement in SQLite: update or insert?

IF() statement alternative in SQLite

Does sqlite support any kind of IF(condition) statement in a select

Hope this helps

Community
  • 1
  • 1
dudebrobro
  • 1,287
  • 10
  • 17
0

You can try using a CASE expression. Quoting the SQLite documentation:

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.

Otherwise, you can do this as three separate SQLite operations, with the if/else logic in JavaScript. Bear in mind that SQLite is local, and so there is no network round-trip overhead per operation, so the cost of doing the if/else in JavaScript versus in the database should not be dramatic.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • `CASE` expressions do not help in this case. The code in the question does not use JavaScript. – CL. Jun 26 '13 at 12:40
  • @CL: "CASE expressions do not help in this case" -- please consider explaining what the problem is with `CASE`. "The code in the question does not use JavaScript" -- according to the OP it does, as the OP is using Sencha Touch via Apache Cordova. – CommonsWare Jun 26 '13 at 12:44
0

SQLite does not have an IF statement.

You could just try the UPDATE, and if no record was actually updated, execute the INSERT:

ContentValues cv = new ContentValues();
cv.put("cgnRegNascimento", ...);
if (db.update("RegNascimento", cv, "codigoAnimal = ?", new String[] { ... }) < 1) {
    cv.put("dataInspecaoRegNascimento", ...);
    long newID = db.insert("RegNascimento", null, cv);
}

Alternatively, if you have a unique index on the codigoAnimal column, you can just use the INSERT OR REPLACE statement:

ContentValues cv = new ContentValues();
cv.put("codigoAnimal", ...);
cv.put("cgnRegNascimento", ...);
cv.put("dataInspecaoRegNascimento", ...);
db.insertWithOnConflict("RegNascimento", null, cv, SQLiteDatabase.CONFLICT_REPLACE);
CL.
  • 173,858
  • 17
  • 217
  • 259