Back to LiteX page

LiteX Automation – automation wrapper for SQLite3 library.

LiteX Automation is an automation wrapper over SQLite3 library.
Binary and sources of this library you can find in LiteX subdirectory of LiteX package.

This library is build in two versions: sqlite3.dll and sqlite3u.dll. Each of them exports all known sqlite3_... functions and two new functions listed below. sqlite3.dll uses UTF-8 text encoding and sqlite3u.dll uses UTF-16LE text encoding.
Automation objects may be registered using regsvr32 tool:

regsvr32 sqlite3.dll

You may unregister automation objects by typing:

regsvr32 /u sqlite3.dll

Please register this library every time you download new version because interface definition may be changed.

If you have trubles with sqlite3.dll library registration it means that you propably have another sqlite3.dll library into your system directory. Putting sqlite3.dll into system directory is a very bad idea so make sure that this library int this location is really nessesary. If you really need this library into system directory please copy LiteX Automation library into system directory and register it. In most situations this solution works but version compatibility problems may occurs – be carefull. Another solution is to run regsvr32 tool as follows:

regsvr32 .\sqlite3.dll

or specify full path to this library even if you run regsvr32 from directory where sqlite3.dll resides.


Additional functions.

sqlite3_register_blob_functions(sqlite3 *)

This function register two new functions for sqlite3 engine that works on blob fields:

Function

Description

Example

tovis( blob <,repl> )

Converts blob argument to string. Unprintable characters are converted to repl character (default ".").

SELECT tovis( b ) FROM blober;
SELECT tovis( b, "_") FROM blober;

tohex( blob <,sep> )

Converts blob argument to string with hexadecimal notation. Optionally bytes may be separated by sep character (no separator by default).

SELECT tohex( b ) FROM blober;
SELECT tohex( b, "|") FROM blober;

sqlite3_register_unacc_functions(sqlite3 *) and sqlite3_unregister_unacc_functions(sqlite3 *, void* )

sqlite3_register_unacc_functions register one function for sqlite3 engine that works on text fields:

Function

Description

Example

unaccent( txt )

Removes accents from specified text.

SELECT unaccent( b ) FROM some_table;

Additionally it registers two collation sequences:

Collation sequence

Description

Example

unaccented

Compares unaccented text. Case sensitive version.

CREATE Table( t TEXT COLLATION unaccented );

unaccentedi

Compares unaccented text. Case insensitive version.

CREATE Table( ti TEXT COLLATION unaccentedi );



This function returns pointer which must be used in sqlite3_unregister_unacc_functions as second parameter.


Connection object.

oDb = new ActiveXObject("LiteX.LiteConnection")

Connection object call sqlite3_register_blob_functions and sqlite3_register_unacc_functions so new functions and collation sequences may be used without extra work.

Properties:

Property

Access mode

Description

Examples

Version

RO

String that describes SQLite engine version.

WScript.Echo( "SQLite3 version", oDb.Version );

Path

RO - if database file is open
RW - if database file isn't open

Path to database file.

oDb.Path = "c:\\temp\\db.db"

Changes

RO - if database file is open
NA- if database file isn't open

The number of database rows that were changed (or inserted or deleted) by the most recent database operation.

WScript.Echo( "Last changes", oDb.Changes );

LastInsertRowid( bLong=VARIANT_TRUE )

RO - if database file is open
NA- if database file isn't open

Integer key of the most recent insert in the database:

  • bLong = VARIANT_TRUE (default) : 64-bit integer is returned

  • bLong = VARIANT_FALSE : one dimension array with two elements - low and high half of 64-bit value is returned, this allow to use this property in Visual Basic 6.0 that doesn't support 64-bit integers

WScript.Echo( "Last ID", oDb.LastInsertRowid );

Methods:

Method

Description

Examples

Open( [path] )

Opens selected database. Parameter path may be omitted if you set Path property before.

oDb.Open();

OpenInMemory()

Opens in-memory database.
Contents of in-memry database is destoryed when database is closed.

Code:

oDb.OpenInMemory();

is equivalent to

oDb.Open( ":memory:" );

Execute( sql, ... )

Fast way to execute single query sql. This query may include parameters. Parameters values you specyfy as additional method parameters - see examples.
If query doesn't returns any result empty value is returned.
If query returns one or more rows first row is returned as Row property of statement object does.

oDb.Execute( "INSERT INTO Test(a,b) VALUES (?,?)", "foo", 12.45 );
oDb.Execute( "INSERT INTO Test(a,b) VALUES (?,?)", null, "peacemaker" );
nMax = oDb.Execute( "SELECT max(a) FROM Test" );

BatchExecute( sql )

Executes many SQL statements at once.
Statements should be non-query but this is not nessesary.

oDb.BatchExecute( "CREATE TABLE Test(a); CREATE INDEX idx ON Test(a); CREATE TestAgain(a); CREATE INDEX idx_again ON TestAgain(a)" )

Prepare( sql )

Prepares SQL statement and returns prepared statement object.

oStmt = oDb.Prepare( "SELECT a,b FROM Test WHERE a > 10" );

Close()

Closes database. You must close all statements associated with this database before.

oDb.Close();


Statement object.

Statement object typically is created by Prepare method of connection object but you may create it manually:

oStmt = new ActiveXObject("LiteX.LiteStatement");

Properties:

Property

Access mode

Description

Examples

ActiveConnection

RW - if not prepared
RO - if prepared

Connection object associated with this statement.

oStmt.ActiveConnection = oDb;

CommandText

RW - if not prepared
RO - if prepared

Statement text - SQL query.

oStmt.CommandText = "SELECT a,b FROM Test ORDER BY a DESC";

ColumnCount

NA - if not prepared
RO- if prepared

Number of columns returned by statement.


ColumnName( idx )

NA - if not prepared
RO- if prepared

Name of idx column. Columns are numbered from 0 to ColumnCount-1.


ColumnType( idx )

NA - if not prepared
RO- if prepared

Type of idx column; idx may be:

  • integer - column index

  • string - column name

Return values:

  • lxNull = 0 – NULL value

  • lxInteger=1 – integer value

  • lxLongInteger=2 – 64-bit integer value

  • lxFloat=3 – floating point value

  • lxString=4 – string

  • lxBinary – binary (BLOB)


ColumnValue( idx, [type] )

NA - if not prepared
RO- if prepared

Value of idx column; idx may be:

  • integer - column index

  • string - column name

You may force return type by optional type parameter .


Row

NA - if not prepared
RO- if prepared

Returns whole row in one dimension array.
If ColumnCount = 0 empty value is returned.
If ColumnCount = 1 no array is returned but one single scalar value.

row = oStmt.Row;

ParameterCount

NA - if not prepared
RO- if prepared

Number of statement's parameters to bind.


ParameterName( idx )

NA - if not prepared
RO- if prepared

Name of idx-th parameter. Parameters are numbered from 1 to ParameterCount. If parameter is nameless or idx is out of range empty string is returned.


Done

NA - if not prepared
RO- if prepared

Indicates that Step method returns without any results, this may be end of record set for example.


RowCount( bLong=VARIANT_TRUE )

NA - if not prepared
RO- if prepared

Returns number of rows returned by statement

  • bLong = VARIANT_TRUE (default) : 64-bit integer is returned

  • bLong = VARIANT_FALSE : one dimension array with two elements - low and high half of 64-bit value is returned, this allow to use this property in Visual Basic 6.0 that doesn't support 64-bit integers


Before use this property you should understand how this property works.
Code:

nCount = oStmt.RowCount

is equivalent to:

nCount = 0;
oStmt.Reset();
while ( !oStmt.Step() ) nCount++;
oStmt.Reset();

As you see to determine number of rows all rows must be iterated. This may take long time.  RowCount property is a little bit faster because it doesn't use expensive Automation calls. Use this property with caution and inside transaction.

Methods:

Method

Description

Examples

Prepare( [sql] )

Prepares statement, parameter sql may be omited if you set CommandText property before.

oStmt.Prepare();

BindParameter( idx, [value], type=lxUnknown )

Binds value to parameter; idx may be:

  • integer - parameter index, parameters are numbered from one to ParameterCount

  • string - parameter name, only if named parameters are used in SQL command

Possible values of type:

  • lxUnknown=-1 – guess value type, default

  • lxNull – bind NULL value, value is ignored

  • lxInteger – bind integer value

  • lxLongInteger – bind 64-bit integer value

  • lxFloat – bind floating point value

  • lxString – bind text

  • lxBinary – bind binary value (BLOB)

oStmt.BindParameter( 1, null );
oStmt.BindParameter( 1, 123, 2 /*lxLongInteger*/ );

BindParameters(...)

One-call parameters binding.
Instead of calling BindParameter method many times you may call BindParameters at once.

oStmt.BindParameters( 1, null, “Hello”, 1.2222 )

Step( [nSteps] )

Makes nSteps steps of statement execution. Returns Done property value.
Statement must be prepared before use this method. Default value of nSteps is one - one step (next row).

while( !oStmt.Step() );
oStmt.Step(10);

Execute()

Non-query statement execution.
After execution statement is ready to re-execute.

oStmt.Prepare( “INSERT INTO Table(a) VALUES (?)”);
for( i=0; i<100; i++ )
{
oStmt.BindParameter( 1, i );
oStmt.Execute();
}

