Hi, I can happily add a new column to a database if it doesn't exist using the code below, but I have tried to adapt it to modify an index if it hasn't already been created, but I can't get the syntax correct.  Could someone point me in the right direction? Obviously I have to change TableName to IndexName or something...
Thanks
Colin
 
     if (Execute('Select * From Information.Tables '+
                 'Where Name='+Engine.QuotedSQLStr('Jobs') +
                 ' and TableName = ' +
                 Engine.QuotedSQLStr('FeedbackDate'))=1)
     then  else
     begin
       Log('Altering the Jobs table');
       Query1.SQL.Clear;
       Query1.SQL.Add('Alter Table "Jobs" Add Column "FeedbackDate" Timestamp;');
       {The above line will become... Query1.SQL.Add('Create Index "ZoneAreaOrder" on "Jobs" ("Zone","Area");'); }
       Query1.ExecSQL;
     end;

oops "modify an index" should have been "add an index"

Colin,

I can't see anything wrong on the syntax. Which error do you get?

If you are not sure about the syntax, just create an index in
EDBManager. After that goto Explorer | SQL History and you see the
matching sql statement. I use that very often.

Uli

Hi Uli,

<< I can't see anything wrong on the syntax. Which error do you get?

The error message says #401 The column TableName does not exist in the temporary table Tables.

I<< f you are not sure about the syntax, just create an index in
EDBManager. After that goto Explorer | SQL History and you see the
matching sql statement. I use that very often.

Yes I will give that a go, I haven't used that feature before.
Thanks
Col

Colin


First I endorse Uli's comment about using EDBManger. Its a brilliant tool. Also, like Uli I can see nothing wrong with the syntax you're using.

>The error message says #401 The column TableName does not exist in the temporary table Tables.

This error puzzles me. Either you've posted a wrong error message or the code you posted to create the sql is not what you're using

Nothing here

Query1.SQL.Add('Create Index "ZoneAreaOrder" on "Jobs" ("Zone","Area");');

is TableName or can be interpreted to be converted to that.

but I am also concerned about {The above line will become... coupled with the use of semicolons (I may be over interpreting here).

It looks as though you could be trying to create a DBISAM style script. Can you capture the sql from the query and post that.

Easiest way is to add a showmessage(query1.sql.text) and use ctrl-c on the displayed info

Roy Lambert [Team Elevate]

Colin,

<<
if (Execute('Select * From Information.Tables '+
                  'Where Name='+Engine.QuotedSQLStr('Jobs') +
                  ' and TableName = ' +
                  Engine.QuotedSQLStr('FeedbackDate'))=1)
>>

The column TableName doesn't exist in Information.Tables. So this query
*must* fail, but not with the error you posted.

Please elaborate what you are doing exactly.

Uli

Correction: The error *does* occur with the query you posted.
Since the column "TableName" does not exist, you get:


ElevateDB Error #700 An error was found in the statement at line 1 and
column 8 (ElevateDB Error #401 The column tablename does not exist in
the temporary table Tables)

If you want to know which indexes exist, you have to use:

select * from information.indexes

Uli

Uli


I now feel totally thick - well spotted - I just glanced at that and it obviously didn't register.

Roy Lambert

Roy,

> I now feel totally thick - well spotted - I just glanced at that and it obviously didn't register.

Same here - I stumbled on "temporary table tables".



Uli

Hi Guys,
Sorry I didn't respond sooner, I've been away from my PC for a while.  Thanks so much to both of you for your help.
I've made the correction and it now works fine.
Regards
Colin