Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > White Paper-Technical > Tutorial: Embedding SQL Anywhere in Commercial App...

Tutorial: Embedding SQL Anywhere in Commercial Applications Requiring a Database
 
RSS Feed
 
 
 

Introduction

Data-driven commercial applications make use of data repositories to store the information needed for its operations. Instead of developing their own database systems, companies look to embed already available database technology in their applications. An embedded database is tightly integrated with the application that utilizes it. Hidden from the end user, it provides applications with the advantages of a relational database, such as data integrity, high performance, and security.

This document describes the different methods of embedding a SQL Anywhere database in commercial Windows applications. There are 4 methods presented here: the SQL Anywhere deployment wizard, copying files, using InstallShield and the SQL Anywhere silent install.

Download paper

Embedding SQL Anywhere in Commercial Applications Requiring a Database

Why use an embedded database?

The reasons to use a relational database management system (RDBMS) in new or existing software projects are clear: fully-managed transactional data access, fast index-based lookup and secure access are only a few among many. But when should a database be embedded with an application? Many Independent Software Vendors (ISVs) and Original Equipment Manufacturers (OEMs) leave database installation and configuration to their customers. This means dedicated database administration staff for end users and increased support and development costs for vendors due to the need to support multiple manufacturers and versions.

SQL Anywhere is a leading embedded database with features targeted at simplifying the development and deployment process and a focus on out-of-box performance. Thousands of ISVs such as Cerner, Ticketmaster and Intuit already depend on the stability and performance of SQL Anywhere to provide mission critical data access to their applications.



"Using SQL Anywhere has improved the productivity of engineers and the functionality of QuickBooks considerably, which makes it a much more valuable tool for our customers."


Siddharth Ram
Group Architect
Intuit

Sybase provides a free Developer Edition of SQL Anywhere specifically licensed for use during the development phase of new software projects. The Developer Edition includes all major components of SQL Anywhere and is a great way to test features in development and test cycles.

Please note that you must be licensed to distribute and deploy SQL Anywhere software. Although the deployment features are enabled, the Developer Edition is not licensed for deployment. All Sybase license terms can be found at http://www.sybase.com/softwarelicenses/productspecificlicenseterms.

There are a variety of licensing options for SQL Anywhere. Contact Sybase Sales for more information.

PREREQUISITISES

  • SQL Anywhere 12
  • SQL Anywhere samples installed
  • Visual Studio 2005 or higher
  • InstallShield 2010 (optional)
  • Windows XP or higher

The ADO.NET sample used in the deployed application requires the v3.5 .NET Framework.

This paper describes deployment for a Windows application as an example. The steps for Method 2: Copying Files and Method 4: Silent Install will be similar for other operating systems. See the SQL Anywhere documentation for more information.

http://dcx.sybase.com/index.html#1201/en/dbprogramming/pg-deploy.html

Preparation

In this document we'll be deploying the SQL Anywhere ADO.NET SimpleViewer sample application that is included in the SQL Anywhere installation. The program is provided in source code and must be compiled. For the InstallShield and copying files methods, a subset of database engine files must be assembled.

Building the SimpleViewer Sample

Every example in this document uses the ADO.NET SimpleViewer sample. To package the executable in a deployment it must first be compiled using Visual Studio.

  1. Launch Visual Studio. Select File->Open->Project/Solution.

    Visual Studio Open Project Step 1

  2. Open the SimpleViewer solution file. The sample can be found at "%SQLANYSAMP%\SQL Anywhere 12\Samples\SQLAnywhere\ADO.NET\SimpleViewer". By default this resolves to "C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples\SQLAnywhere\ADO.NET\SimpleViewer" on Windows XP and "C:\Users\All Users\Documents\SQL Anywhere 12\Samples\SQLAnywhere\ADO.NET\SimpleViewer" on Windows Vista and Windows 7.

    Visual Studio Open Project Step 2

  3. Open Build->Configuration Manager.

    Visual Studio Configuration Manager Step 1

  4. Change the Active solution configuration to 'Release'. Make sure the SimpleViewer project shows 'Release' as it's selected configuration.

    Visual Studio Configuration Manager Step 2

  5. Chose Build->Build Solution. This is will compile SimpleViewer.exe as a .NET executable and place the file in the sample project directory in bin\Release.

SimpleViewer.exe can now be used with the examples in this document.

Preparing Database Engine Files

The InstallShield and copying methods of deployment will be simplified by grouping together the required database engine and application files. Both examples use the following folder structure:

