Board index Informix

Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

Post by Dario Ross » Sun, 31 Dec 1899 09:00:00



Hi,

I don't if its possible to do the following on Informix 7.30 NT I get a SQL
ERROR -201, probably wrong version or syntax error.
Is this supported on IDS2000 ?

create trigger TGU_getrws
    UPDATE of statcond ON COM_RWS_LST
    REFERENCING NEW AS val
    FOR EACH ROW WHEN(val.statcond = 'L')
    (UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
WHERE statcond = 'L')

OR

create trigger TGU_getrws
    UPDATE of statcond ON COM_RWS_LST
    REFERENCING NEW AS val
    FOR EACH ROW WHEN(val.statcond = 'L')
    (EXECUTE PROCEDURE P_tst_rws())

CREATE PROCEDURE P_tst_rws()
        ...
        UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from
COM_RWS_LST WHERE statcond = 'L'
END PROCEDURE;

Thanks for any comment on this

Dario

 
 
 
Top

Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

Post by Rudy Fernande » Sun, 31 Dec 1899 09:00:00


Your problem is related to the UNLOAD command which is a dbaccess command, not
an SQL statement. This prevents it from being used in Triggers or Stored
procedures.

In theory, you could invoke the SYSTEM command, in  an SP, to unload your data,
but the SYSTEM command is quite expensive. Your best bet is to store such
inconsistencies in a table, unloading it once in a while through a separate
process or reporting directly from that table.

Rudy


> Hi,

> I don't if its possible to do the following on Informix 7.30 NT I get a SQL
> ERROR -201, probably wrong version or syntax error.
> Is this supported on IDS2000 ?

> create trigger TGU_getrws
>     UPDATE of statcond ON COM_RWS_LST
>     REFERENCING NEW AS val
>     FOR EACH ROW WHEN(val.statcond = 'L')
>     (UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
> WHERE statcond = 'L')
> ...
>         ...
>         UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from
> COM_RWS_LST WHERE statcond = 'L'
> END PROCEDURE;
> Dario


 
 
 
Top

Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

Post by Art S. Kage » Sun, 31 Dec 1899 09:00:00


Not possible because the UNLOAD command is a built-in verb in dbaccess,
isql, 4gl, and Jonathan Leffler's sqlcmd and NOT a command that the database
server understands or even sees.  

Art S. Kagel


> Hi,

> I don't if its possible to do the following on Informix 7.30 NT I get a SQL
> ERROR -201, probably wrong version or syntax error.
> Is this supported on IDS2000 ?

> create trigger TGU_getrws
>     UPDATE of statcond ON COM_RWS_LST
>     REFERENCING NEW AS val
>     FOR EACH ROW WHEN(val.statcond = 'L')
>     (UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
> WHERE statcond = 'L')

> OR

> create trigger TGU_getrws
>     UPDATE of statcond ON COM_RWS_LST
>     REFERENCING NEW AS val
>     FOR EACH ROW WHEN(val.statcond = 'L')
>     (EXECUTE PROCEDURE P_tst_rws())

> CREATE PROCEDURE P_tst_rws()
>         ...
>         UNLOAD TO 'D:\data\blabla' DELIMITER '|' SELECT statid from
> COM_RWS_LST WHERE statcond = 'L'
> END PROCEDURE;

> Thanks for any comment on this

> Dario

 
 
 
Top

1. memory leaks doing unloads

Hi folks,

Env:  4.10 4GL and 4.10 SE  on SCO Unix

I am writing a program to archive data from the customer's database into
one archive file.  The user selects the customers to archive and I
write unload files and copy all the files into 1 large archive.

My problem is that the user was archiving 6000 master records (and lots of
detail records) and ran out of memory.  The core dump was 15MB in size.

I think I have tracked down the offending statement.  It is the unload.

I have an array of table names and do something like the following:

  define i,j smallint
       tabnames array[50] of char(18),
       str char(200),
       fname char(14)

  let fname = "junk"
  for i = 1 to j
      let str = "select * from ",tabnames[i] clipped,
                " where prospno = \"",pr_prospect.prospno clipped,"\"",
      unload to fname str
  end for

  Other unloads must use joins in the select statement linking 2 tables
  together to unload specific records from one table, but the method of
  executing the unload statement is the same.  Build a str variable and
  unload to fname str

  As the program runs, the core memory size gets larger and larger.....

  By removing the unload commands and simulating the unloads, the rest of
  the program hardly grows in core size at all.

  Does anyone know how to dealloc the memory being sucked up by the unload
  command?  Or, any better ways to export selected bits of tables?

  Dbexport doesn't allow specific records to be unloaded.

  I could write reports, parsing out the syscolumn entries to build lines
  that look like "unload" lines.

Thanks for reading this far.  Any ideas?

------------------------------------------------------------------------------
Alan Goodman                         |    Data Systems Support
Sr.Programmer/Analyst                |    1228 Shelly Ct.

bangpath: uunet.uu.net!dssmktg!alan  |    Voice 714-771-0454  Fax 714-771-3028
------------------------------------------------------------------------------

2. VB and SQL Server

3. OUTPUT TO file and UNLOAD do not work for EXECUTE PROCEDURE

4. Suggestions on Importing .DBF files into SQLServer

5. Can this be done with Stored Procedure/Trigger

6. truncating tables

7. how to unload records using informix stored procedure

8. UNLOAD statement in stored procedure?

9. Using unload in a store procedure

10. unload to/load from a text file

11. Unload to a file


All times are UTC
Board index