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.