1. David Haas
  2. PowerBuilder
  3. Wednesday, 20 March 2019 20:19 PM UTC

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!

 

David Haas Accepted Answer Pending Moderation
  1. Wednesday, 20 March 2019 21:43 PM UTC
  2. PowerBuilder
  3. # 1

Hi Chris,

How do I get new DML string?  I did do a GetSQLSelect and in debugger copied that out to wordpad/notepad. Did the same command after I changed datalibrary with Replace command, copied that right below original and they appear identical except for the different library names.

You mentioned SetSQLCommand.....are you referring to SetSQLSelect command?  If so I did get the successful return code of 1.

Thank you for your assistance!

Comment
  1. Chris Pollach @Appeon
  2. Thursday, 21 March 2019 14:00 PM UTC
I was just thinking about suggesting that you look at using the DW Modify command to change the Table and update properties of the DWO instead of using the SetSQLSelect. For example ...

DS.Modify ("DataWindow.Table.UpdateTable=''")

DS.Modify("DataWindow.Table.UpdateWhere='' ")

etc ...
  1. Helpful
  1. David Haas
  2. Thursday, 21 March 2019 17:23 PM UTC
Thanks Chris!! This works great!

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
  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 21 March 2019 18:58 PM UTC
Hi David;

That is super great news ... glad to be of help! :-)

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 20 March 2019 21:00 PM UTC
  2. PowerBuilder
  3. # 2

Hi David;

  If you run through your code with the PB Debugger, does the new DML string for the SetSQLSelect() look OK?

Also, does the SetSQLCommand get an OK return code?

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.