SQL Anywhere® Server - Programming

1064 Pages·2012·7.25 MB·English

SQL Anywhere® Server
Programming
Version 12.0.1
January 2012
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
Version 12.0.1
January 2012
Copyright © 2012 iAnywhere Solutions, Inc. Portions copyright © 2012 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
Using SQL in applications .................................................................... 1
SQL statement execution in applications ......................................................... 1
Prepared statements ........................................................................................... 2
Cursor usage ........................................................................................................ 5
Working with cursors .......................................................................................... 7
Cursor types ....................................................................................................... 14
SQL Anywhere cursors ..................................................................................... 16
Result set descriptors ....................................................................................... 33
Transactions in applications ............................................................................ 34
.NET application programming .......................................................... 39
SQL Anywhere .NET Data Provider .................................................................. 39
Using the SQL Anywhere .NET Data Provider ................................................ 75
SQL Anywhere ASP.NET Providers ................................................................. 91
SQL Anywhere .NET API reference .................................................................. 99
OLE DB and ADO development ........................................................ 329
OLE DB ............................................................................................................. 329
ADO programming with SQL Anywhere ........................................................ 330
OLE DB connection parameters ..................................................................... 336
OLE DB connection pooling ........................................................................... 338
Configuration of a Microsoft Linked Server using OLE DB ......................... 339
Supported OLE DB interfaces ........................................................................ 342
Registering the OLE DB provider ................................................................... 346
ODBC support .................................................................................... 347
Requirements for developing ODBC applications ....................................... 347
Building ODBC applications ........................................................................... 348
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 iii
SQL Anywhere® Server - Programming
ODBC samples ................................................................................................. 354
ODBC handles .................................................................................................. 355
ODBC connection functions ........................................................................... 357
Server options changed by ODBC ................................................................. 361
SQLSetConnectAttr extended connection attributes ................................... 361
Calling ODBC functions from DllMain ........................................................... 363
Ways to execute SQL statements .................................................................. 364
64-bit ODBC considerations ........................................................................... 368
Data alignment requirements ......................................................................... 372
Result sets in ODBC applications .................................................................. 374
Calling stored procedures .............................................................................. 378
ODBC escape syntax ....................................................................................... 380
Error handling in ODBC .................................................................................. 382
Java in the database .......................................................................... 385
Learning about Java in the database ............................................................. 385
Java in the database FAQ ............................................................................... 386
Java error handling ......................................................................................... 388
Creating a Java class for use with SQL Anywhere ...................................... 388
Java VMs .......................................................................................................... 389
Installing the sample Java class .................................................................... 390
The CLASSPATH environment variable ........................................................ 391
Calling methods in the Java class ................................................................. 391
Installing Java classes into a database ......................................................... 393
Special features of Java classes in the database ......................................... 397
Starting and stopping the Java VM ................................................................ 401
Shutdown hooks in the Java VM .................................................................... 401
JDBC support ..................................................................................... 403
JDBC applications ........................................................................................... 403
JDBC drivers .................................................................................................... 404
JDBC program structure ................................................................................. 406
Differences between client- and server-side JDBC connections ................ 406
SQL Anywhere JDBC drivers ......................................................................... 407
iv Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
SQL Anywhere® Server - Programming
The jConnect JDBC driver .............................................................................. 409
Connections from a JDBC client application ................................................ 412
Data access using JDBC ................................................................................. 419
JDBC callbacks ................................................................................................ 427
JDBC escape syntax ....................................................................................... 430
JDBC 3.0/4.0 API support ................................................................................ 434
Embedded SQL .................................................................................. 435
Development process overview ..................................................................... 436
The SQL preprocessor .................................................................................... 437
Supported compilers ....................................................................................... 441
Embedded SQL header files ........................................................................... 441
Import libraries ................................................................................................. 442
Sample embedded SQL program ................................................................... 443
Structure of embedded SQL programs ......................................................... 443
DBLIB dynamic loading under Windows ....................................................... 444
Sample embedded SQL programs ................................................................. 445
Embedded SQL data types ............................................................................. 449
Host variables in embedded SQL ................................................................... 452
The SQL Communication Area (SQLCA) ....................................................... 461
Static and dynamic SQL .................................................................................. 466
The SQL descriptor area (SQLDA) ................................................................. 469
Fetching data ................................................................................................... 479
Sending and retrieving long values ............................................................... 487
Simple stored procedures in embedded SQL ............................................... 491
Request management with embedded SQL .................................................. 494
Database backup with embedded SQL .......................................................... 494
Library function reference .............................................................................. 495
Embedded SQL statement summary ............................................................. 525
SQL Anywhere database API for C/C++ .......................................... 527
SQL Anywhere C API support ........................................................................ 527
SQL Anywhere C API reference ..................................................................... 528
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 v
SQL Anywhere® Server - Programming
SQL Anywhere external call interface ............................................. 563
Procedures and functions that use external calls ........................................ 563
External function prototypes .......................................................................... 565
External function call interface methods ...................................................... 574
Data type handling ........................................................................................... 578
Unloading external libraries ........................................................................... 580
SQL Anywhere external environment support ............................... 581
The CLR external environment ....................................................................... 584
The ESQL and ODBC external environments ............................................... 587
The Java external environment ...................................................................... 596
The PERL external environment .................................................................... 601
The PHP external environment ....................................................................... 605
Perl DBI support ................................................................................ 611
DBD::SQLAnywhere ........................................................................................ 611
Installing Perl/DBI support on Windows ....................................................... 611
Installing Perl/DBI support on Unix and Mac OS X ...................................... 613
Perl scripts that use DBD::SQLAnywhere ..................................................... 614
Python support .................................................................................. 619
sqlanydb ........................................................................................................... 619
Installing Python support on Windows ......................................................... 620
Installing Python support on Unix and Mac OS X ........................................ 620
Python scripts that use sqlanydb .................................................................. 621
PHP support ....................................................................................... 627
SQL Anywhere PHP extension ....................................................................... 627
SQL Anywhere PHP API reference ................................................................. 640
Ruby support ..................................................................................... 691
SQL Anywhere Ruby API support .................................................................. 691
vi Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
SQL Anywhere® Server - Programming
SQL Anywhere Ruby API reference ............................................................... 699
Sybase Open Client support ............................................................. 721
Open Client architecture ................................................................................. 721
What you need to build Open Client applications ........................................ 722
Data type mappings ......................................................................................... 723
SQL in Open Client applications .................................................................... 724
Known Open Client limitations of SQL Anywhere ........................................ 727
HTTP web services ............................................................................ 729
SQL Anywhere as an HTTP web server ......................................................... 729
Accessing web services using web clients .................................................. 766
Web services references ................................................................................. 803
HTTP web service examples ........................................................................... 806
Three-tier computing and distributed transactions ....................... 839
Three-tier computing architecture ................................................................. 839
Distributed transactions ................................................................................. 843
Database tools interface (DBTools) ................................................. 845
DBTools import libraries ................................................................................. 846
Initializing and finalizing the DBTools library ............................................... 846
Calling the DBTools functions ....................................................................... 847
Callback functions ........................................................................................... 847
Version numbers and compatibility ............................................................... 849
Bit fields ............................................................................................................ 849
A DBTools example ......................................................................................... 850
SQL Anywhere Database Tools C API reference .......................................... 852
Software component exit codes ..................................................................... 912
Database and application deployment ............................................ 915
Types of deployment ....................................................................................... 915
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 vii
SQL Anywhere® Server - Programming
Ways to distribute files ................................................................................... 916
Installation directories and file names .......................................................... 916
The Deployment Wizard .................................................................................. 919
Silent installs using the SQL Anywhere installer ......................................... 923
Requirements for deploying client applications ........................................... 927
Administration tool deployment ..................................................................... 956
Documentation deployment ........................................................................... 978
Database server deployment .......................................................................... 979
Registering DLLs on Windows ....................................................................... 984
External environment support deployment .................................................. 985
Security deployment ....................................................................................... 987
Embedded database application deployment ............................................... 988
Index ................................................................................................... 993
viii Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1
About this book
This book describes how to build and deploy database applications using the C, C++, Java, Perl, PHP,
Python, Ruby, and .NET programming languages such as Visual Basic and Visual C#. A variety of
programming interfaces such as ADO.NET, OLEDB, and ODBC are described.
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 ix
x Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1