Recent

Author Topic: Firebird: ALTER TABLE, error: Token unknown  (Read 10982 times)

cov

  • Full Member
  • ***
  • Posts: 222
Firebird: ALTER TABLE, error: Token unknown
« on: April 10, 2013, 12:54:11 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #1 on: April 10, 2013, 12:59:01 pm »
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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #2 on: April 10, 2013, 01:05:29 pm »
AFAIU, table/column/procedure - database object names in general - may not start with an underscore...

Struggling to find the definition in docs though
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

cov

  • Full Member
  • ***
  • Posts: 222
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #3 on: April 10, 2013, 01:11:20 pm »
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

  • Full Member
  • ***
  • Posts: 222
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #4 on: April 10, 2013, 01:26:06 pm »
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

  • Full Member
  • ***
  • Posts: 222
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #5 on: April 10, 2013, 01:44:05 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #6 on: April 10, 2013, 01:44:46 pm »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #7 on: April 10, 2013, 01:45:39 pm »
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.
Do you commit your transactions?

Perhaps it's time to read sqldbtutorial1 & 2 on the wiki again...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

cov

  • Full Member
  • ***
  • Posts: 222
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #8 on: April 10, 2013, 03:37:39 pm »
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;
« Last Edit: April 10, 2013, 03:39:19 pm by cov »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird: ALTER TABLE, error: Token unknown
« Reply #9 on: April 10, 2013, 04:31:09 pm »
I'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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified