SyntaxFromSQL

[Former Member]

Hello

We have applications in Powerbuilder 11.5 with Oracle 11G.

We don’t use extended attributes, but  we use in our applications SyntaxFromSQL

As you know, this generates a lot of queries.

We have set PBNoCatalog=1 to suppress  queries on the powerbuilder catalog tables

But we still have queries on dictionary tables  :

• SYS.ALL_INDEXES
• SYS.ALL_IND_COLUMNS
• SYS.ALL_SYNONYMS
• SYS.ALL_TAB_COLUMNS
• SYS.ALL_CONSTRAINTS,
• SYS.ALL_CONS_COLUMNS
• SYS.ALL_CATALOG

Could you give us please a solution to removes these queries?

  Thanks for your help

[Former Member · Accepted solution]

Hi Frederic,

I don't know a way and I think it is necessary to query the dictionary tables to generate the datawindow from SQL. Powerbuilder needs to know something about the columns data types and about some constraints (for update properties).

[Former Member · Accepted solution]

Thanks, it gives me a new idea

when I do not specify the schema name :

SELECT   COLUMN_NAME FROM SYS.ALL_IND_COLUMNS WHERE INDEX_NAME = 'RRX1TPERSONN_PK' AND   INDEX_OWNER = 'CM' AND TABLE_OWNER = 'CM' AND TABLE_NAME = 'delete'

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

SELECT   INDEX_NAME, UNIQUENESS FROM   SYS.ALL_INDEXES WHERE TABLE_OWNER = 'CM' AND TABLE_NAME =   'delete'

SELECT   INDEX_NAME, UNIQUENESS FROM   SYS.ALL_INDEXES WHERE TABLE_OWNER = 'delete' AND TABLE_NAME =   'delete'

SELECT   OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE ((OWNER = 'delete'   AND TABLE_TYPE IN ('TABLE','VIEW', 'SYNONYM')) OR (OWNER ='PUBLIC' AND   TABLE_TYPE = 'SYNONYM')) AND TABLE_NAME = 'delete'

SELECT   OWNER, SYNONYM_NAME FROM SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND   SYNONYM_NAME = 'delete'

SELECT   SYS.ALL_CONS_COLUMNS.COLUMN_NAME, SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME   FROM    SYS.ALL_CONSTRAINTS,SYS.ALL_CONS_COLUMNS WHERE  SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE ='P'   AND    SYS.ALL_CONSTRAINTS.TABLE_NAME =   'delete'AND     SYS.ALL_CONSTRAINTS.OWNER = 'delete' AND    SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME =   SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME AND     SYS.ALL_CONSTRAINTS.TABLE_NAME = SYS.ALL_CONS_COLUMNS.TABLE_NAME   AND    SYS.ALL_CONSTRAINTS.OWNER =   SYS.ALL_CONS_COLUMNS.OWNER ORDER BY   SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME,SYS.ALL_CONS_COLUMNS.POSITION

SELECT   OWNER, SYNONYM_NAME FROM SYS.ALL_SYNONYMS WHERE OWNER = 'delete' AND   SYNONYM_NAME = 'delete'

SELECT   OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN   ('TABLE','VIEW')AND OWNER = 'delete'AND TABLE_NAME = 'delete'

when I specify the schema name, I have only

SELECT   SYS.ALL_CONS_COLUMNS.COLUMN_NAME, SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME   FROM    SYS.ALL_CONSTRAINTS,SYS.ALL_CONS_COLUMNS WHERE  SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE ='P'   AND    SYS.ALL_CONSTRAINTS.TABLE_NAME =   'delete' AND     SYS.ALL_CONSTRAINTS.OWNER = 'CM' AND     SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME = SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME   AND    SYS.ALL_CONSTRAINTS.TABLE_NAME =   SYS.ALL_CONS_COLUMNS.TABLE_NAME AND     SYS.ALL_CONSTRAINTS.OWNER = SYS.ALL_CONS_COLUMNS.OWNER ORDER BY SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME,SYS.ALL_CONS_COLUMNS.POSITION

SELECT   OWNER, TABLE_NAME, TABLE_TYPE FROM SYS.ALL_CATALOG WHERE TABLE_TYPE IN   ('TABLE','VIEW')AND OWNER = 'delete' AND TABLE_NAME = 'delete'