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

Resolved sql / dw updates

  1. How-to
0
Votes
Undo
  1. Andrew Davis
    Andrew Davis
  2. PowerBuilder
  3. Friday, 10 September 2021 15:35 PM UTC

Hi, 

I have 3 databases that have a table that is the same structure

 

for example - customers which has fields

account

name

type 

info

other

 

One is the master database by that i mean that it contains all the records

the other 2 have a subset and details are changed by the user ( i can not use a replication database in this case)

Is there an efficient way of updating / inserting the records into main database from the data in the 2 other database - using either datawindows/datastores or SQL statements or even stored procedures

I am using PB2019R2 and SQL Anywhere 16

 

regards and thanks in advance

Andrew

 

 

mailResolveRecipient returning mailReturnFailure!
ClassName for PB2021 Application
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 10 September 2021 16:15 PM UTC
  2. PowerBuilder
  3. # Permalink

Yep .. pipelines are your friend in this case. They were specifically designed to create, update, refresh, etc similar tables and /or DB instances.  ;-)

Comment
Andrew Davis
  1. Andrew Davis
  2. Friday, 10 September 2021 16:19 PM UTC
is there any example code available for pipelines, I have only used them in the powerbuilder IDE and not in an application
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 10 September 2021 18:00 PM UTC
I use them in my STD Framework's "OrderEntry" demo app. You can download the App from here ... http://chrispollach.blogspot.com/2021/09/2021r4.html ( Note that older versions for older PB's are in the "Archive" sub-folder.). Use the "You can download the example application from here" link in the article.

For the pipeline definition, settings and use have a look at the "OES_Contact" PBL. The pipelines are "pl_contact_to_contact2_create" and "pl_contact_to_contact2_refresh" respectively. They are used in the "wn_contact_pipeline_e" window's "oe_postopen" event.

HTH

  1. Helpful
There are no comments made yet.
Responses (3)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 10 September 2021 15:45 PM UTC
  2. PowerBuilder
  3. # 1

Not sure, but :

Use 3 transactions objects. Each one point to one of the 3 databases.

In the sqlpreview event of the 2 non-master databases, you could check all the sql that is not SELECT and apply it to the master database by issuing DELETE, INSERT and UPDATE statements with the "using ... " clause?

 

Comment
Andrew Davis
  1. Andrew Davis
  2. Friday, 10 September 2021 15:56 PM UTC
I do use 3 transaction objects.



I can select all the fields in a sql select statement

If the record doesnt exist - its relatively easy - do an insert with all the fields to the other transobject

but if it exists - i need to check what is different - all can see is to then check each field to be different and then issue an update for each different field - seems like a lot of coding
  1. Helpful
Andrew Davis
  1. Andrew Davis
  2. Friday, 10 September 2021 15:58 PM UTC
maybe i misread your reply - how do i use the sqlpreview ?
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 10 September 2021 15:48 PM UTC
  2. PowerBuilder
  3. # 2

Or maybe you could use pipeline objects. There's ways to dynamically create and run them.

Comment
Load more comments
Andrew Davis
  1. Andrew Davis
  2. Friday, 10 September 2021 18:27 PM UTC
I have managed to use the pipeline and it seems to do the trick nicely
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 10 September 2021 19:20 PM UTC
That is Awesome news Andrew.. thanks for letting us know! :-)
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 10 September 2021 19:59 PM UTC
Glad you solved it Andrew. Sorry I was out and couldn't answer.
  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.