SQLLite3 CreateDB Error on the wiki?
lazarus / 16-Databases

----------------------------------------------------------------------
Mike.Cornflake · 2014-10-07 22:00

Attention all SQLite experts.

Have someone please have a peek at: http://forum.lazarus.freepascal.or
g/index.php/topic,26064.msg159449.html
(err, but please ignore the last few posts - I embarrassed myself)

Over there it was demonstrated that CreateDB fails to create a SQLite
database, and that all that was needed was to Start a transaction,
create a table, then commit the transaction.

However, over on the wiki
http://wiki.lazarus.freepascal.org/SQLite#Tips

it states
QuoteUse the TSQLite3Connection.CreateDB procedure to create a new
SQLite database.
But, yeah.  Apparently CreateDB just triggers an exception.

The wiki needs to be updated, either with steps on how CreateDB should
be used, or detailing the actual steps.

I'm happy to do the editing, I just don't know if CreateDB should be
the one clearly documented, or the other way...

----------------------------------------------------------------------
fpcoder · 2014-10-08 00:28

Not exactly an expert, but I can't find that the CreateDB procedure is
implemented in the TSQLite3Connection class.

In TSQLite3Connection's ancestor class, TSQLConnection, CreateDB is
declared as virtual and raises an error if called:
Code: [Select]procedure TSQLConnection.CreateDB;
begin
  DatabaseError(SNotSupported);
end;
So the TSQLite3Connection doesn't seem to support creating a database
by using CreateDB.

The reason for this could perhaps be that it is not needed. Because
SQLite autocreates the database file on Open anyway. But perhaps
someone else can shine more light on why.

I create SQLite databases by filling out the DatabaseName and call
Connection.Open. If the file exists, then it is used. If it doesn't
exist, it is simply created by SQLite.

This works also at designtime, by toggling Connected to true the
database file is created on disk.

Even more cool is to just fill out the Conn.DatabaseName and go
directly to creating tables with Conn.ExecuteDirect without even
calling Conn.Open. The db file will be autocreated by SQLite:

Code: [Select]Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS
countries(' +
                   ' country_id INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL, ' +
                   ' country_name VARCHAR(30) ' +
                   ')');

Conn.Transaction.Commit;
{--Connection is now active--}

There is a working demo in this thread:
http://forum.lazarus.freepascal.org/index.php/topic,25479.msg154953.ht
ml#msg154953

----------------------------------------------------------------------
Mike.Cornflake · 2014-10-08 06:58

Quote from: kapibara on October 08, 2014, 02:28:13 amNot exactly an
expert, but I can't find that the CreateDB procedure is implemented in
the TSQLite3Connection class.

In TSQLite3Connection's ancestor class, TSQLConnection, CreateDB is
declared as virtual and raises an error if called:
Code: [Select]procedure TSQLConnection.CreateDB;
begin
  DatabaseError(SNotSupported);
end;
So the TSQLite3Connection doesn't seem to support creating a database
by using CreateDB.

Excellent work.   This confirms the Wiki is definitely wrong.  I'll
get it updated tonight.  Many thanks.

Quote from: kapibara on October 08, 2014, 02:28:13 amThere is a
working demo in this thread:
http://forum.lazarus.freepascal.org/index.php/topic,25479.msg154953.ht
ml#msg154953

Gave me a fright :-)   First line of code I saw in there was
.CreateDB!   Stepped into it, and realised it was just a coincidence
of naming :-)  Many thanks also for the link to that post.

----------------------------------------------------------------------
Mike.Cornflake · 2014-10-09 18:10

Deleted Tip from the wiki and added
http://wiki.lazarus.freepascal.org/SQLite#Creating_a_Database