SQL Anywhere® Server Programming

852 Pages·2006·4.23 MB·English

SQL Anywhere® Server
Programming
Published: October 2006
Copyright and trademarks
Copyright © 2006 iAnywhere Solutions, Inc. Portions copyright © 2006 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 ...................................................................................... ix
SQL Anywhere documentation .................................................................................. x
Documentation conventions ................................................................................... xiii
Finding out more and providing feedback ............................................................ xvii
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
Using the SQL Anywhere Explorer ...................................................................... 14
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 .......................................................................................................... 60
Three-tier computing architecture ........................................................................ 61
Using distributed transactions .............................................................................. 64
Using EAServer with SQL Anywhere ................................................................... 66
Copyright © 2006, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - Programming
II. Java in the database .............................................................................. 69
Introduction to Java in the Database ....................................................................... 71
Introduction .......................................................................................................... 72
Java in the database Q & A ................................................................................. 73
Java error handling .............................................................................................. 77
The runtime environment for Java in the database ............................................. 78
Using Java in the Database ...................................................................................... 83
Introduction .......................................................................................................... 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
ADO.NET 2.0 tracing support ............................................................................ 135
Tutorial: Using the SQL Anywhere .NET Data Provider ....................................... 137
Tutorial: Using the Simple code sample ............................................................ 138
Tutorial: Using the Table Viewer code sample .................................................. 142
iAnywhere.Data.SQLAnywhere namespace (.NET 2.0) ........................................ 147
SABulkCopy class ............................................................................................. 149
SABulkCopyColumnMapping class ................................................................... 160
SABulkCopyColumnMappingCollection class ................................................... 167
SABulkCopyOptions enumeration ..................................................................... 175
SACommand class ............................................................................................ 176
iv Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Programming
SACommandBuilder class ................................................................................. 197
SACommLinksOptionsBuilder class .................................................................. 206
SAConnection class ........................................................................................... 213
SAConnectionStringBuilder class ...................................................................... 228
SAConnectionStringBuilderBase class .............................................................. 248
SADataAdapter class ......................................................................................... 255
SADataReader class ......................................................................................... 266
SADataSourceEnumerator class ....................................................................... 295
SADbType enumeration .................................................................................... 297
SADefault class ................................................................................................. 301
SAError class ..................................................................................................... 302
SAErrorCollection class ..................................................................................... 305
SAException class ............................................................................................. 308
SAFactory class ................................................................................................. 312
SAInfoMessageEventArgs class ........................................................................ 318
SAInfoMessageEventHandler delegate ............................................................. 321
SAIsolationLevel enumeration ........................................................................... 322
SAMessageType enumeration .......................................................................... 324
SAMetaDataCollectionNames class .................................................................. 325
SAParameter class ............................................................................................ 334
SAParameterCollection class ............................................................................ 347
SAPermission class ........................................................................................... 363
SAPermissionAttribute class .............................................................................. 366
SARowsCopiedEventArgs class ........................................................................ 369
SARowsCopiedEventHandler delegate ............................................................. 372
SARowUpdatedEventArgs class ....................................................................... 373
SARowUpdatedEventHandler delegate ............................................................. 376
SARowUpdatingEventArgs class ....................................................................... 377
SARowUpdatingEventHandler delegate ............................................................ 379
SASpxOptionsBuilder class ............................................................................... 380
SATcpOptionsBuilder class ............................................................................... 386
SATransaction class .......................................................................................... 396
SQL Anywhere OLE DB and ADO APIs ................................................................. 401
Introduction to OLE DB ...................................................................................... 402
ADO programming with SQL Anywhere ............................................................ 403
Copyright © 2006, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - Programming
Setting up a Microsoft Linked Server using OLE DB ......................................... 409
Supported OLE DB interfaces ........................................................................... 410
ODBC API ................................................................................................................. 415
Introduction to ODBC ......................................................................................... 416
Building ODBC applications ............................................................................... 418
ODBC samples .................................................................................................. 422
ODBC handles ................................................................................................... 424
Connecting to a data source .............................................................................. 427
Executing SQL statements ................................................................................ 430
Working with result sets ..................................................................................... 434
Calling stored procedures .................................................................................. 440
Handling errors .................................................................................................. 442
JDBC API .................................................................................................................. 445
JDBC overview .................................................................................................. 446
Using the iAnywhere JDBC driver ..................................................................... 449
Using the jConnect JDBC driver ........................................................................ 451
Establishing JDBC connections ......................................................................... 455
Using JDBC to access data ............................................................................... 462
Using JDBC escape syntax ............................................................................... 469
SQL Anywhere Embedded SQL ............................................................................. 473
Introduction ........................................................................................................ 474
Sample embedded SQL programs .................................................................... 480
Embedded SQL data types ................................................................................ 485
Using host variables .......................................................................................... 489
The SQL Communication Area (SQLCA) .......................................................... 497
Static and dynamic SQL .................................................................................... 503
The SQL descriptor area (SQLDA) .................................................................... 507
Fetching data ..................................................................................................... 515
Sending and retrieving long values .................................................................... 523
Using stored procedures .................................................................................... 527
Embedded SQL programming techniques ......................................................... 530
SQL preprocessor .............................................................................................. 531
Library function reference .................................................................................. 534
Embedded SQL command summary ................................................................. 554
SQL Anywhere Perl DBD::SQLAnywhere API ...................................................... 557
vi Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - Programming
About DBD::SQLAnywhere ................................................................................ 558
Installing DBD::SQLAnywhere on Windows ...................................................... 559
Installing DBD::SQLAnywhere on Unix .............................................................. 561
Writing Perl scripts that use DBD::SQLAnywhere ............................................. 563
SQL Anywhere PHP API .......................................................................................... 567
About the SQL Anywhere PHP module ............................................................. 568
Installing and configuring SQL Anywhere PHP ................................................. 569
Running PHP test scripts in your web pages ..................................................... 574
Writing PHP scripts ............................................................................................ 577
SQL Anywhere PHP API reference ................................................................... 583
Sybase Open Client API .......................................................................................... 599
Open Client architecture .................................................................................... 600
What you need to build Open Client applications .............................................. 601
Data type mappings ........................................................................................... 602
Using SQL in Open Client applications .............................................................. 604
Known Open Client limitations of SQL Anywhere .............................................. 607
SQL Anywhere Web Services ................................................................................. 609
About web services ............................................................................................ 610
Quick start .......................................................................................................... 611
Creating web services ....................................................................................... 614
Starting a database server that listens for web requests ................................... 617
Understanding how URLs are interpreted ......................................................... 620
Creating SOAP and DISH web services ............................................................ 624
Tutorial: Accessing web services from Microsoft Visual C# .............................. 627
Tutorial: Accessing web services from Java JAX-RPC ..................................... 630
Using procedures that provide HTML documents ............................................. 635
Creating web service client functions and procedures ...................................... 638
Working with return values and result sets ........................................................ 642
Selecting from result sets .................................................................................. 644
Using parameters .............................................................................................. 645
Working with variables ....................................................................................... 648
Working with HTTP headers .............................................................................. 650
Using SOAP services ........................................................................................ 652
Working with SOAP headers ............................................................................. 655
Using HTTP sessions ........................................................................................ 662
Copyright © 2006, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - Programming
Using automatic character set conversion ......................................................... 668
Handling errors .................................................................................................. 669
IV. Using ADO and Visual Basic with SQL Anywhere ........................... 671
Tutorial: Develop a Simple Application in Visual Basic ...................................... 673
About developing a Visual Basic application ..................................................... 674
V. SQL Anywhere Database Tools Interface .......................................... 677
Database Tools Interface ........................................................................................ 679
Introduction to the database tools interface ....................................................... 680
Using the database tools interface .................................................................... 681
DBTools functions .............................................................................................. 688
DBTools structures ............................................................................................ 698
DBTools enumeration types .............................................................................. 735
Exit Codes ................................................................................................................ 741
Software component exit codes ......................................................................... 742
VI. Deploying SQL Anywhere .................................................................. 743
Deploying Databases and Applications ................................................................ 745
Deployment overview ........................................................................................ 746
Understanding installation directories and file names ....................................... 748
Using the Deployment wizard ............................................................................ 751
Using a silent installation for deployment .......................................................... 753
Deploying client applications ............................................................................. 756
Deploying administration tools ........................................................................... 772
Deploying SQL script files .................................................................................. 788
Deploying database servers .............................................................................. 789
Deploying embedded database applications ..................................................... 793
Index .................................................................................................................................. 795
viii Copyright © 2006, iAnywhere Solutions, Inc.
About This Manual
Subject
This book describes how to build and deploy database applications using the C, C++, and Java programming
languages, as well as Visual Studio .NET. Users of tools such as Visual Basic and PowerBuilder can use the
programming interfaces provided by those tools.
Audience
This manual is intended for application developers writing programs that work directly with one of the SQL
Anywhere interfaces.
You do not need to read this manual if you are using a development tool such as PowerBuilder or Visual
Basic, each of which has its own database interface on top of ODBC.
Copyright © 2006, iAnywhere Solutions, Inc. ix
About This Manual
SQL Anywhere documentation
This book is part of the SQL Anywhere documentation set. This section describes the books in the
documentation set and how you can use them.
The SQL Anywhere documentation
The complete SQL Anywhere documentation is available in two forms: an online form that combines all
books, and as separate PDF files for each book. Both forms of the documentation contain identical
information and consist of the following books:
♦ SQL Anywhere 10 - Introduction This book introduces SQL Anywhere 10—a comprehensive
package that provides data management and data exchange, enabling the rapid development of database-
powered applications for server, desktop, mobile, and remote office environments.
♦ SQL Anywhere 10 - Changes and Upgrading This book describes new features in SQL Anywhere
10 and in previous versions of the software.
♦ SQL Anywhere Server - Database Administration This book covers material related to running,
managing, and configuring SQL Anywhere databases. It describes database connections, the database
server, database files, security, backup procedures, security, and replication with Replication Server, as
well as administration utilities and options.
♦ SQL Anywhere Server - SQL Usage This book describes how to design and create databases; how
to import, export, and modify data; how to retrieve data; and how to build stored procedures and triggers.
♦ SQL Anywhere Server - SQL Reference This book provides a complete reference for the SQL
language used by SQL Anywhere. It also describes the SQL Anywhere system views and procedures.
♦ SQL Anywhere Server - Programming This book describes how to build and deploy database
applications using the C, C++, and Java programming languages, as well as Visual Studio .NET. Users
of tools such as Visual Basic and PowerBuilder can use the programming interfaces provided by those
tools.
♦ SQL Anywhere 10 - Error Messages This book provides a complete listing of SQL Anywhere error
messages together with diagnostic information.
♦ MobiLink - Getting Started This manual introduces MobiLink, a session-based relational-database
synchronization system. MobiLink technology allows two-way replication and is well suited to mobile
computing environments.
♦ MobiLink - Server Administration This manual describes how to set up and administer MobiLink
applications.
♦ MobiLink - Client Administration This manual describes how to set up, configure, and synchronize
MobiLink clients. MobiLink clients can be SQL Anywhere or UltraLite databases.
♦ MobiLink - Server-Initiated Synchronization This manual describes MobiLink server-initiated
synchronization, a feature of MobiLink that allows you to initiate synchronization or other remote actions
from the consolidated database.
x Copyright © 2006, iAnywhere Solutions, Inc.