转发
*======= 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
2025-04-25 08:49
sqlite3.rar
(814.5 KB)
