Tbank you in anticipation for any forthcoming advice
I include my program below in Powerbasic (not disimilar to C)
and wonder what I need to do in order to "wrap"
multiple statements (which use prepare,step & finalise) as transactions?
#COMPILE EXE
#DIM ALL
#INCLUDE "sqlite3.inc" 'created 09 May 2013, 15:34:42
GLOBAL ghDb AS DWORD
GLOBAL gDBOPEN AS LONG
SUB Open_Database (fl_nm AS ASCIZ * 512, hd AS DWORD )
IF gDBOPEN = 1 THEN
sqlite3_close(ghDb)
END IF
IF sqlite3_open(fl_nm, BYREF ghDb) <> %SQLITE_OK THEN
? "can't open database",,"error"
gDBOPEN = 0
EXIT SUB
END IF
gDBOPEN = 1
END SUB
FUNCTION exec_sql( _
hDB AS DWORD, _
hD AS DWORD, _
BYVAL pzSql AS ASCIZ PTR, _
BYVAL pzErr_msg AS ASCIZ PTR _
) AS LONG
LOCAL lresult AS LONG
LOCAL pzTail AS ASCIZ PTR
LOCAL pzPrepared AS ASCIZ PTR
LOCAL pzStmt AS ASCIZ PTR
LOCAL azcols() AS ASCIZ PTR ' array of dword pointers to
column name strings
LOCAL azvals() AS ASCIZ PTR ' array of dword pointers to
column values
LOCAL ncols AS LONG ' the column count
LOCAL nErmsg AS LONG
LOCAL szErmsg AS ASCIZ * 512
LOCAL nRetry AS LONG
LOCAL s AS STRING
LOCAL i, l AS LONG
lresult = %SQLITE_OK
IF @pzSql = "" THEN
FUNCTION = %SQLITE_OK
EXIT FUNCTION ' called with null SQL statement
END IF
lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )
IF lresult <> %SQLITE_OK THEN
GOTO finish
END IF
IF pzPrepared = 0 THEN
GOTO finish
END IF
ncols = sqlite3_column_count(pzPrepared)
REDIM azcols(0 TO ncols-1)
FOR i = 0 TO ncols -1
azcols(i) = sqlite3_column_name(pzPrepared, i)
? @azcols(i),,"col heading"
NEXT
REDIM azvals(0 TO ncols-1)
DO WHILE 1
lresult = sqlite3_step(pzPrepared)
s = ""
SELECT CASE lresult
CASE %SQLITE_ROW
FOR i = 0 TO ncols - 1
azvals(i) = sqlite3_column_text(pzPrepared, i)
s = s + @azvals(i) + ","
NEXT
? s,,"row"
CASE %SQLITE_DONE
GOTO Finish
CASE ELSE
? "unexpected result = " + STR$(lresult)
GOTO finish
END SELECT
LOOP
Finish:
IF pzPrepared <> 0 THEN
sqlite3_finalize(pzPrepared)
END IF
IF lresult <> %SQLITE_OK THEN
lresult = sqlite3_errcode(hDB)
pzErr_msg = sqlite3_errmsg(hDB)
END IF
FUNCTION = lresult
END FUNCTION
FUNCTION PBMAIN()
LOCAL sSQL AS STRING
LOCAL pzErr_msg AS ASCIZ PTR
'I don't seem to be able to make transactions work
'ie I was expecting to see a msgbox for each data row inserted
'but I don't
sSQL = "begin transaction;" + $CRLF + _
"create table if not exists membership (member, date,
annualsubs, payments);" + $CRLF + _
"insert into membership values ('Fred', '10-APR-2010', 123,
54);" + $CRLF + _
"insert into membership values ('James', '10-APR-2010', 123,
0);" + $CRLF + _
"insert into membership values ('Molly', '10-MAY-2010', 123,
0);" + $CRLF + _
"insert into membership values ('Angus', '10-APR-2010', 90,
10);" + $CRLF + _
"insert into membership values ('Patrick', '10-JUN-2010', 123,
0);" + $CRLF + _
"select member, date ""date due"", annualsubs - payments
""amount due"" from membership;" + _
"commit transaction;"
open_database("test1.db",ghDb)
sSQL = "create table if not exists membership (member, date,
annualsubs, payments);"
exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )
'no this doesn't seem to work either
' sSQL = "begin transaction; " & "insert into membership values
('Fred', '10-APR-2010', 123, 54);" & " commit transaction;"
'if I replace the above line with this one...it's fine
'I don't understand what it is that's missing to make transactions work
'btw...this replacement line works fine
sSQL = "insert into membership values ('Fred', '10-APR-2010', 123,
54);"
exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )
sSQL = "select member, date ""date due"", annualsubs - payments
""amount due"" from membership;"
exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )
sqlite3_free(BYVAL pzErr_msg)
sqlite3_close(ghDb)
END FUNCTION
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
