Hi

I have written a script that neither my program nor EDB Manager like.

I wrote it initially the old DBISAM3 way with a CREATE TABLE and a CREATE
INDEX separated with ";" and used ConvertSQL to make a script.

I did a showmessage(scrCreateTable.SQL.Text); to capture the script with
Ctrl+C and pasted it into EDB Manager.

On Executing, I get the message:-

---------------------------
Error
---------------------------
There are no statements to execute.
---------------------------
OK
---------------------------

I must have done something silly, but can't spot it - sometimes being a sole
developer sucks!

I've tried it with only the CREATE TABLE and only the CREATE INDEX, same
result.

The script is below.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

SCRIPT ()
BEGIN
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "TEMP00001099"
(
"TenantCode"        VARCHAR(4),
"SortKey"           VARCHAR(20),
"Disburse"          BOOLEAN DEFAULT False,
"AmountHeld"        DECIMAL(19,2),
"LastReceiptDate"   DATE,
"AmountOutstanding" DECIMAL(19,2),
"AddressTo"         VARCHAR(20),
"PropertyName"      VARCHAR(60),
"VacateDate"        DATE,
CONSTRAINT "TenantCode_PrimaryKey" PRIMARY KEY ("TenantCode")
)';
  EXECUTE IMMEDIATE 'CREATE INDEX "AlphaIndex" ON "TEMP00001099"
(SortKey, TenantCode COLLATE UNI_CI)';
END

Jeff,

I copied the script to EDBMgr 2.13 B2 and pasted it into a new script window and it executed fine. (I assume you selected New > Dropdown Arrow > Script to open the script window. I also assume you have selected a database in the tree before you ran the script.).

The TEMP00001099 table was created along with the index. The status bar shows "The script was executed successfully in 0.047 seconds".

To see temporary tables you need to execute:
select * from information.temporarytables;

because temporary tables don't show up in the EDBMgr tree.

I did notice you had "SCRIPT ()" and I don't know why you have the "()". I just use "SCRIPT". I tried it with "SCRIPT ()" and it still worked.

Barry

<Barry> wrote in message
news:9ECE7ACB-8B81-4473-A6B5-8671F57D0264@news.elevatesoft.com...

>  (I assume you selected New > Dropdown Arrow > Script to open the script
> window. I also assume you have selected a database in the tree before you
> ran the script.).
>

Yes, that is what I did - at least that is what I thought that I had done.
I pasted the script created for me using the ConvertSQL.

> The TEMP00001099 table was created along with the index. The status bar
> shows "The script was executed successfully in 0.047 seconds".

That is where I got the error message about no statements to execute. I can
understand that I have done something wrong but can't see the relevance of
the error message.
>
> To see temporary tables you need to execute:
> select * from information.temporarytables;
>
> because temporary tables don't show up in the EDBMgr tree.
>

Aha!  didn't know that, so I may have been confused at some stage - I might
have successfully created the table and not known it.  I was using a Memory
database so there was no folder to look into to see if the tables had been
created.  No sure how you know the index is there if it isn't in the dtabase
tree.  Similarly how would I find the fields created?

> I did notice you had "SCRIPT ()" and I don't know why you have the "()".

Just because that's how the ConvertSQL made it with the "()".

However, after resting my brain for the weekend, I have been through it all
again and all works as expected.  I think that a 3 day week with one work
day and two day weekend would work for me!

Thanks Barry

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff,

>> because temporary tables don't show up in the EDBMgr tree.<<

>Aha!  didn't know that, so I may have been confused at some stage - I might
have successfully created the table and not known it.  I was using a Memory
database so there was no folder to look into to see if the tables had been
created.  No sure how you know the index is there if it isn't in the dtabase
tree.  Similarly how would I find the fields created?<

Unfortunately there isn't an Information.TemporaryTableColumns so you have to write a Delphi rtn to execute "Select * from temptable range 0 to 0' and then use FieldDefs to get access to the columns returned.

Also if you want to know if a table has an index on col1,col2,col3, just do a 'Select * from temptable order by col1,col2,col3' and if it creates a Sensitive query you know it has an index on those columns (assuming the role you are using doesn't have the table unupdateable).

>However, after resting my brain for the weekend, I have been through it all
again and all works as expected.  I think that a 3 day week with one work
day and two day weekend would work for me!<

Are you thinking of running for Congress?

Barry

Barry

>
> Are you thinking of running for Congress?
>
We don't have a Congress, but I get the idea  

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz