Skip to Content

Deploying an SAP SQL Anywhere Database Application Using the WiX Toolset

This document describes how to deploy an SAP SQL Anywhere database alongside a Windows application using an installation package created with the WiX toolset.

Introduction

Windows Installer (i.e. MSI) is a popular method for packaging Windows applications for deployment. There are many ways to create MSI install packages and an easy method is to use the Windows Installer XML (WiX) Toolset. This set of tools provides developers with a quick way of packaging a Windows application for distribution and this document describes how to deploy an SAP SQL Anywhere database alongside a Windows application using an installation package created with the WiX toolset.

There are other ways to achieve the same result, such as using other software such as InstallShield or by copying the required files. If you interested in those methods, please refer to the document “Embedding SAP SQL Anywhere 16 in Commercial Applications Requiring a Database” available here: http://scn.sap.com/docs/DOC-43550.

Prerequisites

The steps described in this document were tested under Windows XP and Windows 7 (32-bit and 64-bit) using SAP SQL Anywhere 16, Visual Studio 2012, and WiX Toolset 3.7.

The ADO.NET sample application used in the WiX deployment packages requires.NET Framework 3.5 to run.

This document describes deployment for a Windows application. The steps will be similar for other operating systems. See the SQL Anywhere documentation for more information: http://dcx.sap.com/index.html#sa160/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 Visual Studio source code is provided and must be compiled. For the WiX Toolset deployment method, a subset of database engine files must be assembled in your computer.

The ADO.NET sample used in the deployed application requires SQL Anywhere .NET assemblies. For installation instructions on how to deploy SQL Anywhere .NET components, see the document: http://dcx.sap.com/index.html#sa160/en/dbprogramming/deploying-adonet-deploy.html.

Please note that the SQL Anywhere .NET assemblies are installed automatically if Visual Studio is installed prior to SQL Anywhere.

Building the SimpleViewer Sample

Compile the sample application using Visual Studio:

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

  2. Open the SimpleViewer solution file - it is found at “%SQLANYSAMP16%\SQL Anywhere 16\Samples\SQLAnywhere\ADO.NET\SimpleViewer”.

    By default, this path resolves to “C:\Documents and Settings\All Users\Documents\SQL Anywhere 16\Samples\SQLAnywhere\ADO.NET\SimpleViewer” on Windows XP and “C:\Users\Public\Documents\SQL Anywhere 16\Samples\SQLAnywhere\ADO.NET\SimpleViewer ” on Windows Vista and Windows 7.

  3. Select the solution ‘SimpleViewer.sln’ and click [OK]. If running Visual Studio 2008 or higher, you will be asked to upgrade the solution. You can go ahead and do that.
  4. Open Build->Configuration Manager.

  5. Change the Active solution configuration to ‘Release’. Make sure the SimpleViewer project shows ‘Release’ as its selected configuration.

  6. Open Project->SimpleViewer Properties.

  7. Change “Target framework” to .NET Framework 3.5.

  8. Chose Build->Build Solution. This compiles the SimpleViewer project as a .NET executable and places the file in the sample project directory inside the folder “bin\Release”.

    Note that this sample application is compiled for .NET Framework 3.5. If you wish to compile for other versions of .NET, simply change the target framework (under project properties) and add the appropriate version of SQL Anywhere ADO.NET data provider.
  9. Shut down Visual Studio.

Preparing Database Engine Files

To simplify the process, it is recommended that you place all the files required to run the application inside a single folder.

  1. Create a folder “C:\SimpleViewerFiles” and copy the necessary files (there are 18 in total) in there:
    • Application Executable (217 KB): SimpleViewer.exe
    • Database (3.7 MB): demo.db
    • Database Server Binaries (23.7 MB): dbcis16.dll, con16.dll, dbctrs16.dll, dbeng16.exe, dbghelp.dll, dbicu16.dll, dbicudt16.dll, dblgen16.dll, dbodbc16.dll, dbscript16.dll, dbserv16.dll, sqlany.cvf
    • Server License File (1 KB): dbeng16.lic
    • Connectivity Library (2.2 MB): iAnywhere.Data.SQLAnywhere.v3.5.dll
    • Database Utilities (811 KB):dbelevate16.exe , dbdsn.exe

