Melnick J., Rawles J. W.
Table of contents :
Contents……Page 3
Send Us Your Comments……Page 23
Audience……Page 25
Organization……Page 26
Sample Pro*COBOL Programs……Page 29
Requirements……Page 30
FIPS Flagger……Page 31
Conventions……Page 32
Documentation Accessibility……Page 35
1 Introduction……Page 37
The Pro*COBOL Precompiler……Page 38
Language Alternatives……Page 39
The SQL Language……Page 40
Pro*COBOL Features and Benefits……Page 41
2 Precompiler Concepts……Page 45
Steps in Developing an Embedded SQL Application……Page 46
Embedded SQL Statements……Page 47
Static Versus Dynamic SQL Statements……Page 50
Host Variables and Indicator Variables……Page 51
Tables……Page 52
Errors and Warnings……Page 53
COBOL Versions Supported……Page 55
Comments……Page 56
Continuation Lines……Page 57
Delimiters……Page 58
Figurative Constants……Page 59
Hyphenated Names……Page 60
Paragraph and Section Names……Page 61
Relational Operators……Page 62
Contents of a Declare Section……Page 63
Precompiler Option DECLARE_SECTION……Page 64
Using the INCLUDE Statement……Page 65
Nested Programs……Page 66
Support for Nested Programs……Page 67
An Example……Page 69
Guidelines……Page 70
Compiling and Linking……Page 71
Sample DEPT and EMP Data……Page 72
Sample EMP Program: SAMPLE1.PCO……Page 73
3 Database Concepts……Page 77
Connecting to Oracle……Page 78
Concurrent Logons……Page 79
Using Username/Password……Page 81
Automatic Logons……Page 85
Connect Without Alter Authorization……Page 86
Using Links……Page 87
How Transactions Guard a Database……Page 88
Beginning and Ending Transactions……Page 89
Using the COMMIT Statement……Page 90
Using the ROLLBACK Statement……Page 91
Using the SAVEPOINT Statement……Page 93
Using the RELEASE Option……Page 95
Using the SET TRANSACTION Statement……Page 96
Using the FOR UPDATE OF Clause……Page 97
Using the LOCK TABLE Statement……Page 98
Handling Distributed Transactions……Page 99
Obtaining Locks……Page 100
X/Open Applications……Page 101
4 Datatypes and Host Variables……Page 103
Internal Datatypes……Page 104
External Datatypes……Page 106
Datetime and Interval Datatype Descriptors……Page 115
Declaring Host Variables……Page 117
Referencing Host Variables……Page 124
Using Indicator Variables……Page 127
Referencing Indicator Variables……Page 128
Declaring VARCHAR Variables……Page 130
Implicit VARCHAR Group Items……Page 131
Referencing VARCHAR Variables……Page 132
Fixed-Length Character Variables……Page 133
Varying-Length Variables……Page 135
Universal ROWIDs……Page 136
Subprogram SQLROWIDGET……Page 137
Globalization Support……Page 139
NLS_LOCAL=YES Restrictions……Page 141
Blank Padding……Page 142
Datatype Conversion……Page 143
Explicit Control Over DATE String Format……Page 145
Datatype Equivalencing……Page 146
Host Variable Equivalencing……Page 147
Guidelines……Page 152
Sample Program 4: Datatype Equivalencing……Page 153
5 Embedded SQL……Page 159
Output Versus Input Host Variables……Page 160
Input Variables……Page 161
Inserting NULLs……Page 162
Fetching NULLs……Page 163
Fetching Truncated Values……Page 164
The Basic SQL Statements……Page 165
Selecting Rows……Page 166
DML Returning Clause……Page 167
Updating Rows……Page 168
Cursors……Page 169
Declaring a Cursor……Page 170
Fetching from a Cursor……Page 172
Closing a Cursor……Page 173
Using the CURRENT OF Clause……Page 174
A Typical Sequence of Statements……Page 175
The PREFETCH Precompiler Option……Page 176
Sample Program 2: Cursor Operations……Page 177
6 Embedded PL/SQL……Page 181
Indicator Variables……Page 182
Integration with Oracle9i……Page 183
Subprograms……Page 184
Packages……Page 185
User-Defined Records……Page 186
Embedding PL/SQL Blocks……Page 187
PL/SQL Examples……Page 188
A More Complex PL/SQL Example……Page 189
VARCHAR Pseudotype……Page 191
Indicator Variables and PL/SQL……Page 192
Handling Truncated Values……Page 193
Host Tables and PL/SQL……Page 194
ARRAYLEN Statement……Page 196
Cursor Usage in Embedded PL/SQL……Page 200
Stored PL/SQL and Java Subprograms……Page 201
Calling a Stored PL/SQL or Java Subprogram……Page 202
Using Dynamic PL/SQL……Page 204
Sample Program 9: Calling a Stored Procedure……Page 205
Cursor Variables……Page 210
Declaring a Cursor Variable……Page 211
Opening a Cursor Variable……Page 212
Fetching from a Cursor Variable……Page 214
Closing a Cursor Variable……Page 215
Sample Program 11: Cursor Variables……Page 216
7 Host Tables……Page 221
Declaring Host Tables……Page 222
Referencing Host Tables……Page 223
Host Group Item Containing Tables……Page 225
Selecting into Tables……Page 226
Batch Fetches……Page 227
Number of Rows Fetched……Page 228
Fetching Truncated Values……Page 229
Sample Program 3: Fetching in Batches……Page 230
Inserting with Tables……Page 232
Updating with Tables……Page 233
Deleting with Tables……Page 234
Using Indicator Tables……Page 235
The FOR Clause……Page 236
Restrictions……Page 237
The WHERE Clause……Page 238
Mimicking the CURRENT OF Clause……Page 239
Tables of Group Items as Host Variables……Page 240
Sample Program 14: Tables of Group Items……Page 242
8 Error Handling and Diagnostics……Page 247
SQLCA……Page 248
ANSI SQLSTATE Variable……Page 249
Declaring SQLSTATE……Page 250
Using the SQL Communications Area……Page 252
Contents of the SQLCA……Page 253
Key Components of Error Reporting……Page 254
SQLCA Structure……Page 256
Getting the Full Text of Error Messages……Page 259
DSNTIAR……Page 260
WHENEVER Directive……Page 261
Coding the WHENEVER Statement……Page 263
Getting the Text of SQL Statements……Page 267
Contents of the ORACA……Page 269
Enabling the ORACA……Page 270
ORACA Structure……Page 271
ORACA Example Program……Page 274
How Errors Map to SQLSTATE Codes……Page 277
Status Variable Combinations……Page 283
9 Oracle Dynamic SQL……Page 287
Advantages and Disadvantages of Dynamic SQL……Page 288
How Dynamic SQL Statements Are Processed……Page 289
Method 1……Page 290
Method 4……Page 291
Guidelines……Page 292
Using Method 1……Page 293
An Example……Page 294
Sample Program 6: Dynamic SQL Method 1……Page 295
Using Method 2……Page 298
Sample Program 7: Dynamic SQL Method 2……Page 300
Using Method 3……Page 303
PREPARE……Page 304
FETCH……Page 305
Sample Program 8: Dynamic SQL Method 3……Page 306
Need for the SQLDA……Page 310
SQLDA Contents……Page 311
Implementing Method 4……Page 312
Using the DECLARE STATEMENT Statement……Page 313
Using PL/SQL……Page 314
With Method 4……Page 315
Caution……Page 316
10 ANSI Dynamic SQL……Page 317
Precompiler Options……Page 318
Overview of ANSI SQL Statements……Page 319
Sample Code……Page 322
Oracle Extensions……Page 323
Reference Semantics……Page 324
Using Tables for Bulk Operations……Page 325
ANSI Dynamic SQL Precompiler Options……Page 328
ALLOCATE DESCRIPTOR……Page 329
DEALLOCATE DESCRIPTOR……Page 330
GET DESCRIPTOR……Page 331
SET DESCRIPTOR……Page 334
DESCRIBE INPUT……Page 337
DESCRIBE OUTPUT……Page 338
EXECUTE……Page 339
Use of EXECUTE IMMEDIATE……Page 340
OPEN Cursor……Page 341
FETCH……Page 343
Differences From Oracle Dynamic Method 4……Page 344
Sample Programs: SAMPLE12.PCO……Page 345
11 Oracle Dynamic SQL: Method 4……Page 353
Information the Database Needs……Page 354
How Information is Obtained……Page 355
Multiple SQLDAs……Page 356
Declaring a SQLDA……Page 357
The SQLDA Variables……Page 360
Using SQLADR……Page 366
Converting Data……Page 367
Coercing Datatypes……Page 370
Handling NULL/Not NULL Datatypes……Page 373
The Basic Steps……Page 374
A Closer Look at Each Step……Page 375
Declare a Host String……Page 376
Declare the SQLDAs……Page 377
Initialize the Descriptors……Page 378
DESCRIBE the Bind Variables……Page 382
Get Values for Bind Variables……Page 385
DESCRIBE the Select List……Page 387
Reset Number of Select-List Items……Page 388
Reset Length/Datatype of Each Select-List Item……Page 389
FETCH Rows from the Active Set……Page 390
CLOSE the Cursor……Page 391
Using Host Tables with Method 4……Page 392
Sample Program 10: Dynamic SQL Method 4……Page 397
12 Multithreaded Applications……Page 407
Runtime Contexts in Pro*COBOL……Page 408
Runtime Context Usage Models……Page 410
Multiple Threads Sharing a Single Runtime Context……Page 411
Multiple Threads Sharing Multiple Runtime Contexts……Page 412
User Interface Features for Multithreaded Applications……Page 413
Embedded SQL Statements and Directives for Runtime Contexts……Page 414
Communication with Pro*C/C++ Programs……Page 415
Multiple Context Examples……Page 416
Multithreaded Example……Page 421
13 Large Objects (LOBs)……Page 429
Security for BFILEs……Page 430
LOB Locators……Page 431
LOB Buffering Subsystem……Page 432
How to Use LOBs……Page 433
Initializing a LOB……Page 435
Rules for LOB Statements……Page 436
For the LOB Buffering Subsystem……Page 437
APPEND……Page 438
ASSIGN……Page 439
CLOSE……Page 440
COPY……Page 441
DISABLE BUFFERING……Page 442
ERASE……Page 443
FILE CLOSE ALL……Page 444
FLUSH BUFFER……Page 445
LOAD FROM FILE……Page 446
READ……Page 448
TRIM……Page 450
WRITE……Page 451
DESCRIBE……Page 452
READ and WRITE Using the Polling Method……Page 455
LOB Sample Program: LOBDEMO1.PCO……Page 457
14 Precompiler Options……Page 469
Case-Sensitivity……Page 470
About the Options……Page 471
Precedence of Option Values……Page 472
Macro and Micro Options……Page 473
Entering Precompiler Options……Page 474
Inline……Page 475
Configuration Files……Page 476
Scope of Precompiler Options……Page 477
Quick Reference……Page 478
ASACC……Page 480
ASSUME_SQLCODE……Page 481
CLOSE_ON_COMMIT……Page 482
CONFIG……Page 483
DATE_FORMAT……Page 484
DECLARE_SECTION……Page 485
DYNAMIC……Page 486
END_OF_FETCH……Page 487
FIPS……Page 488
HOLD_CURSOR……Page 490
HOST……Page 491
INCLUDE……Page 492
IRECLEN……Page 493
LNAME……Page 494
LTYPE……Page 495
MAXLITERAL……Page 496
MAXOPENCURSORS……Page 497
MODE……Page 498
NLS_LOCAL……Page 499
ONAME……Page 500
ORECLEN……Page 501
PICX……Page 502
PREFETCH……Page 503
RELEASE_CURSOR……Page 504
SELECT_ERROR……Page 505
SQLCHECK……Page 506
TYPE_CODE……Page 508
USERID……Page 509
XREF……Page 510
A New Features……Page 513
LOB Support……Page 514
Tables of Group Items……Page 515
Support of Additional Datatypes……Page 516
Explicit Control Over the END-OF-FETCH SQLCODE Returned……Page 517
Date String Format Precompiler Option……Page 518
Support of Other Additional Datatypes……Page 519
Improved Error Reporting……Page 520
Migration From Earlier Releases……Page 521
B Operating System Dependencies……Page 523
Host Variables……Page 524
RETURN-CODE Special Register May Be Unpredictable…….Page 525
Byte-Order of Binary Data……Page 526
C C Reserved Words, Keywords, and Namespaces……Page 527
Reserved Words and Keywords……Page 528
Reserved Namespaces……Page 531
D Performance Tuning……Page 533
Improving Performance……Page 534
Using PL/SQL and Java……Page 535
Optimizing SQL Statements……Page 536
Optimizer Hints……Page 537
Eliminating Unnecessary Parsing……Page 538
Handling Explicit Cursors……Page 539
Using the Cursor Management Options……Page 541
Avoiding Unnecessary Reparsing……Page 545
E Syntactic and Semantic Checking……Page 547
Controlling the Type and Extent of Checking……Page 548
Enabling a Semantic Check……Page 549
F Embedded SQL Statements and Precompiler Directives……Page 553
Summary of Precompiler Directives and Embedded SQL Statements……Page 556
About the Statement Descriptions……Page 558
How to Read Syntax Diagrams……Page 559
Required Keywords and Parameters……Page 560
Syntax Loops……Page 561
ALLOCATE (Executable Embedded SQL Extension)……Page 562
ALLOCATE DESCRIPTOR (Executable Embedded SQL)……Page 564
CALL (Executable Embedded SQL)……Page 565
CLOSE (Executable Embedded SQL)……Page 566
COMMIT (Executable Embedded SQL)……Page 567
CONNECT (Executable Embedded SQL Extension)……Page 569
CONTEXT ALLOCATE (Executable Embedded SQL Extension)……Page 571
CONTEXT FREE (Executable Embedded SQL Extension)……Page 572
CONTEXT USE (Oracle Embedded SQL Directive)……Page 573
DEALLOCATE DESCRIPTOR (Embedded SQL Statement)……Page 575
DECLARE CURSOR (Embedded SQL Directive)……Page 576
DECLARE DATABASE (Oracle Embedded SQL Directive)……Page 578
DECLARE STATEMENT (Embedded SQL Directive)……Page 579
DECLARE TABLE (Oracle Embedded SQL Directive)……Page 581
DELETE (Executable Embedded SQL)……Page 583
DESCRIBE (Executable Embedded SQL)……Page 587
DESCRIBE DESCRIPTOR (Executable Embedded SQL)……Page 589
ENABLE THREADS (Executable Embedded SQL Extension)……Page 590
EXECUTE … END-EXEC (Executable Embedded SQL Extension)……Page 591
EXECUTE (Executable Embedded SQL)……Page 593
EXECUTE DESCRIPTOR (Executable Embedded SQL……Page 595
EXECUTE IMMEDIATE (Executable Embedded SQL)……Page 597
FETCH (Executable Embedded SQL)……Page 599
FETCH DESCRIPTOR (Executable Embedded SQL)……Page 602
FREE (Executable Embedded SQL Extension)……Page 605
GET DESCRIPTOR (Executable Embedded SQL)……Page 606
INSERT (Executable Embedded SQL)……Page 609
LOB APPEND (Executable Embedded SQL Extension)……Page 612
LOB ASSIGN (Executable Embedded SQL Extension)……Page 613
LOB COPY (Executable Embedded SQL Extension)……Page 614
LOB CREATE TEMPORARY (Executable Embedded SQL Extension)……Page 615
LOB DESCRIBE (Executable Embedded SQL Extension)……Page 616
LOB ENABLE BUFFERING (Executable Embedded SQL Extension)……Page 617
LOB ERASE (Executable Embedded SQL Extension)……Page 618
LOB FILE SET (Executable Embedded SQL Extension)……Page 619
LOB FLUSH BUFFER (Executable Embedded SQL Extension)……Page 620
LOB LOAD (Executable Embedded SQL Extension)……Page 621
LOB OPEN (Executable Embedded SQL Extension)……Page 622
LOB TRIM (Executable Embedded SQL Extension)……Page 623
LOB WRITE (Executable Embedded SQL Extension)……Page 624
OPEN (Executable Embedded SQL)……Page 625
OPEN DESCRIPTOR (Executable Embedded SQL)……Page 627
PREPARE (Executable Embedded SQL)……Page 630
ROLLBACK (Executable Embedded SQL)……Page 631
SAVEPOINT (Executable Embedded SQL)……Page 634
SELECT (Executable Embedded SQL)……Page 636
SET DESCRIPTOR (Executable Embedded SQL)……Page 639
UPDATE (Executable Embedded SQL)……Page 642
VAR (Oracle Embedded SQL Directive)……Page 646
WHENEVER (Embedded SQL Directive)……Page 648
Index……Page 651
Reviews
There are no reviews yet.