SQL Anywhere® Server Programming

908 Pages·2007·4.07 MB·English

SQL Anywhere® Server
Programming
Published: March 2007
Copyright and trademarks
Copyright © 2007 iAnywhere Solutions, Inc. Portions copyright © 2007 Sybase, Inc. All rights reserved.
iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.
iAnywhere grants you permission to use this document for your own informational, educational, and other non-commercial purposes; provided
that (1) you include this and all other copyright and proprietary notices in the document in all copies; (2) you do not attempt to "pass-off" the
document as your own; and (3) you do not modify the document. You may not publish or distribute the document or any portion thereof without
the express prior written consent of iAnywhere.
This document is not a commitment on the part of iAnywhere to do or refrain from any activity, and iAnywhere may change the content of
this document at its sole discretion without notice. Except as otherwise provided in a written agreement between you and iAnywhere, this
document is provided “as is”, and iAnywhere assumes no liability for its use or any inaccuracies it may contain.
iAnywhere®, Sybase®, and the marks listed at http://www.ianywhere.com/trademarks are trademarks of Sybase, Inc. or its subsidiaries. ®
indicates registration in the United States of America.
Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Contents
About This Manual ...................................................................................... xi
SQL Anywhere documentation ................................................................................ xii
Documentation conventions .................................................................................... xv
Finding out more and providing feedback ............................................................. xix
I. Introduction to Programming with SQL Anywhere ................................ 1
SQL Anywhere Data Access Programming Interfaces ............................................ 3
SQL Anywhere .NET API ....................................................................................... 4
SQL Anywhere OLE DB and ADO APIs ................................................................ 5
ODBC API .............................................................................................................. 6
JDBC API ............................................................................................................... 7
SQL Anywhere embedded SQL ............................................................................ 8
Sybase Open Client API ........................................................................................ 9
Perl DBD::SQLAnywhere API .............................................................................. 10
SQL Anywhere PHP API ..................................................................................... 11
SQL Anywhere web services ............................................................................... 12
SQL Anywhere Explorer ........................................................................................... 13
Introduction to the SQL Anywhere Explorer ........................................................ 14
Using the SQL Anywhere Explorer ...................................................................... 15
Using SQL in Applications ....................................................................................... 19
Executing SQL statements in applications .......................................................... 20
Preparing statements ........................................................................................... 22
Introduction to cursors ......................................................................................... 25
Working with cursors ........................................................................................... 28
Choosing cursor types ......................................................................................... 35
SQL Anywhere cursors ........................................................................................ 37
Describing result sets .......................................................................................... 53
Controlling transactions in applications ............................................................... 55
Three-Tier Computing and Distributed Transactions ............................................ 59
Introduction to three-tier computing and distributed transactions ........................ 60
Three-tier computing architecture ........................................................................ 61
Using distributed transactions .............................................................................. 64
Copyright © 2007, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - Programming
Using EAServer with SQL Anywhere ................................................................... 66
II. Java in the database .............................................................................. 69
Java in the Database ................................................................................................. 71
Introduction to Java in the database .................................................................... 72
Java in the database Q & A ................................................................................. 73
Java error handling .............................................................................................. 77
The runtime environment for Java in the database ............................................. 78
Tutorial: Using Java in the Database ....................................................................... 83
Introduction to Java in the Database tutorial ....................................................... 84
Installing Java classes into a database ............................................................... 91
Special features of Java classes in the database ................................................ 95
Starting and stopping the Java VM ...................................................................... 99
Unsupported Java classes ................................................................................. 100
III. SQL Anywhere Data Access APIs ..................................................... 101
SQL Anywhere .NET Data Provider ....................................................................... 103
SQL Anywhere .NET Data Provider features .................................................... 104
Running the sample projects ............................................................................. 105
Using the .NET Data Provider in a Visual Studio .NET project .......................... 106
Connecting to a database .................................................................................. 108
Accessing and manipulating data ...................................................................... 111
Using stored procedures .................................................................................... 128
Transaction processing ...................................................................................... 130
Error handling and the SQL Anywhere .NET Data Provider .............................. 132
Deploying the SQL Anywhere .NET Data Provider ........................................... 133
.NET 2.0 tracing support .................................................................................... 135
Tutorial: Using the SQL Anywhere .NET Data Provider ....................................... 139
Introduction to the .NET data provider tutorial ................................................... 140
Using the Simple code sample .......................................................................... 141
Using the Table Viewer code sample ................................................................ 145
SQL Anywhere .NET 2.0 API Reference ................................................................ 149
SABulkCopy class ............................................................................................. 151
SABulkCopyColumnMapping class ................................................................... 163
iv Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Programming
SABulkCopyColumnMappingCollection class ................................................... 170
SABulkCopyOptions enumeration ..................................................................... 179
SACommand class ............................................................................................ 180
SACommandBuilder class ................................................................................. 203
SACommLinksOptionsBuilder class .................................................................. 213
SAConnection class ........................................................................................... 221
SAConnectionStringBuilder class ...................................................................... 238
SAConnectionStringBuilderBase class .............................................................. 259
SADataAdapter class ......................................................................................... 266
SADataReader class ......................................................................................... 277
SADataSourceEnumerator class ....................................................................... 308
SADbType enumeration .................................................................................... 310
SADefault class ................................................................................................. 315
SAError class ..................................................................................................... 317
SAErrorCollection class ..................................................................................... 320
SAException class ............................................................................................. 323
SAFactory class ................................................................................................. 327
SAInfoMessageEventArgs class ........................................................................ 334
SAInfoMessageEventHandler delegate ............................................................. 338
SAIsolationLevel enumeration ........................................................................... 339
SAMessageType enumeration .......................................................................... 341
SAMetaDataCollectionNames class .................................................................. 342
SAParameter class ............................................................................................ 352
SAParameterCollection class ............................................................................ 366
SAPermission class ........................................................................................... 384
SAPermissionAttribute class .............................................................................. 387
SARowsCopiedEventArgs class ........................................................................ 390
SARowsCopiedEventHandler delegate ............................................................. 393
SARowUpdatedEventArgs class ....................................................................... 394
SARowUpdatedEventHandler delegate ............................................................. 397
SARowUpdatingEventArgs class ....................................................................... 398
SARowUpdatingEventHandler delegate ............................................................ 401
SASpxOptionsBuilder class ............................................................................... 402
SATcpOptionsBuilder class ............................................................................... 408
SATransaction class .......................................................................................... 419
Copyright © 2007, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - Programming
SQL Anywhere OLE DB and ADO APIs ................................................................. 425
Introduction to OLE DB ...................................................................................... 426
ADO programming with SQL Anywhere ............................................................ 427
Setting up a Microsoft Linked Server using OLE DB ......................................... 433
Supported OLE DB interfaces ........................................................................... 434
SQL Anywhere ODBC API ...................................................................................... 439
Introduction to ODBC ......................................................................................... 440
Building ODBC applications ............................................................................... 442
ODBC samples .................................................................................................. 446
ODBC handles ................................................................................................... 448
Choosing an ODBC connection function ........................................................... 451
Executing SQL statements ................................................................................ 454
Working with result sets ..................................................................................... 458
Calling stored procedures .................................................................................. 464
Handling errors .................................................................................................. 466
SQL Anywhere JDBC API ....................................................................................... 469
Introduction to JDBC .......................................................................................... 470
Using the iAnywhere JDBC driver ..................................................................... 473
Using the jConnect JDBC driver ........................................................................ 475
Connecting from a JDBC client application ....................................................... 479
Using JDBC to access data ............................................................................... 485
Using JDBC escape syntax ............................................................................... 492
SQL Anywhere Embedded SQL ............................................................................. 495
Introduction to embedded SQL .......................................................................... 496
Sample embedded SQL programs .................................................................... 502
Embedded SQL data types ................................................................................ 507
Using host variables .......................................................................................... 511
The SQL Communication Area (SQLCA) .......................................................... 519
Static and dynamic SQL .................................................................................... 525
The SQL descriptor area (SQLDA) .................................................................... 529
Fetching data ..................................................................................................... 537
Sending and retrieving long values .................................................................... 545
Using simple stored procedures ........................................................................ 549
Embedded SQL programming techniques ......................................................... 552
SQL preprocessor .............................................................................................. 553
vi Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Programming
Library function reference .................................................................................. 557
Embedded SQL command summary ................................................................. 578
SQL Anywhere Perl DBD::SQLAnywhere API ...................................................... 581
Introduction to DBD::SQLAnywhere .................................................................. 582
Installing DBD::SQLAnywhere on Windows ...................................................... 583
Installing DBD::SQLAnywhere on Unix .............................................................. 585
Writing Perl scripts that use DBD::SQLAnywhere ............................................. 587
SQL Anywhere PHP API .......................................................................................... 591
Introduction to the SQL Anywhere PHP module ................................................ 592
Installing and configuring SQL Anywhere PHP ................................................. 593
Running PHP test scripts in your web pages ..................................................... 598
Writing PHP scripts ............................................................................................ 600
SQL Anywhere PHP API reference ................................................................... 606
Sybase Open Client API .......................................................................................... 621
Open Client architecture .................................................................................... 622
What you need to build Open Client applications .............................................. 623
Data type mappings ........................................................................................... 624
Using SQL in Open Client applications .............................................................. 626
Known Open Client limitations of SQL Anywhere .............................................. 629
SQL Anywhere Web Services ................................................................................. 631
Introduction to web services .............................................................................. 632
Quick start to web services ................................................................................ 633
Creating web services ....................................................................................... 636
Starting a database server that listens for web requests ................................... 639
Understanding how URLs are interpreted ......................................................... 642
Creating SOAP and DISH web services ............................................................ 646
Tutorial: Accessing web services from Microsoft .NET ...................................... 649
Tutorial: Accessing web services from Java JAX-RPC ..................................... 652
Using procedures that provide HTML documents ............................................. 657
Working with data types ..................................................................................... 660
Tutorial: Using data types with Microsoft .NET .................................................. 665
Creating web service client functions and procedures ...................................... 670
Working with return values and result sets ........................................................ 675
Selecting from result sets .................................................................................. 677
Using parameters .............................................................................................. 678
Copyright © 2007, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - Programming
Working with structured data types .................................................................... 681
Working with variables ....................................................................................... 686
Working with HTTP headers .............................................................................. 688
Using SOAP services ........................................................................................ 690
Working with SOAP headers ............................................................................. 693
Working with MIME types .................................................................................. 700
Using HTTP sessions ........................................................................................ 703
Using automatic character set conversion ......................................................... 709
Handling errors .................................................................................................. 710
IV. Using ADO and Visual Basic with SQL Anywhere ........................... 713
Tutorial: Develop a Simple Application in Visual Basic ...................................... 715
Introduction to Visual Basic tutorial .................................................................... 716
V. SQL Anywhere Database Tools Interface .......................................... 719
Database Tools Interface ........................................................................................ 721
Introduction to the database tools interface ....................................................... 722
Using the database tools interface .................................................................... 724
DBTools functions .............................................................................................. 731
DBTools structures ............................................................................................ 740
DBTools enumeration types .............................................................................. 777
Exit Codes ................................................................................................................ 783
Software component exit codes ......................................................................... 784
VI. Deploying SQL Anywhere .................................................................. 785
Deploying Databases and Applications ................................................................ 787
Introduction to deployment ................................................................................ 788
Understanding installation directories and file names ....................................... 790
Using the Deployment wizard ............................................................................ 793
Using a silent installation for deployment .......................................................... 795
Deploying client applications ............................................................................. 798
Deploying administration tools ........................................................................... 816
Deploying SQL script files .................................................................................. 837
viii Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Programming
Deploying database servers .............................................................................. 838
Deploying security ............................................................................................. 842
Deploying embedded database applications ..................................................... 843
Index .................................................................................................................................. 847
Copyright © 2007, iAnywhere Solutions, Inc. ix
x