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:
[CODE lang="b4x" title="B4X"]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
[/CODE]

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:
[CODE lang="b4x" title="B4X"]Dim rs As ResultSet = sql.ExecQuery("select sqlite_version()")
rs.NextRow
Log(rs.GetString2(0))
rs.Close
[/CODE]

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.