Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Newbie questions |
Thu, Apr 23 2009 11:26 PM | Permanent Link |
"James Relyea" | I have a bunch of questions and am hoping someone could help clarify:
Triggers a.. OLDROW & NEWROW. Which does what? I'm pretty sure I already found = out I can access newrow fields during inserts. That was pretty clear to = me. What about with update & delete triggers? b.. I'm not sure how trigger conditions work, or what the syntax might = be.=20 c.. Are just the table records visible to trigger conditions or can I = get to the oldrow/newrow fields too? d.. Can triggers be disabled? If not, is there something like a END, = BREAK, ABORT keyword that would exit the trigger and have the underlying = data updates still take effect (raising exceptions won't work cuz it'll = bubble to the calling object). I'm thinking that might be helpful with = troubleshooting along the way if a trigger looks suspect as a root cause = e.. If I have multiple after update triggers as an example, is there = any particular order to how they fire?=20 f.. Are there any plans for an "instead of" trigger type? Security a.. If I grant a group execute permissions on a stored proc that = modifies records, they also need matching table permissions to make the = changes correct? Or does security drill down where "I'm allowed to = execute this stored proc that changes data on TableX, so I am therefore = allowed to make these edits on TableX". Kinda of like permission = chaining with MS SQL perhaps? b.. I'm not even close to trying to reproduce my schema yet. Is there = a relatively fast way for me to create a security group, grab an object = type (say tables) and slap the right permissions on it in a loop = fashion? Maybe a collection of objects? Tables a.. What does a "check" constraint do? Might someone have an example = of how it was applied or of a practical application for it? I can't find = much in the help file for them General a.. Does the "refresh" button refresh the entire explorer or just the = branch highlighted? b.. What should I do (if anything) to release resources on the server = to clean up stored procs etc? I've tried unpreparing but it flushed my = data being returned, so I know that's not the right way. I've not been = doing anything to release resources (like nulling variables for a = cleanup etc with stored procs) c.. I've installed multiple versions of EDB so far 2.02.b8->b11. I'm = not seeing any sample apps with any of them. I've seen forum posts = pointing to a subdir in the installation path, but none of mine have it. = Is there a place that I can download them from? d.. What's a "store"? How do they work? Anyone have any real world = examples to share? I imported 40+ tables from another DB's export as CSV = files. They're being referenced in a store though I am not sure that I = implemented it as it were intended. e.. I know the database gets locked when a transaction is started by = default. When I tested it, I used my app to start a transaction, and = left it on a stop statement. I added a new row to a table using EDB Mgr, = and it sat there until I ended the transaction in my app. Does anyone = know if that's a normal behavior that I should see, or is the "wait til = available" EDB Mgr demonstrated is specific to that app? My CPU wasn't = fluttering at all, so it didn't look like a constant retry or a periodic = retry sort of thing either. Thanks for any feedback. It is appreciated. jr |
Fri, Apr 24 2009 12:38 PM | Permanent Link |
Fernando Dias Team Elevate | James,
Here are some answers to the triggers part: > _Triggers_ > > * OLDROW & NEWROW. Which does what? I'm pretty sure I already found > out I can access newrow fields during inserts. That was pretty > clear to me. What about with update & delete triggers? OLDROW and NEWROW are special row identifiers, that can be accessed from inside trigers, that reference respectively the current and the new values of the row being changed. The individual column values can be referenced using dot notation, for example: NEWROW.Name references the new value for the name column. The following table shows the various possibilities where OLDROW and NEWROW can occour : Type of Trigger OLDROW NEWROW --------------------+-------------------------+------------------------ BEFORE/ERROR INSERT | (*) | Values to insert AFTER INSERT ...... | (*) | Inserted values (R) BEFORE/ERROR UPDATE | Values before update (R)| Values after update AFTER UPDATE | Values before update (R)| Values after update (R) BEFORE/ERROR DELETE | Values before delete (R)| (*) AFTER DELETE | Values before delete (R)| (*) --------------------+-------------------------+------------------------ (*) The identifiers doesn't exist in this context. (R) The identifier is read-only in this context. > * I'm not sure how trigger conditions work, or what the syntax might > be. Trigger conditions are boolean sql expressions, so the syntax is the syntax of sql boolean expressions, except for one detail: Trigger conditions can't reference columns of other tables. If the expression evaluates to false at the moment the trigger fires, no code will be executed, just as if the trigger didn't fire. > * Are just the table records visible to trigger conditions or can I > get to the oldrow/newrow fields too? You can use OLDROW and NEWROW in a trigger conditions as long as they exist in the context of that trigger type. In fact, it's the only way to reference a column inside a trigger condition. > * Can triggers be disabled? If not, is there something like a END, > BREAK, ABORT keyword that would exit the trigger and have the > underlying data updates still take effect (raising exceptions > won't work cuz it'll bubble to the calling object). I'm > thinking that might be helpful with troubleshooting along the > way if a trigger looks suspect as a root cause As far as I know, they can't be disabled. Raising an exception will cancel the operation (insert, delete, etc.) and if I understood correctly that exactly the opposite of what you want. > * If I have multiple after update triggers as an example, is there > any particular order to how they fire? I really don't know. You must wait for Tim's answer. > * Are there any plans for an "instead of" trigger type? I think there are, but the only person that can confirm this is Tim. -- Fernando Dias [Team Elevate] |
Fri, Apr 24 2009 12:57 PM | Permanent Link |
Fernando Dias Team Elevate | James,
Now, security : > _Security_ > > * If I grant a group execute permissions on a stored proc that > modifies records, they also need matching table permissions to > make the changes correct? Or does security drill down where "I'm > allowed to execute this stored proc that changes data on TableX, > so I am therefore allowed to make these edits on TableX". Kinda of > like permission chaining with MS SQL perhaps? No. Stored procedures are executed using the "System" user as the current user. This is deliberately done this way in order to permit the procedure to access resources that the executing user may not have access to. > * I'm not even close to trying to reproduce my schema yet. Is there > a relatively fast way for me to create a security group, grab an > object type (say tables) and slap the right permissions on it in a > loop fashion? Maybe a collection of objects? No. You must set the permissions for each object. However, some privileges are automatically set when you create the objects. For example, when you create a table, full permissions are automatically granted to the users "System" and "Administrator" and to the role "Administrators" and the "Delete", "Insert", "Update" and "Select" privileges are automatically granted to the role "Public". -- Fernando Dias [Team Elevate] |
Fri, Apr 24 2009 1:58 PM | Permanent Link |
Fernando Dias Team Elevate | James,
> _Tables_ > > * What does a "check" constraint do? Might someone have an example > of how it was applied or of a practical application for it? I > can't find much in the help file for them Check constraints allows the specification of rules that define if a row is valid or not. They are defined when the table is created(or added altering the table). For example: ----------------------------------------- CREATE TABLE Tbl1 ( Name VARCHAR(30) , Age INTEGER CHECH( Age>=0 AND Age<200 ), Adress VARCHAR(30) ) ------------------------------------------ This check constraint forces the values of "Age" to be in the range 0..200. The condition (Age>=0 AND Age<200) is automatically tested by EDB when a row is altered or inserted and an exception is raised if the expression evaluates to false. This is called a "column constraint" because it was defined as part of the column definition and is a syntactic shorthand for the "table constraint" shown in the example below: ---------------------------------------------------- CREATE TABLE Tbl1 ( Name VARCHAR(30) , Age INTEGER, Adress VARCHAR(30), CONSTRAINT "Check_Age" CHECK( Age>=0 AND Age<200 ) ) ---------------------------------------------------- EDB automatically rewrites all column check constraints as table check constraints. -- Fernando Dias [Team Elevate] |
Fri, Apr 24 2009 2:31 PM | Permanent Link |
Fernando Dias Team Elevate | James,
> _General_ > > * Does the "refresh" button refresh the entire explorer or just the > branch highlighted? I think it refreshes the selected sub-tree and all sub-trees whose root is at the same level, but I'm not sure... > * What should I do (if anything) to release resources on the server > to clean up stored procs etc? I've tried unpreparing but it > flushed my data being returned, so I know that's not the right > way. I've not been doing anything to release resources (like > nulling variables for a cleanup etc with stored procs) Nothing - it's all automatic. However I think it is a good practice to close and/or unprepare the cursors that are no longer in use, if they are defined WITHOUT RETURN, of course. > * I've installed multiple versions of EDB so far 2.02.b8->b11. I'm > not seeing any sample apps with any of them. I've seen forum posts > pointing to a subdir in the installation path, but none of mine > have it. Is there a place that I can download them from? They are in the following directory: C:\Program Files\ElevateDB VCL Client-Server\Version 2\Dev Studio (Delphi) 2006\examples You must adjust the path according to the Delphi version you are using. > * What's a "store"? How do they work? Anyone have any real world > examples to share? I imported 40+ tables from another DB's export > as CSV files. They're being referenced in a store though I am not > sure that I implemented it as it were intended. A "store" is simply a named storage area where files can be located, or if you prefer, a "store" is an alias to a directory on the hard disk. A store can be local or remote. If it is local, it "points" to a directory in the local hard disk; if it is a remote store, it points to a local store in a remote server, referenced by an IP or host name. The statements BACKUP, RESTORE, IMPORT, EXPORT, SAVE UPDATES and LOAD UPDATES use stores as the source or destination of the files they deal with. Files in a store can also be manipulated using the statements COPY FILE, DELETE FILE and RENAME FILE. > * I know the database gets locked when a transaction is started by > default. When I tested it, I used my app to start a transaction, > and left it on a stop statement. I added a new row to a table > using EDB Mgr, and it sat there until I ended the transaction in > my app. Does anyone know if that's a normal behavior that I should > see, or is the "wait til available" EDB Mgr demonstrated is > specific to that app? My CPU wasn't fluttering at all, so it > didn't look like a constant retry or a periodic retry sort of > thing either. That's normal, and that's why transactions must be committed or rolledback as soon as possible. Also, it's not not simply "wait til available" - after a timeout period an exception is raised and the insert/alter is aborted. -- Fernando Dias [Team Elevate] |
Fri, Apr 24 2009 3:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< d.. Can triggers be disabled? If not, is there something like a END, BREAK, ABORT keyword that would exit the trigger and have the underlying data updates still take effect (raising exceptions won't work cuz it'll bubble to the calling object). I'm thinking that might be helpful with troubleshooting along the way if a trigger looks suspect as a root cause >> You can't disable them right now, but that is on the list along with specifying their order of execution. << f.. Are there any plans for an "instead of" trigger type? >> Yes. << a.. If I grant a group execute permissions on a stored proc that modifies records, they also need matching table permissions to make the changes correct? Or does security drill down where "I'm allowed to execute this stored proc that changes data on TableX, so I am therefore allowed to make these edits on TableX". Kinda of like permission chaining with MS SQL perhaps? >> As Fernando indicated, right now EDB executes procedures under the System user, but we will be allowing you to specify which user to execute the procedure as in an upcoming version. << b.. I'm not even close to trying to reproduce my schema yet. Is there a relatively fast way for me to create a security group, grab an object type (say tables) and slap the right permissions on it in a loop fashion? Maybe a collection of objects? >> Well, you can define a role and then set the permissions for it on various objects, but you have to do so for each table or object that you want to apply the permissions to. << a.. Does the "refresh" button refresh the entire explorer or just the branch highlighted? >> It refreshes the current branch, including all sub-branches that are open. << b.. What should I do (if anything) to release resources on the server to clean up stored procs etc? I've tried unpreparing but it flushed my data being returned, so I know that's not the right way. I've not been doing anything to release resources (like nulling variables for a cleanup etc with stored procs) >> They don't take up that much in terms of resources, so just leave them alone and they will get cleaned up automatically when you dispose of the EDBCommand, etc. that called them. << c.. I've installed multiple versions of EDB so far 2.02.b8->b11. I'm not seeing any sample apps with any of them. I've seen forum posts pointing to a subdir in the installation path, but none of mine have it. Is there a place that I can download them from? >> The DAC products don't have any samples yet. The main reason for this is that, like with ODBC, the .NET Data Provider architecture is pretty standardized, with the differences for each provider falling into the SQL/engine features realm. However, I'll be porting the CDCollector sample application shortly that will give one a good idea of how to create an application use the EDB .NET Data Provider. << d.. What's a "store"? How do they work? Anyone have any real world examples to share? I imported 40+ tables from another DB's export as CSV files. They're being referenced in a store though I am not sure that I implemented it as it were intended. >> You can read about stores here: http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=20 << e.. I know the database gets locked when a transaction is started by default. When I tested it, I used my app to start a transaction, and left it on a stop statement. I added a new row to a table using EDB Mgr, and it sat there until I ended the transaction in my app. Does anyone know if that's a normal behavior that I should see, or is the "wait til available" EDB Mgr demonstrated is specific to that app? My CPU wasn't fluttering at all, so it didn't look like a constant retry or a periodic retry sort of thing either. >> Yes, that's the normal behavior. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Apr 24 2009 9:53 PM | Permanent Link |
"James Relyea" | Thank you Fernando & Tim. Your level of detail is most definitely
appreciated. jr |
Tue, Apr 28 2009 1:51 PM | Permanent Link |
"James Relyea" | > You can't disable them right now, but that is on the list along with=20
> specifying their order of execution. >=20 Some of my triggers run some last minute validation checks before = deleting any records. However, I need to override this when testing with = mass deletes of rows (basically scrapping the validation checks). It looks like if I put in a trigger condition of "false=3Dtrue", the = trigger does not execute.=20 Please correct me if I'm missing something, but my thoughts are that = leveraging an impossible "true" condition does just about the same as = disabling a trigger, no? Thanks jr |
Tue, Apr 28 2009 2:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< It looks like if I put in a trigger condition of "false=true", the trigger does not execute. Please correct me if I'm missing something, but my thoughts are that leveraging an impossible "true" condition does just about the same as disabling a trigger, no? >> Yes, that would also do the trick. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |