What's SQLite?

SQLite is a software library that implements a self-contained, server less, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. It is used in countless desktop computer applications as well as consumer electronic devices including cell phones, PDAs, and MP3 players. The source code for SQLite is in the public domain.

Simply above lines mean:

- SQLite = Database.
- SQLite is free for every purpose.
- SQLite needs no installation nor dependencies on end-user machine. All you need is SQLite plug-in.

SQLite Copyright:

All of the deliverable code in SQLite has been dedicated to the public domain by the authors. All code authors, and representatives of the companies they work for, have signed affidavits dedicating their contributions to the public domain and originals of those signed affidavits are stored in a fire safe at the main offices of Hwaci. Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

Well now with the help of SQLite plug-in you have this power in your hand to make database enabled MMB applications. ;)

What are differences between SQLite syntax and ANSI SQL?

There is no much difference. SQLite understands most of standard SQL commands. here is the page describes SQL features that SQLite does not Implement:

http://sqlite.org/omitted.html

Note for International Users:

I had some difficulties in first version of SQLite Plug-In. but after some examination and tweaking I decided to rewrite some parts of SQLite Plug-in and now you can smoothly use SQLite plug-in in non-Latin based languages such as Arabic and etc. There is no difference in SQLite Plug-In commands if you need to read/write in non-Latin languages. but don't forget to set the font Script property in your MMB application as shown here:

Well now with the help of SQLite plug-in you have this power in your hand to make database enabled MMB applications. ;)

 

SQLite Plug-In Commands :


 SQLiteInit  Top

Used to initialize SQLite engine. It must be called at program startup of before any other commands. It returns 1 on success.

Example:


  PluginRun("PlugIn","SQLiteInit")
  PluginGet("PlugIn","IsInitialized")

Note : Run demo to figure it how

 CreateSQLDB Top
You must use this to create your database file on your hard disk. This commands needs a database file name (e.g : MyDB.db) and its path to work ok. On success this returns 1 and other values on failure.

Example:



Note : Run demo to figure it how

 GetDBFileName Top
This is used with CreateSQLDB command. it must be called before CreateSQLDB to pass database filename to SQLite Plug-in.

Example:



Note : Run demo to figure it how. At runtime you should enter the new name manually in the Destination folder input.


 OpenSQLDB Top
This command used for opening previously created database in order to interact with and pass SQL queries. This returns 1 on success.



Note : Run demo to figure it how.

 Query Top
This is the heart of SQLite plug-in. we use this to send our SQL queries to SQLite. This can be used for all kinds of queries compatible with SQLite. This returns results or result set in a delimited string. On aggregate functions it returns only the result in a string format without any delimiter.
SQLite Plug-in now returns mixed results like this: example for 2 rows of data:

1|name1|password1||2|name2|password2||

at the end of each row there is double pipe line characters. This command also returns one other integer value to show you number of rows in result set.



Note : Run demo to figure it how. SQLite Syntax is here.

 UpdateDB Top
Executes the SQL queries on the database. This function is similar to Query but used specifically for queries that don't have any result. Like Update and Create. Therefore it's not possible to do a 'SELECT' like request with this function. This function is useful for updating records in the database. If Result is 0, then the query could not be executed correctly (due to a SQL fault or a badly-formatted query). The error text can be retrieved using DBError).

Example:



Note : Run demo to figure it how.

 DBFileSize
To get database file size from now on you can use DBFileSize command. it returns database size in bytes.

Example:



Note : Run demo to figure it how.

 DBError  Top

Returns a description of the last database error in text format.

Example:


Note : Run demo to figure it how

 IsDB  Top

This function evaluates if the given database is a valid and correctly-initialized database.If Result is not zero then the object is valid and initialized, otherwise it returns zero. It must be called after OpenSQLDB command. because IsDB needs initialized file resource handle.


 SetRecordDelimiter  Top

You can use SetRecordDelimiter command to pass your record delimiter to SQLite plug-in.


What's it good for? Well sometimes there may be some special characters in your data. for example you need to type # in address field, so if your record delimiter is # then you'll get wrong result. Default delimiter characters for Records is # and for Fields is | .

 SetFieldDelimiter  Top

You can use SetFieldDelimiter command to pass your field delimiter to SQLite plug-in.
What's it good for? Well sometimes there may be some special characters in your data. for example you need to type # in address field, so if your record delimiter is # then you'll get wrong result. Default delimiter characters for Records is # and for Fields is | .

 CloseDB  Top

Close opened database. SQLite also close opened database automatically on exit.


Note : Run demo to figure it how

 Encode64Data  Top

Encodes the specified string using the Base64 algorithm. This is widely used in e-mail programs but can be useful for any other programs which need an ASCII only (7 bits, only from 32 to 127 characters) encoding for raw binary files. Result will contain the length (in bytes) of the encoded string. This can be used for simple data protection or binary data such as pictures. this function returns 0 on failure.

 Decode64Data  Top

Decodes the specified Base64 encoded string. this function returns 0 on failure.

 MD5Data  Top

Returns a 32 characters long MD5 (Message Digest 5) hash code. This can be used for sensitive data such as passwords.

Example:

 SH1Data  Top

Returns a 40 characters long SHA1 (Secure Hash Algorithm 1) hash code of the given string. the mechanism is same as MD5 but different Algorithm.

 SetDBAttribute  Top

Since there is no full protection system available in SQLite Plug-In, setting basic database files attributes along other basic protections maybe useful. So you can hide or make databases read-only on runtime. This command gets an integer from 1 to 4 and returns non-zero value on success.

Input values are:

1 : Hidden Switch

2 : Read-Only Switch

3 : System File Switch

4 : Make Normal Switch

 

 

Example: