* * *

Author Topic: Create table from FieldDef's (SQLDB)  (Read 381 times)

kapibara

  • Sr. Member
  • ****
  • Posts: 441
Create table from FieldDef's (SQLDB)
« on: May 04, 2017, 08:34:37 am »
Is there a way for SQLDB to create a database table from the TSQLQuery's FieldDefs ?

If I remember right, in Delphi you could call Dataset.CreateTable and a table with the designed structure will result from the FieldDef items?
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

GAN

  • Jr. Member
  • **
  • Posts: 65
Re: Create table from FieldDef's (SQLDB)
« Reply #1 on: May 04, 2017, 09:35:47 am »
In SQLite I use
Code: Pascal  [Select]
  1. CREATE TABLE new_table AS
  2.   SELECT expressions
  3.   FROM existing_tables
  4.   [WHERE conditions];

You could create and then delete and have the same result (I think).

Code: Pascal  [Select]
  1. CREATE TABLE table_2 AS
  2. SELECT * FROM table_1
  3. LIMIT 1;
  4.  
  5. DELETE FROM table_2;
Lazarus 1.6 FPC 3.0.0 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

Thaddy

  • Hero Member
  • *****
  • Posts: 3400
Re: Create table from FieldDef's (SQLDB)
« Reply #2 on: May 04, 2017, 10:04:57 am »
Since it descents from TCustomBufDataSet you can call CreateDataset after you have setup the fielddefs.

kapibara

  • Sr. Member
  • ****
  • Posts: 441
Re: Create table from FieldDef's (SQLDB)
« Reply #3 on: May 04, 2017, 12:47:37 pm »
@GAN Thanks for the reply. However, there is no table to select from yet. I want to create the table from scratch first time the application runs. At the moment I use a TSQLScript to create the application tables. But it needs to load some text files with the SQL commands in. So I thought why not replace that and do it in a more RAD way: Add table definitions to FieldDefs at design time and then just call CreateTable at runtime. But it seems that similar to CreateTable is not implemented in SQLDB.

@Thaddy CreateDataset doesn't create tables, does it? It creates the TField objects from available FieldDefs. I actually just wanted a more RAD way of creating tables than running a sqlscript. Maybe I should try implement CreateTables. But I don't see where NOT NULL can be specified in the FieldDefs.
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

Thaddy

  • Hero Member
  • *****
  • Posts: 3400
Re: Create table from FieldDef's (SQLDB)
« Reply #4 on: May 04, 2017, 01:23:04 pm »
Yes, it DOES create the table (actually it can only create one table at a time, so it is a bit misleadingly named) ... in memory... so you will have to persist it. It does not create the table on storage (either on disk or in an actual database).  But that can be solved by either saving to file or assigning to another dataset.

SkyKhan

  • Full Member
  • ***
  • Posts: 141
Re: Create table from FieldDef's (SQLDB)
« Reply #5 on: May 04, 2017, 02:42:00 pm »
Mapping of lazarus field datatype to SQL column datatype would be database specific. Some of databases, especially old ones might not support all types.
Still it is not hard building "create table" query if you know which database you will use. Btw, I think you can use faRequired attribute in TFieldDef.Attributes for "not null"
I will always ignore you! Yes, you too ( except if you are topic owner )

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus