|
|
#16
sych2025-04-28 17:07
转发
*======= vfp api访问sqlite例子 ======
*
*/*
** CAPI3REF: Flags For File Open Operations
**
** These bit values are intended for use in the
** 3rd parameter to the [sqlite3_open_v2()] interface and
** in the 4th parameter to the [sqlite3_vfs.xOpen] method.
*/
#define SQLITE_OPEN_READONLY 0x00000001 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_READWRITE 0x00000002 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_CREATE 0x00000004 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_DELETEONCLOSE 0x00000008 &&/* VFS only */
#define SQLITE_OPEN_EXCLUSIVE 0x00000010 &&/* VFS only */
#define SQLITE_OPEN_AUTOPROXY 0x00000020 &&/* VFS only */
#define SQLITE_OPEN_URI 0x00000040 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_MAIN_DB 0x00000100 &&/* VFS only */
#define SQLITE_OPEN_TEMP_DB 0x00000200 &&/* VFS only */
#define SQLITE_OPEN_TRANSIENT_DB 0x00000400 &&/* VFS only */
#define SQLITE_OPEN_MAIN_JOURNAL 0x00000800 &&/* VFS only */
#define SQLITE_OPEN_TEMP_JOURNAL 0x00001000 &&/* VFS only */
#define SQLITE_OPEN_SUBJOURNAL 0x00002000 &&/* VFS only */
#define SQLITE_OPEN_MASTER_JOURNAL 0x00004000 &&/* VFS only */
#define SQLITE_OPEN_NOMUTEX 0x00008000 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_FULLMUTEX 0x00010000 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_SHAREDCACHE 0x00020000 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_PRIVATECACHE 0x00040000 &&/* Ok for sqlite3_open_v2() */
#define SQLITE_OPEN_WAL 0x00080000 &&/* VFS only */
*/*
** CAPI3REF: Result Codes
** KEYWORDS: SQLITE_OK {error code} {error codes}
** KEYWORDS: {result code} {result codes}
**
** Many SQLite functions return an integer result code from the set shown
** here in order to indicate success or failure.
**
** New error codes may be added in future versions of SQLite.
**
** See also: [SQLITE_IOERR_READ | extended result codes],
** [sqlite3_vtab_on_conflict()] [SQLITE_ROLLBACK | result codes].
*/
#define SQLITE_OK 0 &&/* Successful result */
*/* beginning-of-error-codes */
#define SQLITE_ERROR 1 &&/* SQL error or missing database */
#define SQLITE_INTERNAL 2 &&/* Internal logic error in SQLite */
#define SQLITE_PERM 3 &&/* Access permission denied */
#define SQLITE_ABORT 4 &&/* Callback routine requested an abort */
#define SQLITE_BUSY 5 &&/* The database file is locked */
#define SQLITE_LOCKED 6 &&/* A table in the database is locked */
#define SQLITE_NOMEM 7 &&/* A malloc() failed */
#define SQLITE_READONLY 8 &&/* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 9 &&/* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR 10 &&/* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT 11 &&/* The database disk image is malformed */
#define SQLITE_NOTFOUND 12 &&/* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL 13 &&/* Insertion failed because database is full */
#define SQLITE_CANTOPEN 14 &&/* Unable to open the database file */
#define SQLITE_PROTOCOL 15 &&/* Database lock protocol error */
#define SQLITE_EMPTY 16 &&/* Database is empty */
#define SQLITE_SCHEMA 17 &&/* The database schema changed */
#define SQLITE_TOOBIG 18 &&/* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT 19 &&/* Abort due to constraint violation */
#define SQLITE_MISMATCH 20 &&/* Data type mismatch */
#define SQLITE_MISUSE 21 &&/* Library used incorrectly */
#define SQLITE_NOLFS 22 &&/* Uses OS features not supported on host */
#define SQLITE_AUTH 23 &&/* Authorization denied */
#define SQLITE_FORMAT 24 &&/* Auxiliary database format error */
#define SQLITE_RANGE 25 &&/* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB 26 &&/* File opened that is not a database file */
#define SQLITE_ROW 100 &&/* sqlite3_step() has another row ready */
#define SQLITE_DONE 101 &&/* sqlite3_step() has finished executing */
*/* end-of-error-codes */
#define SQLITE_INTEGER 1
#define SQLITE_FLOAT 2
#define SQLITE_BLOB 4
#define SQLITE_NULL 5
*#ifdef SQLITE_TEXT
*# undef SQLITE_TEXT
*#else
#define SQLITE_TEXT 3
*#endif
#define SQLITE3_TEXT 3
*SET PATH TO "I:\SQLite\sqlite_api"
*SET defaul TO "I:\SQLite\sqlite_api"
DECLARE sqlite3_initialize IN sqlite3.dll
DECLARE integer sqlite3_open_v2 IN sqlite3.dll string dbfn, integer @hd, integer bz,integer temp
DECLARE sqlite3_close IN sqlite3.dll integer dbhand
DECLARE sqlite3_shutdown IN sqlite3.dll
DECLARE integer sqlite3_prepare_v2 IN sqlite3.dll integer dbhand,string sqlchar, integer clen, integer @stmt,integer temp
DECLARE integer sqlite3_step IN sqlite3.dll integer stmt
DECLARE sqlite3_finalize IN sqlite3.dll integer stmt
DECLARE integer sqlite3_column_count IN sqlite3.dll integer stmt
DECLARE integer sqlite3_column_type IN sqlite3.dll integer stmt,integer culm
DECLARE integer sqlite3_column_int IN sqlite3.dll integer stmt,integer culm
DECLARE integer sqlite3_column_double IN sqlite3.dll integer stmt,integer culm
DECLARE string sqlite3_column_text IN sqlite3.dll integer stmt,integer culm
=sqlite3_initialize()
LOCAL dbhd,dbfile,rc,stmthd,jg,culs,js,columtype
dbhd=0
stmthd=0
dbfile="song.db"
rc = sqlite3_open_v2( dbfile, @dbhd,2,0 ) &&SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL )
?rc
IF rc<>0
?'open err'
ELSE
TRY
?'open db ok'
* cx="select * from v_classtype"+CHR(0)
cx="select * from song"+CHR(0)
rc=sqlite3_prepare_v2(dbhd,cx,len(cx),@stmthd,0) &&查询准备
IF rc = 0
hs=0
DIMENSION rsult(100,1)
DO WHILE .t.
jg=sqlite3_step(stmthd) &&执行查询获得一行结果
* ?jg,SQLITE_ROW
IF jg = SQLITE_ROW THEN
culs=sqlite3_column_count(stmthd) &&获得列数
hs=hs+1
DIMENSION rsult(hs,culs)
FOR js=1 TO culs STEP 1
columtype=sqlite3_column_type(stmthd,js) &&获得列类型
DO CASE
CASE columtype = SQLITE_INTEGER
vv=sqlite3_column_int(stmthd,js)
CASE columtype = SQLITE_FLOAT
vv=sqlite3_column_double(stmthd,js)
CASE columtype = SQLITE_TEXT
vv=STRCONV( sqlite3_column_text(stmthd,js),11) && UTF-8 CONVERT
CASE columtype = SQLITE_NULL
vv="NULL"
CASE columtype = SQLITE_BLOB
OTHERWISE
?"columtype err"
ENDCASE
rsult(hs,js)=vv
NEXT
DO CASE
CASE culs=5
?rsult(hs,1),rsult(hs,2),rsult(hs,3),rsult(hs,4),rsult(hs,5),culs &&根据列数目显示所有列内容
CASE culs=4
?rsult(hs,1),rsult(hs,2),rsult(hs,3),rsult(hs,4),culs
CASE culs=3
?rsult(hs,1),rsult(hs,2),rsult(hs,3),culs
CASE culs=2
?rsult(hs,1),rsult(hs,2),culs
CASE culs=1
?rsult(hs,1),culs
ENDCASE
exit
ELSE
exit
ENDIF
ENDDO
sqlite3_finalize(stmthd) &&销毁prepare防止内存泄漏
else
?"a err "
&& 如果用sqlite3_exec 出错则需要用 sqlite3_free 销毁对象防止内存泄漏
endif
CATCH
?"except",MESSAGE(1),MESSAGE()
FINALLY
=sqlite3_close(dbhd) &&关闭连接
ENDTRY
ENDIF
=sqlite3_shutdown()
RETURN
|
