I have a datastore dataobject with SQL select that that has datalibrary that precedes the table name like
SELECT PRODDTA.F554700.NAME
PRODDTA.F554700.ID
Now the above points to production library 'PRODDTA'. For testing purposes using different libraries (i.e. TESTDTA), I do a GetSQLSelect. then I do a search on the string and replace the PRODDTA occurrences with TESTDTA.
Then I take string and do a SetSQLSelect. When it comes to doing update it fails with following error:
SQLSTATE = S0002
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - F564706 in QGPL type *FILE not found.
No changes made to database.
INSERT INTO "F564706" ( "ZAEDTY", "ZAEDSQ", "ZAEKCO", "ZAEDOC", "ZAEDCT", "ZAEDLN", "ZAEDSP", "ZAEDBT","ZAFILE", "ZADOCO", "ZADCTO", "ZAKCOO", "ZAANTY", "ZAAN8", "ZAMLNM", "ZAADD1", "ZAADD2", "ZAADD3", "ZAADD4", "ZAADDZ", "ZACTY1", "ZACOUN", "ZAADDS", "ZACRTE", "ZABKML", "ZACTR", "ZATORG", "ZAUSER", "ZAPID", "ZAJOBN", "ZAUPMJ", "ZATDAY", "ZALNID", "ZAGAN8" ) VALUES ( '0', 0, '00020', 600001, 'SH', 0, ' ', '0', 'F47036', 0, '0', '0', '2', 0, 'WALMART 5397 SC-WINONA', '955 E FRONTENAC RD', '', '0', '0', '559870000', 'WINONA', '0', 'MN', '0', '0', '0', '0', '0', '0', '0', 0, 0, 0, 0 )
Note the bolded "F564706" should have TESTDTA in front of it which is causing error. Should be 'INSERT INTO "TESTDTA.F564706"..........'
So by doing the change I did, it appears the Update properties.
Any suggestions would be much appreciated!
DS.Modify ("DataWindow.Table.UpdateTable=''")
DS.Modify("DataWindow.Table.UpdateWhere='' ")
etc ...
The command I added was 'ads_ds.Modify ("DataWindow.Table.UpdateTable= " + "~'" + ls_libtable_name + "~'")' having variable ls_libtable_name = 'TESTDTA.F564706'.
I didn't have to do any other modifies other than this and a huge improvement from the way I was going to do this
That is super great news ... glad to be of help! :-)
Regards ... Chris