Beginners Guide to OpenEdge SQL

decorumgroveInternet and Web Development

Aug 7, 2012 (5 years and 1 month ago)

2,933 views

B6: Beginners Guide to OpenEdge
®

SQL


via ODBC or JDBC





Richard Banville

Technical Fellow

© 2008 Progress Software Corporation

2

Agenda:


OpenEdge SQL


Component overview


Initial connection


OpenEdge database


Setup and maintenance


Control and performance


OpenEdge SQL specifics


Tools


Applications

Goal:
Make you successful with SQL applications!


© 2008 Progress Software Corporation

3

OpenEdge is Open

SSL

HTTP

HTTP/S

HTML

.NET

Java

HTML



Open Clients

(Non
-
OpenEdge)

OpenEdge
ABL Clients

ODBC

Clients

Service

Interfaces

OpenEdge

DataServers

OpenEdge

ABL Server

OpenEdge

SQL Server

JDBC

Clients

Oracle
®

MSSQL

ODBC

ABL:

Open Clients:

Java


.NET


Web services

(ABL works with
relational DBs)

OpenEdge
SQL


Crystal Reports

WebSphere
®

JBOSS / JRun

Java / JDBC apps

J2EE


/ JTA

.NET / ODBC apps

ADO.NET / VB

Data is fully interoperable: ABL & SQL

(works with
OpenEdge RDBMS)

OpenEdge

RDBMS

“Other”

RDBMS

© 2008 Progress Software Corporation

4

Getting Connected


Client side:


ODBC and JDBC drivers

© 2008 Progress Software Corporation

5


CLASSPATH ( run ‘sql_env’)




Class loader (Loading the JDBC driver)




URL for JDBC DriverManager.getConnection


JDBC: Pure Java Driver

Type 4 JDBC driver (10.1a+)

jdbc:datadirect:openedge://localhost:6748;databaseName=db1

$DLC/java: openedge.jar, util.jar, base.jar

CLASS.FORNAME
com.ddtek.jdbc.openedge.OpenEdgeDriver

© 2008 Progress Software Corporation

6

ODBC DSN


single connection


© 2008 Progress Software Corporation

7

ODBC: Multi
-
Database configuration (10.1B)


© 2008 Progress Software Corporation

8

ODBC DSN
-

Advanced Tab





Isolations: defaults have changed between versions



T w/TZ: determines native or varchar storage



Wide char: varchar:
SQL_WVARCHAR
vs

SQL_VARCHAR



Fetch Array Size: Max # rows driver fetches from server

© 2008 Progress Software Corporation

9

None

Share

Exclusive

SIX

Serializable

Share

IS

Exclusive

IX

Repeatable
Read

Share

IS

Exclusive

IX

Read
Committed


NoLock

NoLock

---

---

Read
Uncommitted

Record
Lock

Table
Lock

Record
Lock

Table
Lock

Isolation
Level

Fetch

Update

Isolation Level Affect on Lock Type

© 2008 Progress Software Corporation

10

Connection


server side

© 2008 Progress Software Corporation

11

Default server setup

SQL

Servers

SQL client

ABL client

SQL client

ABL client

Shared Memory

ABL

Servers

DB

SQL & ABL

Broker

© 2008 Progress Software Corporation

12

“Recommended” server setup

SQL

Servers

SQL client

ABL client

ABL only

Broker

SQL only

Broker

SQL client

ABL client

Shared Memory

ABL

Servers

DB

© 2008 Progress Software Corporation

13

Recommended parameters example

Separating ABL and SQL brokers/servers example

proserve myDB


-
S 6000
-
H localhost


-
ServerType

4GL


-
Mi

1

-
Ma 5


-
minport 6100


-
maxport 6300


-
Mpb 4


-
Mn 8
-
B<n>
-
L<n>
. . .

Start a primary ABL broker

Start a secondary SQL broker

proserve myDB


-
S 5000
-
H localhost


-
ServerType

SQL


-
Mi

5
-
Ma 10


-
minport 5100


-
maxport 5300


-
Mpb 2


m3

Secondary broker uses 1

Mn for itself

© 2008 Progress Software Corporation

14

Security

Authentication

Authenticate then authorize


Identify who I am


Validate I am who
I say I am

Authorization


Being I am who I
say I am…


What am I allowed
to do

© 2008 Progress Software Corporation

15


Database authentication


Performed at connection


SQL


User ID and password required to authenticate


ABL


User ID and password NOT required


Security

ID and passwords

© 2008 Progress Software Corporation

16

SQL Authentication (Am I who I say I am?)

Case 1:
Users have not been created




(no rows in _User table)



No password validation at connection


You are not “authenticated”


You can pretend to be someone you are not


Unable to do much (not authorized)
UNLESS


You created the database


You know an authorized user


Database creator


Someone who was granted privileges

ID and passwords scenarios

© 2008 Progress Software Corporation

17

SQL Authentication (Am I who I say I am?)

Case 2:
Users have been created





(rows exist in _User table)




Password validation at connection


Valid users defined by a DBA or SA


Can be created by SQL
or

ABL


Make sure existing SQL DBA has userid/pswd


Invalid login/password error message:


“Access denied (
Authorisation

failed). (8933)

ID and passwords scenarios…cont’d

© 2008 Progress Software Corporation

18

Authorization


What am I allowed to do?


SQL follows GRANT security model


By default, a connected userid is not
authorized

to do anything.


Exceptions:



the DBA account (
full operations
)



the TABLE owner



DBA controls operation privileges


with GRANT / REVOKE syntax

© 2008 Progress Software Corporation

20

Encountering data access errors


Possible reasons for this:


No authorization privileges


Schema scope


Access denied (Authorization failed) (7512)

select count(*) from customer;

© 2008 Progress Software Corporation

21

Authorization


What can I do?


Database wide (system admin or general creation)

GRANT { DBA, RESOURCE }


TO
user_name
[,
user_name

] , …;

Privileges syntax: GRANT (2 types)

Can do anything

Can CREATE stuff

© 2008 Progress Software Corporation

22

Authorization


What can I do?


Database wide (system admin or general creation)

GRANT { DBA, RESOURCE }


TO
user_name
[,
user_name

] , …;

GRANT {
privilege
[,
privilege

], … | ALL }


ON
table_name


TO {
user_name
[,
user_name

], … | PUBLIC }


[ WITH GRANT OPTION ];

Privileges syntax: GRANT (2 types)


For specified Tables or Views


Where ‘privilege’ is:


{ SELECT | INSERT | DELETE | INDEX |


UPDATE [ (
column

,
column

, ... ) ] |
REFERENCES [ (
column

,
column

, ... ) ] }

© 2008 Progress Software Corporation

23

Authorization


What can I do?


For specified Tables or Views
-

Example









See PSDN whitepaper on authorization for
additional details.



Example Syntax: GRANT

GRANT select ON PUB.Customer


TO richb;

GRANT select ON PUB.Order
-
line


TO PUBLIC;

COMMIT WORK;

ROLLBACK WORK;

© 2008 Progress Software Corporation

24

Encountering data access errors


Possible reasons for this:


No authorization privileges


Schema scope

Access denied (Authorization failed) (7512)

select count(*) from customer;

Schema scope

© 2008 Progress Software Corporation

25

Schema: a logical grouping

PUB schema

Customer

table #1

Mysports
database

In the “SQL world”
schema

is NOT meta data nor is it “Area 6”

Order

table #2

Order
-
line

table #3

richb schema

Customer

table #4

Contacts

table #5

Friends

table #6

© 2008 Progress Software Corporation

26


Users have a default schema attached to their ID


<userid>.<table> richb.customer



ABL uses one “hidden” schema


‘PUB’


Use PUB.customer for access from SQL



Avoiding schema qualification in SQL:





Can set it as a registry entry in ODBC dsn definition

Schemas

CREATE PUBLIC SYNONYM customer FOR


pub.customer;

What is a default schema?

© 2008 Progress Software Corporation

27

Solving data access errors

Access denied (Authorization failed) (7512)

select count(*) from customer;

SELECT count(*) FROM pub.customer;