+-SimpleViewer/
    +-demo.db
    +-SimpleViewer.exe
    +-iAnywhere.Data.SQLAnywhere.v3.5.dll
    +-dbctrs12.dll
    +-dbdsn.exe
    +-dbelevate12.exe
    +-dbeng12.exe
    +-dbeng12.lic
    +-dbicu12.dll
    +-dbicudt.dll
    +-dblgen12.dll
    +-dbscript12.dll
    +-dbserv12.dll
    +-sqlany.cvf
  • Application Executable (240 KB): SimpleViewer.exe
  • Database (3.76 MB): Demo.db
  • Database Server Binaries (14.2 MB): dbeng12.exe, dblgen12.dll, dbctrs12.dll, dbicu12.dll, dbicudt12.dll, dbserv12.dll, dbscript12.dll, sqlany.cvf
  • Server License File (1 KB): dbeng12.lic
  • Connectivity Libraries (2.13 MB): iAnywhere.Data.SQLAnywhere.v3.5.dll
  • Database Utilities (664 KB): dbelevate12.exe, dbdsn.exe

The preceding files are all that is needed to deploy the SimpleViewer sample. Included are several connectivity libraries needed to establish a database connection with .NET applications and the demo database. In total this deployment including the database, database engine and application executable only requires 21.3 MB of disk space.

Method 1: Deployment Wizard

The deployment wizard is the recommended method of deployment as it's quick and easy to use. The wizard generates a Windows installer file that can be packaged with other installers or run independently. The installer created by the deployment wizard can only contain files that the host machine itself has. This means that a system licensed as a client won't be able to deploy a database server. Note that on 32-bit systems, the deployment wizard will only include 32-bit binaries. When run on 64-bit systems with a 64-bit installation of SQL Anywhere 12, there will be the option of including both 32- and 64-bit binaries.

Building the Installer

Note that the deployment wizard does not create the ODBC data source needed for the SimpleViewer application. To create it manually, follow Step 3 from Method 1.

  1. Start the Deployment Wizard by Launching 'Deploy to Windows' from 'Administration Tools' in the SQL Anywhere 12 program folder.

    SQL Anywhere Windows Start Menu

  2. Select 'English' as the language and click [Next >].

    Deployment Wizard Step 1

  3. Your license agreement must allow the redistribution of files. If it does, select 'Yes' and click [Next >].

    Deployment Wizard Step 2

  4. Choose the location of your SQL Anywhere installation and click [Next >].

    Deployment Wizard Step 3

  5. Uncheck all components except the 'ADO.NET' client interface and the SQL Anywhere 'Personal Server'. If desired, check 'Server Tools' to include utilities such as dbbackup.exe. The deployment wizard will automatically include all necessary files to enable the selected functionality.

    SQL Anywhere provides two versions of the database server: the personal server and the network server. The main distinction is that the personal server only supports same-computer connections, while the network server accepts connections from other computers. Typically, the personal server is ideal for embedded database applications.


    Deployment Wizard Step 4

  6. Choose a location to save the installer. Note: This demo will use MSI as the deployment medium. An MSM is a merge module, a separate package that is merged into and becomes a part of an MSI.

    Deployment Wizard Step 5

  7. The wizard will generate a product code as a GUID. Each MSI has a product code that is used to uniquely identify the installation. The second input box is the name of the installation, which is used to identify the product in the installer and in Add or Remove Programs, leave it as the default and click [Next >].

    Deployment Wizard Step 6

  8. Click [Next >] to build the MSI file.

    Deployment Wizard Step 7

  9. When the wizard is finished creating the installer click [Finish].

Deployment

To deploy simply run the MSI on the client system and follow the prompts on screen.

Installation Options

There are several command line options to configure the installation at deployment time.

  • Change installation directory:

    msiexec /package sqlany12.msi SQLANYDIR=c:\sa12

  • Silent install:

    msiexec /qn /package sqlany12.msi

  • Uninstall:

    msiexec /uninstall sqlany12.msi

Method 2: Copying Files

Deploying SQL Anywhere doesn't require any additional software and can be as simple as copying the necessary files. There is a set of files needed to start the database engine and more files are needed for some additional options like JDBC connectivity or Java in the Database functionality. For a full list of extensions see the product documentation:

http://dcx.sybase.com/index.html#1201/en/dbprogramming/server-deploy.html

Note: System management solutions, such as Afaria, can automate the file transfer process for an entire enterprise's mobile workforce.

This demo uses the SimpleViewer ADO.NET sample that is packaged with a normal SQL Anywhere 12 installation. The sample uses the SQL Anywhere 12 demo database, demo.db, which is included in the sample root directory.

Deploying

  1. Copy the application directory to a client machine. This could be a self-extracting zip, an Afaria channel or simply copied to a client machine. For this example, the files will be placed in "C:\Program Files\SimpleViewer\"

    SimpleViewer Application Directory

  2. Use the dbdsn utility to create and configure a SQL Anywhere 12 data source. The SimpleViewer application looks for a data source named 'SQL Anywhere 12 Demo' to get the connection parameters for the database. Run

    dbdsn –pe –w "SQL Anywhere 12 Demo" –c "uid=dba;pwd=sql;DBF=C:\Program Files\SimpleViewer\demo.db;ServerName=SimpleViewerDB;START=C:\Program Files\SimpleViewer\dbeng12 –qi"

    Creating the SQL Anywhere 12 Demo datasource

  3. Optionally, the SQL Anywhere .NET data provider can be added to the global assembly cache. This step is completely optional and is only necessary if multiple applications will be using the provider. This deployment method is the only one that doesn't register the assembly automatically. The gacutil.exe utility is required from the .NET framework. Simply execute the following command:
    gacutil.exe –I iAnywhere.Data.SQLAnywhere.v3.5.dll
    This will make a copy of the driver in the system's global assembly cache and the local copy is no longer needed.

  4. Test the deployment by running SimpleViewer.exe. If any errors appear check that the correct path set with the dbdsn command and all the required files listed at the start of the section are present.

    SimpleViewer Application

Deploying Database Utilities

SQL Anywhere comes with an array of command line utilities that facilitate tasks such as database creation, validation, and backups. These utilities can be used in scripts to automate maintenance and collect database information.

In order to deploy database utilities such as dbbackup.exe and dbinit.exe, you'll need to include 2 additional libraries along with the utility executable. These libraries are dbtool12.dll (1.23 MB) and dblib12.dll (774 KB). Simply include these files with the rest of the deployment.

For a full list of utilities and their functions, see documentation: http://dcx.sybase.com/index.html#1201/en/dbadmin/da-dbutilities.html

Method 3: InstallShield

InstallShield is a sophisticated installation designer that builds on the MSI file type while adding complex logic and extra features. This demonstration will use InstallShield to copy the SimpleViewer application, SQL Anywhere binaries, register the ADO.NET data provider, set the SQLANY12 environment variable and create the ODBC data source.

This demo uses InstallShield 2010 but is applicable to other versions as well.

Building the Installer

  1. Launch InstallShield and start a new project. Choose 'Basic MSI Project', type a name and a location for the project.

    InstallShield Step 1

  2. Click on the 'Installation Designer' tab. On the left you'll see all the configurable options for the installation. These will be referred to in 'Folder->Feature' format. Start by clicking Installation Information->General Information. Find the INSTALLDIR property and change its value to '[ProgramFilesFolder]SimpleViewer'.

    InstallShield Step 2

  3. Click on Organization->Features. Right click on Features in the second panel and click New Feature. Name the feature 'Files'. Change the Display Name property to 'Files'.

    InstallShield Step 3

  4. Click on Application Data->Files and Folders. Under the source computer's folders, navigate to the SimpleViewer application directory. First drag both demo.db and SimpleViewer.exe to the destination computer's files.

    InstallShield Step 4

  5. Next right click SimpleViewer [INSTALLDIR] in the Destination computer's folders and create a new folder called 'sa'. Drag the remaining files to sa on the destination computer's files. If prompted to create a dynamic file link click [No].

    InstallShield Step 5

  6. Change to System Configuration->ODBC Resources. Change the Associated Feature to Settings. In the tree structure, expand ODBC Resources->Drivers & DSNs->SQL Anywhere 12->DSNs. Check off SQL Anywhere 12 Demo. In the properties grid, edit the DatabaseFile field to be '[INSTALLDIR]demo.db' and StartLine to '[INSTALLDIR]SA\dbeng12.exe -qi'.

    InstallShield Step 6

  7. The previous step will re-add the demo.db database file. Change to Organization->Components and expand SQLAnywhere12Demo. Select Files and remove demo.db from the project by right clicking the file and choosing Delete. When prompted, click Yes.

    InstallShield Step 7

  8. While still in the Components view, expand iAnywhere.Data.SQLAnywhere.v3.5.dll->Advanced Settings and right click Assembly, select New .NET Assembly. Change the File Application property to Global Assembly Cache and leave the other values.

    InstallShield Step 8

  9. Click Media->Releases. Right click Releases and click New Product Configuration. Accept the default name.

    InstallShield Step 9

  10. Right click the new product configuration and select New Release. Accept the default name. Click on the new release and change the Compression property to 'Compressed'.

    InstallShield Step 10

  11. Right click the release and choose Build.

    InstallShield Step 11

  12. InstallShield will create a setup.exe in the project root\SimpleViewer\Product Configuration 1\Release 1\DiskImages\DISK1 that can now be sent to other systems to install the SimpleViewer application as well as register the ADO.NET data provider for global use.