Instead of calling Execute() method you can use following sequence:



oStmt.Step();
oStmt.Reset();

Reset()

Resets statement. Begins execution of statement.
Parameters binding remains but may be changed.

oStmt.Reset();

Close()

Closes statement.

oStmt.Close();

Access mode abbreviations:

Abbreviation

Meaning

RO

read only

RW

read and write

NA

not accessible, any access to this property generates error


Error codes:

Error code (hex)

Description

SQLite native error code

00000000

Successful result.
Not an error.

SQLITE_OK

C0000001

SQL error or missing database.

SQLITE_ERROR

C0000002

An internal logic error in SQLite.

SQLITE_INTERNAL

C0000003

Access permission denied.

SQLITE_PERM

C0000004

Callback routine requested an abort.

SQLITE_ABORT

C0000005

The database file is locked.

SQLITE_BUSY

C0000006

A table in the database is locked.

SQLITE_LOCKED

C0000007

A malloc() failed – out of memory.

SQLITE_NOMEM

C0000008

Attempt to write a readonly database.

SQLITE_READONLY

C0000009

Operation terminated by sqlite3_interrupt().

Never raised.

SQLITE_INTERRUPT

C000000A

Some kind of disk I/O error occurred.

SQLITE_IOERR

C000000B

The database disk image is malformed.

SQLITE_CORRUPT

C000000C

(Internal Only) Table or record not found.

SQLITE_NOTFOUND

C000000D

Insertion failed because database is full.

SQLITE_FULL

C000000E

Unable to open the database file.

SQLITE_CANTOPEN

C000000F

Database lock protocol error.

SQLITE_PROTOCOL

C0000010

Database is empty.

SQLITE_EMPTY

C0000011

The database schema changed.

SQLITE_SCHEMA

C0000012

Too much data for one row of a table.

SQLITE_TOOBIG

C0000013

Abort due to constraint violation.

SQLITE_CONSTRAINT

C0000014

Data type mismatch.

SQLITE_MISMATCH

C0000015

Library used incorrectly.

SQLITE_MISUSE

C0000016

Uses OS features not supported on host.

SQLITE_NOLFS

C0000017

Authorization denied.

SQLITE_AUTH

C0000018

Auxiliary database format error.

SQLITE_FORMAT

C0000019

2nd parameter to BindParameter out of range.

SQLITE_RANGE

C000001A

File opened that is not a database file.

SQLITE_NOTADB

40000064

Another row ready.
Not an error.

SQLITE_ROW

40000065

Finished query execution.

Not an error.

SQLITE_DONE

C00000C8

Statement already prepared.

LiteX specific

C00000C9

Connection property not set.

LiteX specific

C00000CA

No SQL statement was given.

LiteX specific

C00000CB

Statement not prepared.

LiteX specific

C00000CC

Unknown binary data.

LiteX specific

C00000CD

Cannot guess data type.

LiteX specific

C00000CE

Cannot get column name.

LiteX specific

C00000CF

Unknown column type or bad column index.

LiteX specific

C00000D0

Cannot create statement object.

LiteX specific

C00000D1

Column index out of range.

LiteX specific

C00000D2

Unknown column name.

LiteX specific

C00000D3

Unknown column index type. Only string or integer values are allowed.

LiteX specific

C00000D4

Parameter index out of range.

LiteX specific

C00000D5

Unknown parameter name.

LiteX specific

C00000D6

Unknown parameter index type. Only string or integer values are allowed.

LiteX specific

C00000D7

Database file is open.

LiteX specific

C00000D8

Database file isn't open.

LiteX specific

C00000D9

Bad step parameter.

LiteX specific




Some examples may be found in Script directory of LiteX package.

Building LiteX Automation from source.

LiteX uses ATL library. The minimum required ATL version is 3.0.

To build LiteX binaries I'm using Visual Studio 2005 compiler. For long time Visual Studio 6.0 was used but for some reason I cannot install this application on my new computer. Project files (dsp,dsw) from VC 6.0 are still included but are out of date - modifications are simple but cannot make them. If you have VC 6.0 compiler and want to help develop LiteX  please contact me. For the same reason I cannot recompile VB example.

LiteX is by default compiled using my libunacc library. You can ommit this stuff using "... no Unacc" (e.g. "Release no Unacc") configuration. Please specify "... no Unacc" configuration if during compilation unacc.h header (from libunacc library) is missing. 

If you have problems with LiteX sources you can allways contact me.  I consider putting LiteX sources in some public repository. If you can help (where?, how?) please contact me too.


Happy scripting!

Contact: roed@onet.eu.