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:48 PM UTC
  2. PowerBuilder
  3. # 1

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.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 10 September 2021 15:45 PM UTC
  2. PowerBuilder
  3. # 2

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.
  • 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.