Uninstall

To remove all the deployed files from this installation simply run setup.exe again and choose the Remove option or use the Windows control panel to uninstall from Add or Remove Programs.

Method 4: Silent Install

A silent install with the standard SQL Anywhere 12 installer uses command line switches to configure which components are included. This is commonly done from CD or network image. Although the command line switches allow the choice of major components, this method of deployment doesn't offer the flexibility and compactness of the other methods shown in this document. A list of command line options can be seen at http://dcx.sybase.com/index.html#1201/en/dbprogramming/using-silent-install-deploy.html

Building the Command Line String

  1. Assuming the SQL Anywhere 12 CD is in drive D the command will start as the following:

    D:\software\sqlanywhere\setup.exe

    Be careful not to use the setup.exe from the parent directory. It does not support silent installs.

  2. To specify the localization use the /L: along with the language identifier. Local ID 1033 identifies U.S. English and other localizations can be found online.

    D:\software\sqlanywhere\setup.exe /L:1033

  3. Everything specified with the /V: switch is passed on to the SQL Anywhere MSI. The rest of the steps will involve adding options to the /V: switch.

    D:\software\sqlanywhere\setup.exe /L:1033 /S "/V:"

  4. The REGKEY property must specify a valid registration key. The registration key determines which components are installed. Step 5 involves limiting which components are installed.

    D:\software\sqlanywhere\setup.exe /L:1033 /S "/V: REGKEY=QEDEV-B888A-6L123-45678-90123"

  5. The value of the INSTALLDIR property is the root directory where SQL Anywhere will be installed. By default this location will be C:\Program Files\SQL Anywhere 12.

    D:\software\sqlanywhere\setup.exe /L:1033 /S "/V: REGKEY=QEDEV-B888A-6L123-45678-90123 INSTALLDIR=c:\SimpleViewer\SA"

  6. This step adds many new properties. Each one controls whether or not a component is installed. These properties cannot be used to override the limitations a registration key puts on included components. A full list of features and their corresponding features can be found at http://dcx.sybase.com/index.html#1201/en/dbprogramming/using-silent-install-deploy.html.

    D:\software\sqlanywhere\setup.exe /L:1033 /S "/V: REGKEY=QEDEV-B888A-6L123-45678-90123 INSTALLDIR=c:\SimpleViewer\SA at32=0 ml32=0 qa32=0 rs32=0 samples=0 sa32=1 mobile=0 sm32=0 sr32=0 ul=0"

  7. Run the command from the previous step to install SQL Anywhere 32-bit including all drivers and utilities, as well as set the appropriate environment variables.

Silent Uninstall

A silent uninstall can be performed from the command line using the msiexec.exe utility. The following command will silently uninstall SQL Anywhere 12 no matter where it was installed on the local machine.

msiexec.exe /qn /uninstall {1DFA77E6-91B2-4DCC-B8BE-98EA70705D39}

{1DFA77E6-91B2-4DCC-B8BE-98EA70705D39} is SQL Anywhere 12's unique product code.

Conclusion

Upon the completion of these demonstrations, you have successfully deployed SQL Anywhere using 4 different methods. To summarize, we completed the following tasks:

  • Using the deployment wizard,
    • Built an MSI containing a only the required subset of SQL Anywhere features
  • Copying the bare-essential database engine files manually,
    • Created a SQL Anywhere data source using dbdsn
  • Using InstallShield,
    • Built an installation around the SimpleViewer application
    • Copied and configured the SQL Anywhere demo data source.
  • Employing the SQL Anywhere 12 installer we,
    • Installed a reduced install silently and completely without user interaction.

While each deployment method has advantages and disadvantages, most users will find SQL Anywhere's deployment wizard to be the easiest and safest way to package SQL Anywhere with a commercial application. Simple and light weight deployment is one of SQL Anywhere's many advantages as an embedded database.

Using SQL Anywhere as an embedded database hides the complexity of running and managing a database server but provides the application with great performance and accelerated development.


 

DOCUMENT ATTRIBUTES
Last Revised: Jun 27, 2011
Product: SQL Anywhere
Technical Topics: SQL Anywhere, Install/Upgrade, SQL ISV Applications
  
Business or Technical: Technical
Content Id: 1093518
Infotype: White Paper-Technical
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics