Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Work in Progress / DARQLite - a SQLite3 wrapper plugin for DBPro

Author
Message
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 30th Dec 2012 11:16 Edited at: 22nd Feb 2013 17:48
Success! I have managed to create a wrapper for SQLite3. It is very much a beta version at the moment. But I have managed now to create .db sqlite3 databases and query them, create tables etc.

This is a beta of the plugin so please DO test it to destruction - no one seems to have done so yet...

Why SQLite3? Because it is serverless, self-contained, requires no configuration and has no dependencies. Took me several years to realise, but SQL is a very powerful, very simple, database language in itself. And it's fast. SQLite does not have the full SQL language but near enough. It does indices, views, multiple tables etc within Databases and the query/search capababilities are very flexible. See the link to SQL syntax below.

Current Version = v0.6b (22/02/2013)
Update Log:-


Current Command List (@ 73 commands)



Notes:-
- Please forgive the awful .txt help files - they were generated by a (rubbish) program I wrote to build .rcs, .inis and help files
- Please also forgive the repetition in some of the commands
- In a sense, I have over-re-invented the wheel with the commands above to simplify SQL query - but the strength is in SQL itself.

Useful Links:-
SQLite Homepage:- http://www.sqlite.org/
SQL syntax:- http://www.w3schools.com/sql/sql_syntax.asp
SQL as understood by SQLite:- http://www.sqlite.org/lang.html
SQLite (freeware) Database Manager GUI I found on the web:- http://osenxpsuite.net/?xp=3 (see bottom of web page) (can import MS Access databases/tables!) download link:-http://link.osenxpsuite.net/?uid=homepage&id=sqlite2009pro.zip

Ideas for further development:-
- commands to allow for 'BLOB's of data ie. to allow maybe for storing of images in database...
- commands for import of CSVs in to Tables / databases - perhaps import of other formats

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Attachments

Login to view attachments
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 30th Dec 2012 22:58
So, with this plugin, you can open .db files as though they were SQL Databases, and run queries on them, without installing SQL server software or setting up ODBC connections in windows? Or am I missing something, because this seems almost too good to be true...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 30th Dec 2012 23:36 Edited at: 1st Jan 2013 18:31
@ GIDustin,

[edit] - Sorry, have now re-read your post. If by .db files you mean SQLite database files, then yes.

If by .db files you mean MS Access database files (.mdb?) then sorry, you're right it IS too good to be true. DARQLite is not a shortcut to using MS Access / ODBC databases, unfortunately.

However, DARQLite IS just a plugin to load or create, update, alter, insert into or interrogate SQLite3 databases. But that is all I really wanted.... You can of course run SQL queries and statements on the open database(s) using SQL(as understood by SQLite) syntax, or the functions I have designed as 'shorthand'...

That freeware link at the bottom of the first posting is a SQLite GUI database manager - but it does allow you to import MS Access databases/tables. So, you could convert over but you couldn't convert back, if you see what I mean...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 1st Jan 2013 17:45 Edited at: 1st Jan 2013 19:10
@ GIDustin & all,

I have posted an updated version of the plugin! - more functionality - see above.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
WLGfx
16
Years of Service
User Offline
Joined: 1st Nov 2007
Location: NW United Kingdom
Posted: 4th Jan 2013 03:26 Edited at: 4th Jan 2013 03:28
Nice work Duffer... Gonna be looking into SQLite now myself...

