SQLite3

This forum is for general support of Xbase++
Message
Author
Pablo Botella
Posts: 8
Joined: Wed Feb 03, 2010 1:04 am

Re: SQLite3

#11 Post by Pablo Botella »

rdonnay wrote: I am interested in why your aliased functions
( example:
@sqlite3:sqlite3_column_name(stmt,n) ) seem to work properly whereas DLLFUNCTION
can produce IDSC's and wrong returned values.
This causes an error because it returns a numeric instead of a string:
sqlite3_column_name( stmt, 2 )
DLLFUNCTION sqlite3_column_name( stmt, n ) USING CDECL FROM sqlite3.dll
I think returning the numeric pointer is the normal behavior for DllCall() and
also for nFpCall() and the related @dll:func() xtranslate
In both cases the result is taked from the EAX register after the functions end

nFpCall() is faster than DllCall(), have more flexibility accepting params,
automatically detect the calling convention or excess of params and fix the
stack if needed, but I think using CDECL in DllCall() and passing the right
number of parameters must give you no troubles.

There are another cases where the called function returns double or int64 and
obviously DllCall() will return EAX that is meaningles in the double case and
just the lower half of the value in the int64 case. ot4xb provide functions
ndFpCall() that get the value from fstp and qwFpCall() that get the return value
by combining EAX with EDX.

QFpCall() and the corresponding @dll:[prototype]:function() xtranslate will
coerce the params and return values to the types supplied in the prototype,
oposite the other *FpCall() functions that use the provided param values to
infer the param types. With this syntax BOOL functions will return .T./.F. and
a Xbase++ string if c_sz is specified in the template
rdonnay wrote: I used DLLFUNCTION wrappers for every function in the ACE32.DLL (Ads) library
with no problems like this.
Never a problem.
Yet SQLite3.dll seems to not work with DLLFUNCTION calls.
Except for the functions returning 64 bit integer or float values I think
cannot see any obvious reason to make DLLFUNTION fail. Of course you must
provide the right number of parameters and the CDECL calling convention.

rdonnay wrote: I assume that you are creating some kind of dynamic call from inside OT4XB.dll.
Yes is a very simple mechanism. If you take a look to my fpCall.cpp file you
will fount this code arround line 103.

Code: Select all

                                    
// push params onto the stack in reverse order                                         
for( n = nParams-1; n >= 0 ; n-- )  
{                                        
   if( pParams[n].m_nSplit < 3 )         
   {                                     
      DWORD dw = pParams[n].m_dwStackVal;
      _asm mov eax , dw;                 
      _asm push eax;                     
   }                                     
}
_asm mov eax , dwFPtr;
_asm call eax;   // call the function 
_asm mov nl ,eax;  // get the result 
rdonnay wrote: I could probably create a complete set of SQLite3_*() function calls by using
your technique,
I've attached a small modification I was made to the class in order to coerce the types
of the resulting columns, just by providing an optional template string with the desired types.

// C - Ansi string
// T - Utf8 string untransformed
// I - integer
// F - double
// B - blob

Code: Select all

stm := db:exec_beginA("SELECT id,rnd,uuid,length(memo) FROM t")
aa  := db:exec_end_ex(stm,,,"IICI" )
Here the string "IICI" means that you will retrieve the rows coerced
in this way { int,int, Ansi String , int}

rdonnay wrote: but then would I have the right to distribute your library?
You can distribute the DLL, LIBs and CHs, I don'e any trouble,
just I would suggest to put a link to my web site to allow users
get the most recent version, source code and examples.
rdonnay wrote: SQLite3 works very similar to Ads when working with a statement cursor. Both
return a numeric handle to a cursor and both have a similar set of functions to
step through the cursor and extract the data.

The diference is that ADS provide a navigable recordset (live or static)
and the sqlite statement must be handled as an iterator.


