New SQLite plugin

[cosenodefi]

Hi, everyone.

I've made a plugin for the access to a SQLite Database (SQliteDB). It's in development stage. So, please, be carefull and read de documentation before.

The plugin relies on an external DLL, 'sqlite3.dll', which can be obtained from the sqlite official site. (Download - precompiled binaries for windows, SQLite library w/o TCL bindings). This DLL can be on the plugins folder or in a PATH folder.

The plugin was developed & tested with version 3.6.3 of sqlite, and runs w/o problems in my tests. It uses a INI file to configure the settings, and has three functions:

Function 1 - Data retrieval
Function 19 - Data retrieval (Debug mode, with filedump)
Function 20 - About Info

UPDATE (2011-Feb-13)

So... I've just found some spare time, and I've done some changes (by 'popular petition'):

- Unlimited queries (selectable by index) in the config file. This config file has a new section 'queries' for that info.
- Customizable message for empty result dataset. ('No data!!', will be shown if none is provided).
- Customizable message for empty query string. ('No query!!', will be shown if none is provided).

* Function 1: (DATA RETRIEVAL)

The parameters are:
<param1> - The index of the query string in the 'queries' section
<param2> - IGNORED

Example: $dll(sqlitedb,1,<qry_idx>,)

The same syntax can be applied to function 19.

A example of a working config file is included in the ZIP.

[caesar]

And a Big Thank You again for another great plugin!

[cosenodefi]

And a Big Thank You again for another great plugin!

Please don't mention it.!

This is only because... I couldn't left alone the MySQL one.

More seriously... there's a lot of SQLite databases out there. Many apps are changing to that system of storage, instead of propietary formats or hard-to-manage files.

So... if there isn't one plugin for LCDSamrtie... let's make it.

[pbs]

nice one, but could you make it have multiple queries in .ini? [like maybe 10 queries, selectable by function param?]

or make the queries in smartie, but substituting delim char for commas, [cuz won't expect it in returned values if using it for delimiter, so it's a character you wouldn't be using anyways] and substitute it back just before making query to dll..
keep the .ini for delim,etc but queries would be done straight in smartie..
then it would be REALLY useful... [unlimited number of queries possible then]

also, return nothing when no data instead of No data!! as that makes things harder when just trying to display db data only when it exists..
[or a configurable null msg in .ini]

[pbs]

well, for those who need an extra query, here's a temp solution for 1 more..
i renamed sqlitedb.dll to sqlited2.dll and hex-edited it, adjustng the .ini file it looks for to "sqlited2.ini"...
so put both copies of .dll and .ini in plugins dir, for 2nd query use sqlited2.dll, etc. and edit it's query in the sqlited2.ini

[cosenodefi]

nice one, but could you make it have multiple queries in .ini? [like maybe 10 queries, selectable by function param?]

or make the queries in smartie, but substituting delim char for commas, [cuz won't expect it in returned values if using it for delimiter, so it's a character you wouldn't be using anyways] and substitute it back just before making query to dll..
keep the .ini for delim,etc but queries would be done straight in smartie..
then it would be REALLY useful... [unlimited number of queries possible then]

also, return nothing when no data instead of No data!! as that makes things harder when just trying to display db data only when it exists..
[or a configurable null msg in .ini]

Well...
I've modified the plugin to do multiples queries (specified in the config file).
I also changed the messages of the plugin, now they're customizable (in the config file too).

The idea of querying the DB on LCDsmartie directly implies more than a simple 2-way character substitution.
Whatever character you choose for delimiter, it can be part of a hypotetical query string in data, so it will be necesary an escape char to deal with. It means every query string will need to be parsed before can be used. It would add an unnecesary complexity, and it wil be more prone to errors that the actual & simple method of copying the SQL query (without changes) to text file.

Regards
JR

[pbs]

sweet...many thanx...been using it for a while now with no problems..
now it'll just be more useful and simpler to setup

[I'm using it to display Npvr 'next' data when program is off, because Npvr's plugin isn't active when program isn't running, for now/next info when sleeping]

i also made a single query that converts 24hr time to 12 hr and adds ap/pm...found the idea online somewhere but it's great cuz it gives a function that sqlite has omitted..
check this out:
[note: it's a single line]

Code: Select all

SELECT 'Next-'||CASE WHEN strftime('%H',start_time,'localtime') > '12' THEN ((strftime('%H',start_time,'localtime') - 12)|| ':' ||strftime('%M',start_time,'localtime')|| 'pm') WHEN strftime('%H',start_time,'localtime') = '12' THEN '12'||(strftime(':%M',start_time,'localtime')|| 'pm') WHEN strftime('%H',start_time,'localtime') = '00' THEN '12'||(strftime(':%M',start_time,'localtime')|| 'am') ELSE (strftime('%H:%M',start_time,'localtime') || ' am')  END AS 'full name', ' ' ||name || ' ' FROM scheduled_recording WHERE status = 0  order by start_time limit 1