• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Modify Datawindow to use Stored procedure for update using code

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

Resolved Modify Datawindow to use Stored procedure for update using code

  1. How-to
0
Votes
Undo
  1. Jim Nesbitt
    Jim Nesbitt
  2. PowerBuilder
  3. Wednesday, 12 April 2023 12:13 PM UTC

Hi 

I'm converting a Powerbuilder project to Powerserver. Some of the windows use SQLPreview to call stored procedures to update the database.

SQLPreview isn't supported by Powerserver, so I want to modify the datawindow dynamically to use the stored procedure.

The powerbuilder help section - Using stored procedures to update the database - suggests this can be done.

In addition, using Modify to enable a DataWindow object to use stored procedures to update the database when it is not already using stored procedures requires that the type qualifier be specified first. Calling the type qualifier ensures that internal structures are built before subsequent calls to Modify. If a new method or method arguments are specified without a preceding definition of type, Modify fails.

I'm confused because Update.Type, Update.Method and Update.Argument are described as Internal use only.

I found the example about changing the select statement from a select to a stored procedure, but I've not been able to find anything about using modify to enable a new stored procedure. 

Is there a way I can do this using modify or will I need to change the entire datawindow syntax?

Thanks in advance

Jim

 

 

 

 

  1. DataWindow
  2. PowerServer Web
  3. Stored Procedure
  4. Syntax
  5. Update
DataWindow column background color from drop-down-...
OLE control MS word 64 bit detaches from ole contr...
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 12 April 2023 16:37 PM UTC
  2. PowerBuilder
  3. # 1

Hi Jim;

    FYI: This relates to ticket # 10060 requesting full support for the SQLPreview event in PS 2022 R2 in order to support this customer's PB App needs. Let me "ping" engineering for you for an update on this for R2.

Regards ... Chris

Comment
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Wednesday, 12 April 2023 18:48 PM UTC
  2. PowerBuilder
  3. # 2

Hi Chris

That sounds promising. It would be fantastic if V2 offers full support for SQLPreview - I'm converting a small app to PowerServer and i've had to change around 40 cases in SQL Preview. 10 of these are calling a stored procedure to replace the standard DW update. Our other apps have loads more than that.

We use SQL preview mainly for assign/unassign type options, and the procedure does an upsert, where it will do an insert  for an assign, delete for unassign. E.g. Assign/Remove users to/from projects

I need this now, so I'm generating the DW Syntax for the stored procedure update from our SQL database so I can modify the DW text to use SP update. This will reduce possibility of manual error for the changes. 

Out of curiosity, I would be interested to know if it is possible to modify DW syntax to use SP Update. Also, is there an approximate ETA for V2?

Cheers

Jim

Comment
Load more comments
Andreas Mykonios
  1. Andreas Mykonios
  2. Thursday, 13 April 2023 12:08 PM UTC
So we have some news. R2 won't be the LTS. There will be a R3... I wonder when we should expect the next major release.

Andreas.
  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Thursday, 13 April 2023 12:55 PM UTC
Hi Chris



We rarely have any complex SQL on the PB Side. DWOs just select from a view and I've only came across 1 which had complex SQL - this is for retrieval anyway, so it doesn't need changing to use an SP.



We started using SQLPreview before SP Update came along so that we could call an SP to update the database where necessary. The code just gets values from the rows and then passes the values to the SP. We continued with this as it couldn't get trashed compared to using Datawindow SP Update. a



My view on using SQL is that any complexity is better in the database, and not in front end code which should only call simple singleton SQL statements or SPs otherwise.



My SP to generate the SP Update syntax is pretty much ready now, so I'll be able to use that. I have a function that displays an error message if a dw is not configured for SP Update when it should be (probably trashed by painter). This runs when the window opens, so it will detect the problem if it's occurred.



Generating the DW syntax from the DB and using the SP Update checker might be useful for your clients!



Cheers



Jim



  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Thursday, 13 April 2023 13:02 PM UTC
Hi Logan



As mentioned in reply, I'm going to change the DW by generating the SP Update syntax and editing the DW. However, I would be interested to know if SP update can be set up dynamically without changing the entire DW syntax. The extract from docs (see below) appears to suggest this is possible, but I couldn't find anything about how to do it.



Cheers



Jim





In addition, using Modify to enable a DataWindow object to use stored procedures to update the database when it is not already using stored procedures requires that the type qualifier be specified first. Calling the type qualifier ensures that internal structures are built before subsequent calls to Modify. If a new method or method arguments are specified without a preceding definition of type, Modify fails.



I'm confused because Update.Type, Update.Method and Update.Argument are described as Internal use only.
  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.