OJSyntax – PB Docs 126

OJSyntax database parameter

Description

Specifies how PowerBuilder formats the SQL syntax
for outer joins for the database back end you are accessing.

Controls

  • ADO.NET

  • ASE, SYC SAP Adaptive Server Enterprise

  • I10 Informix

  • IN9 Informix

  • JDB JDBC

  • ODBC

  • OLE DB

  • O90 Oracle9i

  • O10 Oracle 10g

  • SNC SQL Native Client for Microsoft SQL Server

Syntax

Parameter

Description

value

Specifies how you want SQL syntax to be formatted. Values
are:

  • ANSI_Escape

    Apply ANSI standards and enclose the outer joins in escape
    notation { oj … } that is parsed by the driver
    and replaced with DBMS-specific grammar.

  • ANSI

    Apply ANSI standards.

  • PB

    Maintain rules that applied to PowerBuilder 7.

Default

OJSyntax=ANSI for IN9, SNC,
ASE, and SYC, OJSyntax=ANSI_ESCAPE for ADO.NET, JDBC,
ODBC, and OLE DB, OJSyntax=PB for O90 and O10.

Usage

All PowerBuilder database interfaces provide support for ANSI SQL–92 outer join SQL syntax generation. PowerBuilder supports
both left and right outer joins in graphics mode and full outer
and inner joins in syntax mode.

You must set the OJSyntax parameter to indicate the version
of outer join SQL syntax you
want PowerBuilder to generate. For ADO.NET, JDBC, ODBC, and OLE DB,
the default is ANSI_Escape and can be reset to ANSI or
PB (native). For IN9, SNC, ASE,
and SYC, the default is ANSI and can be reset to PB. For O90 and
O10, the default is PB, which means use Oracle native outer join syntax,
and can be reset to ANSI.

OJSyntax is a dynamic parameter in all database drivers that
support it. It can therefore be changed at any time during the life
of a database connection with a statement such as:

Define outer joins in the SQL Select
painter for portability

When you define an outer join SELECT statement
graphically in the SQL Select
painter, the DataWindow object stores the SQL in
pseudocode. At runtime, the outer join syntax is generated based
on the current OJSyntax parameter setting. This provides some degree
of portability for DataWindow objects among multiple DMBSs.

When you define an outer join SELECT statement
in syntax mode, the DataWindow object stores the SQL as
syntax. This syntax is used without modification at runtime. The
OJSyntax parameter setting does not affect
the SQL.

Using native outer join syntax

The option PB generates native outer join syntax. It is available
for ODBC and OLE DB only if PBOuterJoin and PBOuterJoinOperator
syntax entries are set in the appropriate SYNTAX section for your
DBMS in the SAPSharedPowerBuilderpbodb126.ini file.

The PB option is available for JDBC only if PBOuterJoin and PBOuterJoinOperator
syntax entries are set in the Windows registry in the appropriate
key for your DBMS in the HKEY_CURRENT_USERSoftwareSAPPowerBuilder12.6pbjdbc key.
This key is not installed by default. See the egreg.txt file
in SAPSharedPowerBuilder for
an example of a registry file you could execute to add or change PowerBuilder JDBC
settings for your DBMS.

When you migrate applications from PowerBuilder 7 and earlier
versions of PowerBuilder, using ANSI outer join syntax might produce
errors, depending on how the joins were defined in the painter.
If a table is joined to multiple other tables with right outer joins,
a valid ANSI outer join statement cannot be generated.

For more information about outer joins, see the section on
using ANSI outer joins in the PowerBuilder Users Guide.

OJSyntax does not apply to DIR

For one database interface, DIR, the database connection always
uses ANSI outer join SQL syntax.

Examples

To set the value of OJSyntax:

  • Database profile

    Select the appropriate value from the Outer Join Syntax drop-down
    list on the Syntax page in the Database Profile Setup dialog box.

  • Application

    Type the following in code:


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x