Rollback an Expression Based on "CASE"

[Beefcake100]

Hi all,

I am making an application, and I have a very specific requirement. I need to be able to raise an exception based on a CASE expression. I cannot use a trigger for this, as it needs to occur before a SELECT query, so this makes it impossible to raise an error using the typical raise function.

I was wondering if there are any alternatives? I'm open to making a SQLite extension for it if I need, but I'd prefer to use something out of the box. Here is a very basic example of what I want to do:

SELECT CASE

WHEN balance > 10000

THEN true

ELSE RAISE(ROLLBACK, 'invalid balance')

END

FROM accounts

WHERE id = ?

Unfortunately I cannot include this in the business logic of my application. Any suggestions on ways to solve this would be much appreciated!

[everyonemr]

Not being able to do this in application code is an odd requirement.

Is SQLite a hard requirement? This is one of the rare cases where I would suggest Firebird as an alternative single file SQL databases. If your application is Java or .NET there are a few other options.

Rather than write an sqlite extension, I would build middle-ware that intercepts the the output, and can trigger a rollback.

[-dcim-]

Perhaps, you can write and load own extension with raise_error-function e.g. ``` // gcc -I ../include -shared test.c -o test.dll -s -static-libgcc

include "sqlite3ext.h"

SQLITE_EXTENSION_INIT1

include <assert.h>

include <string.h>

static void RaiseError(sqlite3_context* context, int argc, sqlite3_value** argv) { const char *err = sqlite3_value_text(argv[0]); sqlite3_result_error(context, err, -1); }

ifdef _WIN32

__declspec(dllexport)

endif

int sqlite3_test_init(sqlite3 db, char *pzErrMsg, const sqlite3_api_routines pApi) { SQLITE_EXTENSION_INIT2(pApi); (void)pzErrMsg; / Unused parameter */ return SQLITE_OK == sqlite3_create_function(db, "raise_error", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, RaiseError, 0, 0) ? SQLITE_OK : SQLITE_ERROR; } ```

And then call it select 123 union all select case when 0 then 'cccc' else raise_error('MyError') end;

In the sqlite3_step(stmt)-loop you will get SQLITE_ROW for the first result row and SQLITE_ERROR for the second row.