Questions regarding SQLite

[daveinmb]

Happy Holidays everyone!

I thought I would take a look at the plug-in SQLite that came with 6.0 and I am wondering a few things.

First I do a lot of programming with Access 2000 Developers edition so I am wondering:

1) What are the limits of SQLite? Can it only handle so much data?
2) Is it possible to compact a fragmented data base?
3) Please don't laugh... How do you create the initial .db database? I tried playing with an MS access .mdb file but it does not seem to work.

A lot of the stuff I do in MS Access is very complicated quaries and reports. Am I best to stick with MS Access for this, or .... is it worth the learning curve to switch over to SQLite?

Thanks in advance.

[Worm]

SQLite is great for a single user database. If you're going mult-user, I wouldn't use it.

This link is to the sample app IR put out with the initial plugin for AMS5, it should answer a lot of your questions.

http://www.indigorose.com/forums/showpost.php?p=68061&postcount=6

[daveinmb]

Thanks Worm!

Greatly appreciate it.

When you have a minute or two, could you let me know how to create the initial database file. Or... is this something that is done on the fly.

I tried opening one of the sample files with notepad (I was not sure if it was a csv file or ??. And it is all encoded so I am not sure where to start with building the actual database.

[Worm]

Take a look at the sample app. In the Projects actions, you'll see how the db is created.

[daveinmb]

So if I need to add fields to an existing table, or add more than one table to the database I would just add more lines here?

And... If there was already data in the fields, would it just add the new fields and / or tables? Or would it overwrite the existing data?

I guess I could just try it and see but it's much more fun picking someones brain. ;)

[Roboblue]

If you want to create, browse, and populate an Sqlite DB separate from AMS, go Here (http://sqlitebrowser.sourceforge.net/) and download the sqlite browser. Get version 1.01 as it is the version used in AMS and most compatable.

[Dermot]

And... If there was already data in the fields, would it just add the new fields and / or tables? Or would it overwrite the existing data?
You cannot add or delete fields from an existing table in SQLite. If you need to make changes to a table and keep the existing data, you will need to copy the data to a tempory table, delete the original table, create a new table with the chnages you want and then copy the data back from the temp table.

[daveinmb]

Thanks for all the help!

The browser will be a great tool to work with as well.

[Intrigued]

To add to Dermot's post:

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

[GypsyMoon35]

Hello Worm! I was wondering... I need to build a database for dates, times, people, (Places, etc..) etc.. Like a record. What would I need to start this and how would I even begin? The forum is very helpful, but its also confusing. I got the graphics and trial of APM 6. This database is for a group of friends that are Ghosthunters.

I also want to be able to click on a button, (New Record) and start a new hunting record!! Lol, I know its ghosthunting but what can I say!!!

Thanks,
Gypsymoon35

[azmanar]

Hi,

Which SQLite version is being deployed currently in AM6? Which one is in AM5?

The SQLite 3.O and earlier can ALTER TABLE by ADDing a COLUMN. But after the ADD COLUMN, it has to be followed by a VACUUM statement to optimize the TABLE.

The SQLite 3.1 onwards can ALTER TABLE by ADDing a COLUMN as well. The VACUUM is auto, only if the PRAGMA autovacuum flag is set to 1 before doing any TABLE creation. Otherwise you can still use VACUUM statement.

DELETE COLUMN from a TABLE is not supported by SQLite

For further reference, goto : http://sqlite.org/lang.html

[azmanar]

Hi,

Which SQLite version is being deployed currently in AM6? Which one is in AM5?



Hi,

Ok. I use SQLite.GetVersion(); to get SQLite version in AM6.

It is SQLite 2.8.6 .

Any plans to Upgrade?