Android Question Query error

[Emme Developer]

Hi, i'm tryng to do a query in sqllite, but i get this error:

*** Service (starter) Create ***
sendnotification$ResumableSub_RefreshScheduledWifiresume (B4A line: 79)
Utils.sql.ExecNonQuery($"INSERT INTO scheduled
android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: INSERT INTO scheduled (id, starttime) Select 1, 1507212843636 WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = 1
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (near "1": syntax error (code 1): , while compiling: INSERT INTO scheduled (id, starttime) Select 1, 1507212843636 WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = 1)
#################################################################
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1005)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:570)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2055)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1986)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at b4a.example.sendnotification$ResumableSub_RefreshScheduledWifi.resume(sendnotification.java:282)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:240)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:180)
    at anywheresoftware.b4a.BA$2.run(BA.java:360)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6682)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1520)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1410)
    at de.robv.android.xposed.XposedBridge.main(XposedBridge.java:102)

Code is

Utils.sql.ExecNonQuery($"INSERT INTO scheduled (id, starttime) Select ${rs.GetInt("id")}, ${DateUtils.AddPeriod(DateTime.Now,p)} WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = ${rs.GetInt("id")}"$)

Tried also using ExecNonQuery2... Tried on SqlLiteStudio and works fine.. any help?

 

[DonManfred]

You are mixing a INSERT (non query) and a QUERY command (SELECT) in one command???

And you are wondering that this does not work?

 

[Emme Developer]

You are mixing a INSERT (non query) and a QUERY command (SELECT) in one command???

And you are wondering that this does not work?

https://www.w3schools.com/sql/sql_insert_into_select.asp

I don't understand your comment....

 

[OliverA]

you have a closing ) missing!

Utils.sql.ExecNonQuery($"INSERT INTO scheduled (id, starttime) Select ${rs.GetInt("id")}, ${DateUtils.AddPeriod(DateTime.Now,p)} WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = ${rs.GetInt("id")})"$)

You had )}"$ and it needed to be )})"$

 

[Emme Developer]

you have a closing ) missing!

Utils.sql.ExecNonQuery($"INSERT INTO scheduled (id, starttime) Select ${rs.GetInt("id")}, ${DateUtils.AddPeriod(DateTime.Now,p)} WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = ${rs.GetInt("id")})"$)

You had )}"$ and it needed to be )})"$

Damn you're right. Tried it 2182104 times. Many thanks
@DonManfred as you can see, is possible to put a non query and query in a single command

 

[OliverA]

Code is

If you want a more "global" SQL syntax, then do not skip the FROM in your SQL (as is, it would not work with ORACLE, but it should work fine in MySQL, PostgreSQL, and SQL Server).

Utils.sql.ExecNonQuery($"INSERT INTO scheduled (id, starttime) Select * FROM (VALUES (${rs.GetInt("id")}, ${DateUtils.AddPeriod(DateTime.Now,p)})) WHERE Not EXISTS (Select 1 FROM scheduled WHERE id = ${rs.GetInt("id")})"$)

See this article for explanation: http://modern-sql.com/use-case/select-without-from