Creating AlphaDAO Connection Strings

Description

Alpha Anywhere Data Access Objects (AlphaDAO) let your web or mobile application communicate with a SQL Database. Learn how to create an AlphaDAO connection string to an Access, MySQL, or SQL Server database using the Northwind database that ships with Alpha Anywhere.

Important Notice for Community Edition Users

This tutorial was built using the full Alpha Anywhere Development Environment. This tutorial is intended for developers using older versions of Alpha Anywhere, which did not automatically configure the Development Server to use a port that is not in use.

Alpha Anywhere Community Edition and the full Alpha Anywhere Development Environment automatically configure the Development Server to use an unused port during installation.

Images in this tutorial may not match what you see in Community Edition.

Database Connection Strings

Build a Database Connection String

Alpha Anywhere can communicate with databases using an AlphaDAO connection string. AlphaDAO stands for "Alpha Anywhere Data Access Object". AlphaDAO is an interface through which you can access data stored in SQL, noSQL, DBaaS, SaaS, and other data sources, including static JSON and OData (Open Data Protocol) APIs. (See AlphaDAO Overview and AlphaDAO Objects for information on using AlphaDAO with Xbasic.)

In this step-by-step tutorial, you will create an AlphaDAO connection string to communicate with the Northwind database. The Northwind database is a Microsoft Access database that ships with Alpha Anywhere. If desired, the Northwind database can be imported into SQL Server or MySQL. Choose the type of database connection string you wish to create from the list below, and click the link to get started.

  • Build an Access Connection String

    This section guides you through the process of setting up an Access AlphaDAO connection string to the Northwind database. In order to perform CRUD (Create, Read, Update, Delete) operations on the Access Northwind database that ships with Alpha Anywhere, a connection string to the database needs to be created. This is done using the Create 'AADemo-Northwind' demo connection string link in the AlphaDAO Connections dialog.

    1. From the Web Projects Control Panel in Alpha Anywhere, select Tools > Alpha DAO Connection strings.

      images/connectionstring1.png
    2. The AlphaDAO Connections dialog allows you to create and manage named connection strings in your Alpha Anywhere projects. "Named" connections are useful as they allow you to define a connection string to a database once and reference the AlphaDAO connection throughout your application using a "named" reference.

      For this tutorial, we will be using the AADemo-Northwind connection string, which is a pre-built connection for the MS Access Northwind database that ships with Alpha Anywhere.

      If the AADemo-Northwind connection string is not already listed, click the Create 'AADemo-Northwind' demo connection string link at the bottom of the AlphaDAO Connections dialog to create it.

      images/connectionString_AA1.png
    3. When prompted, click OK - Create a named connection string called 'AADemo-Northwind' to create the connection string.

      images/connectionString_AA2.png
      The Demo Connection String dialog provides useful information about the 'AADemo-Northwind' connection string.
    4. You will be shown a Notice confirming the connection string has been created. Click OK to close the Notice.

      images/connectionString_AA3.png
    5. Click the Close button to exit the AlphaDAO Connections dialog. You can now build a mobile or web application that communicates with the Northwind Microsoft Access database. [Click here] for a list of tutorials that use the Northwind database.

      images/connectionString_AA4.png
      On some systems where Office 365 is installed, you may receive a Can't Load ODBCJI32.dll Error message. To fix this error, you will need to install the Microsoft Access Database Engine. You can download the Microsoft Access Database Engine Installer here. If the Microsoft Access Database Engine is already installed, running a 'Repair' on the engine should fix the issue. If this does not solve the issue, follow the instructions for Build a SQL Server Connection String or Build a MySQL Connection String to create a SQL Server or MySQL version of the Northwind database and connection string.
  • Build a SQL Server Connection String

    This section guides you through the process of creating a SQL Server version of the Northwind database and setting up a SQL Server Alpha DAO connection string to communicate with the SQL Server Northwind database.

    1. Follow the tutorial, Importing the Northwind Access Database into SQL Server, to create a Northwind SQL Server database. When you are finished, return to this guide and proceed to the next step.

    2. Next, create a connection string to the Northwind SQL Server database by creating a new AlphaDAO Connection. From the Web Projects Control Panel, select Tools > Alpha DAO Connection strings.

      images/connectionstring1.png
      Open the AlphaDAO Connections dialog.
      images/connectionstring2.png
      The AlphaDAO Connections Dialog.
    3. Click the New button to create a new connection string. Type in "Northwind" for the Connection Name and click the Build button to construct the Connection String.

      images/connectionstring3.png
      Creating a new connection to the Northwind SQL Server database.
      images/connectionstring4.png
      The Create SQL Connection String dialog.
    4. Click the button to change the Connection Type. Select SQLServer from the list of options in the Select an API window and click OK.

      images/connectionstringSQLServer1.png
      Set the Connection Type to SQLServer.
    5. Enter the Version, Server name, Port, and Credentials information for your SQL Server instance.

      Server names should be in the form server\instanceName. If you omit the instance name, the default instance is assumed.

      You can click the How do I format server names for SQL Server instances? link to open the help popup (shown below) to find more information on the server name.

      images/connectionstringSQLServer7.png
      Configuration options for SQL Server.
      images/connectionstringSQLServer6.png
      Help popup for "How do I format server names for SQL Server instances?"
    6. Test the connection by clicking the Test Connection button. You should see a success message. If you receive an error message, verify the Version, Server, Port and Credentials are correct.

      images/connectionstringSQLServer3.png
      Success! The SQL Server connection works.
    7. Click the Refresh button to populate the Database dropdown box. Select the Northwind SQL Server database you created from the list of options. Click OK to create the SQL Server connection string.

      images/connectionstringSQLServer2.png
      Configuring SQL Server connection string. This is image shows a configuration for connecting to a SQL Server Express instance. The Northwind database in this SQL Server instance is named "northwindSQLServer".
    8. Click OK to save the new Northwind connection string.

      images/connectionstringSQLServer4.png
      Click OK to save the Northwind connection string.
    9. Click the Close button to exit the AlphaDAO Connections dialog. You can now build a mobile or web application that communicates with the Northwind SQL Server database. [Click here] for a list of tutorials that use the Northwind database.

      images/connectionstringSQLServer5.png
      The Northwind AlphaDAO connection string for SQL Server.
  • Build a MySQL Connection String

    This section guides you through creating a MySQL version of the Northwind database and setting up a MySQL AlphaDAO connection string to communicate with the MySQL Northwind database.

    1. Download northwindmysql.zip and unzip the file. This zip file contains a self-contained file of the MySQL Northwind database called "northwindmysql.sql".

      images/northwindMySQL.png
      Contents of the northwindmysql.zip archive.
    2. Import the self-contained Northwind database into MySQL. You can find instructions here for using MySQL Workbench to Import the database. Use the "Import from Self-Contained File" option for the Data Import.

      images/mySQLDataRestore.png
      Doing a Data Import using MySQL Workbench
      There is no schema specified in the northwindmysql.sql file. You must specify the Default Target Schema, otherwise the tables will be imported into the active schema. This may result in data being overwritten if the active schema contains table names matching tables in the Northwind database!
      Backup any existing MySQL databases before importing the Northwind database.
    3. Next, create a connection string to the Northwind MySQL database by creating a new AlphaDAO Connection. From the Web Projects Control Panel, select Tools > Alpha DAO Connection strings.

      images/connectionstring1.png
      Open the AlphaDAO Connections dialog.
      images/connectionstring2.png
      The AlphaDAO Connections Dialog.
    4. Click the New button to create a new connection string. Type in "Northwind" for the Connection Name and click the Build button to construct the Connection String.

      images/connectionstring3.png
      Creating a new connection to the Northwind MySQL database.
      images/connectionstring4.png
      The Create SQL Connection String dialog.
    5. Click the button to change the Connection Type. Select MySQL from the list of options in the Select an API window and click OK.

      images/connectionstringMySQL1.png
      Set the Connection Type to MySQL.
      images/connectionstringMySQL7.png
      The Create SQL Connection String dialog Options for a "MySQL" Connection Type.
      The Create SQL Connection String dialog may appear slightly differently in your version of Alpha Anywhere. The TSL/SSL tab may be missing. For this tutorial, you will not be using settings available on the TSL/SSL tab.
    6. Configure the MySQL Options for the connection. Choose the Version of MySQL that is installed.

    7. Enter "localhost" in Hostname. If the MySQL database is hosted on another computer, enter the host IP address. For example, 127.0.0.1.

    8. Enter the MySQL Port number. The default port number for a typical MySQL installation is "3306".

    9. Enter the User for accessing the MySQL database. On your development machine, this can be "root" or a user account you created.

    10. Enter the password for the User account specified.

    11. Verify the connection to the Northwind database is correct by clicking the Test Connection button. You should see a success message. If you receive an error message, verify the Hostname, Port, User, and Password are correct.

      images/connectionstringMySQL3.png
      Success! The connection string to the MySQL database works.
    12. Select the MySQL Northwind database you created from the Database list. If Database does not have any options, click the Refresh button. If it still does not list any databases, verify the User you have chosen has access to the MySQL Northwind database.

      images/connectionstringMySQL2.png
      Configuring the MySQL connection string. The Northwind database in this MySQL instance is named "northwind".
    13. Click OK to create the MySQL connection string.

      images/connectionstringMySQL4.png
      Click OK to save the MySQL Connection string.
    14. Click OK to save the new Northwind connection string.

      images/connectionstringMySQL5.png
      Click OK to save the Northwind connection string.
    15. Click the Close button to exit the AlphaDAO Connections dialog. You can now build a mobile or web application that communicates with the Northwind MySQL database.

      images/connectionstringMySQL6.png
      The Northwind AlphaDAO connection string for MySQL