Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Advantage SQL migration help |
Fri, May 19 2017 9:07 AM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | 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. |
Fri, May 19 2017 9:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, May 19 2017 10:54 AM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | 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 . . .
|
Fri, May 19 2017 2:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Mon, May 22 2017 4:26 PM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | Thanks Tim. I can live with those differences.
|
Fri, May 26 2017 10:48 AM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | 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 |
Fri, May 26 2017 2:29 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Mon, May 29 2017 10:35 AM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | Thank Tim. In general are there any other SQL limitations you are aware of in the context of making a temporary table?
|
Mon, May 29 2017 12:38 PM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Wed, May 31 2017 12:13 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |