Datasource Setup¶

Requirements¶

One of the following:

• FileMaker Server 9–15 for Windows or OS X
• FileMaker Server 9–12 Advanced for Windows or OS X
• FileMaker Server Advanced 7 or 8 for Windows or OS X

Additionally, the Web Publishing Engine must be installed and each database must be configured according to the instructions in the following section.

Configuring FileMaker Server 9 or Higher¶

This section describes setting up FileMaker Server 9–15 or FileMaker Server 9–12 Advanced for use with Lasso. These versions will be referred to collectively as FileMaker Server.

Follow the instructions included with FileMaker Server carefully. Starting with version 9, setting up FileMaker Server is considerably easier than setting up earlier versions.

• Make sure that the “Web Serving” options are turned on and that the XML interface is enabled.
• The databases that are to be accessed by Lasso must be in the FileMaker Server Data/Databases folder and must be “Open” within FileMaker Server.
• Each database to be accessed by Lasso must have the “fmxml” keyword added to the “Extended Privileges” section of the “Accounts & Privileges” dialog box. The username and password entered into Lasso Server Admin must use a Privilege Set that has access to this extended privilege.
• FileMaker Server database security is only as secure as the Publishing Engine setup. It is possible for web browsers to communicate directly with the Publishing Engine. It is strongly recommended that the security features of FileMaker Server be used to secure web-accessible databases.
• It is strongly recommended that only a single IP address corresponding to the machine on which Lasso Server runs be permitted to access the Publishing Engine.
• For tips on optimizing performance for FileMaker databases, see the FileMaker Data Sources chapter.

Configuring FileMaker Server Advanced 7 or 8¶

This section describes setting up FileMaker Server Advanced for use with Lasso.

Follow the instructions included with FileMaker Server Advanced carefully. There are several steps in the process that are not obvious and require reading the documentation to set up properly. Configuring FileMaker Server Advanced is beyond the scope of this documentation, but some common pitfalls are listed below.

• Make sure both FileMaker Server and the FileMaker Server Advanced Publishing Engine are installed. The machine with the Publishing Engine must be running a supported web server.
• Configure FileMaker Server with a Client Services identifier and passcode. Enter this same identifier and passcode in the Web Publishing Administration Console.
• Verify XML Publishing is turned on in the Web Publishing Administration Console.
• The databases that are to be accessed by Lasso must be in the FileMaker Server Data/Databases folder and must be “Open” within FileMaker Server.
• Each database to be accessed by Lasso must have the “fmxml” keyword added to the “Extended Privileges” section of the “Accounts & Privileges” dialog box. The username and password entered into Lasso Server Admin must use a Privilege Set that has access to this extended privilege.
• FileMaker Server Advanced database security is only as secure as the Publishing Engine setup. It is possible for web browsers to communicate directly with the Publishing Engine. It is strongly recommended that the security features of FileMaker Server Advanced be used to secure web-accessible databases.
• It is strongly recommended that only a single IP address corresponding to the machine on which Lasso Server runs be permitted to access the Publishing Engine.
• For tips on optimizing performance for FileMaker databases, see the FileMaker Data Sources chapter.

Adding a FileMaker Server Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new FileMaker Server host:

1. In the “Datasources” section of Lasso Server Admin, click the filemakerds item.
2. Click the Add host item to reveal the host connection form.
3. Enter the IP address or domain name where the FileMaker Server data sources are being hosted.
4. Enter the TCP port the FileMaker Server communicates on in the “Port” field. See the FileMaker Server documentation for information on where to find or set this. It is commonly “80” for FileMaker Server, or “443” to connect over https.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default. If the host does not require a username or password, leave either field blank.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the FileMaker Server data source added here, inline methods can use the -database parameter to specify the name of the FileMaker database to perform an action on.

Specifying FileMaker Server Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method to access a FileMaker Server data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access a FileMaker Server host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “filemakerds”.
• -name should be specified as the IP address or domain name of the machine hosting FileMaker Server.
• -port is optional, defaulting to “80” if no port is specified.
• -username set to the user to authenticate as.
• -password set to the specified user’s password to authenticate the connection.

