Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
My first attempt at wriiting SQL for EDB |
Thu, Oct 31 2013 4:21 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
I am converting an app from DBISAM3 to ElevateDB and I am stuck already on the first SQL executed. The actual SQL used is modified in code - putting in JOIN and WHERE conditons and ORDER BY according to user selections. I have simplified the script and tried it out in EDBMgr - so what I show below might seem a bit pointless ... but if I can't get the simplified version going what hope is there The problem seems to be that the dropping of the TEMPORARY table doesn't seem to work as I get this message:- ElevateDB Error #400 An error occurred with the statement at line 17 and column 21 (The temporary table TmpLandlords already exists in the schema Default) .... it is saying "in schema Default" - where is that? Should I be able to see that in EDBMgr? Here is the Script:- ------------------------------------------------------------------- SCRIPT BEGIN DECLARE TmpCursor CURSOR WITH RETURN FOR Stmt; DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?' /* Drop the table if it exists */ OPEN InfoCursor USING 'TmpLandlords'; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE TmpLandlords'; END IF ; /* this select will have a WHERE constructed in code to select one or ALL portfolios */ EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE TmpLandlords AS <<<<<<<<<<< line 17 (SELECT DISTINCT LandlordCode FROM Landlords L) WITH DATA' ; EXECUTE IMMEDIATE 'SELECT L.LandlordCode, SortKey, DayTimePhone, AfterHoursPhone, ContactName, LastName, FirstName, EMail, InternalCommentIsWarning FROM Landlords L JOIN TempLandlords T ON T.LandlordCode = L.LandlordCode ORDER BY SortKey' /* ORDER BY will be made in code */ ; END ------------------------------------------------------------------- Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Thu, Oct 31 2013 7:01 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Got it!
This statement ... > PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?' .... should be PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?' Now I just have to work out how to write the script making my Temporary table a memory table. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Fri, Nov 1 2013 4:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Glad to see you solved your first problem. The second one can be solved by running the query in the memory database (ie set the databasename property for the component to whatever your in-memory database is) and referencing the appropriate disk based database as part of your select statement. Now I'm going to be wicked - go and look through these very newsgroups and you'll see I've posted a number of times a script that Tim gave me that takes a piece of SQL (ie you can create it programmatically) as a parameter and will create an in-memory table for you. I modified it to allow creation of an index as well. I'll be kindish - the subject to look for is Temporary Table Script Questions Roy Lambert [Team Elevate] |
Sun, Nov 3 2013 5:21 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Roy
"Roy Lambert" <roy@lybster.me.uk> wrote in message news:CFAB2FDE-75E0-419D-805A-F5092E25A563@news.elevatesoft.com... > Glad to see you solved your first problem. The second one can be solved by > running the query in the memory database (ie set the databasename property > for the component to whatever your in-memory database is) and referencing > the appropriate disk based database as part of your select statement. Thanks for that - don't think that I would picked that up - I'm working in my "real" database, but the script has to work in the memory database and cross reference to the real one. Strikes as being slightly back to front thinking - but I don't want to spark a new "NULL <> ''" debate > Now I'm going to be wicked - go and look through these very newsgroups and > you'll see I've posted a number of times a script that Tim gave me that > takes a piece of SQL (ie you can create it programmatically) as a > parameter and will create an in-memory table for you. I modified it to > allow creation of an index as well. > > I'll be kindish - the subject to look for is Temporary Table Script > Questions AHA! I did read that post as part of my trawling through the newsgroups but at the time I didn't understand scripting enough to realise it had my answers and I don't see it as directly referencing my memory table problem. So, I think:- 1. I'll need to set the DatabaseName to the memory database (? I'll have to find out how to do that) 2. "'SELECT * FROM Information.Tables WHERE Name=?';" will need to change to Information.TemporaryTables. 3. The script has 4 parameters - I can see how to pass the SQLStatement, TableName and IdxSet, but how do I pass the "?" parameter - or should that be Name= "' +TableName + '" instead of Name=?'; Lots to learn about ElevateDB - it's a bit different form DBISAM3, isn't it Many thanks Roy Cheers Jeff > > > Roy Lambert [Team Elevate] >-- Jeff Cook Aspect Systems Ltd www.aspect.co.nz 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 |
Mon, Nov 4 2013 5:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
>Thanks for that - don't think that I would picked that up - I'm working in >my "real" database, but the script has to work in the memory database and >cross reference to the real one. Strikes as being slightly back to front >thinking Not really, it takes a bit of getting your head round but the way to think of it is that the script is more interested in where you're creating the table than where the definition is coming from. It makes still more sense if you think of creating a table full stop. The script/query has to know where to create it and the way to tell it is to specify the database its working in. >- but I don't want to spark a new "NULL <> ''" debate Don't understand >So, I think:- > >1. I'll need to set the DatabaseName to the memory database (? I'll have >to find out how to do that) On the component you're using. Ah the light bulb just went on. Its different to DBISAM in-memory databases are just the same as disk based ones now. You need to create an in-memory database yourself (mine surprisingly is called Memory) and just set the script/query component's DatabaseName to that database's DatabaseName. >2. "'SELECT * FROM Information.Tables WHERE Name=?';" will need to change >to Information.TemporaryTables. Yup >3. The script has 4 parameters - I can see how to pass the SQLStatement, >TableName and IdxSet, but how do I pass the "?" parameter - or should that >be Name= "' +TableName + '" instead of Name=?'; Its this bit PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; <<<<<<<<<<<<< NOTICE this says replace the ? with TableName > >Lots to learn about ElevateDB - it's a bit different form DBISAM3, isn't it > And it was different from DBISAM4 as well!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Roy Lambert [Team Elevate] |
Mon, Nov 4 2013 2:55 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Thanks Roy
This conversation about EDB scripts has help me understand how things work. Not saying I won't be back for more, but at least I understand some of the basics. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Roy Lambert" <roy@lybster.me.uk> wrote in message news:FA4BE264-D543-42D0-9BD5-00A437518BAC@news.elevatesoft.com... |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |