SQLite3 interface

forum.sourcecode (2000-2005) & forum.td.sourcecode (2005-2010)
Dmitry

SQLite3 interface

Post by Dmitry » 19 Nov 2007, 09:14

 Posted by:  Dmitry 

Hi all!

I send for you simple sample for use SQLite3.dll. See detail on
http://www.sqlite.org.
This interface realize work with 2 DB files: Class sql3f - fixed database
and sql3 - temporary database (with remove at close).
You can explore this db files with SQLite Expert. See
http://www.sqliteexpert.com/download.html

Dmitry

Attachment: SQLite3G.Sample.rar
Attachment: SQLite3G.Source.rar

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 1088
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

SQLite3 interface

Post by Dave Rabelink » 19 Nov 2007, 09:56

Very usefull. Thanks !

Dave

Ingo Pohl

SQLite3 interface

Post by Ingo Pohl » 19 Nov 2007, 11:44

 Posted by:  Ingo Pohl 

Great Work Thx...

I never have heard from SQLite, but it seems to be very usefull for small
projects with 2 or 3 PC's where there is no money to host a DB-Server...

Dmitry

SQLite3 interface

Post by Dmitry » 20 Nov 2007, 10:02

 Posted by:  Dmitry 

This sample shows how to save and restore table columns state.
Can whomever this will be useful.

Dmitry

Attachment: SQLite.Source.zip
Attachment: SQLiteG.Sample.zip

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 1088
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

SQLite3 interface

Post by Dave Rabelink » 24 Jul 2008, 23:17

This is an old thread, but I'm curious about the current usage of
Sqlite in TD applications out there.

I'm looking for a (free) lightweight DB solution to be used in a TD
build application to store and retreive small amounts of data.

Right now I use a custom build text database, quite rational which
actually does the job right but it is a pain to maintain.
I need to enhance the functionality of the application and I think
Sqlite could replace the current implementation.

The DB solution should be free and no install necessary. The database
should be located on a networkdrive and will have no more than 3-5
users. The amount of data is small. The largest filesize I have seen
on the current text database implementation is 150Kb.
It should not be a server like environment.

Concurrency is not an issue, right now the database is locked when a
user accesses it in write mode.

Looking at the samples provided here I wonder about the custom DLL
which is acting like an interface to Sqlite. Can TD access the Sqlite
interface DLL directly ?

Any hints to a valuable solution is welcome.

Dave

Dmitry

SQLite3 interface

Post by Dmitry » 25 Jul 2008, 07:11

 Posted by:  Dmitry 

Hi, Dave!

SQLite database header declared as
struct sqlite3 {
sqlite3_vfs *pVfs; /* OS Interface */
int nDb; /* Number of backends currently in use */
Db *aDb; /* All backends */
int flags; /* Miscellanous flags. See below */
int openFlags; /* Flags passed to sqlite3_vfs.xOpen() */
int errCode; /* Most recent error code (SQLITE_*) */
int errMask; /* & result codes with this before returning */
u8 autoCommit; /* The auto-commit flag. */
u8 temp_store; /* 1: file 2: memory 0: default */
u8 mallocFailed; /* True if we have seen a malloc failure */
signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */
int nTable; /* Number of tables in the database */
CollSeq *pDfltColl; /* The default collating sequence (BINARY) */
i64 lastRowid; /* ROWID of most recent insert (see above) */
i64 priorNewRowid; /* Last randomly generated ROWID */
int magic; /* Magic number for detect library misuse */
int nChange; /* Value returned by sqlite3_changes() */
int nTotalChange; /* Value returned by sqlite3_total_changes() */
sqlite3_mutex *mutex; /* Connection mutex */
struct sqlite3InitInfo { /* Information used during initialization */
int iDb; /* When back is being initialized */
int newTnum; /* Rootpage of table being initialized */
u8 busy; /* TRUE if currently initializing */
} init;
int nExtension; /* Number of loaded extensions */
void **aExtension; /* Array of shared libraray handles */
struct Vdbe *pVdbe; /* List of active virtual machines */
int activeVdbeCnt; /* Number of vdbes currently executing */
void (*xTrace)(void*,const char*); /* Trace function */
void *pTraceArg; /* Argument to the trace function */
void (*xProfile)(void*,const char*,u64); /* Profiling function */
void *pProfileArg; /* Argument to profile function */
void *pCommitArg; /* Argument to xCommitCallback() */
int (*xCommitCallback)(void*); /* Invoked at every commit. */
void *pRollbackArg; /* Argument to xRollbackCallback() */
void (*xRollbackCallback)(void*); /* Invoked at every commit. */
void *pUpdateArg;
void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
void *pCollNeededArg;
sqlite3_value *pErr; /* Most recent error message */
char *zErrMsg; /* Most recent error message (UTF-8 encoded) */
char *zErrMsg16; /* Most recent error message (UTF-16 encoded) */
union {
int isInterrupted; /* True if sqlite3_interrupt has been called */
double notUsed1; /* Spacer */
} u1;
#ifndef SQLITE_OMIT_AUTHORIZATION
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*);
/* Access authorization function */
void *pAuthArg; /* 1st argument to the access auth function */
#endif
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
int (*xProgress)(void *); /* The progress callback */
void *pProgressArg; /* Argument to the progress callback */
int nProgressOps; /* Number of opcodes for progress callback */
#endif
#ifndef SQLITE_OMIT_VIRTUALTABLE
Hash aModule; /* populated by sqlite3_create_module() */
Table *pVTab; /* vtab with active Connect/Create method */
sqlite3_vtab **aVTrans; /* Virtual tables with open transactions */
int nVTrans; /* Allocated size of aVTrans */
#endif
Hash aFunc; /* All functions that can be in SQL exprs */
Hash aCollSeq; /* All collating sequences */
BusyHandler busyHandler; /* Busy callback */
int busyTimeout; /* Busy handler timeout, in msec */
Db aDbStatic[2]; /* Static space for the 2 default backends */
#ifdef SQLITE_SSE
sqlite3_stmt *pFetch; /* Used by SSE to fetch stored statements */
#endif
u8 dfltLockMode; /* Default locking-mode for attached dbs */
};
and I don`t know how to declare this header in TD.
And another one. Prepared statement sqlite3_stmt (SQL cursor) also struct and all functions work with pointer on this struct. (Right now I can`t to find info about sqlite3_stmt. Sorry =o)

