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 / New n improved SQLITE plugin

Author
Message
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 29th Jun 2013 15:42 Edited at: 29th Mar 2014 13:21
Hi All,

- Please can you test this plugin to destruction and provide feedback -

I'd had a go at creating a plugin along these lines before. See these links. :- http://forum.thegamecreators.com/?m=forum_view&t=202527&b=18 and http://forum.thegamecreators.com/?m=forum_view&t=202548&b=8

Those links also contain lots of useful information about SQLITE3 but I set out below some of those here fore your ease of reference:-

http://www.w3schools.com/sql/sql_syntax.asp (this site is ruddy fantastic and you can learn most of what you need to know about SQL in general within say 30 mins)
http://www.sqlite.org/about.html (this is the SQLITE site)
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

This is a new version (of the above rougher older version) of a SQLITE3 plugin with a simplified command set to start with. I've now added to that original command set with a huge number of new commands.

SQLITE3 is damned fast. With the SQL language (which is easy to use and understand btw) it is very flexible.

In short, I cannot understand why anyone would NOT use SQLITE (which has no dependencies) to store their game data...

The plugin as it stands still very much needs testing and honing. And I really would like people to feedback commands they would like to see.

[edit]
Current Version 0.9i (29/03/2014)

5 more functions to increase functionality (126 functions in total now).

Easier functions to change string, integer or float cell within a table.

Function to show number of rows affected by an updating SQL statement.



With functions, Rows start at 1 in commands but columns start at 0 - except with SQLITE Array commands where columns start at 1.

A column count of 7 would indicate there were columns 0,1,2,3,4,5,6.

Command Set


Please I would be really grateful if anyone who downloads would consider testing this plugin to destruction.

Aside from any bugs found, would be interested in any additional commands / functions people would be interested in seeing?

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

Attachments

Login to view attachments
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 1st Jul 2013 03:20
I'm interested in this, I'm currently testing things out. I plan to use this in my game. If this works properly I'm going to use it for just about everything, from entity properties to the language definitions. A feature I definitely would be interested in seeing is commands to handle sql "blobs".. that's what they're called, right? But yeah, this seems especially interesting and I don't think many people realize how useful this could be.

Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 1st Jul 2013 10:03
@ thenerd,

I could look at blobs - and importing/exporting basic csvs?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Ortu
DBPro Master
15
Years of Service
User Offline
Joined: 21st Nov 2007
Location: Austin, TX
Posted: 2nd Jul 2013 22:01
im also looking at using something like this pretty heavily, ill give it a go sometime this week!

Shazam!
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 17th Jul 2013 05:54 Edited at: 17th Jul 2013 06:17
So, I'm having trouble with this. For some reason, I can't even get the simplest SQL Queries to work. For example, I have a system for retrieving a user's country based on their ip address. The system calculates a value based on the IP, and then looks that up in a database of known IP address ranges for countries using a query, returning the name.

The query script looks like this, where <value> is an integer calculated based on the IP address.





In my SQlite editor, the result of the Query looks like this, it's just a 1x1 table with the country name. This works perfectly fine from within any SQL editor. However, I can't get this to work from within Dbpro using the plugin. Here's the basic code not including loading the database, it's as simple as things could get:



But this doesn't work for me. Value$ comes out as an empty string, although the table should have a value in it in Row 1, Column 1. I have a feeling I'm missing something extremely obvious...

[Edit] Upon further testing, I found out something that does work, however. If I add another throwaway column to the start of the sql statement, the code works. The new code is the same as above, however I've added another column... the new SQL Query starts with SELECT i, name.

In my sql database, i is a blank column with a value of 1 for every row. Totally pointless, but when I do a query with two or more columns, this works:

value$=DSQLITE GET RECORD CURRENT ROW STRING$(1,1)

Value$ now returns a value, for example AUSTRALIA, but notice even though I have the column at 1, it skips over the i value, which is the true first column. The real table looks like this:



