SET TRIGGERS FOR table DISABLED error

HI, RDS 7.24.UC5 SQL 6.05.UC1 HP-UX 10.20 I'm trying to disable a trigger using SET TRIGGERS FOR table_name DISABLED and I get unknown error 876 Any idea's -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .

Tony Flaherty wrote: > RDS 7.24.UC5 > SQL 6.05.UC1 OnLine version? > HP-UX 10.20 > > I'm trying to disable a trigger using SET TRIGGERS FOR table_name > DISABLED and I get unknown error 876 A runtime error or a compile time error? Most probably a run-time error -- does the OnLine version you have actually support the syntax? Another way of asking this is "can you do it in DB-Access?" If not, then it won't work. If you can, what are you really doing in the I4GL code? If you're trying to use the table name as a host variable, it will not work - ever. Otherwise, we need to see code...short, succinct code -- probably 10 lines max should reproduce the problem. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

I'm just running the command from isql, running it from dbaccess has worked, DOH, this is the 2nd time I have been caught out by a difference between dbaccess and isql, what exactly is the difference? I always thought that isql had the greater functionality, not dbaccess. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Jonathan Leffler wrote in message <36FED2BC.14F3@earthlink.net>... >Tony Flaherty wrote: >> RDS 7.24.UC5 >> SQL 6.05.UC1 > >OnLine version? > >> HP-UX 10.20 >> >> I'm trying to disable a trigger using SET TRIGGERS FOR table_name >> DISABLED and I get unknown error 876 > >A runtime error or a compile time error? Most probably a run-time >error -- does the OnLine version you have actually support the syntax? >Another way of asking this is "can you do it in DB-Access?" If not, >then it won't work. If you can, what are you really doing in the >I4GL code? If you're trying to use the table name as a host variable, >it will not work - ever. Otherwise, we need to see code...short, >succinct code -- probably 10 lines max should reproduce the problem. > >-- >Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) >Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN >#include <disclaimer.h> >

OK. sigh, I have spent an interesting hour or two trying to solve the following problem; I have a program (i4gl) which accesses two databases simutaneously. one of these databases is always the same (mars) the other can vary for each run depending on an environmental variable, but the schema is always the same. All SQL statements for database mars have mars: preceding the table names, the 2nd database just has the table names and is therefore taken from the currently open DB. E.g. . . . DATABASE mfsl ---- this is done in a function and the db name comes from an environmental variable. . . . SELECT de.detail_serial, dlcust.dlcus_customer FROM mars:detail de, dlcust <------ this table is in the mfsl database :o) WHERE ...... . Now, my program can be run in "Live" or "Test" mode, if running in "Live" mode I want to disable an audit trigger on table mars:detail before I perform any updates on it, by this stage the program has locked the table, so no one else can change it. I've tried various ways of disabling and re-enabling the trigger and fallen foul of problems with not being able to amend a trigger in a different database. My solution is to write a small 4gl which switches the trigger on and off based on a paramater, I can then run this from within my main program. This works fine for disabling the trigger, however when I run it in the tidyup section of my main program to re-enable the trigger it complains about having non exclusive access to the table. In a nutshell; How can I enable/disable a trigger in a different database from within my 4gl program? Can I explicitly close a table from within a 4GL? Versions;- cat $INFORMIXDIR/etc/*-cr INFORMIX-4GL Interactive Debugger Version 6.05.UC1 INFORMIX-4GL Version 6.05.UC1 INFORMIX-4GL Rapid Development System Version 6.05.UC1 INFORMIX-SQL Version 6.05.UC1 INFORMIX-OnLine Dynamic Server Version 7.24.UC5 --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf

Tony Flaherty wrote: > > I'm just running the command from isql, running it from dbaccess has worked, > DOH, this is the 2nd time I have been caught out by a difference between > dbaccess and isql, what exactly is the difference? I always thought that > isql had the greater functionality, not dbaccess. More functionality but a much older syntax parser. Much of the latest SQL feature set is not supported. Basically if OL 4.10 did not have it it is probably not supported in ISQL. Art S. Kagel

Tony Flaherty wrote: > OK. sigh, > > I have spent an interesting hour or two trying to solve the following > problem; Well, now we have enough information that we can probably help you. > I have a program (i4gl) which accesses two databases simutaneously. > One of these databases is always the same (mars) the other can vary > for each run depending on an environmental variable, but the schema > is always the same. OK; the same schema is important, of course. > All SQL statements for database mars have mars: preceding the table > names, the 2nd database just has the table names and is therefore > taken from the currently open DB. E.g. > > . > . > . > DATABASE mfsl ---- this is done in a function and the db name > ---- comes from an environmental variable. > . > . > . > SELECT de.detail_serial, dlcust.dlcus_customer > FROM mars:detail de, > dlcust --<---- this table is in the mfsl database > :o) > WHERE .....> . > . > > Now, my program can be run in "Live" or "Test" mode, if running in > "Live" mode I want to disable an audit trigger on table mars:detail > before I perform any updates on it, by this stage the program has > locked the table, so no one else can change it. > > I've tried various ways of disabling and re-enabling the trigger > and fallen foul of problems with not being able to amend a trigger > in a different database. My solution is to write a small 4gl which > switches the trigger on and off based on a paramater, I can then run > this from within my main program. This works fine for disabling the > trigger, however when I run it in the tidyup section of my main > program to re-enable the trigger it complains about having > non-exclusive access to the table. Does your database have transactions? I didn't think so. You could therefore release the lock by doing UNLOCK TABLE. Alternatively, you could close the current database before running the auxilliary program. > In a nutshell; > How can I enable/disable a trigger in a different database from > within my 4gl program? Since you've got version 6/7 software, you can actually use multiple connections within a single program. Your default connection is created by the DATABASE statement. You could go to the IIUG site and find the I4GL interface code to the ESQL/C CONNECT statements, and then use those to open a new connection to the alternative (non-mars) database, then prepare and execute the SET TRIGGERS statement, then switch back to the default connection, do the rest of your work, and so on. You probably still need to close the default connection and/or release the lock. You may need to use the WITH CONCURRENT TRANSACTIONS option. You may have to create the default connection WITH CONCURRENT TRANSACTIONS too - meaning you may have to do an explicit CONNECT to the non-mars database. > Can I explicitly close a table from within a 4GL? No, but you can release all the locks on it by terminating the transaction, or by explicitly unlocking the table after closing all cursors if you have no transaction log. You should always ensure you have no open cursors on the table; you may even have to free them all to ensure you have no locks on the table. > Versions;- > > cat $INFORMIXDIR/etc/*-cr > INFORMIX-4GL Interactive Debugger Version 6.05.UC1 > INFORMIX-4GL Version 6.05.UC1 > INFORMIX-4GL Rapid Development System Version 6.05.UC1 > INFORMIX-SQL Version 6.05.UC1 > INFORMIX-OnLine Dynamic Server Version 7.24.UC5 Thanks for the version info -- it helps! -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>