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

Resolved DW SQL Checker

  1. Advice
0
Votes
Undo
  1. Markus Schröder
    Markus Schröder
  2. PowerBuilder
  3. Friday, 23 August 2019 06:48 AM UTC

Hello,

is there a tool to check the sql in all Datawindows in a workspace against the database?

We are migrating a huge project from Oracle to Postgres (sql join syntax has changed from (+) to "left outer join"). In some of the existing Datawindows Powerbuilder created a wrong join syntax.

The Advanced Powerbuilder Utilities from Bruce Armstrong didn't work.

 

Thanks

  1. DataWindow
  2. PB Classic
  3. SQL
DDDW list content detached from its field
PB 2019 and Datawindow Save to PDF
Responses (5)
Markus Schröder
Markus Schröder Accepted Answer Pending Moderation
  1. Friday, 27 September 2019 08:00 AM UTC
  2. PowerBuilder
  3. # 1

I wrote my own little tool.

It exports Syntax from all DWs in a workspace, creates a dynamic datastore for each in a loop, gets the needed retrieval arguments via describe and does a retrieve.

If the return values is 0 (i use retrieval args like 0 or empty string) the DW is ok.

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 27 September 2019 16:55 PM UTC
Excellent .. glad that worked for you.

It's doing the same thing as my SQLExtract utility does.
  1. Helpful
There are no comments made yet.
Markus Schröder
Markus Schröder Accepted Answer Pending Moderation
  1. Monday, 2 September 2019 14:38 PM UTC
  2. PowerBuilder
  3. # 2

Thanks for the useful hint.

PBLPeeper works great if i am connect to an oracle database (oracle native driver).

If i am connect to a postgres database via odbc, pblpeeper shows no errors.

But there should be many error messages, for example in dw's with "select sysdate cdate, col2, col3,... from table..." as postgres can't handle sysdate.

Comment
There are no comments made yet.
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Friday, 23 August 2019 15:38 PM UTC
  2. PowerBuilder
  3. # 3

Not that it helps you now, and I am involved in a very similar endeavor currently (and will be speaking about it at ELEVATE 2019) but if we had all switched to ANSI 92 compliant syntax (using JOIN syntax) 'back in the day' this wouldn't be much of an issue  today...

Hindsight is a beautiful, haunting thing...

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 23 August 2019 13:08 PM UTC
  2. PowerBuilder
  3. # 4

Hi.

For that I use PBLPeeper, which has a function like that.

You can try it. It's free...

http://www.techno-kitten.com/PBL_Peeper/Download/download.html

If it help's you, credits should go to Terry Voth.

 

Andreas.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 23 August 2019 13:04 PM UTC
  2. PowerBuilder
  3. # 5

Hi Samuel;

1) I developed a PB utility many years ago that reconstitutes DW SQL exactly as it would be executed in production. The extracted SQL can then be saved to an external file. The utility is called SQLExtract. 

2) Once I extract the working SQL from any DWOs, I would then run this through Quest Software's SQL Optimizer product which would a) check the SQL and b) evaluate it for performance. The Quest utility would then recommend and even rewrite for me the SQL with the necessary changes.

Note: There are other products on the market that can help perform SQL syntax validation as well (see release notes link below)

HTH

Regards ... Chris

FYI: SQLExtract release.

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.