ProblemS with LOAD statement in 4gl

I see from an earlier thread (a little over 9 years ago) that I cannot PREPARE a LOAD or UNLOAD statement. Annoying, but there seems to be a reasonable workaround: > LET selstmnt = "INSERT INTO ", tabl_name CLIPPED > LOAD FROM filename selstmnt Problem 1: This seems to have an implicit transaction since it closes my cursor I've now declared it "WITH HOLD", but I expect that I may have to change that cursor to something not as simple and I won't be able to use "WITH HOLD". Problem 2: SQLCA.SQLCODE is zero after the INSERT even though it is failing How am I supposed to do error checking on this? (A klugey way just came to mind, but I don't like it: Try selecting what you just inserted) Just in case anyone has a better idea on how to do this, I'm trying to copy certain records in our database and change 1 of the key values at the same time. To oversimplify the scenario, let's say store_id is part of the primary key for a whole bunch of tables. I want to add a whole set of records that matches store_id = 1, except it will have store_id = 2. There are enough tables that I'm planning to select all the tables with a store_id column, build up a SELECT statement replacing the actual store_id column with the new store_id (and any serial keys with zero), unload it and reload it. (I wanted to do an INSERT INTO .... SELECT FROM...., but you can't do that if you're selecting from the same table you're inserting into).

Cartlon Shew wrote: > I see from an earlier thread (a little over 9 years ago) that I cannot > PREPARE a LOAD or UNLOAD statement. > > Annoying, but there seems to be a reasonable workaround: > > >> LET selstmnt = "INSERT INTO ", tabl_name CLIPPED >> LOAD FROM filename selstmnt> > > Problem 1: This seems to have an implicit transaction since it > closes my cursor > > I've now declared it "WITH HOLD", but I expect that I may have to > change that cursor to something not as simple and I won't be able to > use "WITH HOLD". > > > > Problem 2: SQLCA.SQLCODE is zero after the INSERT even though it > is failing > > How am I supposed to do error checking on this? > > (A klugey way just came to mind, but I don't like it: Try selecting > what you just inserted) > > > > > Just in case anyone has a better idea on how to do this, I'm trying to > copy certain records in our database and change 1 of the key values at > the same time. > > To oversimplify the scenario, let's say store_id is part of the > primary key for a whole bunch of tables. > > I want to add a whole set of records that matches store_id = 1, except > it will have store_id = 2. > > There are enough tables that I'm planning to select all the tables > with a store_id column, build up a SELECT statement replacing the > actual store_id column with the new store_id (and any serial keys with > zero), unload it and reload it. > > (I wanted to do an INSERT INTO .... SELECT FROM...., but you can't do > that if you're selecting from the same table you're inserting into). Try using a temporary table: - read whatever you need into it. - update fields - copy/update into target You have full control over transctions and error checking plus you will not bother the file system with your data. HTH Michael