SQLite Plug-in, free database plug-in for MMB, It was a dream that comes true! Options

[silvercover]

Hi Guys,

These days I have a little more spare time and therefor I spend it on making plug-ins for Lovely MMB community.This time I have a big surprise and that is SQLite plug-in to have database features in our lovely app, Multimedia Builder. Ladies and Gentlemen let me first introduce you with SQLite.

What is SQLite?


SQLite is a software library that implements a self-contained, serverless, 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 cellphones, 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.


Visit SQLite Official Website

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 firesafe 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.

***

Note:
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.

And finally here is SQLite Plug-in full package (second attachment only contains DLL file):

This post has been edited by silvercover: Sep 8 2009, 08:14 PM

Attached File(s)

 SQLitePlugIn1_1.zip ( 636.25K ) Number of downloads: 493
 SQLite1_1_PlugnInDLL.zip ( 175.75K ) Number of downloads: 339

 

[Nevercom]

WoW !!! You're a Genius Silvercover !
I'm Gonna Test it Now.....

Thanks.

[Saldash]

Well, someone has been working overtime..
Welldone on the plugin SilverCover!

I'll see what I can do with it as soon as Core Update 1 is complete for my DSL Agent.


Regards,
Saldash

[silvercover]

Thank you guys.

There is one important question. How can we pass mixed results to MMB?

For example:

sql$='SELECT username,password FROM users_table;'

this returns two column and if we get it in delimited format then there is difficulty in finding what fields belong to what column?
what is your solution?

if we could pass native arrays from Plug-in to MMB then we could do better.

[Danny]

Hi Guys,
Well these days I have a little more spare time and therefor I spend it on making plug-ins for Lovely MMB community.This time I came with a big surprise and that is SQLite plug-in to have database features in our lovely app, Multimedia Builder. Ladies and Gentlemen let me first introduce you with SQLite.
And finally here is SQLite Plug-in(without any demo):

Hello Silvercover,

Helas, I am a complete layman and ignorant for scriptlanguage and programming.
In spite of that, I can see clearly the powerfull potential for this plugin.
Once there are examples, people like me will be on the road too. (hopefully )
Thanks for allways trying to improve the possibilities of this nice programm (MMB).
Danny

[DestroY]

Thank you guys.

There is one important question. How can we pass mixed results to MMB?

For example:

sql$='SELECT username,password FROM users_table;'

this returns two column and if we get it in delimited format then there is difficulty in finding what fields belong to what column?
what is your solution?

if we could pass native arrays from Plug-in to MMB then we could do better.

Keep the query results in an array inside the instance of the dll and add a fetch(row,col) function. I think it's the easiest way.

[Nevercom]

Hello Silvercover,
I've tested your plug, it was good....
but something's wrong..., when i execute a SQL query, such as "SELECT Username FROM Users;" i get the records in an array (works fine), but if i execute another query e.g. "SELECT ID FROM Users;" i will get the records that i've got from first command + records from ID field (given by second SQl query)....
And it doesn't makes any sense if I empty the String that the Data was puted in.

[silvercover]

Hello Silvercover,
I've tested your plug, it was good....
but something's wrong..., when i execute a SQL query, such as "SELECT Username FROM Users;" i get the records in an array (works fine), but if i execute another query e.g. "SELECT ID FROM Users;" i will get the records that i've got from first command + records from ID field (given by second SQl query)....
And it doesn't makes any sense if I empty the String that the Data was puted in.

Yes I am aware of this problem and it's fixed. I'm working on mixed results as I said in my previous post and when finished I'll upload it here.

Thank you.

[silvercover]

SQLite Plug-in updated. To download new version see the first post.

Fixes:
- Repeated results now gone and you get fresh result every time you run your query. (mentioned by Nevercom).
- You can now get mixed results without any problem.

Addition:
- Query command returns one other integer value to show you number of rows in result set.
- 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 # character.

Example:

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

SaveFile("Text Files (*.sql)|*.sql|All Files|*.*||","*.sql")
PluginSet("PlugIn","OpenFile$")
PluginRun("PlugIn","GetDBFileName")
PluginRun("PlugIn","OpenSQLDB")

sql$= 'select * from profile;'
PluginSet("PlugIn","sql$")
PluginRun("PlugIn","Query")
PluginGet("PlugIn","dbquery$")
PluginGet("PlugIn","rows")
rows$ = CHAR(rows)
Message("Rows","rows$")
Message("","dbquery$")

PluginRun("PlugIn","DBError")
PluginGet("PlugIn","dberror$")


Note:

In order to get familiar with SQLite you can use one handy visual tool called SQLite Database Browser accessible from here:

SQLite Database Browser



Official Website

by using this tool you can simply make your queries and examine it.

[Nevercom]

Nice Update Silvercover.
BTW, Does DBError returns Last Database Error ? If so, if I execute a query and no errors occurs, DBError Function returns error message of last Database error or nothing ?

[Shafayat]

Thanks silvercover.

You rock! and so does your plugin.

Regards
Shafayat

[silvercover]

Nice Update Silvercover.
BTW, Does DBError returns Last Database Error ? If so, if I execute a query and no errors occurs, DBError Function returns error message of last Database error or nothing ?

If there was no error then return value of DBError is empty string.

[Nevercom]

Hi Again,
Silvercover, It seems that "IsDB" Function is not working well,
I used this codes in my project and i got "0" as result:

CODE

  PluginRun("PlugIn","IsDB")
  PluginGet("PlugIn","Database")
  Message("","Database")

Whether i'm working with DB and i can get Data from it....

[silvercover]

Please provide me full source. I've tested it several times and found no error.

[Nevercom]

Sure, Here is the Source:  DBTest.rar ( 173.7K ) Number of downloads: 193

[silvercover]

It must be called after OpenSQLDB command. because IsDB needs initialized file resource handle.

Thank you.

[silvercover]

I made a quick step by step guide for SQLite plug-in to help users undersand it better.

Attached File(s)

 SQLiteIntro.zip ( 134.19K ) Number of downloads: 194
 SQLitePlugInIntroduction.png ( 27.79K ) Number of downloads: 24

 

[Nevercom]

BTW, Is there any way to get the list of Tables of Database, and list of Fields of each Table ?
Can be done using SQL Commands or needs PlugIn support ?

[silvercover]

You can easily get list of tables by passing below query to SQLite plug-in:

CODE

sql$= 'SELECT name FROM sqlite_master WHERE type=\'table\' ORDER BY name;'
PluginSet("PlugIn","sql$")
PluginRun("PlugIn","Query")
PluginGet("PlugIn","dbquery$")

then SQLite plug-in returns alphabetically sorted table names separated by two pipe lines.

Also try this and see what happens:

CODE

sql$= 'SELECT * FROM sqlite_master WHERE type=\'table\' ORDER BY name;'
PluginSet("PlugIn","sql$")
PluginRun("PlugIn","Query")
PluginGet("PlugIn","dbquery$")

Useful tip:

Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CODE

   CREATE TABLE sqlite_master (
      type TEXT,
      name TEXT,
      tbl_name TEXT,
      rootpage INTEGER,
      sql TEXT
    );

For tables, the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

The SQLITE_MASTER table is read-only. You cannot change this table using UPDATE, INSERT, or DELETE. The table is automatically updated by CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.

[silvercover]

This time I came with new things. to download new version please see the first post.

Addition:
- To get database file size from now on you can use DBFileSize command. it returns database size in bytes.
- One simple sample added to SQLite plug-in package. more samples will be added soon.



Updated:
- 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(record) there is # character.