1. Lee Quinn
  2. PowerBuilder
  3. Thursday, 10 September 2020 21:36 PM UTC

Is there a way to keep PB from selecting from ALL_SYNONYMS in Oracle?

 

May be generated by call to SyntaxFromSQL

Brad Wery Accepted Answer Pending Moderation
  1. Monday, 14 September 2020 14:11 PM UTC
  2. PowerBuilder
  3. # 1

From what I can recall, using SyntaxFromSQL on an Oracle connection was expensive (the too many calls to ALL_SYNONYMS that you mention).

When developing my query tool, https://powertothebuilder.com/UltimateSuiteSQL.html, I ended up solving this issue by creating my own SyntaxFromSQL function for Oracle. The solution uses a small C++ DLL to call out to the Oracle API.

I posted the code here: https://powertothebuilder.com/downloads/PBOracleDist.zip

The main object is n_cst_oracle_dw_helper. Try the of_syntaxfromsql in that object to see if it helps. There's a dependency on a string processing object, nv_cppstringx. You'll have to import this as well. The n_cst_oracle_dw_helper object is using a Replace function from nv_cppstringx, but you can replace that with your own implementation if you have one. The nv_cppstringx object came from someone else many many years ago but I can't remember who.

There are two dependencies on dlls, PBOracle.dll and pbstringx.dll (both included in the zip). If you replace the reference to the Replace method in nv_cppstringx, then you won't need pbstringx.dll.

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 6 October 2020 07:38 AM UTC
  2. PowerBuilder
  3. # 2

Hi,

have same problems with SyntaxFromSQL and Oracle 12.

I found out that PB tries to find the table name for each column.

So PB first calls SELECT TABLE_NAME FROM SYS.ALL_TAB_COLUMNS ... for the first possible table in SQL.

If not found PB tries with SELECT SYNONYM_NAME FROM SYS.ALL_SYNONYMS S, SYS.ALL_TAB_COLUMNS T ...

PB retries it for each possible table.

 

I think, Appeon should find out another way or check for synonyms only after checking in all tables.

 

To make SyntaxFromSQL faster you can:

  • qualify column names in SELECT with table name if you have more than one table in SQL (so PB don't need to search for the table)

  • don't use Oracle keywords like SYSDATE allone in SELECT clause. PB thinks it is a column name (don't know why) and tries to find this column in one of the tables. As workaround you can use the keyword in a function or expression (e. g. SYSDATE + 0)

 

HTH,

René

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Friday, 11 September 2020 14:24 PM UTC
  2. PowerBuilder
  3. # 3

What version of PowerBuilder are you using?
Waht is the platform you are on?
What version of Oracle are you using?

And finally, what is the issue you are experiencing?

Comment
There are no comments made yet.
Lee Quinn Accepted Answer Pending Moderation
  1. Monday, 14 September 2020 12:58 PM UTC
  2. PowerBuilder
  3. # 4

PB Version: 2017 R3 build 1915

Platform: Windows 10

Oracle Version: 12.2.0.1.0

Issue:

PowerBuilder is generating the following Oracle data dictionary queries hundreds of times:

SELECT OWNER, SYNONYM_NAME
FROM
SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = :1;

SELECT TABLE_OWNER, TABLE_NAME
FROM
SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = :1 AND (DB_LINK
IS NULL OR DB_LINK = '');

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Monday, 14 September 2020 16:32 PM UTC
  2. PowerBuilder
  3. # 5

Do you have your table anmes all defined with a PUBLIC SYNONYM?

That's what we do: every table in a schema has a PUBLIC synomym, making it easy for each transaction to find the correct tables.

Unless all of your activity is within a single schema, your code MAY be hunting for the correct schema.tablename for each transaction.


Olan

Comment
  1. mike S
  2. Monday, 14 September 2020 17:15 PM UTC
we use 'set schema <schemaname>' as part of the login process. That way you don't need to create all those public schemas.
  1. Helpful
There are no comments made yet.
Thomas Winkler Accepted Answer Pending Moderation
  1. Thursday, 15 June 2023 09:06 AM UTC
  2. PowerBuilder
  3. # 6

When developing my query tool, https://powertothebuilder.com/UltimateSuiteSQL.html, I ended up solving this issue by creating my own SyntaxFromSQL function for Oracle. The solution uses a small C++ DLL to call out to the Oracle API.

 

Thank your for your own SyntaxFromSQL-Function. This works very well for us and is much faster than the original Powerbuilder-Function.

Now we migrate our program to 64bit and your PBOracle.dll is 32bit. Is there a possibility to get the PBOracle.dll compiled in 64bit?

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.