B4J Question SQL command including RETURNING

[Guenter Becker]

Hello,
I have a SQlite Database and I want to use this command to insert/update or delete data and get returned the affected records.
By SQL Syntax this is possible by adding the "RETURNING Columnname" command.

Example:
INSERT OR REPLACE INTO Proddetail (prodno,prodname,info) VALUES (4,'D','XYZ') RETURNING prodno,info

Testing command in DBBrowser it works perfect, using it in B4X it fails.

I tried to use it in B4A or B4J like

Dim cmd As String ="INSERT OR REPLACE INTO Proddetail (prodno,prodname,info) VALUES (4,'D','XYZ') RETURNING prodno,info"
'fails Error: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "RETURNING": syntax error)
dim rs as resultset = SQL.ExecQuery(cmd)

I know that used way in the shown code is unusal because INSERT/UPDATE a.s.o. is normally done by sql.ExceNonQuery. But this way it will not return the affected records and will also not accept RETURNING command.

Question:
What is the correct way to use the SQL Command with RETURNING ?

 

[zed]

To use RETURNING, you must execute the query as a query that returns a ResultSet, therefore with ExecuteQuery (or ExecQuery2) and not with ExecNonQuery.

Try this:

Dim rs As ResultSet = sql.ExecQuery("INSERT OR REPLACE INTO Proddetail (prodno, prodname, info) VALUES (4, 'D', 'XYZ') RETURNING prodno, info")

Do While rs.NextRow
    Log(rs.GetInt("prodno"))
    Log(rs.GetString("info"))
Loop
rs.Close

ExecNonQuery is designed for commands that return nothing.
However, as soon as you add RETURNING, the command becomes a SELECT query, so B4X expects a ResultSet.

RETURNING nécessite SQLite ≥ 3.35

To check the version:

Dim rs As ResultSet = sql.ExecQuery("select sqlite_version()")
rs.NextRow
Log(rs.GetString2(0))
rs.Close

untested code

 

[LucaMs]

RETURNING nécessite SQLite ≥ 3.35

I think that's his problem.

Try using a more recent driver. The "RETURNING" command was introduced in version 3.35.0.
https://www.b4x.com/android/forum/threads/sqlite-jdbc-library-version-updates.133792/

 

[Guenter Becker]

Hello Luca,
thank you for the quick response. I checked the additional jar files in my B4J Test environment and found that '#AdditionalJar: sqlite-jdbc-3.30.0 was installed and used.
changed it to #AdditionalJar: sqlite-jdbc-3.46.0.0 and it works.
In Germany there is a folk wisdom for this situation as "You can't see the forest for the trees." Sorry my stupid fault.

 

[LucaMs]

Sorry my stupid fault.

I don't see any stupidity!

Don't think I knew exactly which version "Returning" was added to; I just looked it up.