value$=DSQLITE GET RECORD CURRENT ROW STRING$(1,2) returns a blank value.
value$=DSQLITE GET RECORD CURRENT ROW STRING$(1,1) returns AUSTRALIA.
value$=DSQLITE GET RECORD CURRENT ROW STRING$(1,0) also returns AUSTRALIA.
^ (Although the third one shouldn't work, according to the help files columns start at 1)
There's no way to get the value of anything from the first column.

In fact, as far as I've tested, it always skips over the first column for some reason. The only way I've been able to get a Query to work properly without crashing or returning a blank value is by adding another column to the start of the lookup, that the plugin appears to skip over and return the correct value for the second column still using a column id of 1.

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 17th Jul 2013 09:39 Edited at: 17th Jul 2013 09:45
@ thenerd,

I'll check this properly tonight if I can - but one immediate observation - are you using the new and improved plugin (see first post)? I only ask as your commands all seem to start with DSQLITE whereas the new plugin starts with SQLITE.... (again, see first post).

I am aware that some versions of the older plugin didn't work, period.

Just try that first ie. uninstall the older plugin and just try this new plugin and it's commands. If that doesn't work still, let me know and I'll get to it tonight.

[edit]

Also, not too sure about the syntax of both your top lines in code snippets. Try without those brackets around the where/and logic.

[edit]

see http://www.w3schools.com/sql/sql_and_or.asp

also, I don't know whether this helps from the SQLITE website:-

http://www.sqlite.org/faq.html#q24

If still no good, as I say, let me know, and I'll check it out tonight if I can.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 17th Jul 2013 19:49 Edited at: 17th Jul 2013 19:51
Quote: "I'll check this properly tonight if I can - but one immediate observation - are you using the new and improved plugin (see first post)? I only ask as your commands all seem to start with DSQLITE whereas the new plugin starts with SQLITE.... (again, see first post).
"


Sorry, I forgot about that when posting - I switched back to the old plugin to try and see if somehow that one worked. I just tested it with the current plugin. Unfortunately, it has the same results: I can't get the value of the first column.

Quote: "Also, not too sure about the syntax of both your top lines in code snippets. Try without those brackets around the where/and logic."


It works with and without brackets, just tried that. There shouldn't be a problem with quotes either, because I'm only comparing integers. The dbpro code has quotes for the id, but once the SQL query string is generated it looks something like this:

SELECT name FROM OpenFPS_LocationDB WHERE (start_value <= 1234512345) AND (end_value >= 1234512345)

I still think the problem can be narrowed down to not being able to retrieve the first row. I'm pretty sure it isn't the SQL or the database, because all this works fine from within an SQL editor.

Thanks for the help so far

Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 17th Jul 2013 21:09 Edited at: 17th Jul 2013 21:11
@ thenerd,

No worries. Have too much other stuff to cover tonight but will work on this by weekend.

Re-reading your first post at a more leisurely pace I note that it is basically the DSQLITE GET RECORD CURRENT ROW STRING$ command that is not working - it appears out of order re the correct row?

I'll check it but I think that, if you are using SQLITE RECORD CURRENT ROW STRING$? that would mean say database 1, row x?

If that's right then database 1, column 1 should return "Australia" and does.

If you try for a column 0 it defaults to Australia (my failsafe) as you can't get lower than column 1 in the current row - so again "Australia"...

If you try for database 1, column 2, there is no column 2 so you get nowt?

Before I got further, does that work / make sense now?

[edit] ignore me, I'd thought the 'i' column was a system one and not a proper column in the record/table - but it is, isn't it?

I'll go back to the drawing board - it may be as simple as shunting on one in the code.....

bear with me...

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

I doubt it will be such a simple fix, but I've recoded that possible error (column miscount) and all related records and table commands.

Here is the amended plugin.

Let me know how the revised version (also added to first post) works.

Thanks btw for testing out my plugin - really very much appreciated.

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

Attachments

Login to view attachments
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 18th Jul 2013 02:19 Edited at: 18th Jul 2013 02:24
Quote: "If that's right then database 1, column 1 should return "Australia" and does.

If you try for a column 0 it defaults to Australia (my failsafe) as you can't get lower than column 1 in the current row - so again "Australia"...

If you try for database 1, column 2, there is no column 2 so you get nowt?

Before I got further, does that work / make sense now?

[edit] ignore me, I'd thought the 'i' column was a system one and not a proper column in the record/table - but it is, isn't it?"


Yes, the 'i' column is one I've created. So, using SELECT i, name... should generate a two column table: column 1 should return 'i' and column 2 should return 'Australia' but instead the behavior is what you've described.

I'll test your new plugin right now.

[EDIT] yeah, doesn't seem to be making any different. I think before either of us try to debug this more, we need to recreate the problem somewhere else to ensure it's not my code. It seems pretty easy to notice, just query a table with multiple columns and try to access the first column.

Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 18th Jul 2013 09:41 Edited at: 18th Jul 2013 09:42
@ thenerd,

OK, will do, I jumped ahead of myself there anyway with the change to the code of the plugin.

Amazed though as clearly the plugin should always be referencing one column before...

Will test it all with a stooge database (which I'll copy with code to you) for the weekend. Did you fully install my latest version of the plugin (I know, stupid question) before re-testing>?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 21st Jul 2013 01:50
Quote: "Will test it all with a stooge database (which I'll copy with code to you) for the weekend. Did you fully install my latest version of the plugin (I know, stupid question) before re-testing>?"


Yeah, unless something is somehow conflicting I don't think that fixed it.

Have you tested the bug?

Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 21st Jul 2013 10:04 Edited at: 21st Jul 2013 11:38
@ thenerd,

OK - see attached - it has a basic test project.

The first column is still not readable. I am working on it at the mo.

[edit]

So I've changed it (see attached) so that first column starts with 0 but first row starts with 1. Let's see if that works...

(the attached includes the example project)

[edit]

Eureka! It works. This is so silly. I can't understand why the original code didn't work. Anyways, rows start at 1, columns start at 0, but a column count of say 7 would mean there were columns 0,1,2,3,4,5,6, if you see what I mean.

[edit]

@ thenerd,

Let me know if your example now works? p.s. keep testing the plugin though...

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

Attachments

Login to view attachments
thenerd
13
Years of Service
User Offline
Joined: 9th Mar 2009
Location: Boston, USA
Posted: 21st Jul 2013 16:02 Edited at: 21st Jul 2013 16:07
Quote: "Let me know if your example now works? p.s. keep testing the plugin though...
"


I'll try it out now! I'm already using this in my game to detect location based on IP, I'm planning on using a database to store language-specific UI, weapon properties, and anything else that would be cleaner in a database than in a separate file.

Now, back to what we were talking about before this bug. My next feature request is still BLOBS for images. You also said you could add CSV importing / exporting, but in my opinion that's not a high priority feature for a plugin like this because there are already countless free SQL editors that can do this.

I use sqliteadmin, found at http://sqliteadmin.orbmu2k.de/

[EDIT] Yeah, it all works great, I can confirm the bug is gone. Thanks for your time working on this plugin, it's a real help!

Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 23rd Jul 2013 21:35
@ thenerd,

Good to hear. If I squeeze in some time at the weekend I'll start thinking about BLOBS, so to speak.

It may be possible to attach a file as a BLOB (so image, sound, whatever) but not a memblock or an image or other resource directly (because I am simply not that good at coding...).

I will also sort the import/export csv thing - more for my own use than any other reason.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 25th Jul 2013 19:41
@ All,

Some slight tweaks and one more new command. See first post as well.

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

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 17th Nov 2013 13:40 Edited at: 17th Nov 2013 13:50
@ All,

I've justed added @ 16 new commands and functions to this SQLITE3 plugin.

I've tried to really open up functionality and I've poured in all my knowledge of the sqlite_master table, pragmas and SQL statements in to the help files.

More to come. Anyway, see the first post in this thread for more.

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

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 24th Nov 2013 14:06
@ All,

I've added yet a further 27 commands over and above those in v0.6.



See first post on this thread. Basically, I've allowed you to use functions to do things with SQLITE3 that you cannot normally do (using writable schema pragma and diligent interrogation of the sqlite_master table etc).

Really grateful if people could test all of this to destruction... and provide some feedback.

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

Attachments

Login to view attachments
Ortu
DBPro Master
15
Years of Service
User Offline
Joined: 21st Nov 2007
Location: Austin, TX
Posted: 25th Nov 2013 20:49
Quote: "Really grateful if people could test all of this to destruction... and provide some feedback."


sorry man, i keep meaning to test this but just havent gotten around to it. its on my list though! looks good and comprehensive

Kezzla
14
Years of Service
User Offline
Joined: 21st Aug 2008
Location: Where beer does flow and men chunder
Posted: 28th Nov 2013 05:06
hmmm, I am afraid that my antivir antivirus false-positives it so aggressively that I cannot download or even install the plugin.

Not really sure of a way around it, every time i switch it back on it cracks it instantly.

Burn retina, burn!
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 28th Nov 2013 08:35 Edited at: 28th Nov 2013 08:36
@ Kezzla,

Strange. I run AVG and no probs with that. It's a rar file and maybe it does not like the look of that? Anyway, as a partial work around, here is the ini file (of course, with no help files now)...

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

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 28th Nov 2013 08:37 Edited at: 28th Nov 2013 08:39
@ Kezzla,

And here is the .dll

... I can email you the help files if you like as well...

It would just be so good if someone could provide testing and feedback. I am still looking at adding export and import (.csv) commands and yet more commands to produce field strings from recordsets etc. Also better and more search/find commands within recordsets...

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

Attachments

Login to view attachments
Kezzla
14
Years of Service
User Offline
Joined: 21st Aug 2008
Location: Where beer does flow and men chunder
Posted: 28th Nov 2013 12:01
I have it installed now, yet the dll is blocked at every turn, even when avira antivir is disabled windows will still not permit it to run.

I am running windows 8 with Avira Antivir.

anyone else had similar issues?

Burn retina, burn!
gwheycs62egydws
13
Years of Service
User Offline
Joined: 17th Aug 2009
Location: The World
Posted: 3rd Dec 2013 20:15
hi

I grabbed the latest dll and ini
and I have downloaded

sqliteadmin

I have that running

now how to link the two together

and a demo program is ware I need more info at

to move side ways - is to move forward
Since a Strait line gets thin fast
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 3rd Dec 2013 20:19 Edited at: 3rd Dec 2013 20:20
@ Resourceful,

[edit] apologies for being slack and providing no demo dba code. To be honest all my effort has gone in to the dll, ini and the help commands - and there are so many functions now it really needs people to test it to destruction, not just me, to iron out all the inevitable bugs....

If you have no particular anti-virul problems (I don't) then download the .rar from the first post on this thread.

That contains all the help files etc.

If you click f1 in DBPro for help on any of the commands you can see a fairly good description of the function/command and its use.

By way of example, check out the help on the pragma commands - I've included a wealth of background sqlite info...

Let me know how you get on.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
gwheycs62egydws
13
Years of Service
User Offline
Joined: 17th Aug 2009
Location: The World
Posted: 3rd Dec 2013 20:42
@Duffer

so far the dll has not been locked out by "avg 2014"

I understand about getting the dll up and running

at this point it a confusing trip

example files are ware I'm stuck

I know the command have there purpose but
not knowing what links to what dose not help if you have never see it before

to move side ways - is to move forward
Since a Strait line gets thin fast
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 3rd Dec 2013 21:20
@ Resourceful,

SQLITE OPEN DATABASE to start interacting with a database or create one.

SQLITE OPEN DATABASE to close a database and save it.

Most commands/functions fall imbetween those two.

Functions are divided between those which open a sql query on a database using a sql statement, those then that move up and down the rows in the resultant recordset, and along the columns, and those that retrieve strings, integers or floats from those same columns. Then also a function to end a sql query.

There are then the updating sql function(s) which effect a table/database all in one command.

There are then some functions which effectively do all of it for you, open a sql query, find something, end the sql query, and return the relevant string, integer or float...

Hope that starts to make some sense...?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 3rd Dec 2013 21:27
core system commands:-


other system commands:-


opening sql query alternative commands:-


moving about and getting stuff from resultant recordset:-


then finishing a sql query (and removing the resultant record set:-


updating sql statement function:-


The rest should be fairly obvious when you have used the above commands - as you go through and use any have an f1 look at the help on the commands.

As I say, you also need to look at those links at the top about how to use SQL.

I will get round to demo projects, promise.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
gwheycs62egydws
13
Years of Service
User Offline
Joined: 17th Aug 2009
Location: The World
Posted: 3rd Dec 2013 21:49
@Duffer

yes that help

this go's a few steps further than DarkData did ;o)

at least with this what is posted on the computer can be used in a game
and shown on web page at the same time

to move side ways - is to move forward
Since a Strait line gets thin fast
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 3rd Dec 2013 22:01
Agreed... in theory...

Proceed with caution, most of the core commands and straightforward functions/commands should now work and are compatible with SQLITE3.

The ones that muck around with the definition/type/attributes of columns are highly experimental...

More commands to come including import from and export to .csv files which you can access from Excel etc.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 8th Dec 2013 10:57 Edited at: 8th Dec 2013 11:03
@ All,

See original post on this thread. 7 more (*thoroughly untested) commands giving more search functionality within recordsets, more index functions, printstring functions so you can more easily display on more than one line your recordset results or tables and.... (hopefully)

also export to csv and import from csv commands which will also allow retention of all table column definition, type, constraints, primary key, foreign key, indices, triggers, views, in theory.

Anyway, as ever, would be grateful if someone could test all this.

Let me know if anyone also wants the .dll and the .ini separately as well.

When I build up the spiritual will for it, I may now attempt the blob, so to speak...

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

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 14th Dec 2013 12:21
@ All,

Just created version 0.8 (14/12/2013)

I've tweaked and improved the import/export csv/table commands and added a command to provide a fieldstring of all the tables in a database.

See original post on this thread and attached.

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

Attachments

Login to view attachments
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 30th Jan 2014 17:26
I dunno how your query call is supposed to work -_-



I am using SQLite library version 3.8.2

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 30th Jan 2014 19:09
@ Lucas W,

Basically, if you're looking to create a query recordset thengo with the SQLITE BEGIN (and later after moving through resultant recordset) and FINISH QUERY functions.

However, if you are going to create or alter or delete (and if you're not using the custom commands I have created to do that elsewhere) - ie. if you are going to use your new command of SQL to accomplish an update, insertion, deletion, change, alteration (rather than a query with resultant recordset) then try using the SQLITE UPDATING SQL STATEMENT function instead - works for me?

Again, really appreciate you feeding back bugs and problems - the plugin has the basic query and update cover functions/commands and then a whole raft of other functions to shortcut and make things - hopefully - easier - but I haven't had time to properly test it and it is no doubt full of bugs - but the basic query and update commands work and with those alone you have your working plugin for SQLITE.

Please report in those bugs though....

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 31st Jan 2014 02:50
Sorry for a long delay, I was not at home.
Thank you, this command SQLITE Updating SQL Statement() is working the way I am familiar with! From now I will properly read the command list you have supplemented with the plugin

This code now successfully creates two tables.

OK, I will let you know about any more problems or (if any)bugs I come across.

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 31st Jan 2014 09:02 Edited at: 31st Jan 2014 09:04
@ Lukas W,

Great! At least all the essential/basic commands work.

You WILL come across further bugs, trust me!

That function you used to get the creation SQL was one of the last I created - the creation sql string is so damned useful - glad it worked...

When you start looking at the various pragma commands you get to see just how powerful SQLITE can be...

I have more or less tested out the functions which get strings etc from row/columns in either the original table or from SQL query recordsets.

The functions which are no doubt bug-ridden and largely untested are the ones where I'm trying to use the sqlite_master file to allow you to do things you shouldn't be able to do in SQLITE like change the types/definitions/constraints/names in/of the columns etc. Also the dodgy import/export commands...

There are rather a lot of functions/commands - and I will no doubt add more - because I am lazy and like to sidestep creating the SQL statement strings and feeding them in - rather I like the function to do most of that for you....

Reading back through your posts on this thread and the other one, not sure you need to put any dlls in the folder in which you are running your database.... should just work like any other user-plugin in DBPro?

Anyways, do let me know if any particular function or series of functions are playing up.

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 2nd Feb 2014 00:42 Edited at: 2nd Feb 2014 00:48
Yea, you are right! Don't know why I was thinking that the sqlite dll was needed in project folder, maybe because I'm so used to Newton ;D

So far I don't appear to have many problems, just a little to get used to compared to php/mysql - for example I am used to write something like this (not sure since I don't have xampp installed at the moment):

And in your plugin it would be along the lines of:


Still haven't looked at all the commands yet, so maybe there is a more convenient way

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 2nd Feb 2014 10:54 Edited at: 3rd Feb 2014 17:03
@ Lukas W,

That's about the size of it. No dependencies make SQLITE King for this sort of programming, in my humble estimation.

I haven't come across much in raw SQL that isn't the same in SQLITE (in the SQL statement or query), at a basic level:- [href] http://www.w3schools.com/sql/sql_syntax.asp[/href]

Just remember that there are some things you can ordinarily do with "ALTER" in SQL which you can't do in SQLITE. See the links in the first posting on this thread.

Remember always that with my plugin functions, the rows (on the SQL recordset or the table - see below) start at 1 but columns start at 0.

In your first example of code above you are doing a SQL query. You can do that as well in my plugin and then run through and pull data from the resultant recordset. I also have commands that allow you to look at what is in what x,y cell in a table (but in the background that is just really another SQL query and pulling data from resultant recordset).

In other words, I have one set of commands that appear to be pulling data etc from a basic table, then I have another set of commands (within the SQLITE BEGIN SQL QUERY ..... SQLITE FINISH SQL QUERY) which allow you to set up a SQL query on a table producing a recordset (with its own rows and columns) and interrogate that - the resultant SQL recordset not the table.

If you tap F1 you'll get my (rather simplistic text) help on each command and you'll see which ones would close any earlier SQL query to carry out their function.

Hoping that all makes sense.

[edit]
TIP - If you are doing a lot of alter, insert and/or update sql statements together at once, put them in the one begin/commit transaction (see help on transaction commands) as that will greatly speed up things. As you add SQL statements to the statement you should really add at the end of each string a semi-colon - but don't worry if you forget as the plugin will add missing semi-colons in for you....

[further edit]
Please let me know which functions don't work at all, which functions don't work as they should and which functions or commands you would then like to also see to make your life easier...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 5th Feb 2014 18:01 Edited at: 5th Feb 2014 18:51
I don't know if it is a bug or a limit in sqlite but I can not have a column with name "Group".



In this code, I create a table "mytable" with column "Name" and "Group". I make a new row with cell "Name=my name". And then I update that row with "Group=my group". But it does not work, if I change name of column to for example "Group2" then it works

Edit:
Quote: " I also have commands that allow you to look at what is in what x,y cell in a table (but in the background that is just really another SQL query and pulling data from resultant recordset)."
I assume you are talking about command: SQLITE Table Cell [String$/Integer/...]( db, table, column, row ). I run this 17 times in a row and it cause a crash. Don't have more time today but it should be faster (and not crash probably) using record set commands. I will try that tomorrow.

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 5th Feb 2014 19:38
@ Lukas W,

Curious. Not sure whether it is the square brackets that are doing it or the use of the word 'group'.

When in MS Access I find it quibbles at 'group' as a column name or 'type' for some reason. So I usually put say columns as "Weapon_ID", "Weapon_Name", "Weapon_Type", "Weapon_Description"...

Have a look at this link:-

http://www.sqlite.org/lang_createtable.html

You can simplify your types and constraints in SQLITE.... check it out.

Let me know whether 'type' causes problems too...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 7th Feb 2014 18:20 Edited at: 7th Feb 2014 18:24


Using that is definitely faster than using what I posted in my previous post.

For some weird reason it would only crash if a row had cell-value "persec" and if that was stored in an array.. if it was stored in normal string variable it didn't crash and after I manually changed value for that row to something else it was able to store in array and not crash >.> I cannot replicate that error because after I made a new project to try it never crashed again.
I did not try "Type" cell-name yet, but out of habit I never use Type since that is a keyword in dbpro ;D

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 7th Feb 2014 18:36 Edited at: 7th Feb 2014 18:54
Interesting...

Think I'm right in saying that SQLITE throws a wabbler if you try and move backwards at all on a row without moving a row, if you see what I mean. You can't pull data right to left, only left to right...

[edit]
Is it working ok with your current game project though?

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 7th Feb 2014 23:26 Edited at: 7th Feb 2014 23:32
Here is to show the speed difference that I was talking about between recordset vs command (4ms vs 105ms @ 20 rows) and also transaction vs command (106ms vs 906ms @ 10 rows each):


Quote: " if you try and move backwards at all on a row without moving a row, if you see what I mean. You can't pull data right to left, only left to right..."

The command "Sqlite Record Row Count( db )" should reset to top (or left) of the list - I tested it. If that is not what you meant then I don't know what you mean lol.

Quote: "Is it working ok with your current game project though?"

Yea it is working fine. I still have quite a lot of code to rewrite in order to fully implement the sqlite into my game/editor but slowly making progress. During this implementation I also decided to rename a few array and variables for better organisation in code as well as some other stuff that is making me spend more time than I want.. and ofc then there is my job also >.>


By the way, I started to write some .htm files (really just copy+paste from newton since I like that layout) to replace the .txt help files available in your download. It's slowly building up as I test various commands with different examples. If that would be interesting I can upload these files here when finished. It's really just for my own benefit since I have easy time to forget new commands, I still use dbp core help files constantly: like what was the command to convert string into a value

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 7th Feb 2014 23:54 Edited at: 7th Feb 2014 23:55
@ Lukas W,

Sincere thanks for continuing to bomb test my plugin...

With the recordset functions, as you move to say a row and work your way along it from column 0 to column 1 etc, you can't (I am told, havent tried it) go back to column 0 (ie. back from right to left) - but you're right that function and others too will either shift recordset row back to the first one or to another row and then that's fine.... (probably still don't make sense, do I!)

Re the speed tests, very interested to see that and that confirms everything I understood would be the case - the recordset functions will be quicker than me lazy table inquisition style functions (for me, as I'm lazy) and the SQL within transactions will be massively faster - and glad to see it's working for you too!

Any .htms with (which is great) or without examples gratefully received! The .txt help files are partly auto generated by the software I wrote to turn the original code into the plugin and, well, they're not pretty...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 8th Feb 2014 11:57
Just a quick note:

Sqlite Close Database( db [, vacuum] ) command will tell me "parameter mismatch" error if I put vacuum tag. Dunno if that parameter was removed from your dll or not, but in your help files there is a comment it should be possible.

IS that a game, a program, or 3 year old drawing.
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 8th Feb 2014 14:28 Edited at: 8th Feb 2014 14:50
@ Lucas W,

Shame on me - bug went to something fairly fundamental about functions/commands with optional parameters.... now (hopefully) fixed.... and a number of other functions with optional parameters as well .... see above and see attached...

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

Attachments

Login to view attachments
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 8th Feb 2014 16:41
Thanks , that update fixed the bug for me.

Quote: "With the recordset functions, as you move to say a row and work your way along it from column 0 to column 1 etc, you can't (I am told, havent tried it) go back to column 0 (ie. back from right to left) "

Yep you are right, I understand what you mean now.


I finished "core commands" .htm file (and keyword .ini file) and attach a screencap from the ide to preview. Since it is 1080p resolution you need to press "view" in bottom corner

IS that a game, a program, or 3 year old drawing.

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 8th Feb 2014 16:50
@ Lukas W,

Great! Really glad I managed to fix that.

Wow - you're htm files look rather massively better than the .txt files - looking forward to them...

a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
Lukas W
19
Years of Service
User Offline
Joined: 5th Sep 2003
Location: Sweden
Posted: 8th Feb 2014 16:50 Edited at: 8th Feb 2014 16:53
Here is another from different ide without so much text, and showcase syntax highlight (hard coded >.< ) with a simple example.

Anyway I just copy+paste template from the Newton help files, I dunno maybe Walaber wrote that

IS that a game, a program, or 3 year old drawing.

Attachments

Login to view attachments
Duffer
19
Years of Service
User Offline
Joined: 9th Feb 2003
Location: chair
Posted: 8th Feb 2014 16:52
Excellent!

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

Login to post a reply

Server time is: 2022-12-06 11:44:56
Your offset time is: 2022-12-06 11:44:56