SQL Anywhere® Server Programming

1044 Pages·2008·5.01 MB·English

SQL Anywhere® Server
Programming
June 2008
Version 11.0.0
Copyright and trademarks
Copyright © 2008 iAnywhere Solutions, Inc. Portions copyright © 2008 Sybase, Inc. All rights reserved.
This documentation is provided AS IS, without warranty or liability of any kind (unless provided by a separate written agreement between
you and iAnywhere).
You may use, print, reproduce, and distribute this documentation (in whole or in part) subject to the following conditions: 1) you must retain
this and all other proprietary notices, on all copies of the documentation or portions thereof, 2) you may not modify the documentation, 3) you
may not do anything to indicate that you or anyone other than iAnywhere is the author or source of the documentation.
iAnywhere®, Sybase®, and the marks listed at http://www.sybase.com/detail?id=1011207 are trademarks of Sybase, Inc. or its subsidiaries.
® indicates registration in the United States of America.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Contents
About this book ........................................................................................... ix
About the SQL Anywhere documentation ................................................................ x
I. Introduction to Programming with SQL Anywhere ................................ 1
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
SQL Anywhere Perl DBI API ................................................................................. 9
SQL Anywhere Python Database API ................................................................. 10
SQL Anywhere PHP API ..................................................................................... 11
SQL Anywhere web services ............................................................................... 12
Sybase Open Client API ...................................................................................... 13
2. SQL Anywhere Explorer ....................................................................................... 15
Introduction to the SQL Anywhere Explorer ........................................................ 16
Using the SQL Anywhere Explorer ...................................................................... 17
3. Using SQL in applications .................................................................................... 21
Executing SQL statements in applications .......................................................... 22
Preparing statements ........................................................................................... 24
Introduction to cursors ......................................................................................... 27
Working with cursors ........................................................................................... 30
Choosing cursor types ......................................................................................... 37
SQL Anywhere cursors ........................................................................................ 39
Describing result sets .......................................................................................... 56
Controlling transactions in applications ............................................................... 58
4. Three-tier computing and distributed transactions ........................................... 63
Introduction to three-tier computing and distributed transactions ........................ 64
Three-tier computing architecture ........................................................................ 65
Using distributed transactions .............................................................................. 68
Using EAServer with SQL Anywhere ................................................................... 70
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 iii
SQL Anywhere® Server - Programming
II. Java in the database .............................................................................. 73
5. Java support in SQL Anywhere ........................................................................... 75
Introduction to Java support ................................................................................ 76
Java in the database Q & A ................................................................................. 78
Java error handling .............................................................................................. 82
The runtime environment for Java in the database ............................................. 83
6. Tutorial: Using Java in the database ................................................................... 87
Introduction to Java in the database tutorial ........................................................ 88
Installing Java classes into a database ............................................................... 95
Special features of Java classes in the database ................................................ 99
Starting and stopping the Java VM .................................................................... 103
Unsupported Java classes ................................................................................. 104
III. SQL Anywhere Data Access APIs ..................................................... 105
7. SQL Anywhere .NET Data Provider ................................................................... 107
SQL Anywhere .NET Data Provider features .................................................... 108
Running the sample projects ............................................................................. 109
Using the .NET Data Provider in a Visual Studio project ................................... 110
Connecting to a database .................................................................................. 112
Accessing and manipulating data ...................................................................... 115
Using stored procedures .................................................................................... 132
Transaction processing ...................................................................................... 134
Error handling and the SQL Anywhere .NET Data Provider .............................. 136
Deploying the SQL Anywhere .NET Data Provider ........................................... 137
Tracing support .................................................................................................. 139
8. Tutorial: Using the SQL Anywhere .NET Data Provider ................................... 143
Introduction to the .NET Data Provider tutorial .................................................. 144
Using the Simple code sample .......................................................................... 145
Using the Table Viewer code sample ................................................................ 148
9. Tutorial: Developing a simple .NET database application with Visual
Studio ....................................................................................................................... 153
Lesson 1. Create a table viewer ........................................................................ 154
Lesson 2. Add a synchronizing data control ...................................................... 158
10. SQL Anywhere .NET 2.0 API Reference .......................................................... 163
iv Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
SQL Anywhere® Server - Programming
iAnywhere.Data.SQLAnywhere namespace (.NET 2.0) .................................... 164
iAnywhere.SQLAnywhere.Server namespace (.NET 2.0) ................................. 422
11. 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 ......................................... 434
Supported OLE DB interfaces ........................................................................... 435
12. SQL Anywhere ODBC API ................................................................................ 441
Introduction to ODBC ......................................................................................... 442
Building ODBC applications ............................................................................... 444
ODBC samples .................................................................................................. 449
ODBC handles ................................................................................................... 450
Choosing an ODBC connection function ........................................................... 453
SQL Anywhere connection attributes ................................................................ 456
Executing SQL statements ................................................................................ 458
Working with result sets ..................................................................................... 462
Calling stored procedures .................................................................................. 471
Handling errors .................................................................................................. 473
13. SQL Anywhere JDBC API ................................................................................. 477
Introduction to JDBC .......................................................................................... 478
Using the iAnywhere JDBC driver ..................................................................... 481
Using the jConnect JDBC driver ........................................................................ 483
Connecting from a JDBC client application ....................................................... 487
Using JDBC to access data ............................................................................... 493
Using JDBC escape syntax ............................................................................... 502
iAnywhere JDBC 3.0 API support ...................................................................... 505
14. SQL Anywhere embedded SQL ........................................................................ 507
Introduction to embedded SQL .......................................................................... 508
Sample embedded SQL programs .................................................................... 514
Embedded SQL data types ................................................................................ 518
Using host variables .......................................................................................... 522
The SQL Communication Area (SQLCA) .......................................................... 531
Static and dynamic SQL .................................................................................... 537
The SQL descriptor area (SQLDA) .................................................................... 541
Fetching data ..................................................................................................... 550
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 v
SQL Anywhere® Server - Programming
Sending and retrieving long values .................................................................... 558
Using simple stored procedures ........................................................................ 562
Embedded SQL programming techniques ......................................................... 565
SQL preprocessor .............................................................................................. 566
Library function reference .................................................................................. 569
Embedded SQL statement summary ................................................................. 591
15. SQL Anywhere External Function API ............................................................. 593
Calling external libraries from procedures ......................................................... 594
Creating procedures and functions with external calls ...................................... 595
External function prototypes .............................................................................. 597
Using the external function call API routines ..................................................... 599
Handling data types ........................................................................................... 603
Unloading external libraries ............................................................................... 606
16. SQL Anywhere Perl DBD::SQLAnywhere API ................................................ 607
Introduction to DBD::SQLAnywhere .................................................................. 608
Installing DBD::SQLAnywhere on Windows ...................................................... 609
Installing DBD::SQLAnywhere on Unix .............................................................. 611
Writing Perl scripts that use DBD::SQLAnywhere ............................................. 613
17. SQL Anywhere for Python Database API ........................................................ 617
Introduction to sqlanydb ..................................................................................... 618
Installing sqlanydb on Windows ......................................................................... 619
Installing sqlanydb on Unix ................................................................................ 620
Writing Python scripts that use sqlanydb ........................................................... 621
18. SQL Anywhere PHP API .................................................................................... 625
Introduction to the SQL Anywhere PHP module ................................................ 626
Installing and configuring SQL Anywhere PHP ................................................. 627
Running PHP test scripts in your web pages ..................................................... 632
Writing PHP scripts ............................................................................................ 634
SQL Anywhere PHP API reference ................................................................... 640
19. SQL Anywhere external environment support ............................................... 683
Overview of external environments ................................................................... 684
The PERL external environment ........................................................................ 688
The PHP external environment .......................................................................... 692
The ESQL and ODBC external environments ................................................... 696
The CLR external environment .......................................................................... 702
vi Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
SQL Anywhere® Server - Programming
20. Sybase Open Client API .................................................................................... 705
Open Client architecture .................................................................................... 706
What you need to build Open Client applications .............................................. 707
Data type mappings ........................................................................................... 708
Using SQL in Open Client applications .............................................................. 710
Known Open Client limitations of SQL Anywhere .............................................. 713
21. SQL Anywhere web services ............................................................................ 715
Introduction to web services .............................................................................. 716
Quick start to web services ................................................................................ 717
Creating web services ....................................................................................... 722
Starting a database server that listens for web requests ................................... 725
Understanding how URLs are interpreted ......................................................... 728
Creating SOAP and DISH web services ............................................................ 732
Tutorial: Accessing web services from Microsoft .NET ...................................... 735
Tutorial: Accessing web services from JAX-WS ................................................ 738
Using procedures that provide HTML documents ............................................. 743
Working with data types ..................................................................................... 746
Tutorial: Using data types with Microsoft .NET .................................................. 752
Tutorial: Using data types with JAX-WS ............................................................ 757
Using the iAnywhere WSDL compiler ................................................................ 763
Creating web service client functions and procedures ...................................... 765
Working with return values and result sets ........................................................ 770
Selecting from result sets .................................................................................. 772
Using parameters .............................................................................................. 773
Working with structured data types .................................................................... 776
Working with variables ....................................................................................... 782
Working with HTTP headers .............................................................................. 784
Using SOAP services ........................................................................................ 786
Working with SOAP headers ............................................................................. 789
Working with MIME types .................................................................................. 796
Using HTTP sessions ........................................................................................ 799
Using automatic character set conversion ......................................................... 805
Handling errors .................................................................................................. 806
IV. SQL Anywhere Database Tools Interface ......................................... 809
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 vii
SQL Anywhere® Server - Programming
22. Database tools interface ................................................................................... 811
Introduction to the database tools interface ....................................................... 812
Using the database tools interface .................................................................... 814
DBTools functions .............................................................................................. 821
DBTools structures ............................................................................................ 831
DBTools enumeration types .............................................................................. 871
23. Exit codes ........................................................................................................... 877
Software component exit codes ......................................................................... 878
V. Deploying SQL Anywhere ................................................................... 879
24. Deploying databases and applications ........................................................... 881
Introduction to deployment ................................................................................ 882
Understanding installation directories and file names ....................................... 884
Using the Deployment Wizard ......................................................................... 887
Using a silent install for deployment .................................................................. 889
Deploying client applications ............................................................................. 891
Deploying administration tools ........................................................................... 910
Deploying SQL script files .................................................................................. 933
Deploying database servers .............................................................................. 934
Deploying security ............................................................................................. 939
Deploying embedded database applications ..................................................... 940
VI. Glossary ............................................................................................... 945
25. Glossary ............................................................................................................. 947
Index .......................................................................................................... 977
viii Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0
About this book
Subject
This book describes how to build and deploy database applications using the C, C++, Java, PHP, Perl, Python,
and .NET programming languages such as Visual Basic and Visual C#. A variety of programming interfaces
such as ADO.NET and ODBC are described.
Audience
This book is intended for application developers writing programs that work directly with one of the SQL
Anywhere interfaces.
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 ix
About this book
About the SQL Anywhere documentation
The complete SQL Anywhere documentation is available in three formats that contain identical information.
● HTML Help The online Help contains the complete SQL Anywhere documentation, including the
books and the context-sensitive help for SQL Anywhere tools. The online Help is updated with each
release of the product.
If you are using a Microsoft Windows operating system, the online Help is provided in HTML Help
(CHM) format. To access the documentation, choose Start » Programs » SQL Anywhere 11 »
Documentation » Online Books.
The administration tools use the same online documentation for their Help features.
● Eclipse On Unix platforms, the complete online Help is provided in Eclipse format. To access the
documentation, run sadoc from the bin32 or bin64 directory of your SQL Anywhere 11 installation.
● PDF The complete set of SQL Anywhere books is provided as a set of Portable Document Format
(PDF) files. You must have a PDF reader to view information. To download Adobe Reader, visit
Adobe.com.
To access the PDF documentation on Microsoft Windows operating systems, choose Start »
Programs » SQL Anywhere 11 » Documentation » Online Books - PDF Format.
To access the PDF documentation on Unix operating systems, use a web browser to open install-dir/
documentation/en/pdf/index.html.
About the books in the documentation set
SQL Anywhere documentation
The SQL Anywhere documentation consists of the following books:
● SQL Anywhere 11 - Introduction This book introduces SQL Anywhere 11—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 11 - Changes and Upgrading This book describes new features in SQL Anywhere
11 and in previous versions of the software.
● SQL Anywhere Server - Database Administration This book describes how to run, manage, and
configure SQL Anywhere databases. It describes database connections, the database server, database
files, backup procedures, security, high availability, and replication with Replication Server, as well as
administration utilities and options.
● SQL Anywhere Server - Programming This book describes how to build and deploy database
applications using the C, C++, Java, PHP, Perl, Python, and .NET programming languages such as Visual
Basic and Visual C#. A variety of programming interfaces such as ADO.NET and ODBC are described.
● SQL Anywhere Server - SQL Reference This book provides reference information for system
procedures, and the catalog (system tables and views). It also provides an explanation of the SQL
Anywhere implementation of the SQL language (search conditions, syntax, data types, and functions).
x Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0