OJSyntax – PB Docs 2019

Description

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

Applies to

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

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

Default value

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
%AppeonInstallPath%CommonPowerBuilderRuntime [version]pbodb.ini or
%AppeonInstallPath%CommonPowerBuilderRuntime [version]x64pbodb.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_USERSoftwareSybasePowerBuilder19.0pbjdbc key. This key
is not installed by default. See the egreg.txt file in
%AppeonInstallPath%PowerBuilder [version]IDE for an example of a registry file
you could execute to add or change PowerBuilder JDBC settings for your
DBMS.

When you upgrade 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 the section called “Using ANSI outer joins” in 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: