Recent

Author Topic: [SOLVED] Creating SQLite3 tables during runtime  (Read 3818 times)

guest48180

  • Guest
[SOLVED] Creating SQLite3 tables during runtime
« 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

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Creating SQLite3 tables during runtime
« Reply #1 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.

guest48180

  • Guest
Re: Creating SQLite3 tables during runtime
« Reply #2 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

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Creating SQLite3 tables during runtime
« Reply #3 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;

guest48180

  • Guest
Re: Creating SQLite3 tables during runtime
« Reply #4 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

cdbc

  • Full Member
  • ***
  • Posts: 236
    • http://www.cdbc.dk
Re: [SOLVED] Creating SQLite3 tables during runtime
« Reply #5 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
If it ain't broke, don't fix it ;)

guest48180

  • Guest
Re: [SOLVED] Creating SQLite3 tables during runtime
« Reply #6 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.