Questions? Feedback?powered byOlark live chat software

Standard Support

Bug 1918

PB Oracle db connection with proxy connection 13 December, 2018

Developer1 Dorsum
26 November, 2018
Product: PowerBuilder Category: Database
Version: 2017 R3 Publishing: Public
Status: Analyzing Priority: P3
Classification: Resolution:
Chris Pollach 13 December, 2018
Syntax mode should keep the SQL "As Is". Are you saying that it adds the table name back after you exit the SQL Painter back to the DW Painter?
Developer1 Dorsum 06 December, 2018
designe_screen.jpg (311KB)

2. screen
Developer1 Dorsum 06 December, 2018
syntax_screen.jpg (260KB)

I always use syntax mode. When i use "Return" button, it make worse Db Name.
Chris Pollach 05 December, 2018
Hi;

  Did "Syntax" mode help to correct your DW SQL issue?

Regards ... Chris
Chris Pollach 27 November, 2018
Hi;

    Ahhh - OK ...  I think that now understand your issue / problem. The DW object is trying to outsmart the SQL because you built the DW object while in SQL "Graphic" mode. Please try using "Syntax" mode instead. 

   You can alter the current DW object for that. Open the DWO in the DW Painter. Then enter the SQL Painter (aka DataSource on the DW Painter's toolbar). While back in the SQL Painter, select the "Design => Convert to Syntax" menu. Now in syntax mode, paste in the correct SQL statement you want the DWO to use & close the SQL painter. 

Note: Do not return the SQL painter into "Graphics Mode" when exiting back to the DW painter.

HTH
Regards ... Chris
Developer1 Dorsum 27 November, 2018
Server: database01
Shema: dev1
table: table1[id,name,description,sparm,lparm]

powerbuilder database connection settings: 
server: dev1
login id: developer1[dev1]
passw:***
Powerbuilder Catalog Table Owner: dev1
Table Owner: dev1

I create datawindow: 'Select id,name,description,sparm,lparm from table1'

Pb run this select: 
SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN ('TABLE','VIEW')AND OWNER = developer1[dev1] AND TABLE_NAME =table1;

Return row is zero because, this select is incorrect. 

Correct select:
SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN ('TABLE','VIEW')AND OWNER = dev1 AND TABLE_NAME =table1;
Chris Pollach 26 November, 2018
   Your User ID needs Catalog table access privileges. 

   The other alternative is to use another User ID that does have Catalogue privileges when you are building your DW objects. Then at run-time have two Transaction Objects. One for Catalogue processing and the other (ie:SQLCA) for normal business App processing.
Developer1 Dorsum 26 November, 2018
I don't have DBO privileges. But if it does, the select is incorrect. Because User id not the shema user.
Chris Pollach 26 November, 2018
Hi;

  It sounds like your Oracle User ID does not have "DBO" privileges. Please check with your DBA group if that is the case and let us know.

Regards ... Chris
Developer1 Dorsum 26 November, 2018
*Phenomenon:
I connect to the database with a proxy connection with my own user.  When I create datawindow, the DB name is not good. Because it does not use the catalog table owner to query the data, it is use the connected login ID.
False:
SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN ('TABLE','VIEW')AND OWNER =<login ID> /*'<user name>[<shema name>])'*/ AND TABLE_NAME =<table name>

Correct(as it should):
SELECT OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN ('TABLE','VIEW')AND OWNER ='<pb catalog table owner>' AND TABLE_NAME =<table name>


*Reproduce Steps:
Set the db connection. Use proxy connection. Create datawindow. pl.: "select id, description from user"
--> Finish --> db name is incorrect. 

Remark:
What should be set to make the pb catalog owner?
OS:
All 
Platform:
32-bit 
Database Type:
Oracle 
Database Version:
10g