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/sho...61&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 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?