I am exploring ways in which Fileman structures can be supported by PIP. One possible method would be to create stored procedures for inserts and updates, and then customize the M code that implements those procedures. The biggest drawback to this method I can see is the potential for the customized code to be overwritten if/when the M routine is regenerated as a result of updates to the stored procedure or possibly updates to PIP. Is there a recommended way to customize stored procdure M routines to prevent this from happening?
Ed
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm not exactly sure what you mean to stored procedure M routines, nor am I familiar with Fileman structures, so this answer might not be quite on target.
Your best bet is to map the Fileman globals to Data-Qwik tables. Based on the global structures, it may be necessary that multiple table definitions are required to fully map a single global. It may also be necessary to use the Query (DBTBL1.QID1) field on the table definition page to manage the mapping.
If you can get the globals mapped to tables, then you should be able to use a combination of triggers and your own Data-Qwik procedures to write necessary code. All of these will be independent of the PIP software, provided there are no naming conflicts.
For now, stay away from names (of tables and procedures) that begin with DB, PSL, SCA, and UC, at least. Your absolutely safest bet would be to use names beginning with "Z", until we further clarify customziation rules, but that may be too restrictive. There will be more definition related to this at some future point.
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was referring to the S000000?.m routine that is automatically generated in response to a "CREATE PROCEDURE <PROC-NAME> AS ..." SQL command (stored in the DBTBLSP table). I was wondering if the code could be customized, and if so, how the customizations could be protect from being over written.
I think that using triggers will solve most of my problems. How would I go about using the Query field? Can you provide an example?
I have noticed that any triggers I create or edit using the WebTools are ignored when I rebuild the filer routine for the table (i.e. the trigger doesn't get compiled). Creating triggers using the console tools is working, even though I get this message:
/tools/misc/filecdt is missing.\n
DBSEDIT will not behave correctly.\n
Please install the appropriate executable.\n
Would the fact that I am editing the triggers in WebTools as the "e0101001" user (which is not in the SCAU table) prevent the trigger code from being included when building the filer routine?
Ed
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'll have to let someone else respond on the S00000n stored procedures, but I do not think they can or should be customized (or, if they are, that they won't get overwritten).
Regarding DBTBL1.QID1, if you run the query SELECT FID,QID1 FROM DBTBL1 WHERE QID1 IS NOT NULL, you'll see a number of DBTBL tables that make use of this feature. If you look at the structure of the global and other tables sharing the same global stucture, you should see what's going on.
With regard to triggers, they are stored in the tables DBTBL7 and DBTBL7D. From Webtools, if you send them to Profile, make sure they actually end up in these tables. If they aren't, then that would explain why the don't get included in the filer (of course, not why they aren't getting there). If they are in those tables and still not getting into the filer, we'll need some more information - probably a sample of what DBTBL7/7D look like for a trigger that's not getting compiled into the filer. [One thing that may be happening is that the header in the Webtools for a trigger contains information - those 0's and 1's - that indicate what type of trigger it is. This can best be seen on the screen associated with editing triggers via function @DBSTRGM. But if all these flags are zero, that could be the issue.]
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The problem with the Webtools trigger appears to related to the IFCOND field in DTBTL7:
^DBTBL("SYSDEV",7,"VMTHUSER","AFTER_DELETE")="Deleted user record|0|0|0|0|0|1||61094|e0101001|""| |"
^DBTBL("SYSDEV",7,"VMTHUSER","BEFORE_INSERT")="New user record|1|0|0|0|0|0||61091|vmacs|37714"
The AFTER_DELETE trigger above won't compile because the last field (IFCOND) contains a space, whereas the BEFORE_INSERT trigger created with the console tools does compile. Also, if I directly set the 12th piece to "", the AFTER_DELETE trigger will compile. However, the IFCOND field is set back to a space whenever I edit and resave the trigger in Webtools. Is there some value I should enter in the properties page of the Webtools editor to make this work?
Ed
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am exploring ways in which Fileman structures can be supported by PIP. One possible method would be to create stored procedures for inserts and updates, and then customize the M code that implements those procedures. The biggest drawback to this method I can see is the potential for the customized code to be overwritten if/when the M routine is regenerated as a result of updates to the stored procedure or possibly updates to PIP. Is there a recommended way to customize stored procdure M routines to prevent this from happening?
Ed
I'm not exactly sure what you mean to stored procedure M routines, nor am I familiar with Fileman structures, so this answer might not be quite on target.
Your best bet is to map the Fileman globals to Data-Qwik tables. Based on the global structures, it may be necessary that multiple table definitions are required to fully map a single global. It may also be necessary to use the Query (DBTBL1.QID1) field on the table definition page to manage the mapping.
If you can get the globals mapped to tables, then you should be able to use a combination of triggers and your own Data-Qwik procedures to write necessary code. All of these will be independent of the PIP software, provided there are no naming conflicts.
For now, stay away from names (of tables and procedures) that begin with DB, PSL, SCA, and UC, at least. Your absolutely safest bet would be to use names beginning with "Z", until we further clarify customziation rules, but that may be too restrictive. There will be more definition related to this at some future point.
Dan
Dan,
I was referring to the S000000?.m routine that is automatically generated in response to a "CREATE PROCEDURE <PROC-NAME> AS ..." SQL command (stored in the DBTBLSP table). I was wondering if the code could be customized, and if so, how the customizations could be protect from being over written.
I think that using triggers will solve most of my problems. How would I go about using the Query field? Can you provide an example?
I have noticed that any triggers I create or edit using the WebTools are ignored when I rebuild the filer routine for the table (i.e. the trigger doesn't get compiled). Creating triggers using the console tools is working, even though I get this message:
/tools/misc/filecdt is missing.\n
DBSEDIT will not behave correctly.\n
Please install the appropriate executable.\n
Would the fact that I am editing the triggers in WebTools as the "e0101001" user (which is not in the SCAU table) prevent the trigger code from being included when building the filer routine?
Ed
Ed,
I'll have to let someone else respond on the S00000n stored procedures, but I do not think they can or should be customized (or, if they are, that they won't get overwritten).
Regarding DBTBL1.QID1, if you run the query SELECT FID,QID1 FROM DBTBL1 WHERE QID1 IS NOT NULL, you'll see a number of DBTBL tables that make use of this feature. If you look at the structure of the global and other tables sharing the same global stucture, you should see what's going on.
With regard to triggers, they are stored in the tables DBTBL7 and DBTBL7D. From Webtools, if you send them to Profile, make sure they actually end up in these tables. If they aren't, then that would explain why the don't get included in the filer (of course, not why they aren't getting there). If they are in those tables and still not getting into the filer, we'll need some more information - probably a sample of what DBTBL7/7D look like for a trigger that's not getting compiled into the filer. [One thing that may be happening is that the header in the Webtools for a trigger contains information - those 0's and 1's - that indicate what type of trigger it is. This can best be seen on the screen associated with editing triggers via function @DBSTRGM. But if all these flags are zero, that could be the issue.]
Dan
The problem with the Webtools trigger appears to related to the IFCOND field in DTBTL7:
^DBTBL("SYSDEV",7,"VMTHUSER","AFTER_DELETE")="Deleted user record|0|0|0|0|0|1||61094|e0101001|""| |"
^DBTBL("SYSDEV",7,"VMTHUSER","BEFORE_INSERT")="New user record|1|0|0|0|0|0||61091|vmacs|37714"
The AFTER_DELETE trigger above won't compile because the last field (IFCOND) contains a space, whereas the BEFORE_INSERT trigger created with the console tools does compile. Also, if I directly set the 12th piece to "", the AFTER_DELETE trigger will compile. However, the IFCOND field is set back to a space whenever I edit and resave the trigger in Webtools. Is there some value I should enter in the properties page of the Webtools editor to make this work?
Ed