CubeSQL INSERT Exception 'another row available' when using RETURNING

Databases
1

I have a CubeSQL database and am trying to INSERT a new record (see below) using SQLDatabaseMBS, but I want to return the new record ID so I am appending ‘RETURNING id’.

INSERT INTO Lockers (ApplicationName, CloudStorageID, Created, DatabaseFieldID, DatabaseFieldName, DatabaseName, DatabaseTable, ExpiryDate, isActive, isArchived, isDeleted, isFile, isFolder, isImmovable, isImmutable, isMarkedForDeletion, isNotifyOwner, isOnCloud, isOnLocalDrive, isOut, isPublic, isString, isTemplate, isURL, Keywords, LastOutDate, LastOutIPAddress, LastOutUserID, LockerURL, Modified, Notes, ObfuscatedPathParent, Status, UserID, VersionsToKeep, Name, NativePathParent, ObfuscatedFilename, ZipPassword) VALUES (:ApplicationName, :CloudStorageID, :Created, :DatabaseFieldID, :DatabaseFieldName, :DatabaseName, :DatabaseTable, :ExpiryDate, :isActive, :isArchived, :isDeleted, :isFile, :isFolder, :isImmovable, :isImmutable, :isMarkedForDeletion, :isNotifyOwner, :isOnCloud, :isOnLocalDrive, :isOut, :isPublic, :isString, :isTemplate, :isURL, :Keywords, :LastOutDate, :LastOutIPAddress, :LastOutUserID, :LockerURL, :Modified, :Notes, :ObfuscatedPathParent, :Status, :UserID, :VersionsToKeep, :Name, :NativePathParent, :ObfuscatedFilename, :ZipPassword) RETURNING id;

When I leave off the ‘RETURNING id’ the record inserts properly (no exception, but I get no ID). When I include the ‘RETURNING id’ it gives me an SQLErrorExceptionMBS of ‘another row available’.

It fails on the line (SelectSQL gives the same exception.):
rs = tempSQLPreparedStatementMBS.SelectSQLMT

Yet if I do a manual SQL INSERT command, it works eg
INSERT INTO Lockers (ApplicationName) VALUES ('myApp Name') RETURNING id;

I’m the only user on the database. What is the cause or solution? I can’t find any solutions on the Forum or in web searches. Is it likely to be an MBS issue?

2

BTW the INSERT with the RETURNING command works without exception and returning a value when pointed at an SQLite file using SQLDatabaseMBS.

Note: I use the RETURNING command with DELETE, INSERT and UPDATE to help log the records to which changes have been made.

3

From the MBS docs i think you may need this?

db.CubeSQLLastInsertID

4

After the insert run. You may need to call FetchNext to get the result with the ID.
Or for SQLDatabaseMBS use SQLSelect or SelectSQL to get the result.

5

Thank you @Thomas_Kaltschmidt and @Christian_Schmitz, that worked. I have been able to perform the INSERT (without the RETURNING command on CubeSQL), then use db.CubeSQLLastInsertID to get the rowid, then use the rowid to SELECT the list of RETURNING fields.

While INSERT has the workaround above, it doesn’t solve RETURNING fields from the DELETE and UPDATE commands, since multiple records may be affected.

How might I solve returning multiple record IDs with DELETE and UPDATE commands in CubeSQL using SQLDatabaseMBS?

6

RETURNING in SQLite is newer than my time with Xojo.

Are you using a version of CubeSQL that’s built upon a version of SQLite that actually supports RETURNING?

Update: I can’t find the word “returning” (using cmd-f) in the entire Command Reference PDF https://sqlabs.com/download/cubesql/manuals/CubeSQL%20Commands%20Reference.pdf

7

From SQLite:
“The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021-03-12).” (RETURNING)

@Tim_Parnell the RETURNING parameter is a function within SQLite and not CubeSQL. The latest CubeSQL uses SQLite uses 3.46.1 (using SELECT sqlite_version();), so it should work OK and I have proven it by using the INSERT command manually within CubeSQL’s SQLiteManager app.

It is a very useful extension of SQL that some other SQL brands have adopted to save a second SQL action. Using the RETURNING parameter in an UPDATE lets me see only the records that have been changed (if no changes, nothing returned). Both UPDATE and DELETE with the RETURNING parameter let me log who did the changes to which records with a single SQL command.

8

I’m not challenging the usefulness of RETURNING, I just thought maybe that was the source of the issue since it’s relatively new.

1 Like
9

It’s a shame that RETURNING doesn’t ‘just work’ with CubeSQL and SQLDatabaseMBS, but I have managed to trap the DELETE, INSERT and UPDATE commands and run a SELECT command with the same parameters to get the results before (for DELETE and UPDATE)/after (for INSERT) the SQLPreparedStatementMBS.ExecuteSQLMT command runs.

Thank you all.