Adam (DF)
Active Member
Need *new* workaround to update columnx = \'stuff\' where columnx = \'junk\'
I need a new workaround. I need to update tableA set columnX = 'stuff' where columnX = 'junk'. It seems the old workarounds aren't viable, and I'll tell you why:
1. I need to do this with the same column in some 70 distinct tables. So I could perform the old select, fetch next, update rope-a-dope, but it would require 1000 lines of code or so. I don't want to do that.
So dynamic SQL is in order...
2. I have written my own ODBC interface to OneWorld, but I cannot use that because it would require giving the users a read-write account on the database. Also, the ODA driver does not allow updates.
3. I cannot use a database trigger because "that is code outside of OneWorld."
4. I tried to hack the JDB_UpdateTableColsExtended function, but I it pays too much attention to what key you tell it to use, so the SQL it generates cannot update anything.
I'm at a loss for ideas here, so I'm about to jump off the ledge and start writing 1000 lines of select, fetch next, update, and will end up with functions that require a modification anytime the users decide to add or subtract a table from the update list.
So I'm hoping that someone on the list will have the creative juices flowing today and could offer something I haven't thought of yet. Any ideas? Anyone?
Yours truly,
Adam
OneWorld Xe
AS/400-DB/2
I need a new workaround. I need to update tableA set columnX = 'stuff' where columnX = 'junk'. It seems the old workarounds aren't viable, and I'll tell you why:
1. I need to do this with the same column in some 70 distinct tables. So I could perform the old select, fetch next, update rope-a-dope, but it would require 1000 lines of code or so. I don't want to do that.
So dynamic SQL is in order...
2. I have written my own ODBC interface to OneWorld, but I cannot use that because it would require giving the users a read-write account on the database. Also, the ODA driver does not allow updates.
3. I cannot use a database trigger because "that is code outside of OneWorld."
4. I tried to hack the JDB_UpdateTableColsExtended function, but I it pays too much attention to what key you tell it to use, so the SQL it generates cannot update anything.
I'm at a loss for ideas here, so I'm about to jump off the ledge and start writing 1000 lines of select, fetch next, update, and will end up with functions that require a modification anytime the users decide to add or subtract a table from the update list.
So I'm hoping that someone on the list will have the creative juices flowing today and could offer something I haven't thought of yet. Any ideas? Anyone?
Yours truly,
Adam
OneWorld Xe
AS/400-DB/2
