Create Database + Table From Scratch (Programatically)

[kwanbis]

I'm making a very simple "portable" (and open source), application.

I have not programed in Delphi DB in the last 8 years, so i don't remember much.

The good thing is that now there is "the internets", but i can not find some answers.

I want my application to start, and if it does not finds a database, create it and create the tables.

So far, i have:

Code: Select all

  ZConnection.Disconnect;
  ZConnection.Protocol := 'sqlite-3';
  ZConnection.Database := ExtractFilePath(Application.ExeName) + '/database.SQLite3';
  if not FileExists(ZConnection.database) then
  begin
    ZConnection.Properties.Add('createnewdatabase=create database '''+ZConnection.Database+''' user ''sysdba'' password ''masterkey'' page_size 4096 default character set iso8859_2;');
    try
      ZConnection.Connect;
    except
      ShowMessage('no pude conectar (y no existia!)');
      application.Terminate;
    end;
  end else
  begin
    try
      ZConnection.connect;
    except
      ShowMessage('no pude conectar (y SI existia!)');
      application.Terminate;
    end;
  end;

But, how do i create a simple table, that has 1 text field, 1 data field, and one memo field, indexed by the 1st text field?

Thanks.

[Wild_Pointer]

kwanbis,

But, how do i create a simple table, that has 1 text field...

I would recommend using ZConnection.ExecuteDirect('CREATE TABLE FOO( ....'


Good luck!

[kwanbis]

Now, that worked. I have to get used to make it using SQL commands.

ZConnection.ExecuteDirect('CREATE TABLE NOTES (noteTitle TEXT PRIMARY KEY,noteDate DATE,noteNote TEXT)');

The problem i have now, is that the only way of displaying the TEXT fields, is under a DBmemo.

How can i display the TITLE field on a DBLitBox or a DBgrid?

[kwanbis]

If i create the TEXT field with noteTitle CHAR(255) i can see it on a dbgrid.

Now, i can not show anything, not even the date field, on the dblistbox.

Has anyone done that?

[Pitfiend]

get a look in this post http://zeos.firmos.at/viewtopic.php?t=2627 you also need to know that not all datatypes can be used in sqlite.

[kwanbis]

Thanks for your reply. But the post you link to, has no reference to my current problem:

If i create the TEXT field with noteTitle CHAR(255) i can see it on a dbgrid.

Now, i can not show anything, not even the date field, on the dblistbox.

Has anyone done that?

[Pitfiend]

are you aware of the dynamic typing of sqlite? that means you can store anything inside any field. also, there are some restrictions related to primary keys. you need to be aware that there are 4 generic datatypes only and a NULL value. more info here.