SET SCHEMA ‘pub’;

SELECT count(*) FROM customer;

Solution #1:

SELECT count(*) FROM richb.customer;

Solution #2:

Solution #3:

© 2008 Progress Software Corporation

28


Four level naming convention


4 Part Naming


Multi
-
Database Query

Fully Qualified Names


catalog is database name

catalog.schema.table.column
-
name

© 2008 Progress Software Corporation

29


Four level naming convention



Example




4 Part Naming


Multi
-
Database Query

Fully Qualified Names


catalog is database name

catalog.schema.table.column
-
name

SELECT Pub.Customer.CustNum,


SportsPrimary.Pub.Customer.Name,


SportsAux1.Pub.Order.OrderNum …

© 2008 Progress Software Corporation

30


Four level naming convention



Example





ABL has 3 level naming convention

4 Part Naming


Multi
-
Database Query

Fully Qualified Names


catalog is database name

catalog.schema.table.column
-
name

SELECT Pub.Customer.CustNum,


SportsPrimary.Pub.Customer.Name,


SportsAux1.Pub.Order.OrderNum …

catalog.table.column
-
name

© 2008 Progress Software Corporation

31

OpenEdge Specifics

SQL is a standard,


but each vendor has it’s own dialect


© 2008 Progress Software Corporation

32

OpenEdge SQL Specifics
-

Quoting


Hyphenated names:

Non
-
SQL standard names

SELECT cust
-
num FROM PUB.Customer;

SELECT “cust
-
num” FROM PUB.Customer;

Column CUST cannot be found (13865)


Solution: quoting (double quotes)

Most reporting applications will do this automatically.

© 2008 Progress Software Corporation

33

Overstuffed fields
-

error


ABL allows more data than column definition


SQL restricted to _field._sql
-
width value




Column name in table PUB.customer has value
exceeding it’s max length.

SELECT name from PUB.customer;



Solution:



ALTER table… ALTER column… SET PRO_SQL_WIDTH


<value>;

Fix _sql
-
width via SQL “ALTER TABLE or Data Dictionary

© 2008 Progress Software Corporation

34

OpenEdge Specifics
-

Overstuffed fields

Strategies for managing:


Dbtool : percentage option
($DLC/bin/dbtool)

1. SQL Width & Date Scan w/Report Option

2. SQL Width Scan w/Fix Option


Choice:
2


