SQL Anywhere® Server - Programming

1194 Pages·2009·6.07 MB·English

SQL Anywhere® Server
Programming
February 2009
Version 11.0.1
Copyright and trademarks
Copyright © 2009 iAnywhere Solutions, Inc. Portions copyright © 2009 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 ........................................................................................... xi
About the SQL Anywhere documentation .............................................................. xii
Introduction to Programming with SQL Anywhere ................................... 1
SQL Anywhere data access programming interfaces ............................................................. 3
SQL Anywhere .NET support ................................................................................ 4
SQL Anywhere OLE DB and ADO support ............................................................ 5
SQL Anywhere ODBC support .............................................................................. 6
SQL Anywhere JDBC support ............................................................................... 7
SQL Anywhere embedded SQL ............................................................................ 8
SQL Anywhere C language support ...................................................................... 9
SQL Anywhere Perl DBI support ......................................................................... 10
SQL Anywhere Python support ........................................................................... 11
SQL Anywhere PHP support ............................................................................... 12
SQL Anywhere Ruby support .............................................................................. 13
SQL Anywhere web services support .................................................................. 14
Sybase Open Client support ................................................................................ 15
SQL Anywhere Explorer .................................................................................................... 17
Introduction to the SQL Anywhere Explorer ........................................................ 18
Using the SQL Anywhere Explorer ...................................................................... 19
Using SQL in applications ................................................................................................. 23
Executing SQL statements in applications .......................................................... 24
Preparing statements ........................................................................................... 26
Introduction to cursors ......................................................................................... 29
Working with cursors ........................................................................................... 32
Choosing cursor types ......................................................................................... 39
SQL Anywhere cursors ........................................................................................ 41
Describing result sets .......................................................................................... 58
Controlling transactions in applications ............................................................... 60
Three-tier computing and distributed transactions ............................................................... 65
Introduction to three-tier computing and distributed transactions ........................ 66
Three-tier computing architecture ........................................................................ 67
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 iii
SQL Anywhere® Server - Programming
Using distributed transactions .............................................................................. 70
Using EAServer with SQL Anywhere ................................................................... 72
Java in the database .................................................................................. 75
Java support in SQL Anywhere .......................................................................................... 77
Introduction to Java support ................................................................................ 78
Java in the database Q & A ................................................................................. 80
Java error handling .............................................................................................. 84
The runtime environment for Java in the database ............................................. 85
Creating a Java class for use with SQL Anywhere .............................................. 89
Choosing a Java VM ............................................................................................ 91
Install the sample Java class ............................................................................... 93
Using the CLASSPATH variable .......................................................................... 94
Accessing methods in the Java class .................................................................. 95
Accessing fields and methods of the Java object ................................................ 96
Installing Java classes into a database ............................................................... 98
Special features of Java classes in the database .............................................. 102
Starting and stopping the Java VM .................................................................... 106
SQL Anywhere Data Access APIs .......................................................... 107
SQL Anywhere .NET Data Provider ................................................................................. 109
SQL Anywhere .NET Data Provider features .................................................... 110
Running the sample projects ............................................................................. 111
Using the .NET Data Provider in a Visual Studio project ................................... 112
Connecting to a database .................................................................................. 114
Accessing and manipulating data ...................................................................... 117
Using stored procedures .................................................................................... 134
Transaction processing ...................................................................................... 136
Error handling and the SQL Anywhere .NET Data Provider .............................. 138
Deploying the SQL Anywhere .NET Data Provider ........................................... 139
Tracing support .................................................................................................. 141
Tutorial: Using the SQL Anywhere .NET Data Provider ..................................................... 145
Introduction to the .NET Data Provider tutorial .................................................. 146
Using the Simple code sample .......................................................................... 147
iv Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Programming
Using the Table Viewer code sample ................................................................ 150
SQL Anywhere ASP.NET Providers ................................................................................. 155
Adding the SQL Anywhere ASP.NET provider schema to the database ........... 157
Registering the connection string ...................................................................... 158
Registering the SQL Anywhere ASP.NET providers ......................................... 159
Membership provider XML attributes ................................................................. 161
Roles provider table schema ............................................................................. 162
Profile provider table schema ............................................................................ 163
Web Part Personalization provider table schema .............................................. 164
Health Monitoring provider table schema .......................................................... 165
Tutorial: Developing a simple .NET database application with Visual Studio ....................... 167
Lesson 1: Create a table viewer ........................................................................ 168
Lesson 2: Add a synchronizing data control ...................................................... 172
SQL Anywhere .NET 2.0 API reference ............................................................................ 177
iAnywhere.Data.SQLAnywhere namespace (.NET 2.0) .................................... 178
SQL Anywhere OLE DB and ADO development ............................................................... 447
Introduction to OLE DB ...................................................................................... 448
ADO programming with SQL Anywhere ............................................................ 449
Setting up a Microsoft Linked Server using OLE DB ......................................... 456
Supported OLE DB interfaces ........................................................................... 457
SQL Anywhere ODBC API .............................................................................................. 463
Introduction to ODBC ......................................................................................... 464
Building ODBC applications ............................................................................... 466
ODBC samples .................................................................................................. 471
ODBC handles ................................................................................................... 473
Choosing an ODBC connection function ........................................................... 476
SQL Anywhere connection attributes ................................................................ 479
Executing SQL statements ................................................................................ 481
64-bit ODBC considerations .............................................................................. 485
Data alignment requirements ............................................................................. 489
Working with result sets ..................................................................................... 491
Calling stored procedures .................................................................................. 496
Handling errors .................................................................................................. 498
SQL Anywhere JDBC driver ............................................................................................ 501
Introduction to JDBC .......................................................................................... 502
Using the iAnywhere JDBC driver ..................................................................... 505
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 v
SQL Anywhere® Server - Programming
Using the jConnect JDBC driver ........................................................................ 507
Connecting from a JDBC client application ....................................................... 511
Using JDBC to access data ............................................................................... 517
Using JDBC escape syntax ............................................................................... 526
iAnywhere JDBC 3.0 API support ...................................................................... 529
SQL Anywhere embedded SQL ....................................................................................... 531
Introduction to embedded SQL .......................................................................... 532
Sample embedded SQL programs .................................................................... 538
Embedded SQL data types ................................................................................ 542
Using host variables .......................................................................................... 546
The SQL Communication Area (SQLCA) .......................................................... 555
Static and dynamic SQL .................................................................................... 561
The SQL descriptor area (SQLDA) .................................................................... 565
Fetching data ..................................................................................................... 574
Sending and retrieving long values .................................................................... 582
Using simple stored procedures ........................................................................ 586
Embedded SQL programming techniques ......................................................... 589
SQL preprocessor .............................................................................................. 590
Library function reference .................................................................................. 593
Embedded SQL statement summary ................................................................. 615
SQL Anywhere C API reference ....................................................................................... 617
Introduction to the SQL Anywhere C API version 1.0 ........................................ 618
sacapidll.h .......................................................................................................... 619
sacapi.h ............................................................................................................. 621
a_sqlany_bind_param struct .............................................................................. 639
a_sqlany_bind_param_info struct ...................................................................... 640
a_sqlany_column_info struct ............................................................................. 641
a_sqlany_data_info struct .................................................................................. 642
a_sqlany_data_value struct ............................................................................... 643
SQLAnywhereInterface struct ............................................................................ 644
a_sqlany_data_direction enumeration ............................................................... 647
a_sqlany_data_type enumeration ...................................................................... 648
a_sqlany_native_type enumeration ................................................................... 650
sacapi_error_size constant ................................................................................ 651
sqlany_current_api_version constant ................................................................ 652
SQL Anywhere C API examples ........................................................................ 653
vi Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Programming
SQL Anywhere External Function API .............................................................................. 669
Calling external libraries from procedures ......................................................... 670
Creating procedures and functions with external calls ...................................... 671
External function prototypes .............................................................................. 673
Using the external function call API methods .................................................... 681
Handling data types ........................................................................................... 685
Unloading external libraries ............................................................................... 688
SQL Anywhere external environment support ................................................................... 689
Overview of external environments ................................................................... 690
The CLR external environment .......................................................................... 694
The ESQL and ODBC external environments ................................................... 697
The Java external environment ......................................................................... 706
The PERL external environment ........................................................................ 711
The PHP external environment .......................................................................... 715
SQL Anywhere Perl DBD::SQLAnywhere DBI module ....................................................... 721
Introduction to DBD::SQLAnywhere .................................................................. 722
Installing DBD::SQLAnywhere on Windows ...................................................... 723
Installing DBD::SQLAnywhere on Unix and Mac OS X ..................................... 725
Writing Perl scripts that use DBD::SQLAnywhere ............................................. 727
SQL Anywhere Python Database support ......................................................................... 731
Introduction to sqlanydb ..................................................................................... 732
Installing sqlanydb on Windows ......................................................................... 733
Installing sqlanydb on Unix and Mac OS X ........................................................ 734
Writing Python scripts that use sqlanydb ........................................................... 735
SQL Anywhere PHP API ................................................................................................. 739
Introduction to the SQL Anywhere PHP module ................................................ 740
Installing and configuring SQL Anywhere PHP ................................................. 741
Running PHP test scripts in your web pages ..................................................... 746
Writing PHP scripts ............................................................................................ 748
SQL Anywhere PHP API reference ................................................................... 754
Building the SQL Anywhere PHP module on Unix and Mac OS X .................... 803
SQL Anywhere for Ruby .................................................................................................. 809
Ruby support in SQL Anywhere ........................................................................ 810
Rails support in SQL Anywhere ......................................................................... 812
Ruby-DBI Driver for SQL Anywhere .................................................................. 814
SQL Anywhere Ruby API .................................................................................. 818
Sybase Open Client API .................................................................................................. 839
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 vii
SQL Anywhere® Server - Programming
Open Client architecture .................................................................................... 840
What you need to build Open Client applications .............................................. 841
Data type mappings ........................................................................................... 842
Using SQL in Open Client applications .............................................................. 844
Known Open Client limitations of SQL Anywhere .............................................. 847
SQL Anywhere web services ........................................................................................... 849
Introduction to web services .............................................................................. 850
Quick start to web services ................................................................................ 851
Creating web services ....................................................................................... 856
Starting a database server that listens for web requests ................................... 859
Understanding how URLs are interpreted ......................................................... 861
Creating SOAP and DISH web services ............................................................ 865
Tutorial: Accessing web services from Microsoft .NET ...................................... 868
Tutorial: Accessing web services from JAX-WS ................................................ 871
Using procedures that provide HTML documents ............................................. 877
Working with data types ..................................................................................... 880
Tutorial: Using data types with Microsoft .NET .................................................. 887
Tutorial: Using data types with JAX-WS ............................................................ 892
Using the iAnywhere WSDL compiler ................................................................ 898
Creating web service client functions and procedures ...................................... 900
Working with return values and result sets ........................................................ 905
Selecting from result sets .................................................................................. 907
Using parameters .............................................................................................. 908
Working with structured data types .................................................................... 911
Working with variables ....................................................................................... 917
Working with HTTP headers .............................................................................. 919
Using SOAP services ........................................................................................ 922
Working with SOAP headers ............................................................................. 925
Working with MIME types .................................................................................. 932
Using HTTP sessions ........................................................................................ 935
Using automatic character set conversion ......................................................... 941
Handling errors .................................................................................................. 942
SQL Anywhere Database Tools Interface .............................................. 945
Database tools interface .................................................................................................. 947
viii Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1
SQL Anywhere® Server - Programming
Introduction to the database tools interface ....................................................... 948
Using the database tools interface .................................................................... 950
DBTools functions .............................................................................................. 957
DBTools structures ............................................................................................ 967
DBTools enumeration types ............................................................................ 1007
Exit codes .................................................................................................................... 1013
Software component exit codes ....................................................................... 1014
Deploying SQL Anywhere ...................................................................... 1015
Deploying databases and applications ........................................................................... 1017
Introduction to deployment .............................................................................. 1018
Understanding installation directories and file names ..................................... 1020
Using the Deployment Wizard ....................................................................... 1023
Using a silent install for deployment ................................................................ 1027
Deploying client applications ........................................................................... 1029
Deploying administration tools ......................................................................... 1048
Deploying database servers ............................................................................ 1074
Deploying external environment support ......................................................... 1079
Deploying security ........................................................................................... 1081
Deploying embedded database applications ................................................... 1082
Glossary .................................................................................................. 1087
Glossary ...................................................................................................................... 1089
Index ........................................................................................................ 1119
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 ix
x