SQLite scalar functions extension example

main.c

// gcc main.c sqlite3.c -o main.exe
#include <stdio.h>
#include "sqlite3.h"

int main() {
	sqlite3* db;
	if (SQLITE_OK != sqlite3_open_v2(":memory:", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL)) {
		printf("Error: can't open a database");
		return -1;
	}
	
	sqlite3_enable_load_extension(db, 1);
	if (SQLITE_OK != sqlite3_load_extension(db, "scalar.dll", 0, 0)) 
		printf("Error: can't load an extension\n");
	
	sqlite3_stmt* stmt;
	if (SQLITE_OK == sqlite3_prepare_v2(db, "select ?1, square(?1) union all select ?2, square(?2) union all select ?3, square(?3)", -1, &stmt, 0)) {
		sqlite3_bind_int(stmt, 1, 5);
		sqlite3_bind_null(stmt, 2);
		sqlite3_bind_text(stmt, 3, "abcdef", 6,  SQLITE_TRANSIENT); 
		int rc = 0;
		while (SQLITE_ROW == (rc = sqlite3_step(stmt))) 
			printf("x: %s, square: %s\n", sqlite3_column_text(stmt, 0), sqlite3_column_text(stmt, 1));		

		if (SQLITE_ERROR == rc)
			printf(sqlite3_errmsg(db));		
	} else {
		printf(sqlite3_errmsg(db));
	}
	sqlite3_finalize(stmt);
	sqlite3_close_v2(db);
	
	return 0;
}

scalar.c

// gcc -shared scalar.c -o scalar.dll -s -static
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <stdio.h>

static void square (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
	int type = sqlite3_value_type(argv[0]);
	double d = sqlite3_value_double(argv[0]);	
	
	switch (type) {
		case SQLITE_NULL:
			sqlite3_result_null(ctx);
			break;
			
		case SQLITE_INTEGER:			
		case SQLITE_FLOAT:		
			sqlite3_result_double(ctx, d * d);		
			break;
			
		case SQLITE_TEXT:			
		case SQLITE_BLOB:	
			sqlite3_result_error(ctx, "Unsupported", -1);					
			break;
			
		default:
			sqlite3_result_error(ctx, "Impossible", -1);
	}
}

static void odd (sqlite3_context *ctx, int argc, sqlite3_value **argv){		
	int *pCounter = (int*)sqlite3_get_auxdata(ctx, 0);
	if (pCounter == 0) {
		pCounter = sqlite3_malloc(sizeof(*pCounter));
		if (pCounter == 0) 
			return sqlite3_result_error_nomem(ctx);
		
		*pCounter = sqlite3_value_type(argv[0]) == SQLITE_NULL ? 0 : sqlite3_value_int(argv[0]);
		sqlite3_set_auxdata(ctx, 0, pCounter, sqlite3_free);
	} else {
		++*pCounter;
	}
	
	sqlite3_result_int(ctx, *pCounter % 2);
}


#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_scalar_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
	SQLITE_EXTENSION_INIT2(pApi);
	return SQLITE_OK == sqlite3_create_function(db, "square", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, square, 0, 0) &&
		SQLITE_OK == sqlite3_create_function(db, "odd", -1, SQLITE_UTF8, 0, odd, 0, 0) ?		
		SQLITE_OK : SQLITE_ERROR;
}