sqlite3.lib

[ramguru]

This is the last update of the demo... If there will be one in future, it will be not very soon. Now my app supports unicode, shows total number of records. If some-one is interested in conversion between unicode and utf8, yuo can find the routines in source...

[attachment deleted by admin]

[Mark Jones]

Nice Job Ramguru. Nice skining example too. :)

Edit: There is a new version of the sqlite dll available, get it from http://www.sqlite.org/

Edit2: Actually here's a freshly built package with libs and inc. (Partial courtesy of Erol's Lib2Inc.) The MobyDic example compiles fine with these libs, but this has otherwise not been tested so consider it beta software.

[attachment deleted by admin]

[s5vi]

Thank for this sqlite lib/ibc/dll combo.

Now i can write a simple app reading tables from mdb or sqlite database.
I read many docs about that but i ask your opinions (i'm a noob in database apps).

access mdb pros: popular fileformat, easy to use with odbc, some self-repair feature (redundant format?)
acces mdb cons:  no transactions (commit/rollback), nonfree acces runtime needed
sqlite pros: free, robust, transactions, odbc driver (in alpha stage)
sqlite cons: some missing SQL-92 functions

What will happens if database get corrupted, all data will lost?
Which is better, all tables in one file or split database into more files and attach them into one virtual database ? (sqlite3 can do that)

Here is my sample sqlite code (if someone need i will post more)

Code Select Expand


;
; Get column names to dest listview
;
SQLite3_GetColumns proc uses edi hStmt:DWORD,hDest:DWORD,lpColW:DWORD,iStart:DWORD
LOCAL nCol:DWORD
LOCAL lvc:LV_COLUMN

invoke sqlite3_step,hStmt
invoke SQLite3_CheckError
invoke sqlite3_column_count,hStmt
mov maxCol,eax
mov eax,iStart
mov nCol,eax
mov edi,lpColW
.while eax!=maxCol
invoke sqlite3_column_name,hStmt,nCol
mov lvc.imask,LVCF_TEXT+LVCF_WIDTH+LVCF_FMT
mov lvc.pszText,eax
mov eax,[edi]
mov lvc.lx,eax
add edi,4
mov lvc.fmt,LVCFMT_LEFT
invoke sqlite3_column_type,hStmt,nCol
invoke SendMessage,hDest, LVM_INSERTCOLUMN,nCol,addr lvc
inc nCol
mov eax,nCol
.endw
invoke sqlite3_reset,hStmt
invoke SQLite3_CheckError
ret
SQLite3_GetColumns endp

SQLite3_Get1RowLV proc uses ebx hStmt:DWORD,hDest:DWORD,iStart:DWORD
LOCAL lvi:LV_ITEM
LOCAL nCol:DWORD
mov eax,iStart
mov nCol,eax
.while eax!=maxCol
invoke sqlite3_column_text,hStmt,nCol
mov ebx,nCol
.if ebx==iStart
push eax
pop lvi.pszText
    mov lvi.imask, LVIF_TEXT
push ActRow
pop lvi.iItem
push ActRow
pop lvi.lParam
mov lvi.iSubItem,0
invoke SendMessage,hDest,LVM_INSERTITEM, 0,addr lvi
.else
push eax
pop lvi.pszText
push nCol
pop lvi.iSubItem
.if iStart==1
dec lvi.iSubItem
.endif
invoke SendMessage,hDest,LVM_SETITEM, 0,addr lvi
.endif
inc nCol
mov eax,nCol
.endw
inc ActRow
ret
SQLite3_Get1RowLV endp

SQLite3_GetAllRowLV proc hStmt:DWORD,hDest:DWORD,iStart:DWORD
mov ActRow,0
invoke sqlite3_step,hStmt
.while eax==SQLITE_ROW
invoke SQLite3_Get1RowLV,hStmt,hDest,iStart
invoke sqlite3_step,hStmt
.endw
push ActRow
pop maxRow
ret
SQLite3_GetAllRowLV endp

SQLite3_GetTableLV proc lpTable:DWORD,hDest:DWORD,lpColW:DWORD,iStart:DWORD
LOCAL buffer[256]:BYTE
LOCAL hStmt:DWORD
invoke lstrcpy,addr buffer,StrAddr ( "SELECT * FROM " )
invoke lstrcat,addr buffer,lpTable
invoke sqlite3_prepare,hDB,addr buffer,-1,addr hStmt,NULL
invoke SQLite3_CheckError
invoke SQLite3_GetColumns,hStmt,hDest,lpColW,iStart
invoke SQLite3_GetAllRowLV,hStmt,hDest,iStart
invoke sqlite3_reset,hStmt
invoke SQLite3_CheckError
invoke sqlite3_finalize,hStmt
invoke SQLite3_CheckError
mov eax,hDest
mov lvsort.handle,eax
mov lvsort.direction,FALSE ; false = forward/ true = rev
mov lvsort.column,0
mov lvsort.stype,1 ; 1 = text
invoke SendMessage, hDest, LVM_SORTITEMSEX, addr lvsort, addr LVSortProc
ret
SQLite3_GetTableLV endp


[Mark Jones]

Here's the latest stable SQLite 3.4.2 .DLL and includes, plus a console-mode testbed app illustrating many SQLite commands and features with customizable error reporting and even a nifty hand-made SQL3 icon. The .inc, .def, and .lib files were made with Erol's DLL2INC and Pelle's POLIB utility. Comments welcome. :U

SQLite3 Sample Application by MCJ 2007
DB File: SQLiteTest.db

Press a key to open/create database.
...Done.

Press a key to query SQLite_Master.
table, Table1, Table1, 2, CREATE TABLE 'Table1'(
[ID] int PRIMARY KEY
,[Name] varchar
,[Handle] varchar
,[Likes] varchar
,[Dislikes] varchar
)
table, sqlite_stat1, sqlite_stat1, 4, CREATE TABLE sqlite_stat1(tbl,idx,stat)

Press a key to show Table1 contents...

Edit: minor optimizations.

[attachment deleted by admin]

[Vortex]

Hi Mark,

Thanks for the example.

I rebuilt the import library with def2lib.exe Could you try this new version of sqlite3.lib on your system?

Code Select Expand

def2lib sqlite3.def
[attachment deleted by admin]

[Mark Jones]

Sure, I reassembled the project with this .lib and it works. To thoroughly test any .lib however, more than three exports should be tested. :P

The new .lib is 230 bytes smaller. Curious, do you think there is much of a difference in the two?

It seems like "DEF2LIB" is only one step away from "DLL2LIB." :wink

[Vortex]

Hi Mark,

The new import library does not have any debug sections, this is why it's smaller. Coding a dll2lib to create import libraries from DLLs is possible but such a tool would have very limited functionnality because DLLs does not contain any information about the number of parameters passed to each exported function.

[Sameer]

i have attached a chm which can be used as a reference

This page defined the C-language interface to SQLite. This page is intended as a reference to what SQLite is suppose to do. This is not a tutorial. This page is designed to be precise, not easy to read.

This page contains all C-language interface information in a single HTML file. The same information is also available broken out into lots of small pages for easier viewing, if you prefer.

The content on this document is extracted from comments in the source code.

[attachment deleted by admin]

[farrier]

Sameer,

Thanks for putting all this information together in one very useful file.  It will help me, as I am using SQLite more and more.

I appreciate this!

farrier

[Draakie]

Hi Farrier & Vortex,

Any chance of converting the C native connector for MYSQL to an ASM Lib and Inc ? I have played around with
the OBDC connector in the past - I was able to successfully connect to the DBase and do simple queries - but the
more complex SQL API calls effectively made me loose interest (wildcard bind etc.). I understand the C native
connector greatly reduces speed and complexity.

Thanx
Draakie

[farrier]

Draakie,

I started to try MySQL--just sold for 1 BILLLLion dollars to Sun--but I gave up when I started using SQLite.  It does everyhthing I need, is small fast and open!

farrier

[Draakie]

Hi Farrier,

"....is small fast and open"

Unfortunately for my needs, that's the problem. I need something that can do replication, huge databases, backup & roll-back,
web hosting support etc. Thus MySQL. Hope you can still help though..... :U

Draakie

[Mark Jones]

Thanks to Erol for the DEF2LIB utility, here's a newer SQLite DLL v3.5.4 and .def/.inc/.lib files.

[attachment deleted by admin]

[Mark Jones]

These need to be added to the sqlite3.inc file:

Code Select Expand


SQLITE_OPEN_READONLY equ 00000001h
SQLITE_OPEN_READWRITE equ 00000002h
SQLITE_OPEN_CREATE equ 00000004h
SQLITE_OPEN_DELETEONCLOSE equ 00000008h
SQLITE_OPEN_EXCLUSIVE equ 00000010h
SQLITE_OPEN_MAIN_DB equ 00000100h
SQLITE_OPEN_TEMP_DB equ 00000200h
SQLITE_OPEN_TRANSIENT_DB equ 00000400h
SQLITE_OPEN_MAIN_JOURNAL equ 00000800h
SQLITE_OPEN_TEMP_JOURNAL equ 00001000h
SQLITE_OPEN_SUBJOURNAL equ 00002000h
SQLITE_OPEN_MASTER_JOURNAL equ 00004000h


And this... first person to convert it to masm-syntax gets a cookie! :toothy

Code Select Expand


typedef struct sqlite3_vfs sqlite3_vfs;
struct sqlite3_vfs {
  int iVersion;            /* Structure version number */
  int szOsFile;            /* Size of subclassed sqlite3_file */
  int mxPathname;          /* Maximum file pathname length */
  sqlite3_vfs *pNext;      /* Next registered VFS */
  const char *zName;       /* Name of this virtual file system */
  void *pAppData;          /* Pointer to application-specific data */
  int (*xOpen)(sqlite3_vfs*, const char *zName, sqlite3_file*,
               int flags, int *pOutFlags);
  int (*xDelete)(sqlite3_vfs*, const char *zName, int syncDir);
  int (*xAccess)(sqlite3_vfs*, const char *zName, int flags);
  int (*xGetTempname)(sqlite3_vfs*, int nOut, char *zOut);
  int (*xFullPathname)(sqlite3_vfs*, const char *zName, int nOut, char *zOut);
  void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
  void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
  void *(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol);
  void (*xDlClose)(sqlite3_vfs*, void*);
  int (*xRandomness)(sqlite3_vfs*, int nByte, char *zOut);
  int (*xSleep)(sqlite3_vfs*, int microseconds);
  int (*xCurrentTime)(sqlite3_vfs*, double*);
  /* New fields may be appended in figure versions.  The iVersion
  ** value will increment whenever this happens. */
};


[mitchi]

It's a structure like this.

Code Select Expand

sqlite3_vfs STRUCT
iVersion DWORD ? ;/* Structure version number */
szOsFile DWORD ? ;/* Size of subclassed sqlite3_file */
mxPathname DWORD ? ;/* Maximum file pathname length */
pNext DWORD ? ;/* Next registered VFS */
zName DWORD ? ;/* Name of this virtual file system */
pAppData DWORD ? ;/* Pointer to application-specific data */
func1 DWORD ?
func2 DWORD ?
func3 DWORD ?
func4 DWORD ?
func5 DWORD ?
func6 DWORD ?
func7 DWORD ?
func8 DWORD ?
func9 DWORD ?
func10 DWORD ?
func11 DWORD ?
func12 DWORD ?
sqlite3_vfs ENDS


func1, func2, func3... are pointers to functions.