sqlite3.bac

'
' Simple SQLite3 wrapper - PvE May 2010, GPL.
'
' SQL syntax here: http://www.sqlite.org/lang.html
'
' Version 1.0: initial release
' Version 1.1: fixed compile warning in DB_ERR$
' Version 1.2: adjustments for including separate functions
' Version 1.3: adapted for BaCon 3.0 and higher
' Version 1.4: added callback method to obtain records.
' Version 1.5: improved import logic.
' Version 1.6: callbacked function in sqlite3_exec should return a value
'---------------------------------------------------------------------------------------------------------

CALL INIT

REM --------------------------------------------------------------------------------------------------

SUB INIT

    LOCAL sqlite_LIB$
    LOCAL sqlite_seq = -1

    CATCH GOTO sqlite_import_retry

    sqlite_LIB$ = "libsqlite3.so.0"
    sqlite_seq = -1

    LABEL sqlite_import_retry
        INCR sqlite_seq
    
        IF sqlite_seq > 100 THEN GOTO Print_Error
        sqlite_LIB$ = LEFT$(sqlite_LIB$, INSTRREV(sqlite_LIB$, ".")) & STR$(sqlite_seq)

        IMPORT "sqlite3_open(char*,long)" FROM sqlite_LIB$ TYPE int

    CATCH RESET

    IMPORT "sqlite3_close(long)" FROM sqlite_LIB$ TYPE int
    IMPORT "sqlite3_errmsg(long)" FROM sqlite_LIB$ TYPE char*
    IMPORT "sqlite3_exec(long,char*,void*,void*,long)" FROM sqlite_LIB$ TYPE int
    IMPORT "sqlite3_free(void*)" FROM sqlite_LIB$ TYPE void
    IMPORT "sqlite3_libversion(void)" FROM sqlite_LIB$ TYPE char*

    CONST SQLITE_OK = 0

    DECLARE DB_FS$, DB_RS$, DB_RESULT$, DB_ERROR$
    DECLARE sqlite_internal_flag

    DB_FS$ = TAB$(1)
    DB_RS$ = NL$

    DB_VERSION$ = sqlite3_libversion()

    EXIT SUB

    REM Show error
    LABEL Print_Error
        PRINT "SQLite library not found!"
        END

END SUB

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_OPEN(STRING name)

    LOCAL db, result

    result = sqlite3_open(name, ADDRESS(db))

    IF result THEN RETURN result

    RETURN db

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION sqlite_response(void *NotUsed, int amount, char **value, char **column_name) : REM INCLUDE

    LOCAL i

    IF sqlite_internal_flag AND amount > 0 THEN
        DB_RESULT$ = ""
        FOR i = 0 TO amount - 1
            DB_RESULT$ = DB_RESULT$ & column_name[i] & DB_FS$
        NEXT
        DB_RESULT$ = DB_RESULT$ & DB_RS$
        sqlite_internal_flag = FALSE
    ENDIF

    IF amount > 0 THEN
        FOR i = 0 TO amount - 1
            DB_RESULT$ = DB_RESULT$ & value[i] & DB_FS$
        NEXT
        DB_RESULT$ = DB_RESULT$ & DB_RS$
    END IF

    RETURN FALSE

ENDFUNCTION

FUNCTION DB_SQL(NUMBER db, STRING sql$)

    LOCAL result TYPE int
    LOCAL err_msg TYPE char*

    DB_ERROR$ = ""
    sqlite_internal_flag = TRUE
    result = sqlite3_exec(db, sql$, sqlite_response, 0, ADDRESS(err_msg))

    IF result <> SQLITE_OK THEN
        DB_ERROR$ = err_msg
        sqlite3_free(err_msg)
        result = FALSE
    ELSE
        result = TRUE
    END IF

    RETURN result

END FUNCTION

'---------------------------------------------------------------------------------------------------------

SUB sqlite_results(void (*UserFunc)(int, char**, char**), int amount, STRING value$[], STRING column$[]) : REM INCLUDE

    CALL (*UserFunc)(amount, column$, value$)

END SUB

FUNCTION DB_QUERY(NUMBER db, STRING sql$, void* CallB)

    LOCAL result TYPE int
    LOCAL err_msg TYPE char*

    IF sqlite3_exec(db, sql$, sqlite_results, CallB, ADDRESS(err_msg)) <> SQLITE_OK THEN
        DB_ERROR$ = err_msg
        sqlite3_free(err_msg)
        result = FALSE
    ELSE
        result = TRUE
    END IF

    RETURN result

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_CLOSE(NUMBER db)

    LOCAL result TYPE int

    result = sqlite3_close(db)

    RETURN result

END FUNCTION

'---------------------------------------------------------------------------------------------------------

FUNCTION DB_STATUS$(NUMBER db)

    LOCAL status$

    status$ = sqlite3_errmsg(db)

    RETURN status$

END FUNCTION

'---------------------------------------------------------------------------------------------------------

CATCH RESET