Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (8)

Resolved Datawindow SetSqlSelect problem

  1. Issue
0
Votes
Undo
  1. Marcin Jurkowski
    Marcin Jurkowski
  2. PowerServer Mobile (Obsolete)
  3. Monday, 30 September 2019 10:29 AM UTC

Hi,

I have the below simple code that executes when I click on a button.
This is on Appeon Mobile App (PB 2017 R3 1858).

ls_new_sql gets the sql from DW and adds extra parameters to WHERE clause.

The proble I'm have:
When I click the button for the FIRST time this will show me the new SQL (in messagebox) but Retrieve will use the SQL from DW without extra parameters (not the SQL I see in the messagebox).

When I click the button NEXT time the Retrieve will use the code from ls_new_sql.

Why this doesn't work first time?

Messagebox('1',ls_new_sql)
dw_1.Reset()
dw_1.SetSqlSelect(ls_new_sql)
dw_1.SetTransObject(sqlca)
dw_1.Retrieve()
dw_1.Object.DataWindow.HideGrayLine='Yes'
dw_1.Show()

Regards,
Marcin

  1. DataWindow
  2. PowerBuilder
  3. PowerServer Mobile
Appeon Workspace on Android 9 device
PowerServer Mobile Printing
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 12:16 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # Permalink

Maybe it would be best if you could give us a small sample application for us to reproduce the problem.

Does Modify() return an empty string (=success) or something like an error?

One last thing: When you edit the source code of your datawindow, on the first line, do you see:

release 17;

or some older version?

If older, then edit the datawindow and do a small change, like moving a column a bit to the right and back and then Save.

Check the edited source code to make sure it's "release 17;" now.

Sorry but that's all I can think of

regards

Comment
There are no comments made yet.
Responses (8)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 10:52 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 1

Not sure if I understand well.

What does SetSQLSelect() return ?

 

Retrieval arguments?

From the PB help:

Limitations to using SetSQLSelect

Use SetSQLSelect only if the data source for the DataWindow object is a SQL SELECT statement without retrieval arguments and you want PowerBuilder to modify the update information for the DataWindow object:

dw_1.Modify("DataWindow.Table.Select='select...'")

Modify does not verify the SELECT statement or change the update information, so it is faster but more susceptible to user error. Although you can use Modify when arguments are involved, this is not recommended because of the lack of verification.

 

Does that clear anything?

Maybe we need more information like the Select statement your assigning.

Cheers.

Comment
Marcin Jurkowski
  1. Marcin Jurkowski
  2. Monday, 30 September 2019 11:03 AM UTC
Hi Migueal,

The problem is that SetSqlSelect doesn't set the code from ls_new_sql



For example, I have something like this:

ls_sql = dw_1.GetSQLSelect()

ls_new_sql = ls_sql + " And ColumnC = 'ABC'"



When I click the button FIRST time dw_1.SetSqlSelect(ls_new_sql) doesn't set the new SQL (ls_new_sql).

dw_1.Retrieve() executes only the code from DW (the same as in ls_sql).

When I click the button again, everything works as expected.



I don't understand why this doesn't work after the first click.
  1. Helpful
Marcin Jurkowski
  1. Marcin Jurkowski
  2. Monday, 30 September 2019 11:05 AM UTC
P.S ls_sql will be something like "Select columnA, columnB, columnC From TableA Where 1=1"
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 11:17 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 2

What if you compare the 2 strings, are they the same?

 

first time:

ls_sql = dw_1.GetSQLSelect()

with the second time:

ls_sql = dw_1.GetSQLSelect()

 

Does your dw have retrieval arguments?

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 11:17 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 3

Sorry, for the corrections...

Do you do a SetTransObject(sqlca) after the SetSQLSelect() ?

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 30 September 2019 11:21 AM UTC
Duh .. I see that indeed you do that in the code that you pasted.

So please compare the first and second time results of doing GetSQLSelect()
  1. Helpful
There are no comments made yet.
Marcin Jurkowski
Marcin Jurkowski Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 11:23 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 4

Yes, I set SqlTransObject as in the main example.
I don't use any retrieval arguments on dw_1.Retrieve().
GetSQLSelect() is the same every time.

Also this works fine when I run the code from IDE.
It doesn't work when I deploy the app to the server/device. It looks like a bug to me?

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 30 September 2019 11:34 AM UTC


Is there anything (an existing datawindow object) assigned to the dataobject of dw_1 ?

Is the dataobject dynamically changed of dw_1?
  1. Helpful
Marcin Jurkowski
  1. Marcin Jurkowski
  2. Monday, 30 September 2019 11:44 AM UTC
yes, there is a dataobject object assigned to dw_1 and it's static. this is the sql code (from dw_1) that gets executed during first time on the mobile app
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 11:42 AM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 5

What if you try to do a 

dw_1.Modify("DataWindow.Table.Select='select...'")

instead of SetSQLSelect()

Comment
Marcin Jurkowski
  1. Marcin Jurkowski
  2. Monday, 30 September 2019 12:03 PM UTC
in this case it doesn't work also in IDE - it alwys (not only the first time) executes original sql from DW not from ls_new_sql
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 13:00 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 6

Hi Marcin,

There is another way to modify SELECT statement "in-flight".

I prefer having DW control own full responsibility for its internals, incl. its SQL statements. I don't like SQL logic spread among buttons etc. on screen. Rather have any such button call user events or user-defined functions on DW control to request SQL changes - then DW control itself makes the actual changes.

SQLPreview event of DataWindow control. One of the arguments is sqlSyntax = SELECT statement *JUST BEFORE* sent to database. In this event you can call SetSQLPreview to modify the SELECT statement (remember to check return code of SetSQLPreview).

Your button could notify DW control of relevant SQL changes for use during SQLPreview.

NOTE: SQLPreview is called during both Retrieve, ReselectRow, and Update so you should check the arguments request and sqlType to ensure you only modify SQL statement when relevant. You dont't want an INSERT/UPDATE/DELETE statement turned into a SELECT statement.

HTH /Michael

Comment
There are no comments made yet.
Marcin Jurkowski
Marcin Jurkowski Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 13:28 PM UTC
  2. PowerServer Mobile (Obsolete)
  3. # 7

I think there was a problem with DW source.
I recreated this DW as new object with basic columns and everything is working as expected.

Thank you for your help guys.
Regards,
Marcin

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 30 September 2019 15:56 PM UTC
Ha, YW. Something similar happened to me the other day with a dw which didn't do anything when clicking on a vscrollbar between the slider and the arrows.

happy it works now.
  1. Helpful
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.