Adam

>I have replaced virtually all of them with SQL Statements which call VIEWS. Views essentially allow the creation of a "permanent memory table"

Sorry Adam I have to disagree with you. They create a query which can be queried. They do not create a table. Don't get me wrong Views are a very useful construct and some day I may have a use for them but there's no way they can replace the complexity / efficiency of some of my parameterised scripts creating a memory (or temporary) table.

> 1. You can use a simple query statement, rather than the slightly more cumbersome script.

I hated scripts to start with but now I like them. On the negative side its also a way to avoid looking for the "better" solution which doesn't involve a script, and may be more efficient.

> 2. You can access MyView from _any_ statement. Before I was re-writing INTO MEMORY over and over and over, basically creating the same MEMORY tables, which were used by multiple users in multiple views. This also caused problems of LOCKING ... which EDB Views overcome.

That one baffles me. Can you explain the locking problem?

Roy Lambert

Adam,

<< From Temporary table MaxCSNo M1 >>

You don't need to use the TEMPORARY keyword when referencing temporary
tables.  They exist in the same space as normal tables for "existence"
purposes.  In fact, if you name a temporary table the same name as a normal
table, the temporary table will "hide" the normal table until the temporary
table is dropped.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Does that produce a result set as well as a temporary table? Asking to
save me having to try it out. >>

Yes, the WITH RETURN does that.

However, there will be an issue with that script over a single
prepare/multiple executions without this addition:

SCRIPT
BEGIN
  DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;

  UNPREPARE Stmt;

  BEGIN
     Execute Immediate 'Drop Table "TempTable1"';
  EXCEPTION
  END;

  Execute Immediate 'Create Temporary Table "TempTable1" as select *
from "RealTable1" with data';

  Prepare Stmt from 'Select *  from "TempTable1"';
  Open CustCursor;
END

It seems weird to UNPREPARE a statement that hasn't been explicitly PREPAREd
yet in code, but if you think about it, the Stmt object will stay prepared
as long as the outer script stays prepared.  Therefore, if you prepare the
script once, but execute the script multiple times, the DROP TABLE will fail
because the TempTable1 will still be open due to the Stmt object still being
prepared from the PREPARE operation in the previous execution.  Cursors that
use WITH RETURN keep their corresponding statement objects around between
executions, for obvious reasons.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< In DBISAM in-memory was a nice programmer friendly feature. In ElevateDB
its been elevated to the same status as a disk database and is as friendly
as a cornered rattlesnake. The biggest problem is that memory is just
treated as a temporary disk drive. You can have lots of memory based
databases but there are all the restrictions that apply to moving data
between disk based databases apply. >>

What restrictions are you referring to ?  Using in-memory databases in EDB
is easier, IMO, than with DBISAM.  In DBISAM you had to preface everything
with the special funky "Memory\" syntax, whereas with EDB you can use any
naming that you want, and the naming is the same for in-memory databases as
it is for on-disk databases.

And, just so you know - the in-memory databases in EDB are implemented in
exactly the same fashion as they are with DBISAM.  The problem with DBISAM
was that DBISAM had no way of naming in-memory databases in a
user-configurable way, ala the configuration file and the CREATE/ALTER/DROP
DATABASE statement that EDB has.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< In DBISAM in-memory was a nice programmer friendly feature. In ElevateDB
>its been elevated to the same status as a disk database and is as friendly
>as a cornered rattlesnake. The biggest problem is that memory is just
>treated as a temporary disk drive. You can have lots of memory based
>databases but there are all the restrictions that apply to moving data
>between disk based databases apply. >>
>
>What restrictions are you referring to ? Using in-memory databases in EDB
>is easier, IMO, than with DBISAM. In DBISAM you had to preface everything
>with the special funky "Memory\" syntax, whereas with EDB you can use any
>naming that you want, and the naming is the same for in-memory databases as
>it is for on-disk databases.

In your opinion that may well be so, in mine the reverse applies. I remember from DBISAM that there is at least one customer who wanted multiple in-memory databases but I'll be surprised if that applies to many of us.  It, for me at least, was far more convenient to be able to refer to the in-memory database without having to create it first and in a consistent fashion (strangely enough my one and only in-memory database is called MEMORY).

The restrictions I was referring to are just the standard ones - eg no more SELECT INTO. I know you don't agree with me, and you're following the standard but a lot of the DBISAM syntax you developed is just plain better.

>And, just so you know - the in-memory databases in EDB are implemented in
>exactly the same fashion as they are with DBISAM. The problem with DBISAM
>was that DBISAM had no way of naming in-memory databases in a
>user-configurable way, ala the configuration file and the CREATE/ALTER/DROP
>DATABASE statement that EDB has.

The internal implementation may be the same but the external has changed as you yourself have just stated.

Roy Lambert

Tim


>It seems weird to UNPREPARE a statement that hasn't been explicitly PREPAREd
>yet in code, but if you think about it, the Stmt object will stay prepared
>as long as the outer script stays prepared. Therefore, if you prepare the
>script once, but execute the script multiple times, the DROP TABLE will fail
>because the TempTable1 will still be open due to the Stmt object still being
>prepared from the PREPARE operation in the previous execution. Cursors that
>use WITH RETURN keep their corresponding statement objects around between
>executions, for obvious reasons.

That makes sense. The weird part to me is producing a script that will produce two copies of the same data Although having just written that it could be a neat trick for editing the temporary table and then using the result set to check for differences and post.

Roy Lambert

> and you're following the standard but a lot of the DBISAM syntax you developed is just plain better.

+1

Tim - YOU should have been setting the industry standard!  

I came to DBISam partly because of how well and simple it operated. (I
had come from looking at a more industry standard in Interbase but...
you seem to know what developers like me wanted much more! I have no
problems with industry standard... but it's even better to have it PLUS
some extra simpler options in there. <vbg>

Adam

>Tim - YOU should have been setting the industry standard!

Bloody good idea!

Roy Lambert

Roy,

<< In your opinion that may well be so, in mine the reverse applies. I
remember from DBISAM that there is at least one customer who wanted multiple
in-memory databases but I'll be surprised if that applies to many of us.
It, for me at least, was far more convenient to be able to refer to the
in-memory database without having to create it first and in a consistent
fashion (strangely enough my one and only in-memory database is called
MEMORY). >>

Really ?  The fact that you have to execute a *single* CREATE DATABASE
statement is a restriction ????

<< The restrictions I was referring to are just the standard ones - eg no
more SELECT INTO. I know you don't agree with me, and you're following the
standard but a lot of the DBISAM syntax you developed is just plain better.
>>

Yes, but those changes have nothing to do with in-memory databases,
specifically.  The INTO statement is used in standard SQL for something else
completely (single-column, single-row output to an output parameter), so it
had to go in favor of the standard.  There was no way around that.

--
Tim Young
Elevate Software
www.elevatesoft.com