bb.donnay-software.com

Donnay Software Web Forums
It is currently Sun Jun 07, 2020 1:57 am

All times are UTC - 7 hours




Post new topic Reply to topic  [ 20 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: SQLite3
PostPosted: Thu Feb 04, 2010 5:58 am 
Offline

Joined: Wed Feb 03, 2010 1:04 am
Posts: 7
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:
                                   
// 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:
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 436 times
Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Thu Feb 04, 2010 6:18 pm 
Offline
User avatar

Joined: Sat Jan 30, 2010 7:23 am
Posts: 28
Location: UK
Pablo

Very informative, thanks.

Michael


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Sun Feb 28, 2010 6:55 pm 
Offline

Joined: Sun Feb 28, 2010 6:50 pm
Posts: 2
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.


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Sun Mar 07, 2010 7:13 am 
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.


Top
  
 
 Post subject: Re: SQLite3
PostPosted: Mon Mar 22, 2010 7:04 pm 
Offline
User avatar

Joined: Sat Jan 30, 2010 7:23 am
Posts: 28
Location: UK
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


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Tue Mar 23, 2010 1:50 am 
Offline
User avatar

Joined: Sat Jan 30, 2010 7:23 am
Posts: 28
Location: UK
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


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Wed Apr 07, 2010 8:00 am 
Offline
User avatar

Joined: Sat Jan 30, 2010 7:23 am
Posts: 28
Location: UK
SQLite has several abstraction wrappers ... any interest?

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


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Wed May 26, 2010 4:03 pm 
Offline

Joined: Wed Feb 03, 2010 1:04 am
Posts: 7
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:
#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


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Fri Dec 07, 2018 11:51 am 
Offline
User avatar

Joined: Thu Feb 11, 2010 1:39 pm
Posts: 490
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


Top
 Profile  
 
 Post subject: Re: SQLite3
PostPosted: Fri Dec 07, 2018 12:30 pm 
Offline
Site Admin
User avatar

Joined: Wed Jan 27, 2010 6:58 pm
Posts: 4128
Location: Boise, Idaho USA
I have never done anything with SqlLite.

_________________
The eXpress train is coming - and it has more cars.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 20 posts ]  Go to page Previous  1, 2

All times are UTC - 7 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group