Opening and creating Sqlite databases using the USQLStatement class

[Till Warweg]

The Xbase++ runtime contains an embedded SQLite engine which it uses for executing local SQL statements. By default, a global in-memory database is used as the data source for local queries and commands.

Methods of the USQLStatement class, such as :createVirtualTableFromWorkArea(), can be used to augment this local data set. In addition, an external SQlite database can be opened and attached to the in-memory database using :attachDatabase().

In addition to the global in-memory database, the embedded SQLite engine also supports using an external SQlite database as the data source. To do this, the database must be opened using the method openDatabase( <cFile> ). If the specified database does not exist, it is created. This can be used for creating a new SQLite database from scratch.

Note that using openDatabase() causes the global in-memory database to be closed.

Example:

// Open a SQLite database as the storage for the embedded SQLite engine
USQLStatement():openDatabase( "c:\driverlog.db")

// Execute a query on the "route" table
oStmt := USQLStatement():new():fromChar( "SELECT * FROM route" )
oStmt:build()
oStmt:query(USQL_RESULT_OBJECTS, @aData)

? aData[1]:start_poi
? aData[1]:end_poi

 

[Frans Vermeulen]

In order to create geopackage files, see: https://www.geopackage.org/
I wrote an Xbase++ interface for SQLite some time ago.
In order to create those geopackage-files, you need SQLite including spatiallite.

Since this option is now available directly from Xbase++, I thought it would be handy to try it this way.
So far, without succes. Leaving some questions open:
- USQLStatement():New() creates a new object, reading the docs ...:fromChar('...') too, is this correct?
- Does the Xbase++ SQLite-engine include spatiallite?
- Can :attachDatabase() do the same trick?
- fromChar() gives an error I do not understand, what does it mean?

:Select(),:Insert(), :Update(), elete() or :FromChar() needs execute first
genCode : 9999
subCode : 9999
subSystem : USQL

TIA,
Frans Vermeulen

 

[Till Warweg]

Dear Frans,

USQLStatement:new() is a constructor method. It returns an new/initialized SQL statement instance. The method :fromChar() is different; it can only be called on an instance. In other words, you first need to call :new() before you can use :fromChar()!

:fromChar() sets up the SQL statement using the supplied SQL statement string. This may involve making available parameters as instance members, for example. A statement object cannot be used unless :fromChar() was called. This is why the documentation says :fromChar() to "create" an object.
I hope this helps clearing things up a bit.

Regarding your enquiry about SpatiaLite, could you please direct this request to the support department? Please also describe what you want to do. If you need to use an existing Sqlite database, :attachDatabase() may be what you need. However, this depends on the use-case. Thanks!

Regards,
Till Warweg

 

[Frans Vermeulen]

Thanks Till,

This adresses exactly my questions, though I was a bit surprised to see that penDatabase can create a new database,
and :attachDatabase can not, since a "0"-byte sized file is a valid empty SQLite database.

It should not be too hard, to redirect the SQL's and commands used to create such a geopackage-file,
into a textfile, and show support with a few lines of code, what I try to achieve.

Regards,
Frans Vermeulen