I'm having trouble converting the sql statement
     Query1.SQL.Add('CREATE INDEX if not exists "BillingDateOrder" on Jobs(BillingDate) compress full;');
into EDB.  I've worked out how to convert other commands, but I'm stuck on this one.  Any help appreciated.
Colin

Colin

>I'm having trouble converting the sql statement
> Query1.SQL.Add('CREATE INDEX if not exists "BillingDateOrder" on Jobs(BillingDate) compress full;');
>into EDB. I've worked out how to convert other commands, but I'm stuck on this one. Any help appreciated.
>Colin

Essentially you can't. None of the nice IF EXISTS stuff made it through to ElevateDB, and COMPRESS is no longer needed. What you can do is

Query1.SQL.Add('CREATE INDEX "BillingDateOrder" on Jobs(BillingDate);');

If you want the IF EXISTS functionality then you have three options:

1. Use a try .. except block
2 & 3. Use  a script
2. Query the information tables before creating the index
3. use an exception block

Both 2 & 3 will require some knowledge of SQL/PSM - Tim's programming language for scripts

What I would suggest is to use EDBManager's facilities to carry out these commands a) there is a very good OLH and b) you can use Explorer - SQL History to get the syntax that EDBManager generated to execute the command.

Roy Lambert [Team Elevate]

For option 2

SCRIPT
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Indexes WHERE Name = ''BillingDateOrder'' AND TableName = ''BillingDate''';
OPEN InfoCursor;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'CREATE INDEX "BillingDateOrder" on Jobs(BillingDate)';
END IF;

CLOSE InfoCursor;
END


For Option 3

SCRIPT
BEGIN

BEGIN
 EXECUTE IMMEDIATE 'CREATE INDEX "BillingDateOrder" on Jobs(BillingDate)';
EXCEPTION
END;

END

Both of the above are untested

Roy Lambert wrote:

Query1.SQL.Add('CREATE INDEX "BillingDateOrder" on Jobs(BillingDate);');

Thanks Roy,
That's put me on the right track!
Col

Roy
Unfortunately I can't seem to get this to work.  This code...
       Query1.SQL.Add('Create Table "Incident" (');
       Query1.SQL.Add('"IncidentID" Integer,');
       Query1.SQL.Add('"CrestCallSign"  Varchar(30) Collate "ANSI_CI",');
       Query1.SQL.Add('"Location"       Varchar(30) Collate "ANSI_CI",');
       Query1.SQL.Add('"CallerName"     Varchar(40) Collate "ANSI_CI",');
       Query1.SQL.Add('"CallerCallSign" Varchar(30) Collate "ANSI_CI",');
       Query1.SQL.Add('"CallerRego"     Varchar(10) Collate "ANSI_CI",');
       Query1.SQL.Add('"Notes"          Blob,');
       Query1.SQL.Add('"CreatedOn"      Timestamp Default Current_Timestamp,');
       Query1.SQL.Add('"FinishedOn"     Timestamp,');
       Query1.SQL.Add('Constraint  "ID_PrimaryKey"  Primary Key ("IncidentID",)');
       Query1.SQL.Add('CREATE INDEX "ID_CrestCallSign_Order" on Questions("IncidentID","CrestCallSign"));');
       Query1.ExecSQL;

fails on the create index line with an error #700 Expected data type but instead found INDEX.  I'm possably leaving something basic out, but I just can't spot the mistake.  Could you please help once again?
Thanks

Colin

>Unfortunately I can't seem to get this to work. This code...
> Query1.SQL.Add('Create Table "Incident" (');
> Query1.SQL.Add('"IncidentID" Integer,');
> Query1.SQL.Add('"CrestCallSign" Varchar(30) Collate "ANSI_CI",');
> Query1.SQL.Add('"Location" Varchar(30) Collate "ANSI_CI",');
> Query1.SQL.Add('"CallerName" Varchar(40) Collate "ANSI_CI",');
> Query1.SQL.Add('"CallerCallSign" Varchar(30) Collate "ANSI_CI",');
> Query1.SQL.Add('"CallerRego" Varchar(10) Collate "ANSI_CI",');
> Query1.SQL.Add('"Notes" Blob,');
> Query1.SQL.Add('"CreatedOn" Timestamp Default Current_Timestamp,');
> Query1.SQL.Add('"FinishedOn" Timestamp,');
> Query1.SQL.Add('Constraint "ID_PrimaryKey" Primary Key ("IncidentID",)');
> Query1.SQL.Add('CREATE INDEX "ID_CrestCallSign_Order" on Questions("IncidentID","CrestCallSign"));');
> Query1.ExecSQL;
>
>fails on the create index line with an error #700 Expected data type but instead found INDEX. I'm possably leaving something basic out, but I just can't spot the mistake. Could you please help once again?

Of course I can, but I'm going to suggest again that you try these things out in EDBManager - it will help - trust me I'm a Team Elevate member

What you're trying to do can no longer be done in a single query in ElevateDB. You need a script. However, scripts have changed totally between DBISAM and ElevateDB. Back in DBISAM scripts were simply lists of sql commands and executed independently one after another, and when you shoved a load into a query it effectively created one query after another to run them. In ElevateDB a query can only handle one statement, and you have two. You can either choose to use two queries (one for the create table and one for the create index) or choose to use a script.  I'll assume you can do the former To use a script you have to use a script component not a query component and its contents would be:


SCRIPT
BEGIN

EXECUTE IMMEDIATE 'Create Table "Incident" (
'"IncidentID" Integer,
'"CrestCallSign" Varchar(30) Collate "ANSI_CI",
'"Location" Varchar(30) Collate "ANSI_CI",
'"CallerName" Varchar(40) Collate "ANSI_CI",
'"CallerCallSign" Varchar(30) Collate "ANSI_CI",
'"CallerRego" Varchar(10) Collate "ANSI_CI",
'"Notes" Blob,
'"CreatedOn" Timestamp Default Current_Timestamp,
'"FinishedOn" Timestamp,
'Constraint "ID_PrimaryKey" Primary Key ("IncidentID",)';

EXECUTE IMMEDIATE 'CREATE INDEX "ID_CrestCallSign_Order" on Questions("IncidentID","CrestCallSign")';

END