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


You do not have the required permissions to view the files attached to this post.

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

Re: SQLite3 interface

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

Very useful. Thanks !

Dave

[FUTURE EDIT: See https://wiki.tdcommunity.net/index.php/SQLite ]

Ingo Pohl

Re: 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 useful for small
projects with 2 or 3 PC's where there is no money to host a DB-Server...

Dmitry

Re: 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


You do not have the required permissions to view the files attached to this post.

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

Re: 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

Re: SQLite3 interface

Post by Dmitry » 25 Jul 2008, 07:11

 Posted by:  Dmitry 

Hi, Dave!

SQLite database header declared as

Code: Select all

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: 2206
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: SQLite3 interface

Post by FRBhote » 25 Jul 2008, 07:38

Try Access?

Dmitry

Re: 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

Re: 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: 3384
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: 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

Re: 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:

Code: Select all

!!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