I have used Advantage Database for years and am now investigating migrating to EDB.  I have found many positives in EDB and look forward to taking advantage of them going forward.  There is one MAJOR issue that I have.  In many applications I extensively use temporary files both in tables and queries/scripts.  It allows me a lot of power and flexibility.  A simple example would be for a invoice edit:

TRY DROP TABLE #Tmp1; CATCH ALL END;
SELECT I.*,C.Custno, C.Name CustName, C.Address CustAddress
INTO #Tmp1 FROM invoice I
LEFT OUTER JOIN customer C ON I.customerID=C.ID
ORDER BY C.Custno;
SELECT * FROM #Tmp1;

In this simple example I get an editable (sensitive/dynamic) cursor. I have a rowversion field in the invoice table to track other user's changes.  I write back changes in the AfterPost event of this query giving me the opportunity to log changes or do other verification before any real change takes place.  I have consistency in that my table editing and temporary table editing use the same components.  I can run other queries on temp table work product if necessary.  I can even open/create a temp table directly from a table component.

As someone new to EDB it is very possible that I am missing something big, but how do I accompilish this in EDB?  I am open to sugestions of how to accomplish these goals in a very different way if necessary.

So far I am pretty dissappointed with the temporary table capabilities in EDB.  It just seems quite messy.  I thought other SQL implementations used the '#' to specify temp tables as well.  

Thanks for any help provided.

gbh100


I think what you want is included as part of the CREATE TABLE AS WITH DATA capability. Tim doesn't say its different so I assume its the SQL 2003 standard.

To achieve the same result as you want you'll want either a script or several queries. The following is a script Tim helped me with agres ago

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END


I use it to create in-memory tables and run it from the memory database context. To create a temporary table use

PREPARE ResultStmt FROM 'CREATE TEMPORARY TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';

instead

Roy Lambert

Thanks Roy, I will have to investigate what is happening in your example as clearly I don't know enough yet on this subject.  It seems really messy to me, but hopefully I will get over/past that to make it work . . .

<< In this simple example I get an editable (sensitive/dynamic) cursor. I have a rowversion field in the invoice table to track other user's changes.  I write back changes in the AfterPost event of this query giving me the opportunity to log changes or do other verification before any real change takes place.  I have consistency in that my table editing and temporary table editing use the same components.  I can run other queries on temp table work product if necessary.  I can even open/create a temp table directly from a table component.

As someone new to EDB it is very possible that I am missing something big, but how do I accompilish this in EDB?  I am open to sugestions of how to accomplish these goals in a very different way if necessary. >>

There are two issues here:

1) EDB does not allow semicolon-delimited queries using the TEDBQuery component.  They're a security issue with web applications (SQL injection), so they aren't supported.

2) What you want to use is a TEDBScript component, not a TEDBQuery component.  With it, you can do the following to replicate what you're trying to do:

SCRIPT
BEGIN
  DECLARE MyCursor CURSOR WITH RETURN FOR MyStmt;
  BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE Temp1';
  EXCEPTION
  END;
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp1 AS
                     SELECT I.*,C.Custno, C.Name CustName, C.Address CustAddress
                     FROM invoice I
                     LEFT OUTER JOIN customer C ON I.customerID=C.ID
                     ORDER BY C.Custno
                     WITH DATA';
  PREPARE MyStmt FROM 'SELECT * FROM Temp1';
  OPEN MyCursor;
END

<< So far I am pretty dissappointed with the temporary table capabilities in EDB.  It just seems quite messy.  I thought other SQL implementations used the '#' to specify temp tables as well. >>

Some do, but EDB uses the SQL-2003 syntax (CREATE TABLE AS...), not the non-standard INTO syntax.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim.  I can live with those differences.

I have finally got to working with a real example and it seems there are some issues.  One of the goals was to get a sensitive cursor so I assume that I would need that in my declaration.  The bigger issue is that the ORDER BY does not seem to be allowed when creating the temporary table.  If I move the ORDER BY to the PREPARE line then I don't get a sensitive cursor.

SCRIPT
BEGIN
 DECLARE MyCursor SENSITIVE CURSOR WITH RETURN FOR MyStmt;
 BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Temp1';
 EXCEPTION
 END;
 EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp1 AS
     SELECT U.* FROM secuser U ORDER BY "name" WITH DATA';
 PREPARE MyStmt FROM 'SELECT * FROM Temp1';
 OPEN MyCursor;
END

<< I have finally got to working with a real example and it seems there are some issues.  One of the goals was to get a sensitive cursor so I assume that I would need that in my declaration.  The bigger issue is that the ORDER BY does not seem to be allowed when creating the temporary table.  If I move the ORDER BY to the PREPARE line then I don't get a sensitive cursor. >>

If you want a sensitive cursor with an ORDER BY, then you'll need to make sure that the temporary table can provide one by making sure that you add the proper indexes before executing the SELECT statement that returns the sensitive result cursor:

SCRIPT
BEGIN
 DECLARE MyCursor SENSITIVE CURSOR WITH RETURN FOR MyStmt;
 BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Temp1';
 EXCEPTION
 END;
 EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp1 AS
     SELECT U.* FROM secuser U ORDER BY "name" WITH DATA';
 EXECUTE IMMEDIATE 'CREATE INDEX MyIndex ON Temp1..... ';
 PREPARE MyStmt FROM 'SELECT * FROM Temp1 ORDER BY xxxx';
 OPEN MyCursor;
END

Tim Young
Elevate Software
www.elevatesoft.com

Thank Tim.  In general are there any other SQL limitations you are aware of in the context of making a temporary table?

I am slightly off topic, but I wonder whether you have looked at Client Data Sets (CDS) in Delphi in the context that you are working?

I have a similar methodology to you, but rather than creating a temp-table on the db I write simple SQL which I return to a Delphi CDS.

It is then possible create an UPDATE statement from the CDS to post back changes.

Then no complex scripts, stored procs or temporary tables are required on the Database.

<< Thank Tim.  In general are there any other SQL limitations you are aware of in the context of making a temporary table? >>

That limitation really isn't specific to temporary tables.  Temporary tables act just like normal tables in all respects, with the exception being that they aren't stored in the normal database directory and aren't shared.

Tim Young
Elevate Software
www.elevatesoft.com