Lazarus

Programming => Databases => Topic started by: guest48180 on October 30, 2014, 07:18:18 pm

Title: [SOLVED] Creating SQLite3 tables during runtime
Post by: guest48180 on October 30, 2014, 07:18:18 pm
Other than the wiki tutorial on databases, this is my first one flying solo. All the db components are connected and set at designtime. Now while I have figured out a few things on my own, I'm having trouble creating a table during runtime. The error I'm getting is:

Code: [Select]
Project project1 raised exception class 'EDatabaseError' with message:
near "?": syntax error

with the following code:

Code: [Select]
procedure TMainForm.Button1Click(Sender: TObject);
begin
  SQL1.Close;
  SQL1.SQL.Text:='CREATE TABLE :TName(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)';
  SQL1.Params.ParamByName('TName').AsString:= Edit1.Text;
  SQL1.Open;
end;     

When I break, SQL1.Open is highlited.

Aside from using the param TName, the SQL.Text is what I'd use to create a table at the command line. I have also tried to use ?TName in place of :TName, but that gives me a different error that suggests :TName is the correct syntax. Ultimately I want to create a table for every name entered that will store a history of that person. But for now I'm using an edit box just to be able to input a name then click to create the table. And as my history will suggest, this is probably some stupid oversight on my part. But I'm trying  :D

Anyone see what I'm doing wrong?

Kindest regards,
Landslyde
Title: Re: Creating SQLite3 tables during runtime
Post by: goodname on October 30, 2014, 07:27:07 pm
Don't make TName a parameter. This means the table name is treated as a field value string with quotes instead of a table identifier without quotes.
Title: Re: Creating SQLite3 tables during runtime
Post by: guest48180 on October 30, 2014, 07:59:48 pm
Don't make TName a parameter. This means the table name is treated as a field value string with quotes instead of a table identifier without quotes.

I don't know how to do it any other way. I'm new at this. All I have to go by is the SQLdb tutorials on the wiki, and the way I did it is the way they show. So if that way is wrong, how would I create a table with a name entered on the DBGrid? That's what I'm using to input everything. For each name, I want to create a separate table to store payment history, etc.

For example:  If I have two entries (Bob and Gary) for the Table called EntryPoint, how then do I create Table Bob and Table Gary from that? This is what's confusing me. I want this to be done via my code.

Do you understand what I'm trying to say?

Landslyde
Title: Re: Creating SQLite3 tables during runtime
Post by: goodname on October 30, 2014, 08:08:06 pm
Parameters are for field values not identifiers like table names.
Code: [Select]
procedure TMainForm.Button1Click(Sender: TObject);
begin
  SQL1.Close;
  SQL1.SQL.Text:='CREATE TABLE '+ Edit1.Text +'(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)';
  SQL1.Open;
end;
Title: Re: Creating SQLite3 tables during runtime
Post by: guest48180 on October 30, 2014, 08:36:28 pm
That was the ticket, goodname  :)   Thank you for sharing that with me. I'm having a lot of fun with this...just not a lot of info on it (that I've found).

Much appreciated,
Landslyde
Title: Re: [SOLVED] Creating SQLite3 tables during runtime
Post by: cdbc on October 31, 2014, 07:53:17 am
Hi
Another approach might be:
Code: [Select]
SQL1.SQL.Text:=format('CREATE TABLE %s(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)',[Edit1.Text]);
Just a thought  :)
Regards Benny
Title: Re: [SOLVED] Creating SQLite3 tables during runtime
Post by: guest48180 on October 31, 2014, 04:30:58 pm
Hi
Another approach might be:
Code: [Select]
SQL1.SQL.Text:=format('CREATE TABLE %s(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)',[Edit1.Text]);

I really appreciate your input and will give that a try, Benny. Thanks to goodname's insight, I got a lot done last night...more than usual. Right now I'm trying to figure out how to create tables from the name field I enter into the DBGrid, to hold their payment history. That's what I had the TEdit  for. I was using entries there to test my CREATE TABLE code. Got my day cut out for me  :D

Thank you again,
Landslyde

Update: This works:

Code: [Select]
tblName:= SQL1.FieldByName('name').Value;
SQL2.SQL.Text:='CREATE TABLE IF NOT EXISTS '+tblName+
            ' (ID INT PRIMARY KEY, NAME TEXT NOT NULL,'+
            'AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)';
SQL2.ExecSQL;

Reads the "name" value from one table and creates a new table from that value.
TinyPortal © 2005-2018