<connect>: (0=single
-
user 1=self
-
service >1=#threads)?
3


Padding % above current max:
25


<table>: (Table number or all)? all


<area>: (Area number or all)? all

© 2008 Progress Software Corporation

35

OpenEdge Specifics
-

Overstuffed fields

Strategies for managing


ABL client startup parameter



<progress
-
client>

checkwidth n


where "
n
" can be one of the following:

0


Ignore: Default is to ignore _width value.

1


WARNING: Store the data and generate a warning.

2


ERROR: Do not store data and generate an error.

.lg and screen:

Width of data is greater than x.customer.Name _width.

© 2008 Progress Software Corporation

36

OpenEdge Specifics


Arrays / Extents



Selecting array columns as a whole




Result: semi
-
colon separated
varchar

value


102332.67;330002.77;443434.55;333376.50



Selecting array column individually


SQL99 (10.1a)





Result: numeric value


102332.67

SELECT quarterlySales from PUB.MySales;

SELECT quarterlySales[1] from PUB.MySales;

© 2008 Progress Software Corporation

37

OpenEdge Specifics


Arrays / Extents


Using views to break out array element

CREATE VIEW pubView.QuarterSalesView


(qS1, qS2, qS3, qS4) AS


SELECT quarterlySales[1], quarterlySales[2],


quarterlySales[3], quarterlySales[4]


FROM PUB.MySales;

Result: numeric values

102332.67 330002.77 443434.55 333376.50

SELECT qS1, qS2, qS3, qS4


FROM pubView.QuarterSalesView;


GRANT select ON pubView.QuarterSalevVIew


TO PUBLIC;

© 2008 Progress Software Corporation

38


Q: What is it going to cost to run

my query?

TIME =

?

Query Performance

© 2008 Progress Software Corporation

39


Q: What is it going to cost to run

my query?

TIME =

Query Performance

© 2008 Progress Software Corporation

40

Basic Performance: What is the cost?

Database without statistics

10K

rows

50K

100K

Optimizer:

How many rows do I think you have?

© 2008 Progress Software Corporation

41

Basic Performance: Here’s the cost.

Database with Update Statistics

10K

rows

50K

100K

© 2008 Progress Software Corporation

42



All Statistics: Table Cardinality, indexes and all
columns

Query Performance: Update Statistics

UPDATE TABLE STATISTICS AND


INDEX STATISTICS AND


[ALL] COLUMN STATISTICS;

UPDATE STATISTICS syntax

UPDATE TABLE STATISTICS AND


INDEX STATISTICS AND


[ALL] COLUMN STATISTICS


FOR pub.customer;



Statistics
-

particular table

© 2008 Progress Software Corporation

44

Basic Performance: Viewing Query Plans




Query Plans Located in VST “_Sql_Qplan”

SELECT SUBSTRING("_Description",1,80)


FROM pub."_Sql_Qplan“


WHERE "_Pnumber" =


(SELECT MAX( "_Pnumber" )


FROM pub."_Sql_Qplan"


WHERE "_Ptype" > 0 );

Viewing query plan constructed by cost
-
based optimizer

Remembers last 10 queries for a client

SET
PRO_SERVER

LOG [ ON | OFF ]


[ WITH ( { STATEMENT, QUERY_PLAN } ) ];

© 2008 Progress Software Corporation

45


Simple single table select


SELECT … FROM pub.customer


WHERE “cust
-
num” between 1000 and 1100
[ NoExecute ]

Query plan: What to look for

table

index

index keys,
predicates

SELECT COMMAND.

PROJECT [66] (

| PROJECT [64] (

| | PUB.CUSTOMER. [0](

| | |
INDEX SCAN

OF (

| | | | cust
-
num,

| | | | | (PUB.CUSTOMER.cust
-
num)


between (1000,1100))

© 2008 Progress Software Corporation

46


Simple single table select


SELECT … FROM pub.customer


WHERE ”cust
-
num” between 3000 and 3100 [ NoExecute ]

Query plan: What to look for

table

index

index keys,
predicates

SELECT COMMAND.

PROJECT [66] (

| PROJECT [64] (

| | PUB.CUSTOMER. [0](

| | |
INDEX SCAN

OF (

| | | | cust
-
num,

| | | | | (PUB.CUSTOMER.cust
-
num)


between (3000,3100))

WITH (INDEX (name))

with index
hint

© 2008 Progress Software Corporation

47

In Summary


Initial SQL connection



Setup and maintenance in
OpenEdge database for
security and performance



Specifics of OpenEdge with
SQL applications

© 2008 Progress Software Corporation

48

For More Information, go to…


PSDN


Developing Performance
-
Oriented ODBC/JDBC OpenEdge
Applications



OpenEdge SQL: Authorization Explained


OpenEdge SQL in a 10.1B Multi
-
Database Environment


OpenEdge
®

Database Run
-
time Security Revealed



OpenEdge Technical Support
-

KBases


Basic Guide to Defining Progress SQL Database Permissions &
Security



Progress eLearning Community


Using OpenEdge SQL



Documentation


10.1C OpenEdge Data Management: SQL Development




10.1C OpenEdge Data Management: SQL Reference

© 2008 Progress Software Corporation

49

Questions

?

© 2008 Progress Software Corporation

50

Some Questions Answered


Is there a way to grant a user

select privilege for all
tables in one statement?


There is no way to grant privileges for all tables in one
statement.


Other than grant dba ;)


Does the field level extent (array fields) reporting
where members are separated by ";" work for ALL
data type?


yes


If so, then how is a ";" handled within a character
field?


Is it quoted when encountered to differentiate
between an array entry separator?




An embedded semi
-
colon in a varchar array will be
preceded by a ~

© 2008 Progress Software Corporation

51

Thank You

© 2008 Progress Software Corporation

52