The following code shows how a connection to a FileMaker Server data source hosted on the same machine as Lasso might appear:

inline(
   -host=(:
      -datasource='filemakerds',
      -name='localhost',
      -port='80',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}

If there are no databases or tables listed, check the following links in a web browser to verify that the Web Publishing Engine is working correctly. Replace “filemaker_host” and “database_name” with values for your particular situation.

• http://filemaker_host/fmi/xml/FMPXMLRESULT.xml?-dbnames
• http://filemaker_host/fmi/xml/FMPXMLRESULT.xml?-db=database_name&-layoutnames

If either URL returns an error code other than 0 or fails in any way, Lasso will be unable to submit requests to FileMaker Server. Verify that XML Publishing is enabled or consult the FileMaker Server documentation on how to proceed.

Requirements¶

• MySQL Server 3.23 or MySQL Server 4.x or MySQL Server 5.x
• The MySQL service must be running and accepting TCP/IP connections on a port with no conflicts. This is port 3306 by default.
• MySQL access privileges must be properly assigned for the machine running Lasso Server to be allowed to authenticate.
• The MySQL client libraries are included with the connector, or automatically installed by the package manager.

Configuring MySQL Server¶

MySQL is operated via a command-line interface application which is normally located in the “bin” directory of the MySQL installation on the server machine. For information on how to use this, consult the MySQL documentation. Various installers for MySQL may have the service automatically start when the machine boots up, so also check the installation instructions for the installation method you are using.

Security for MySQL data sources can be set at any level (server-level, database-level, table-level, etc.). For unrestricted operation, all permissions for all levels of security need to be given to the user Lasso Server uses to connect. This involves setting a new user and password for Lasso Server in MySQL with the appropriate permissions, and then entering the username and password in Lasso Server Admin. Follow the procedure below for granting all permissions to Lasso Server in MySQL using the MySQL command-line utility.

1. From the command line, log in to MySQL as your root user by entering the following command:

   $> mysql -u root -p
   

   You will be prompted for the MySQL root user’s password specified during the MySQL installation.

2. After entering the password, you’ll see the MySQL command prompt (mysql>). Enter the following to create a new user with a username and password and access to all levels of security in MySQL:

   mysql> GRANT ALL ON *.* TO Username@Hostname IDENTIFIED BY "Password";
   

   Replace “Username” and “Password” with the username and password values you wish for the user to have, and replace “Hostname” with the IP address or domain name that Lasso Server will be connecting from.

Now there is a user with all permissions that can communicate with MySQL from the machine Lasso Server is running on. This user can now be used when configuring the MySQL host in the “Datasources” section of Lasso Server Admin.

Adding a MySQL Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new MySQL host:

1. In the “Datasources” section of Lasso Server Admin, click the MySQLDS item.
2. Click the Add host item to reveal the host connection form.
3. Enter the IP address or domain name where the MySQL databases are being hosted in the “Host” field.
4. Enter the TCP port the MySQL service communicates on in the “Port” field. This is commonly “3306” for MySQL.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the MySQL data source added here, inline methods can use the -database parameter to specify the name of the MySQL database to perform an action on.

Specifying MySQL Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method that accesses a MySQL data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access a MySQL host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “mysqlds”.
• -name should be specified as the IP address or domain name of the machine hosting MySQL.
• -port is optional, defaulting to “3306” if no port is specified.
• -username set to the user to authenticate as.
• -password set to the specified user’s password to authenticate the connection.

The following code shows how a connection to a MySQL data source hosted on the same machine as Lasso might appear:

inline(
   -host=(:
      -datasource='mysqlds',
      -name='localhost',
      -port='3306',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}

Requirements¶

• Oracle Database 10g or later
• The Lasso Server machine must have the Oracle “Instant Client” installed if Lasso Server and Oracle are running on separate machines. The Instant Client download can be found on the Oracle website. (Make sure to download just the basic Instant Client files rather than the complete Oracle 10g client or database installer.)

Configuring Oracle¶

The Oracle database server must be configured with a user that has access to all of the databases, tables, and other resources that will be published through Lasso. Consult the Oracle documentation for help configuring Oracle’s built-in security. The Oracle website has a “Getting Started” section which explains how to install and perform basic configuration of an Oracle database server.

Adding an Oracle Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new Oracle host:

1. In the “Datasources” section of Lasso Server Admin, click the Oracle item. (Restart Lasso if necessary to make it appear.)
2. Click the Add host item to reveal the host connection form.
3. Enter the IP address or domain name where the Oracle data sources are being hosted, the port, and the database name using the “host:port/database” format in the “Host” field (e.g. “www.example.com:1521/MyDatabase”).
4. Enter the TCP port of the Oracle service in the “Port” field. This is commonly “1521” for Oracle.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the Oracle Server data source added here, inline methods can use the -database parameter to specify the name of the Oracle database to perform an action on.

Specifying Oracle Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method that accesses an Oracle data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access an Oracle host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “oracle”.
• -name should be specified as the IP address or domain name of the machine hosting Oracle, followed by a colon and the port to connect on, and ending with a slash and the database name (e.g. “www.example.com:1521/MyDatabase”).
• -port is optional, defaulting to “1521” if no port is specified.
• -username set to the user to authenticate as.
• -password set to the specified user’s password to authenticate the connection.

The following code shows how a connection to an Oracle data source might appear:

inline(
   -host=(:
      -datasource='oracle',
      -name='oracle.example.com:1521/MyDatabase',
      -port='1521',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}

Requirements¶

• PostgreSQL 8.x
• The Lasso Server machine must have the PostgreSQL “libpq” library installed. This comes packaged with OS X and is installed automatically with Lasso on Linux. Windows users can find libpq.dll included with the PostgreSQL ODBC driver package; once installed, adding the containing directory to the Path environment variable will allow Lasso to load the native driver.

Configuring PostgreSQL¶

The PostgreSQL database server must be configured with a user that has access to all of the databases, tables, and other resources that will be published through Lasso. Consult the PostgreSQL documentation for help configuring its built-in security.

Adding a PostgreSQL Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new PostgreSQL server host:

1. In the “Datasources” section of Lasso Server Admin, click the PostgreSQL item.
2. Click the Add host item to reveal the host connection form.
3. Enter the IP address or domain name where the PostgreSQL data source is being hosted in the “Host” field.
4. Enter the TCP port the PostgreSQL service is listening on in the “Port” field. This is commonly “5432” for PostgreSQL.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the PostgreSQL data source added here, inline methods can use the -database parameter to specify the name of the PostgreSQL database to perform an action on.

Specifying PostgreSQL Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method that accesses a PostgreSQL data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access a PostgreSQL host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “postgres”.
• -name should be specified as the IP address or domain name of the machine hosting PostgreSQL.
• -port is optional, defaulting to “5432” if no port is specified.
• -username set to the user to authenticate as.
• -password set to the specified user’s password to authenticate the connection.

The following code shows how a connection to a PostgreSQL data source hosted on the same machine as Lasso might appear:

inline(
   -host=(:
      -datasource='postgres',
      -name='localhost',
      -port='5432',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}

Requirements¶

• An ODBC driver that has been configured as a System DSN in the ODBC control panel.

  OS X:	ODBC data sources are configured using “ODBC Manager” which can be downloaded from http://www.odbcmanager.net/ and installed in the /Applications/Utilities folder. (Note that the folder /Library/ODBC must be created first.)
  Linux:	Consult the documentation of the ODBC drivers for information about how to set up data sources on Linux. Many ODBC drivers ship with a control panel that allows configuration of those drivers.
  Windows:	ODBC data sources are configured using “ODBC Data Source Administrator” which is normally accessed through the Windows Control Panel under Administrative Tools.

Configuring ODBC Connections¶

Consult the documentation for your data sources and ODBC drivers for details about how to secure access to the data made available through the driver. Most data sources will require the following steps:

1. Install your ODBC driver using the provided installer or instructions. This may involve creating an odbcinst.ini file.
2. Create a System DSN in the ODBC administration application, or edit the odbc.ini file. Note that the System DSN name, username, and password configured here will need to be entered in Lasso.
3. Locate and configure the SQL.ini file for your driver, if applicable. This file sets the options for your ODBC driver including the location of your data source. Consult your driver’s documentation for details about where to find this file and what options can be configured.
4. Follow the steps below to add the data source to Lasso.

Adding an ODBC Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new ODBC host:

1. In the “Datasources” section of Lasso Server Admin, click the ODBC item.
2. Click the Add host item to reveal the host connection form.
3. Enter the System DSN name of the ODBC connection in the “Host” field.
4. Enter the TCP port of the ODBC connection in the “Port” field.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the ODBC data source added here, inline methods can use the -database parameter to specify the name of the database to perform an action on.

Specifying ODBC Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method that accesses an ODBC data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access an ODBC host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “odbc”.
• -name should be specified as the System DSN.
• -username set to the user to authenticate as, if required.
• -password set to the specified user’s password to authenticate the connection, if required.

The following code shows how a connection to an ODBC data source hosted on the same machine as Lasso might appear:

inline(
   -host=(:
      -datasource='odbc',
      -name='System_DSN_Name',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}

Requirements¶

• Microsoft SQL Server 2005–2012
• The Lasso Server machine must have the SQL Server client libraries installed.

  OS X and Linux:	The FreeTDS libraries need to be compiled and installed, for which the source can be found at http://www.freetds.org/. (Instead of compiling from source, you may first want to look into installing via a package manager such as apt, yum, macports, or homebrew.) See Configuring ODBC Connections for an example configuration.
  Windows:	The required client libraries are pre-installed with the operating system.

Configuring SQL Server¶

The SQL Server database server must be configured with a user that has access to all of the databases, tables, and other resources that will be published through Lasso. Consult the SQL Server documentation for help configuring its built-in security.

Adding a SQL Server Data Source Host¶

For general information about navigating Lasso Server Admin and adding a host to a data source, see the section Configuring Datasources in the Instance Administration and Configuration chapter.

To add a new SQL Server database host:

1. In the “Datasources” section of Lasso Server Admin, click the SQLServer item.
2. Click the Add host item to reveal the host connection form.
3. Enter the IP address or domain name where the SQL Server data source is being hosted followed by a backslash and the name of a database in the “Host” field. (e.g. “www.example.com\MyDatabase”)
4. Enter the TCP port the SQL Server service is listening on in the “Port” field. This is commonly “1433” for SQL Server.
5. Select “Yes” from the Enabled drop-down to enable the host.
6. Enter a username for the host in the “Username” field and a password for the host in the “Password” field. Lasso will connect to the data source and all databases therein using this username and password by default.
7. Click the Add host button.
8. Once the host is added, the new host appears in the “Hosts” listing below.

Databases in newly created hosts are enabled by default. The administrator can disable databases by expanding the database listing and setting the Enabled drop-down to “No”. With the SQL Server data source added here, inline methods can use the -database parameter to specify the name of the SQL Server database to perform an action on.

Specifying SQL Server Hosts in Inlines¶

Setting up a data source host in Lasso Server Admin is the best way to ensure that access to the data source is centrally controlled. However, it can sometimes be beneficial to access a data source host without a lot of configuration. This section describes how to construct an inline method that accesses a SQL Server data source host. See the section Inline Connection Options for full details about specifying hosts in inlines.

To access a SQL Server host directly in an inline method, the -host parameter can specify all of the connection parameters. The -host parameter takes an array that should contain the following elements:

• -datasource should be specified as “sqlserver”.
• -name should be specified as the IP address or domain name of the machine hosting SQL Server.
• -port is optional, defaulting to “1433” if no port is specified.
• -username set to the user to authenticate as.
• -password set to the specified user’s password to authenticate the connection.

The following code shows how a connection to a SQL Server data source hosted on the same machine as Lasso might appear:

inline(
   -host=(:
      -datasource='sqlserver',
      -name='(local)\MYDB',
      -username='username',
      -password='secret'
   ),
   -findAll,
   -database='database',
   -table='table'
) => {^
   found_count
^}