[SOLVED] Creating SQLite3 tables during runtime
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2014-10-30 18:18

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 

Anyone see what I'm doing wrong?

Kindest regards,
Landslyde

----------------------------------------------------------------------
goodname · 2014-10-30 18:27

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 · 2014-10-30 18:59

Quote from: goodname on October 30, 2014, 07:27:07 pmDon'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 · 2014-10-30 19:08

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 · 2014-10-30 19:36

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 · 2014-10-31 06:53

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

----------------------------------------------------------------------
guest48180 · 2014-10-31 15:30

Quote from: cdbc on October 31, 2014, 07:53:17 amHi
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 

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.