I think that to realize directly interface impossible.

Dmitry

FRBhote
India
Posts: 1233
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

SQLite3 interface

Post by FRBhote » 25 Jul 2008, 07:38

Try Access?

Dmitry

SQLite3 interface

Post by Dmitry » 25 Jul 2008, 07:57

 Posted by:  Dmitry 

Has quite forgotten. )
You can try to use ODBC driver for SQLite. See
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc for more info.

Bud

SQLite3 interface

Post by Bud » 11 Aug 2008, 13:42

 Posted by:  Bud 

Hi Dave,

May I recommend Firebird database. You can use it as you describe ( no
install necessary and a very small footprint ). I have used it from
very small databases up to databases that contain millions of rows per
table with excellent response time. It is Open Sourced ( free to use ).
Actually, it is an advanced version of Borland's Interbase that was
Open Sourced about 8 years ago. You can access it easily using their
ODBC drivers.

Please check out: http://www.firebirdsql.org

All the best,

Bud Ingraham

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 1088
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

SQLite3 interface

Post by Dave Rabelink » 11 Aug 2008, 15:08

Hi Bud,

Sounds interesting.

Do you have a small TD sample for us which shows a simple open, select
and close action on a simple table ?
I am wondering about the ODBC config settings needed to get connection
to a database somewhere on a network or in the application folder.

Regards,

Dave

Mike

SQLite3 interface

Post by Mike » 06 Oct 2008, 15:32

 Posted by:  Mike 

Use any of the standard odbc things from microsoft, so paradox, dbase or best msaccess...

E.g. for access:
!!CB!! 70
Set SqlDatabase=''
Set SqlUser='Admin'
Set SqlPassword=''
If not SqlCreateSession(hSession,'Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=' || sAccessFileName)
Call fMsgBox('Err open session')
Return FALSE
If not SqlCreateStatement(hSession,hSqlODBC)
Call fMsgBox('Err open statement')
Return FALSE
Set SqlDatabase=SqlDatabaseOld
Set SqlUser=SqlUserOld
Set SqlPassword=SqlPasswordOld
Set n=0
Call SqlPrepareAndExecute(hSqlODBC,'select nr,str into :nTemp,:sTemp from mytable order by nr')
While SqlFetchNext(hSqlODBC,nInd)
Set nDirections[n]=nTemp
Set sDirections[n]=sTemp
Set n=n+1
....
Set bRes=SqlGetSessionHandle(hSqlODBC,hSession)
Set bRes=SqlFreeSession(hSession)

sqlite works find with odbc, I tried it myself... But this way is even easier, any default windows machine has msaccess support.

Mike

Return to “td.sourcecode”

Who is online

Users browsing this forum: [Ccbot] and 0 guests