Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 16 total |
Temporary Table Script Questions |
Wed, Sep 25 2013 1:26 AM | Permanent Link |
Adam H. | Hi,
Before I go too much further, I was just wanting to confirm that I'm doing the right thing with my temporary table. It's my understanding that temporary tables are 'per user' and can not be shared? (That I can use them for whatever I want, not worrying about conflicts), and in comparison Memory tables are like a real table in the database and can be shared amongst users? And if correct, I have the following SQL Script below. SCRIPT BEGIN DECLARE Result CURSOR WITH RETURN FOR stmt; BEGIN Execute immediate 'Drop table "DiaryContM"'; Exception End; Execute Immediate 'Create TEMPORARY TABLE "DiaryContM" as ( Select True as Selected, GrowerID, GrowerNGR, Tonnage, CommodityID, VarietyID, GradeID, Price From Diary D left outer join Names N on (N.ID = D.GrowerID) where (True) /*1*/ and ((D.ID = 13) or (D.ID = 14) or (D.ID = 15)) ) WITH DATA'; Prepare Stmt from 'Select * from DiaryContM' ; Open Result; END The purpose of the script is to: a) Delete the temporary table if it already exists (as I want to create a new one). b) Give me a result set I can work with. c) Be able to load the temporary table into a TEDBTable component so the user can edit the data, and d) Perform queries (insert / update statements) on the data after the user has changed the data. My questions are: 1) Do I have the correct understanding with Memory vs Temporary tables? 2) Although it works, how do I know that the "Drop Table" statement is working on a temporary table, as opposed to a real table - or are they effectively one and the same as far as each session is concerned? 3) if I remove the 'Prepare Stmt from 'Select *...', etc - from this, can I simply open up DiaryContM from a TEDBTable component, make changes, and then run an update script using data in that table as a result? 4) I have a statement that can achieve the same result (as below). The difference (as I understand it) is that the statement will not delete the table if it already exists. This statement is easier for me to use / understand, but I'm guessing that I would need to use a script anyway (or something) to detect if the table already exists to delete it first. Is one approach preferred / best practice over another? Create Temporary Table Tmp123 as Select True as Selected, GrowerID, GrowerNGR, Tonnage, CommodityID, VarietyID, GradeID, Price, N.Code, N.Company From Diary D left outer join Names N on (N.ID = D.GrowerID) where (True) /*1*/ and ((D.ID = 13) or (D.ID = 14) or (D.ID = 15)) WITH DATA Thanks & Regards Adam |
Wed, Sep 25 2013 4:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Before I go too much further, I was just wanting to confirm that I'm >doing the right thing with my temporary table. > >It's my understanding that temporary tables are 'per user' and can not >be shared? (That I can use them for whatever I want, not worrying about >conflicts), and in comparison Memory tables are like a real table in the >database and can be shared amongst users? Almost, you have to add in wether its C/S or F/S. If its C/S you're right, but you can have temporary memory tables as well, if its F/S then all memory tables are per user. Memory is just another database unlike in DBISAM. >1) Do I have the correct understanding with Memory vs Temporary tables? See above >2) Although it works, how do I know that the "Drop Table" statement is >working on a temporary table, as opposed to a real table - or are they >effectively one and the same as far as each session is concerned? You shouldn't be able to create a temporary table with the same name as another table in the database - try it in EDBManager and see (I just did >3) if I remove the 'Prepare Stmt from 'Select *...', etc - from this, >can I simply open up DiaryContM from a TEDBTable component, make >changes, and then run an update script using data in that table as a result? Once the table is there you can treat it as you would any other table, so since you've created the table you will be able to do with it as you wish >4) I have a statement that can achieve the same result (as below). The >difference (as I understand it) is that the statement will not delete >the table if it already exists. This statement is easier for me to use / >understand, but I'm guessing that I would need to use a script anyway >(or something) to detect if the table already exists to delete it first. >Is one approach preferred / best practice over another? You can't create a table if its already there so your choice becomes "do I use a script or two queries" - your choice - who cares Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name. Roy Lambert [Team Elevate] 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 |
Wed, Sep 25 2013 11:42 PM | Permanent Link |
Barry | Adam,
>> PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; And if you want to check for the existence of a temporary table, you would use: PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?'; Barry (Version 2.13 B2) |
Thu, Sep 26 2013 3:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry / Adam
Or if you know you want to drop it if its there just use an exception block. BEGIN EXECUTE IMMEDIATE 'DROP table'; EXCEPTION END; Roy Lambert [Team Elevate] |
Thu, Sep 26 2013 8:07 PM | Permanent Link |
Adam H. | Hi Roy,
Thanks for that. So to clarify: > Almost, you have to add in wether its C/S or F/S. If its C/S you're > right, but you can have temporary memory tables as well, if its F/S > then all memory tables are per user. Memory is just another database > unlike in DBISAM. - Temporary Tables will always be per user - never shared. - Memory tables can be shared on C/S, but not on F/S >> 2) Although it works, how do I know that the "Drop Table" statement is >> working on a temporary table, as opposed to a real table - or are they >> effectively one and the same as far as each session is concerned? > > You shouldn't be able to create a temporary table with the same name as another table in the database - try it in EDBManager and see (I just did Thanks. So basically - temporary tables and real tables are identical except that tempory tables are: - per user, and - cleaned up when the session finishes. > Once the table is there you can treat it as you would any other table, so since you've created the table you will be able to do with it as you wish Excellent > You can't create a table if its already there so your choice becomes "do I use a script or two queries" - your choice - who cares Interesting. So, the only thing really stopping me from using a DBISam type solution is the fact that queries don't support multiple statements within the same SQL seperated by a semi column? (As in, I just need multiple phyical TEDBQuery components instead of one?) > Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name. Thanks. One day I'm going to learn what everything means in those scripts, instead of just relying on copy and paste. Thanks for your help! Adam. |
Thu, Sep 26 2013 8:09 PM | Permanent Link |
Adam H. | Thanks Barry and Roy,
I guess when it comes to SQL I like the idea of being able to run the same SQL in the EDBManager. (For debugging and building). The Exception (which I have used) stops the script and you have to continue in EDBManager, so the non-exception script (whilst longer) might be neater in the end... Cheers Adam. |
Fri, Sep 27 2013 3:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Thanks. So basically - temporary tables and real tables are identical >except that tempory tables are: > >- per user, and > >- cleaned up when the session finishes. I'm not sure if its when the session closes or when the app closes. I use memory tables partly from DBISAM habits and partly because even if the app crashes they get "cleaned up". >Interesting. So, the only thing really stopping me from using a DBISam >type solution is the fact that queries don't support multiple statements >within the same SQL seperated by a semi column? >(As in, I just need multiple phyical TEDBQuery components instead of one?) Almost The fact that a DBISAM style script wouldn't respond well to exceptions and has no facilities for branching / conditional statements may also have something to do with it. >> Personally, what I'd do is use the script Tim gave me ages ago (yet another copy below my sig) and simply pass it the statement and table name. > >Thanks. One day I'm going to learn what everything means in those >scripts, instead of just relying on copy and paste. To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy. Roy Lambert |
Fri, Sep 27 2013 7:38 PM | Permanent Link |
Adam H. | Hi Roy,
>> Thanks. So basically - temporary tables and real tables are identical >> except that tempory tables are: >> >> - per user, and >> >> - cleaned up when the session finishes. > > I'm not sure if its when the session closes or when the app closes. I use memory tables partly from DBISAM habits and partly because even if the app crashes they get "cleaned up". I see. I have a few concerns with EDB memory tables: 1) That memory tables can be shared between other users or instances of the application if the client is running C/S. 2) That they operate differently between F/S and C/S. I would prefer my applications to work the same if I switch from one to the other. These are why I'm looking at using temporary tables. The requirements I have is for a private temporary result set (or sets) to be created that I can work with and then have dropped at the end. I don't want them to remain if the app crashes, and I definitely don't want them to be available shared amongst multiple instances of the application or users. (Just incase two users are running the same procedure but for different data at the same time). As I understand it, temporary tables are the only tables that I can be assured won't be shared and will be private to that own session, whether F/S or C/S which is what I need. I also expected temporary tables to get cleaned up too, but after reading your reply now I'm not sure. What happens if someone does crash with a temporary table? (I'm assuming if they restart, there's a new session, so a new 'temporary' area - and if so - how does one ever get to 'clean up' the ghost temporary tables? You also mentioned temporary memory tables. Maybe this is what I should be using to have the best of both worlds. (ie, would this ensure since they are temporary that they will be private per user, but being memory they will also automatically clean up in an app crash)? >> Thanks. One day I'm going to learn what everything means in those >> scripts, instead of just relying on copy and paste. > > To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy. It's my understanding that EDB is supposed to use an industry standard for SQL? If that's the case, would I potentially just have to find any examples for other SQL's such as Oracle or MS SQL, or is it not that simple? I mean - it's great to be able to copy examples and tweak them to my requirements, but I spend so much time in SQL ever day (currently almost all with DBISAM) whether it be designing new SQL statements, or doing once off bulk updates to data, etc - I see that I'm really going to need to understand EDB - not just 'get by' for me to be able to be efficient in it's implementation in my future apps. Cheers Adam. |
Sat, Sep 28 2013 3:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>1) That memory tables can be shared between other users or instances of >the application if the client is running C/S. True unless its created as a temporary table >2) That they operate differently between F/S and C/S. I would prefer my >applications to work the same if I switch from one to the other. Use temporary memory tables and they will >I also expected temporary tables to get cleaned up too, but after >reading your reply now I'm not sure. What happens if someone does crash >with a temporary table? (I'm assuming if they restart, there's a new >session, so a new 'temporary' area - and if so - how does one ever get >to 'clean up' the ghost temporary tables? If you think about it memory tables are released because they are part of the applications memory and are returned to the pool when the application closes, even if they were left around a reboot will fix it. Temporary disk tables I just realised I'm not sure of without either Tim telling us or doing some experimenting. If the temporary tables table is held in memory then when the app crashes its gone and it can no longer know what to delete on a proper close and Tim can't allow ElevateDB to go around deleting files it isn't certain it owns. If the temporary tables table is held on disk then the app could clean up the next time it closes. >You also mentioned temporary memory tables. Maybe this is what I should >be using to have the best of both worlds. (ie, would this ensure since >they are temporary that they will be private per user, but being memory >they will also automatically clean up in an app crash)? Yup >>> Thanks. One day I'm going to learn what everything means in those >>> scripts, instead of just relying on copy and paste. >> >> To do that you need a proper manual with worked examples for the language. The OLH is very good but not necessarily organised in a way that makes learning easy. > >It's my understanding that EDB is supposed to use an industry standard >for SQL? If that's the case, would I potentially just have to find any >examples for other SQL's such as Oracle or MS SQL, or is it not that simple? Yes, that's true for the core SQL unless Tim has added an extension (eg IMPORT TABLE) but I don't know how much of the SQL/PSM language is standard. My statement above was wrong. I've just found that if you use the contents for the olh rather than the index as I generally do there is a section for PSM giving all the keywords so you should be able to use that to get up to speed reasonably quickly. Roy Lambert [Team Elevate] |
Sun, Sep 29 2013 6:40 PM | Permanent Link |
Adam H. | Hi Roy,
Thanks again for the help. So, temporary + memory is the way to go for full independent tables that will be cleaned up automatically at some stage regardless of what the application does. It seems to me as though the only reason to go with the memory side of things is because there's no way of dealing with stray temporary tables. (ie, that they can't be deleted automatically and there's no way of knowing if a table is temporary or not). If it wasn't for that, it seems as though temporary tables would be fine. > My statement above was wrong. I've just found that if you use the contents for the olh rather than the index as I generally do there is a section for PSM giving all the keywords so you should be able to use that to get up to speed reasonably quickly. Excellent - thanks for that. I guess I do the same thing as you (I normally use context sensitive help, or the index too). I'll check out the contents help. Cheers Adam. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |