[SOLVED]error populating SQLite Database

[hy]

Hi I creating an SQLite Database with the following code:

   

Code: [Select]

sqlite3conn.connected := false;
   SQLite3Conn.DatabaseName := '/media/work/testarea/ymap.db';
   qrySQLiteMain.SQL.Clear;
   qrySQLiteMain.SQL.add('Create table SD (id integer,iX integer,iY integer)');
   SQLite3conn.connected := True;
   qrySQLiteMain.ExecSQL;   //created

   qrySQLiteMain.SQL.Clear;
   qrySQLiteMain.SQL.add('insert into SD VALUES (1,8,9);');
   qrySQLiteMain.ExecSQL;

   qrySQLiteMain.SQL.Clear;
   qrySQLiteMain.SQL.add('insert into SD VALUES (2,18,29);');
   qrySQLiteMain.ExecSQL;

   qrySQLiteMain.SQL.Clear;
   qrySQLiteMain.SQL.add('insert into SD VALUES (3,18,19);');
   qrySQLiteMain.ExecSQL;

   SQLite3conn.connected := FALSE;
   The file is created with the size of 0 - so no data is stored at all... what am I doing wrong?
Thanks in advance

EDIT: When using

Code: [Select]

SQLite3conn.CreateDB;I get a runtime-error:

Code: [Select]

EDatabaseError: Operation is not supported by this type of database

[Mike.Cornflake]

Well, according to
http://wiki.lazarus.freepascal.org/SQLite

CreateDB is the correct way to go.

Where in your code do you insert the .CreateDB line?

Ug.  The following page confirms your exception, which tells me that TSQLiteConnection does not support CreateDB, which contradicts the wiki...

http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.createdb.html

Look, I'm going to believe the wiki here.  Something else must be causing that error.

UPDATE:  I've now reversed my position after a mere 5 minutes searching these forums.  Everything you're doing is apparently correct.  I've no idea :-(

[hy]

I inserted
SQLite3conn.CreateDB;
after SQLite3conn.connected:=true;

[Mike.Cornflake]

Yeah, see my update above.  5 minutes searching on these forums, and it looks like your code is correct.

I'll keep searching...

[hy]

Yeah, see my update above.  5 minutes searching on these forums, and it looks like your code is correct.

I'll keep searching...

I'm searching for 4 hours now... )-;

[Mike.Cornflake]

Aha!  I was looking in the wrong place.

Lazarus - Project - Close Project
Choose View Example Projects
Filter for SQLite, there's only 1 - don't worry that it's an encryption demo, we'll ignore that side
There's a button on there "Create DB". In the code, there's two lines different to yours.
After .Connected := True stick

Code: [Select]

SQLTransaction1.StartTransaction;
after all your .ExecSQL's and before your .Connected := False; stick

Code: [Select]

SQLTransaction1.Commit;
(Or whatever your transaction is named).

Can't believe I fell for this two days in a row...

Basically, everything that happens in SQLDB code happens inside a Transaction (so really, starting the transaction is optional, but good practise).  If you don't Commit the transaction, nothing gets saved...   The permanent transaction only came in a few releases ago, which is why all the historical examples on the forum don't mention it...

[hy]

Thanks i only inserted
Starttransaction
and
endtransaction

i forgot about the commit... (It's some 10 years that I did serious DB-programming)
Thanks again

[Mike.Cornflake]

And all this means I've got a wiki to go update...

Can any SQLite experts confirm if the tip on the wiki is in fact correct?
http://wiki.lazarus.freepascal.org/SQLite#Tips

Use the TSQLite3Connection.CreateDB procedure to create a new SQLite database.

Is there *some* way that CreateDB would have worked?

[hy]

Is there *some* way that CreateDB would have worked?

I think not, because when using createDB it crashes instantly.

[Mike.Cornflake]

   Yeah, but maybe we missed the magic

Code: [Select]

.DoThisBeforeCallingCreateDBToMakeCreateDBMagicallyWork;
I just want some verification before I barge in and edit some text on the wiki on a subject I really don't know anything about :-)

[hy]

Sorry, I don't get it.
What should I do?
DoThisBeforeCallingCreateDBToMakeCreateDBMagicallyWork
doesn't actually exist in the internet (aside from some biblical sites) - nor does it in the source...

[Mike.Cornflake]

Sorry @Hy, you don't need to do anything - we've got your code working. 

Sorry for spamming this topic - your issue has made me think there is something wrong in the wiki, and I'd like one of the SQLite experts to confirm before I fix it.  I should have started a new topic.

To be clear, I'm not asking you for any clarification, but I agree, using your topic to try and grab the attention of others was confusing.