rdonnay wrote: I am familiar with this technique
in Ads and have written a complete set of higher-level functions that return
data in a format more friendly to Xbase++ programmers, ie in an array or in a
browse.
It would be nice to build this kind of library for SQLite3 and I don't mind
writing the complete set of wrappers provided that I know they will work with
your aliased concept.

Almost all the basic functionality is covered now inside my class, but
maybe will be nice to have all the wrappers as #xtranslates inside the ch
providing the prototypes, there is no problem if you miss a param or pass
the incorrect type.

And also a recordset like you described will be usefull, implementing
pseudo-properties for field names using an access assign method.
For a few thousands recordset my exec_end_ex() method with type
coercing will be enough but as this database is intended primarily
for local usage, will be really powerfull to use
limit/offset/count to navigate large datasets of a lot of
thousand or even million records smouthly.

Regards,
Pablo Botella
Attachments
testsqlite.zip
(5.73 KiB) Downloaded 1080 times

User avatar
sfsltd
Posts: 28
Joined: Sat Jan 30, 2010 7:23 am
Location: UK

Re: SQLite3

#12 Post by sfsltd »

Pablo

Very informative, thanks.

Michael

rjackson
Posts: 2
Joined: Sun Feb 28, 2010 6:50 pm

Re: SQLite3

#13 Post by rjackson »

There is a SQLite ODBC driver (third-party) at: http://www.ch-werner.de/sqliteodbc/. I tried it out (via Boris Borzic's SQLExpress) and it worked fine for the very limited things I tried. I did not use it extensively.

John Hohensee

Re: SQLite3

#14 Post by John Hohensee »

How about taking a look at MYSQL another open source SQL that started with Linux and has been ported to Windows. They have a area with various connectors that include Java, OBDC and .NET available. The OBDC and .NET are both .MSI loaders while the OBDC has a lot of individual files within it.

User avatar
sfsltd
Posts: 28
Joined: Sat Jan 30, 2010 7:23 am
Location: UK

Re: SQLite3

#15 Post by sfsltd »

Hi John

The issue is about finding an embedded (file based) SQL datastore that will scale up to a SQL service, which will
a) require a minimum of code changes in XBase++ to implement
b) is affordable to support and distribute

Our goal is to enable our XBase++ client application to scale the same way a Microsoft Access application does... from a file based data store (MDB) to a MS-SQL server.

Advantage server 'would be' an easy solution if the Advantage 'Local Server' was ACID compliant and could be accessed over a network. Unfortunately, Sybase don't support accessing Local Server over a network, nor do they to appear have plans to make it ACID.

SQLite is a simple, widely used file based SQL datastore, so it is high on our 'something like this' list, despite the fact it dosn't have a SQL service equivalent.

We are trying to avoid ODBC, because of the install implications and we are cautious about MySQL embedded until Alaska support it. Have you had any experience with this?

Given that Alaska say the Arctica 2.0 XBase++ release will include support for PostgreSQL, I am in contact with EnterpriseDB to see if we can use an 'embedded' version of PostgreSQL as the low end client solution. http://www.postgresql.org/docs/8.0/inte ... /ecpg.html

As Microsoft throttles back on extending Visual FoxPro, the language options for writing Rich Client applications is shrinking. The way I see it, there is an opportunity for Alaska to fill the void, providing it has a scalable (SQL embedded to SQL server) offering.

Any thoughts on Postgres v MySQL ??

Michael

User avatar
sfsltd
Posts: 28
Joined: Sat Jan 30, 2010 7:23 am
Location: UK

Re: SQLite3

#16 Post by sfsltd »

Licensing...

... should also point out that one of the BIG advantages of SQLite is it's BSD/MIT licencing:

SQLite can be distributed royalty free with either Commercial or Open Source code.

Most Open Source databases are licensed under GPL ... restricting them to Open Source distributions.

In the case of MySQL you have to pay for a royalty for commercial distribution. Because we develop client applications (versus web services) the per site costs of each database are significant. In the case of Advantage they would double our costs.

Michael

User avatar
sfsltd
Posts: 28
Joined: Sat Jan 30, 2010 7:23 am
Location: UK

