Firebird: ALTER TABLE, error: Token unknown
lazarus / 16-Databases

----------------------------------------------------------------------
cov · 2013-04-10 10:54

I'm trying to add a column to my Table, called FIELDS.

Code: [Select]      SQLQuery1.SQL.Add('ALTER TABLE FIELDS ADD _TITLE
VARCHAR(255);');
      SQLQuery1.ExecSQL; 
I get the following error:

Code: [Select]IBConnection1 : PrepareStatement:
-Dynamic SQL Error
-SQL Error code = -104
-Token unknown - line 1, column 24
-_TITLE
According to this:
http://www.firebirdsql.org/refdocs/langrefupd20-alter-table.html it
should work. :+(

----------------------------------------------------------------------
BigChimp · 2013-04-10 10:59

FYI: the Firebird docs are split into the original Interbase 6 docs
and the latest language reference update (2.5 as of now).

You can download/find both of those sets on the Firebird site, under
documentation.

Are you sure that column names may start with an underscore?

----------------------------------------------------------------------
BigChimp · 2013-04-10 11:05

AFAIU, table/column/procedure - database object names in general - may
not start with an underscore...

Struggling to find the definition in docs though

----------------------------------------------------------------------
cov · 2013-04-10 11:11

Oh.

Just trying to make sure that any field name doesn't conflict with a
keyword.

I'll try ending with an underscore, instead.

----------------------------------------------------------------------
cov · 2013-04-10 11:26

Okay.

It does seem that the underscore is the problem.

However I'm looping through a list of name=value pairs, and am getting
the error on the second itteration.

Code: [Select]  IBConnection1.GetFieldNames('FIELDS',fieldList);
  SelectedAttributes.Add('TITLE=My Document');
  SelectedAttributes.Add('AUTHOR=Dave Coventry');
  for i:=0 to SelectedAttributes.Count-1 do
  begin
    if fieldList.IndexOf(SelectedAttributes.Names[i]+'_')<0 then
    begin
      s:='ALTER TABLE FIELDS ADD
'+UpperCase(SelectedAttributes.Names[i])+'_ VARCHAR(255);';
      SQLQuery1.SQL.Add(s);
      SQLQuery1.ExecSQL;
    end;
  end;   
'ALTER TABLE FIELDS ADD TITLE_ VARCHAR(255);'<- Executes successfully
'ALTER TABLE FIELDS ADD AUTHOR_ VARCHAR(255);'<-Fails.

Code: [Select]-Token unknown - line 2, column 1
-ALTER
Baffling...

----------------------------------------------------------------------
cov · 2013-04-10 11:44

Hmm.

FlameRobin suggests that the FIELDS table does not have the Column
'TITLE_' added. This would suggest that SQLQuery1.ExecSQL; has not
executed, despite the debugger stepping over it successfully.

----------------------------------------------------------------------
BigChimp · 2013-04-10 11:44

You're adding strings to the stringlist SQL.Text.

Do you ever take them away? Try throwing in a writeln(SQL.Text); just
before the .Execute.
Try running that text in Flamerobin and see what happens.

----------------------------------------------------------------------
BigChimp · 2013-04-10 11:45

Quote from: cov on April 10, 2013, 01:44:05 pmHmm.

FlameRobin suggests that the FIELDS table does not have the Column
'TITLE_' added. This would suggest that SQLQuery1.ExecSQL; has not
executed, despite the debugger stepping over it successfully.
Do you commit your transactions?

Perhaps it's time to read sqldbtutorial1 & 2 on the wiki again...

----------------------------------------------------------------------
cov · 2013-04-10 13:37

I've used IBConnection1 instead, which seems to work:

Code: [Select]      s:='ALTER TABLE FIELDS ADD
'+UpperCase(SelectedAttributes.Names[i])+'_ VARCHAR(255);';
      IBConnection1.ExecuteDirect(s);
      SQLTransaction1.Commit; 
However, although I don't get any noticeable error, neither can I see
the records added to the Table in FlameRobin when I use the same
technique to INSERT a record:

Code: [Select]  sfield:='INSERT INTO FIELDS(ID';
  svalue:=' VALUES(0';
  for i:=0 to SelectedAttributes.Count-1 do
  begin
    //
    sfield+=','+SelectedAttributes.Names[i]+'_';
    svalue+=','''+SelectedAttributes.ValueFromIndex[i]+'''';
  end;
  sfield+=')';
  svalue+=')';
  IBConnection1.ExecuteDirect(sfield+svalue);
  SQLTransaction1.Commit;

----------------------------------------------------------------------
BigChimp · 2013-04-10 14:31

Quote from: cov on April 10, 2013, 03:37:39 pmI've used IBConnection1
instead, which seems to work:
The problem with your earlier code was that you put 2 update
statements in one sql statement. Firebird does not support this and
therefore errors out. As far as I can see, it had nothing to do with
SQLQuery1...

Re the second code: do you also start your transaction then? TBH, I
always do an explicit .starttransaction and .commit; I don't know how
sqldb reacts if you don't.
Apart from that, at first sight, the code looks fine.