All these files are needed to deploy the SimpleViewer sample application. 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 approximately 30 MB of disk space.

The Wix Toolset

The WiX Toolset builds Windows installation packages from XML source code. This document uses the WiX Toolset to copy the SimpleViewer application and SQL Anywhere binaries, and create its ODBC data source.

This document uses the WiX Toolset 3.7, but is applicable to other versions as well.

It is worth noticing that the installer package we are about to create does not have a user interface (i.e. it’s a silent install). It also does not check whether the target computer already has the .NET Framework installed. If you would like to add a user interface and perform checks during the installation, please consult the WiX Toolset documentation for instructions on how to code those operations. For reference, the appendix at the end of this paper has a code listing that does exactly that.

Building the Installer

  1. Launch Visual Studio 2012 and start a new project. On the left panel, select Installed > Templates > Windows Installer XML and choose ‘Setup Project’. Type a name (e.g.: SimpleViewerInstaller) and a location for the project (e.g.: C:\SimpleViewerInstaller). Make sure the project is using .NET Framework 3.5.

    Note: if you do not see the “Windows Installer XML” project template, then you do not have the WiX Toolset installed. Please install it before proceeding.

  2. In Solution Explorer, right-click on SimpleViewerInstaller->Add…->Existing Item.

  3. Browse to the SimpleViewerFiles directory you created earlier. Select all the files and click the [Add] button.

  4. Open the file Product.wxs. Modify the Product start tag to change the Name, Language, version, Manufacturer, and the Upgrade Code.

    <ProductId="*"Name="SimpleViewer"Language="1033"Version="1.0.0.0"Manufacturer="SAP"
            
    UpgradeCode="881e7266-7d54-4762-b0db-671f25bda163">

    Important: For a 64-bit deployment project, modify the Package tag to include the Platform attribute and set it to “x64”.

    <PackageInstallerVersion="200"Compressed="yes"InstallScope="perMachine"
            
    Platform="x64" />
  5. Enter ‘SimpleViewer’ as the product name in MajorUpgrade tag.

    <MajorUpgrade DowngradeErrorMessage="A newer version of SimpleViewer is already installed." />

  6. Modify the directory tag as follows to deploy the application to C:\SQL Anywhere Demos\SimpleViewer.

    <DirectoryId="TARGETDIR"Name="SourceDir">
       <DirectoryId="SQLAnywhereDemos"Name="SQLAnywhere Demos">
          <DirectoryId="INSTALLFOLDER"Name="SimpleViewer" />
       </Directory>
    </Directory>

  7. For this project, we will create two components, ProductComponent and DemoDataSource. Since we will only create components, delete the ComponentGroup that is created by default. Modify the Feature tag as follows to reference to these two components.

    <FeatureId="ProductFeature"Title="SimpleViewerInstaller"Level="1">
       <ComponentRefId="ProductComponent" />
       <ComponentRefId="SA16DemoDataSource"/>
    </Feature>

  8. For each component, it is necessary to add a GUID attribute. To create a GUID in Visual Studio, click on Tools->Create GUID. Change the Format to Registry Format and click on Copy.


  9. Add a new Fragment to contain the ProductComponent element. Replace the text “PUT-GUID-HERE” by pasting the GUID obtained in the previous step.

    Note: For a 32-bit deployment, change the Win64 attribute to “no”.

    <Fragment>
       <
    Component Id="ProductComponent" Directory="INSTALLFOLDER" Win64="yes"
                 
    Guid="PUT-GUID-HERE">
          <!--
    Add files -->
          <!--
    Remove files on install/uninstall-->
       </
    Component>
    </
    Fragment>

  10. Include the following File elements in ProductComponent.

    <FileId="demoDB"Name="demo.db" />

    <FileId="SimpleViewerEXE"Name="SimpleViewer.exe" />

    <FileId="iAnywhereDLL"Name="iAnywhere.Data.SQLAnywhere.v3.5.dll" />
    <
    FileId="dbcis16DLL"Name="dbcis16.dll" />

    <FileId="dbcon16DLL"Name="dbcon16.dll" />

    <FileId="dbctrs16DLL"Name="dbctrs16.dll" />

    <FileId="dbdsnEXE"Name="dbdsn.exe" />

    <FileId="dbelevate16EXE"Name="dbelevate16.exe" />

    <FileId="dbeng16EXE"Name="dbeng16.exe" />

    <FileId="dbeng16LIC"Name="dbeng16.lic" />

    <FileId="dbghelpDLL"Name="dbghelp.dll" />

    <FileId="dbicu16DLL"Name="dbicu16.dll" />

    <FileId="dbicudt16DLL"Name="dbicudt16.dll" />

    <FileId="dblgen16DLL"Name="dblgen16.dll" />

    <FileId="dbodbc16DLL"Name="dbodbc16.dll" />

    <FileId="dbscript16DLL"Name="dbscript16.dll" />

    <FileId="dbserv16DLL"Name="dbserv16.dll" />

    <FileId="sqlanyCVF"Name="sqlany.cvf" />



  11. Include the RemoveFile element in ProductComponent. Set Name to be “*.*” to allow all the files added in this component to be removed.

    <RemoveFile Id="RemoveFiles" Name="*.*" On="both" />

  12. Add the following fragment to create the SA16DemoDataSource component. It creates the ‘SQL Anywhere 16 Demo’ ODBC data source name on install. Use a new GUID for this component (see step #8) and remember to replace the text “PUT-GUID-HERE” with that new GUID.

    Note: For a 32-bit deployment, changes Win64 attribute to “no”.

    <Fragment>
       <DirectoryRef Id="TARGETDIR">
          <Component Id="SA16DemoDataSource" Win64="no" Guid="PUT-GUID-HERE">
             <RegistryKey Root="HKCU" Key="Software\ODBC\ODBC.INI\SQLAnywhere 16 Demo">
                <RegistryValue Type="string" Name="ServerName" Value="SimpleViewerDB" />
                <RegistryValue Type="string" Name="DatabaseFile" Value="[INSTALLFOLDER]demo.db" />
                <RegistryValue Type="string" Name="Driver" Value="[INSTALLFOLDER]dbodbc16.dll" />
                <RegistryValue Type="string" Name="UID" Value="dba" />
                <RegistryValue Type="string" Name="PWD" Value="sql" />
                <RegistryValue Type="string" Name="StartLine" Value="C:\SQL Anywhere Demos\SimpleViewer\dbeng16.exe" />
             </RegistryKey>
             <RegistryKey Root="HKCU" Key="Software\ODBC\ODBC.INI\ODBC Data Sources">
                 <RegistryValue Type="string" Name="SQL Anywhere 16 Demo" Value="SQL Anywhere 16" />
             </RegistryKey>
          </Component>
       </DirectoryRef>
    </Fragment>

  13. Save the file.

  14. Open Build->Configuration Manager.


  15. Change the Active solution configuration to ‘Release'. Make sure the project shows ‘Release’ as its selected configuration.


  16. Choose Build->Build Solution. This will create three files: cab1.cab, SimpleViewerInstaller.msi, and SimpleViewerInstaller.wixpdb. The files cab1.cab and SimpleViewerInstaller.msi can now be sent to other systems to install the SimpleViewer application.

Uninstall

To remove all the deployed files from the installation, use Windows Control Panel to uninstall via Add or Remove Programs.

Conclusion

Upon the completion of the steps presented in this document, you’ll have successfully deployed a SQL Anywhere database-driven application using the WiX Toolset. To summarize, you completed the following tasks:

  • Built a Windows installation package for the SimpleViewer sample application
  • Included the necessary SQL Anywhere database and server binaries in the installation package
  • Configured the SQL Anywhere data source used by the 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 and provides the application with great performance and accelerated development.

Appendix: Code Listing of PRODUCT.WXS


The following code was used to create the MSI package and test the steps in this paper. There are two additions that were not covered above:

  1. It uses a minimal user interface.It checks for an installation of .NET Framework 3.5 and terminates if that component is not found.
  2. If you want to use this code listing, please replace the text PUT-GUID-HERE with the actual GUID value you obtained in Visual Studio.

Code listing:

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" xmlns:netfx="http://schemas.microsoft.com/wix/NetFxExtension">

<Product Id="*" Name="SimpleViewer" Language="1033" Version="1.0.0.0" Manufacturer="SAP" UpgradeCode="6b4a9f0e-b640-4b85-a043-86fb738c421e">

    <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" Platform="x64"/>
  <MajorUpgrade DowngradeErrorMessage="A newer version of SimpleViewer is already installed." />
  <MediaTemplate />

  <Feature Id="ProductFeature" Title="SimpleViewerInstaller" Level="1">
   <ComponentRef Id="ProductComponent" />
      <ComponentRef Id="SA16DemoDataSource"/>
  </Feature>

    <UI>
      <UIRef Id="WixUI_Minimal" />
    </UI>
   
</Product>

<Fragment>
    <PropertyRef Id="NETFRAMEWORK35"/>
    <Condition Message="The SimpleViewer application requires .NET Framework 3.5. Please install that component and run this installer again.">
      <![CDATA[Installed OR NETFRAMEWORK35]]>
    </Condition>
  <Directory Id="TARGETDIR" Name="SourceDir">
   <Directory Id="SQLAnywhereDemos" Name="SQL Anywhere Demos">
    <Directory Id="INSTALLFOLDER" Name="SimpleViewer" />
   </Directory>
  </Directory>
</Fragment>

<Fragment>
    <Component Id="ProductComponent" Directory="INSTALLFOLDER" Win64="yes" Guid="PUT-GUID-HERE" >
      <File Id="demoDB" Name="demo.db" />
      <File Id="SimpleViewerEXE" Name="SimpleViewer.exe" />
      <File Id="iAnywhereDLL" Name="iAnywhere.Data.SQLAnywhere.v3.5.dll" />
      <File Id="dbcis16DLL" Name="dbcis16.dll" />
      <File Id="dbcon16DLL" Name="dbcon16.dll" />
      <File Id="dbctrs16DLL" Name="dbctrs16.dll" />
      <File Id="dbdsnEXE" Name="dbdsn.exe" />
      <File Id="dbelevate16EXE" Name="dbelevate16.exe" />
      <File Id="dbeng16EXE" Name="dbeng16.exe" />
      <File Id="dbeng16LIC" Name="dbeng16.lic" />
      <File Id="dbghelpDLL" Name="dbghelp.dll" />
      <File Id="dbicu16DLL" Name="dbicu16.dll" />
      <File Id="dbicudt16DLL" Name="dbicudt16.dll" />
      <File Id="dblgen16DLL" Name="dblgen16.dll" />
      <File Id="dbodbc16DLL" Name="dbodbc16.dll" />
      <File Id="dbscript16DLL" Name="dbscript16.dll" />
      <File Id="dbserv16DLL" Name="dbserv16.dll" />
      <File Id="sqlanyCVF" Name="sqlany.cvf" />
      <RemoveFile Id="RemoveFiles" Name="*.*" On="both" />
    </Component>
</Fragment>

  <Fragment>
    <DirectoryRef Id="TARGETDIR">
      <Component Id="SA16DemoDataSource" Win64="yes" Guid="PUT-GUID-HERE">
        <RegistryKey Root="HKCU" Key="Software\ODBC\ODBC.INI\SQL Anywhere 16 Demo">
          <RegistryValue Type="string" Name="ServerName" Value="SimpleViewerDB" />
          <RegistryValue Type="string" Name="DatabaseFile" Value="[INSTALLFOLDER]demo.db" />
          <RegistryValue Type="string" Name="Driver" Value="[INSTALLFOLDER]dbodbc16.dll" />
          <RegistryValue Type="string" Name="UID" Value="dba" />
          <RegistryValue Type="string" Name="PWD" Value="sql" />
          <RegistryValue Type="string" Name="StartLine" Value="C:\SQL Anywhere Demos\SimpleViewer\dbeng16.exe" />
        </RegistryKey>
        <RegistryKey Root="HKCU" Key="Software\ODBC\ODBC.INI\ODBC Data Sources">
          <RegistryValue Type="string" Name="SQL Anywhere 16 Demo" Value="SQL Anywhere 16" />
        </RegistryKey>
      </Component>
    </DirectoryRef>
  </Fragment>

</Wix>

Tags:

No comments