Re: SQLite3

#17 Post by sfsltd »

SQLite has several abstraction wrappers ... any interest?

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Pablo Botella
Posts: 8
Joined: Wed Feb 03, 2010 1:04 am

Re: SQLite3

#18 Post by Pablo Botella »

Hi,
Just uploaded to my site a new version of the sqlite wrappers, with a few enhancements over the TSqliteDb class and a new optional command layer to make the ussage still simpler.

http://www.xbwin.com/sqlite

Sample code

Code: Select all

#include "inkey.ch"
#include "ot4xb.ch"
#include "sqlite3_commands.ch"
//----------------------------------------------------------------------------------------------------------------------
proc main()
local db := SQLITE OPEN ( "test.db" )
local n,nn,nCount,nPageCount,nPage, nKey 
local GetList := {}
local r

// ------
? "TRYING TO CREATE THE TABLE"
? "=========================="
SQLITE BEGINTEXT
      CREATE TABLE t
      (
         id integer PRIMARY KEY AUTOINCREMENT UNIQUE ,
         rnd  ,
         uuid ,
         memo blob 
      );           
ENDTEXT
SQLITE EXECUTE   

? SQLITE LAST MSG  
? " Press a key to continue ... "
inkey(0) ; CLS
// ------
? "INSERTING SOME RECORDS IN BATH MODE"
? "=================================="
SQLITE BEGINTEXT
   INSERT INTO t(rnd,uuid)  VALUES (  1 , 'C3037FBDCAE6457BA84D196D0728FF68');
   INSERT INTO t(rnd,uuid)  VALUES (  2 , 'E9A743C7E6FE4A89ADC52877B35BC8BE');
   INSERT INTO t(rnd,uuid)  VALUES (  3 , '619D5B90F92D47B4B6B224F459A6CB5D');
   INSERT INTO t(rnd,uuid)  VALUES (  4 , 'CA994143AF13488EA12B4A6452E3DE8F');
   INSERT INTO t(rnd,uuid)  VALUES (  5 , '1BDE70B65B3142348129E85B2FF1C4A1');
   INSERT INTO t(rnd,uuid)  VALUES (  6 , '268D092FBE1947B89F32C7B8B051C20F');
   INSERT INTO t(rnd,uuid)  VALUES (  7 , 'B17D4C36CA5E4206846C9CE25B784AEC');
   INSERT INTO t(rnd,uuid)  VALUES (  8 , 'BA917345BB3E4688963A4B901AB9F8F9');
   INSERT INTO t(rnd,uuid)  VALUES (  9 , '768BF028172741DF8109BD1FAAE245E9');
   INSERT INTO t(rnd,uuid)  VALUES ( 10 , '5B7726B1C56240488B85215B2A97D6A0');
ENDTEXT                        

SQLITE ON BATH RESULT {|r,ac,cmd,_db| QOut( cPrintf(,"%s\r\n%i rows affected.",cmd,_db:nChanges))}
SQLITE ON BATH ERROR  {|ne,ce,cmd,_db| QOut( cPrintf(,"%s\r\nError: %i - %s",cmd,ne,ce))}
SQLITE BATH EXECUTE
? " Press a key to continue ... "
inkey(0) ; CLS 
// ------
? "INSERTING A RECORDS WITH PARAM BINDING"
? "======================================"
SQLITE BEGINTEXT
   INSERT INTO t(rnd,uuid)  VALUES ( :rn , :uu )
ENDTEXT
SQLITE PARAM rn   := ( INT  ) @msvcrt:rand()
SQLITE PARAM uu   := ( TEXT ) cUuidCreateName()
SQLITE EXECUTE
if SQLITE FAILED
   ? cPrintf(,"%s\r\nError: %i - %s",SQLITE LAST CMD,SQLITE LAST ERROR,SQLITE LAST MSG)
else
   ? cPrintf(,"%s\r\n%i rows affected.",SQLITE LAST CMD,SQLITE LAST ROWS AFFECTED)
end
? " Press a key to continue ... "
inkey(0) ; CLS 
// ------
? "INSERTING SOME RECORDS WITH PARAM BINDING "
? "BUT REUSING THE QUERY"
? "=========================================="
SQLITE BEGINTEXT
   INSERT INTO t(rnd,uuid)  VALUES ( :rn , :uu );
ENDTEXT          
for n := 1 to 10
   SQLITE PARAM rn   := ( INT  ) @msvcrt:rand()
   SQLITE PARAM uu   := ( TEXT ) cUuidCreateName()
   SQLITE EXECUTE SAVE
   if SQLITE FAILED
      ? cPrintf(,"%s\r\nError: %i - %s",SQLITE LAST CMD,SQLITE LAST ERROR,SQLITE LAST MSG)
   else
      ? cPrintf(,"%s\r\n%i rows affected.",SQLITE LAST CMD,SQLITE LAST ROWS AFFECTED)
   end
next        
SQLITE CLEAR   // release the query when not needed anymore
? " Press a key to continue ... "
inkey(0) ; CLS 
// ------
nCount := ( SQLITE PERFORM "SELECT count() FROM t" CAST "I" )[1][1]     
nPageCount := nRShift(nCount,4) + iif(lAnd(nCount,0x0F),1,0)  
nPage      := 1
// 16 records per page 
nKey := 0
while nKey != 27       
   
   cls
   SQLITE BEGINTEXT
      SELECT 
         id,rnd,uuid 
         FROM t
         LIMIT  16
         OFFSET :offset
   ENDTEXT          
   SQLITE PARAM offset := ( INT  ) nLShift( nPage - 1 , 4 )
   SQLITE COL CAST "IIC"
   r := SQLITE EXECUTE
   nn := len( r )
   ? cPrintf("  %10.10s    %10.10s    %s","id","rnd","uuid")
   ? ChrR(45,70)
   for n := 1 to nn
      ? cPrintf("  %10.10i    %10.10i    %s",r[n][1],r[n][2],r[n][3])
   next
   ? ChrR(45,70)
   ? cPrintf( "Page %i of %i." , nPage , nPageCount ) 
   ? cPrintf( "Total records: %i." , nCount ) 
   ? "[Esc]   - EXIT "
   ? "[0 - 9] - goto page "
   ? "[R]     - Refresh query"
   

   nKey := inkey(0)      
   if nKey == 27 ; exit
   elseif nKey == K_PGUP ; nPage--
   elseif nKey == K_PGDN ; nPage++
   elseif nKey == K_HOME ; nPage := 1
   elseif nKey == K_END  ; nPage := nPageCount
   elseif nKey == K_F5
      nCount := ( SQLITE PERFORM "SELECT count() FROM t" CAST "I" )[1][1]     
      nPageCount := nRShift( nCount,4) + iif(lAnd(nCount,0x0F),1,0)  
   elseif Chr(nKey) $ "0123456789"
      KEYBOARD ( Chr(nKey) )
      cls
      SetCursor(1)
      ? "Current Page: " , nPage     
      ? ""
      ? "Last    Page: " , nPageCount
      @ (Row() + 2) , 0 SAY "Goto Page: " GET nPage PICTURE "999999999999"
      READ
   end                            
   if nPage < 1 ; nPage := 1 ; end
   if nPage > nPageCount ; nPage := nPageCount ; end
end



SQLITE CLOSE          
return
Regards,
Pablo Botella

User avatar
unixkd
Posts: 565
Joined: Thu Feb 11, 2010 1:39 pm

Re: SQLite3

#19 Post by unixkd »

Hi Roger

There was an extensive discussion on this topic several years ago. I would like to know if you were able to make progress on the SQLite stuff. I am working on a project that will require several thousands of installations our best consideration for datastorage is SQLite.

Thanks

Joe

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: SQLite3

#20 Post by rdonnay »

I have never done anything with SqlLite.
The eXpress train is coming - and it has more cars.

Post Reply