Mental arithmetic? Me? (That's for computers) I can't subtract a fart from a plate of beans!
Warning! May contain Nuts!
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 4th Jan 2013 08:51 Edited at: 4th Jan 2013 08:54
@ WLGfx,

Thanks,

Was pretty much the ignoramus on SQL until I started looking in to SQLite and its differences with the other SQL APIs. In my humble opinion, it is very much overlooked.

Hoping to release an upgraded version of the wrapper at the weekend (I suppose it is no longer a wrapper if I add functions - although most of this can be done with standard SQL - although I am using SQLite specific PRAGMAS a lot).

Aiming for far more functions on table columns, their definitions (datatype) and constraints. Also more functions allowing for copying of tables and deleting of columns etc - whilst retaining other metadata like definitions, contraints, primary and foreign keys...

That's the idea anyway...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 5th Jan 2013 15:49
@ WLGfx, all,

I've added quite a few commands to allow for accurate mapping of database table columns and indices. See update notes. I've posted the updated version of the plugin here and in the first post.

I'd be v. grateful if someone could check this plugin for errors. I have not got around to even a half-check so far and very much a beta plugin...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Attachments

Login to view attachments
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 12th Jan 2013 17:46 Edited at: 13th Jan 2013 17:23
@ All,

I have released the beta build v0.4b. It has 17 new commands and you can delete any columns in a SQLITE database table (as long as it has been 'mapped'). You can insert columns in such a 'mapped' table anywhere in a 'mapped' table, you can alter column definitions and constraints. Also, I have even tried to create a command to export a 'mapped' table to a CSV file!

The new version is attached and also attached at the top of this thread.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Attachments

Login to view attachments
mr_d
DBPro Tool Maker
17
Years of Service
User Offline
Joined: 26th Mar 2007
Location: Somewhere In Australia
Posted: 18th Jan 2013 15:49
haven't used this plugin myself yet, but it is sure to be very useful in the future - have been waiting for a plugin like this for a while - keep up the good work Duffer!

Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 19th Jan 2013 00:00 Edited at: 19th Jan 2013 00:02
@ mr_d,

Thanks. I may even build up the courage sometime to add 'blob' functionality. But first either I need to test what I've done to date to destruction (I haven't done yet - it's still very much a probably-faulty beta) or people on the forum need to.

Blob functionality would allow people to add in and extract files like graphics and sound files from the database. However, the associated coding is rather daunting....

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 19th Jan 2013 00:37
I have been waiting for a good amount of time to test out this plugin. What part of "destruction" do you need tested? Deleting rows?

Also, for blobs, any commands that would take a memblock and store it would lessen the coding we would have to do on our end. Not sure what that would entail for you though...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 19th Jan 2013 10:39 Edited at: 19th Jan 2013 10:43
@ GIDustin,

Re testing, frankly, I'd start with the basic opening and closing/saving, work from there to the basic querying, then the creation of tables, then the pragma querying, the moving up and down columns and rows queries on tables and on records created by queries, then I'd move on to all the 'mapped' table commands (which are probably wired up like a christmas tree) finishing your testing with the deleting (or changing definition - datatype - and constraints) commands.

Again, wouldn't surprise me if many of these commands fall flat on their proverbials. But it would be good to focus effort where it is needed...

With the blobs, I'd have to do any number of global additional things to start tinkering with and including in DBPro commands internally in the plugin, but it may be possible to add commands allowing you add in a 'blob' column within a table and add to that column files, then extract these files from those column/rows. The files could then be anything, be it a saved memblock file, a graphics file, a sound file....

A get memblock from blob type command would be possible, ultimately, but only if I doubled the size of the plugin, and only if I managed to figure out those initial blob commands as well.... I'd rather not do that. I am pretty daunted re any blob commands but hell would they be useful...!

I should also really get around to an import CSV file as table type command....

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 4th Feb 2013 20:29
@Duffer,

Been trying to get this to work and having some problems. Not sure what I am doing wrong, but I cannot get any rows inserted. Test code attached.



Also, it seems your DSQLITE GET LAST DATABASE ERROR$() throws an error every time I run it.
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 4th Feb 2013 20:53
@ GIDustin,

First of all, much thanks for testing this plugin.

I put in the database error command really as an afterthought.

I'll look at the main part of your code first, aside from the database error command, is it the insert bit which is falling flat? Assume the other bits work?

I'm away from home for a few days but may be able to look at this by the weekend close.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 4th Feb 2013 20:55
@ GIDustin,

Before I get to testing it, take away the autoincrement and asc bits when setting of the table - not sure that autoincrement is necessary/works with a primary key integer in SQLite3 - just an initial thought.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 4th Feb 2013 21:18 Edited at: 4th Feb 2013 21:23
Recreated the example to remove the ID entirely. Still doesn't work;



My hope was to create a table with an auto-increment ID, so if it was id autoincrement, Name TEXT, and I insert a row with only the Name, that is uses the next index available for ID and assigns it automatically. This is a pretty basic function in SQL, but the syntax is just a little different here...

Edit: I got the above to work. I needed to add single quotes around my text value of Meh. Gonna try auto-increment next.
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 4th Feb 2013 21:32
@Duffer,

Not sure if you saw the edit in my last post. I needed single quote marks around my text. Ran some more tests and auto increment doesn't appear to work, and it looks like any command that returns a string is broken. DSQLITE GET RECORD CURRENT ROW STRING$(1,2) also crashes the program.

On another note, any chance of returning a row by record number and column name?

String$ = DSQLITE GET RECORD STRING$(DatabaseID, RecordNumber, "Column Name")
x = DSQLITE GET RECORD INTEGER(DatabaseID, RecordNumber, "Column Name")
etc...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 16th Feb 2013 13:41 Edited at: 16th Feb 2013 16:57
@ GIDustin,

Been away from home for a while. Will try and look at this now in next two weeks. Cant understand why plugin no returning strings properly. Will look in to it.

[edit]

reading your footnote under your edit on your post, looks like most but not all of the string returns work? could you just summarise what does not work?

I am taking that when you want to feed a string value in it has to be '[string value]' (within single quotes) - and that then needs to be within double quotes (")?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 16th Feb 2013 17:25 Edited at: 16th Feb 2013 17:38
@ GIDustin,

Some of what is going wrong may be in your code - remember this is the SQLITE variant.

See http://www.sqlite.org/faq.html#q1

So for an 'autoincrement' you do not use 'autoincrement' it is enough to mention an integer primary key...

ie. tes = dsqlite create table(1,"TestTable","PID INTEGER PRIMARY KEY, DISPLAY_NAME TEXT, SYSTEM_NAME TEXT, DESCRIPTION TEXT") ??

Have a look at this code (which works):-



a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 16th Feb 2013 18:20 Edited at: 16th Feb 2013 19:31
@ GIDustin & All,

Doh! I've found what I think was the cause of all the string returns crashing. Basically I was incorrectly naming the DBPro core .dll (should be DBProCore.dll) when pointing to it / GlobalPtr. School boy error based on early name for the same .dll.

Anyway, attached (and in first post) is version v0.5b.

Fingers crossed.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Attachments

Login to view attachments
WLGfx
16
Years of Service
User Offline
Joined: 1st Nov 2007
Location: NW United Kingdom
Posted: 17th Feb 2013 17:46
@Duffer - Excellent stuff. I doubt now I'll have any need to go back to the one I started. At the time it was only an experiment anyways.

I read in an earlier post about blob objects and had a thought. If someone is using blobs in an sql db then as far as I know, there's a 2Gb limit on the filesize. But, if blobs can be attached via memblocks then the user could compress memblocks using any number of plugins before adding it as a blob object. This would be handy for encrypting and hiding data for the user.

Again, keep up the good work...

Mental arithmetic? Me? (That's for computers) I can't subtract a fart from a plate of beans!
Warning! May contain Nuts!
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 17th Feb 2013 22:12
@ WLGfx,

Thanks. I'm going through the commands now and checking that they do all truly work. Currently working on v0.6. Truly annoying re the GlobalPtr thing. If i'd realised sooner I'd have got more done sooner. N'er mind.

Blobs would be a real challenge. I think the easiest thing would be to allow you to add a file in as a blob, then extract as a file...

First things first, will try and get as much of it working as possible with the original commands (73 commands, no less!) and then work a bit on BLOBs....

I'm working on it on and off (a few weeks gap each time) so bear with me...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 18th Feb 2013 16:01
@Duffer:

I tried your test SQL that you said works with primary key. It inserts the rows, but the PID is still 0 for each one... Unless you see something that I don't. Here is the code I used:


Also, starting with the new DLL, running any EXE that uses your DLL drags my PC to a halt. I will run some tests if I get a chance to narrow it down to something specific.
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 18th Feb 2013 21:17
@ GIDustin,

If you try the code below:-


You'll see that all those commands, at least, work - the DSQLITE GET TABLE COLUMN NAME$(1,"TestTable",2) command works, but only once, then it crashes. I am working on that command now for v0.6.

I haven't even started on the 'Record' commands.... they come next.

No idea why your PC shows to a crawl. Will look at speed stuff after I've cleared most of the commands... A slow process. Not at home much at the moment. Bear with me.

But you'll see from the commands that do work above that you can access and use data from the sqlite dbase....

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 22nd Feb 2013 17:49
I've had a go at improving the speed and the dsqlite get table column name command. See first post and attached.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Attachments

Login to view attachments
GIDustin
15
Years of Service
User Offline
Joined: 30th May 2008
Location:
Posted: 25th Feb 2013 23:05
@Duffer,

The string commands seem to be fixed. My test code runs fine except the auto-increment thing, but I haven't really looked into that much to see if I am even doing it right. I think the speed issue was due to the program running with uncapped FPS, so it drained my system. I replaced the "wait key" at the end with "nice wait key" and it seems to work much better now.

Do you still have things to work on with the DLL or is it ready for mass testing?
Phaelax
DBPro Master
20
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 25th Feb 2013 23:56
What?! No screenshot?! I won't download it then!

"You're all wrong. You're all idiots." ~Fluffy Rabbit
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 2nd Mar 2013 10:55
@ Phaelax,

Not sure what you want as a screenie?

@ GIDustin,

Can't seem to make any commands asking for the name of a particular column to work (more than once, then the plugin crashes on next string return command) - gotta be something bleedin obvious but still working on that.

Also, haven't yet checked on the mapped table commands. Almost dreading doing so...

Hence v0.6beta.

So not really ready for mass testing but if you have the time, please continue to test it to destruction.

Hey at least the updating and standard sql query stuff works, and the transactional stuff, and most of the basic stuff....

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Phaelax
DBPro Master
20
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 2nd Mar 2013 18:23
It was sarcasm

"You're all wrong. You're all idiots." ~Fluffy Rabbit
Duffer
21
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 3rd Mar 2013 21:30
Ahhh, sarcasm, totally lost on me...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...

Login to post a reply

Server time is: 2024-03-28 22:09:40
Your offset time is: 2024-03-28 22:09:40