Re: Using dbload to update rather than insert?

My original post: : I am trying to load some large flat files into Online using dbload. I have : two seperate files with ID numbers in them. The two files may contain : duplicates and I only allow for unique ID numbers in the table. Is there a : way with dbload to have it UPDATE rather than INSERT. I get these flat : files from our mainframe from time to time and need to update the Informix : tables associated with them. Thanks in advance for any ideas on the : problem. Thanks to replies and suggestions from: Jack Parker (jparker@hpbs3645.boi.hp.com) Colin McGrath (colin@scdipc0.ueci.com) DBLOAD does not have the power to do updates if the record already exists in the table. There are some less than great ways to use DBLOAD to accomplish this but it is best to use 4GL. I wrote the following "quick and dirty" program using the text file stuff from the Informix FAQ. I thought some other Informix newbies might be able to benefit from this. -----begin----load.4gl---------- DATABASE library DEFINE m_temp_exists SMALLINT, update_rec INT, insert_rec INT MAIN DEFINE x CHAR(400), rc SMALLINT, ct INT, open_comm CHAR(50) LET update_rec = 0 LET insert_rec = 0 PROMPT "Enter: cat filename " FOR open_comm IF open_flat(open_comm) THEN LET x = read_flat() WHILE x != "!EOF!" LET rc = patron_load(x) LET ct = ct + 1 LET x = read_flat() END WHILE DISPLAY "Records read:", ct DISPLAY "Records inserted:", insert_rec DISPLAY "Records updated:", update_rec END IF END MAIN FUNCTION patron_load(patron_line) DEFINE patron_line CHAR(400) DEFINE exists SMALLINT, fld1 CHAR(9), fld2 CHAR(2), fld3 CHAR(30), fld4 CHAR(1), fld5 CHAR(30), fld6 CHAR(30), fld7 CHAR(30), fld8 CHAR(30), fld9 CHAR(30), fld10 CHAR(30), fld11 CHAR(30), fld12 CHAR(30), fld13 CHAR(30), fld14 CHAR(30), fld15 CHAR(6), fld16 CHAR(1), fld17 CHAR(1), p_dump RECORD LIKE patron.*, p_patron RECORD LIKE patron.*, p_haddr RECORD LIKE address.*, p_laddr RECORD LIKE address.* LET fld1 = patron_line[1,9] LET fld2 = patron_line[10,11] LET fld3 = patron_line[12,41] LET fld4 = patron_line[42,42] LET fld5 = patron_line[43,72] LET fld6 = patron_line[73,102] LET fld7 = patron_line[103,132] LET fld8 = patron_line[133,162] LET fld9 = patron_line[163,192] LET fld10 = patron_line[193,222] LET fld11 = patron_line[223,252] LET fld12 = patron_line[253,282] LET fld13 = patron_line[283,312] LET fld14 = patron_line[313,342] LET fld15 = patron_line[343,348] LET fld16 = patron_line[349,349] LET fld17 = patron_line[350,350] LET p_patron.id_number = fld1 LET p_patron.card_number = fld2 LET p_patron.name = fld3 LET p_patron.id_status = fld4 LET p_patron.college = fld15 LET p_patron.standing = fld16 LET p_patron.entitlement = fld17 LET p_haddr.id_number = fld1 LET p_haddr.street = fld5 LET p_haddr.city = fld6 LET p_haddr.state = fld7 LET p_haddr.zip = fld8 LET p_haddr.phone = fld13 LET p_haddr.addr_type = "H" LET p_laddr.id_number = fld1 LET p_laddr.street = fld9 LET p_laddr.city = fld10 LET p_laddr.state = fld11 LET p_laddr.zip = fld12 LET p_laddr.phone = fld14 LET p_laddr.addr_type = "L" LET exists = FALSE PREPARE qry1 FROM "SELECT * FROM patron WHERE id_number = ?" DECLARE cur1 CURSOR FOR qry1 OPEN cur1 USING fld1 FETCH cur1 INTO p_dump.* IF status = NOTFOUND THEN LET exists = FALSE ELSE LET exists = TRUE END IF CLOSE cur1 IF exists = FALSE THEN INSERT INTO patron VALUES (p_patron.*) INSERT INTO address VALUES (p_haddr.*) INSERT INTO address VALUES (p_laddr.*) LET insert_rec = insert_rec + 1 ELSE UPDATE patron SET * = p_patron.* WHERE id_number = p_patron.id_number UPDATE address SET * = p_haddr.* WHERE id_number = p_haddr.id_number AND addr_type = p_haddr.addr_type UPDATE address SET * = p_laddr.* WHERE id_number = p_laddr.id_number AND addr_type = p_laddr.addr_type LET update_rec = update_rec + 1 END IF RETURN TRUE END FUNCTION FUNCTION open_flat(l_unix_cmd) ... (see appendix to FAQ for code) END FUNCTION FUNCTION read_flat() ... (see appendix to FAQ for code) END FUNCTION -----end------load.4gl---------- -- +----------------------------+-----------------------------------------------+ | BRIAN K. HOLMAN | E-Mail: brian_holman@byu.edu | | Programmer/Systems Analyst | URL: http://library.byu.edu/staff/bkh.html | | Library Automations | Mail: 2330 HBLL, Provo, UT 84602 | | Brigham Young University | Phone: (801) 378-8162 | +----------------------------+-----------------------------------------------+