Lasso Documentation

Overview provides an overview of data source connectors in Lasso Professional Server.
FileMaker Pro Data Sources provides instructions for setting up FileMaker Pro 4, 5, or 6 data sources.
FileMaker Server Data Sources provides instructions for setting up FileMaker Server 7, 8, or 9 Advanced and FileMaker Server 9 data sources.
JDBC Data Sources provides instructions for setting up data sources using a JDBC driver.
MySQL Data Sources provides instructions for setting up a MySQL data source.
ODBC Data Sources provides instructions for setting up data sources using an ODBC driver.
OpenBase Data Source provides instructions for setting up OpenBase data sources.
Oracle Data Source provides instructions for setting up Oracle data sources.
PostgreSQL Data Source provides instructions for setting up PostgreSQL data sources.
Spotlight Data Source provides instructions for configuring the Spotlight data source on Mac OS X.
SQL Server Data Sources provides instructions for setting up Microsoft SQL Server data sources.
SQLite Data Source describes the SQLite data source built into Lasso.
Other Data Sources provides instructions for setting third-party data sources.
Setting Databases Preferences describes how to set database-level preferences in Lasso Professional Server.
Setting Table Preferences describes how to set table-level preferences in Lasso Professional Server, including Unicode-encoding preferences.
Setting Field Preferences describes how to set field-level preferences in Lasso Professional Server.

Overview

[p]Lasso Professional Server communicates with data sources using Lasso data source connectors, which are modular components configured using the Setup > Data Sources section of Site Administration. Lasso Professional Server provides built-in connectors all of the data sources listed in the following table. [/p]

[p]Most connectors can access data sources which are installed on the same machine as Lasso or on a remote machine. Some connectors such as the Spotlight data source can only access files on the local machine. Whether or not an ODBC or JDBC data source can communicate with Lasso on a separate machine depends on whether or not the driver can communicate via TCP/IP. [/p]

[p]Custom data source connectors for other data sources can also be created for use with Lasso Professional Server using LJAPI, LCAPI, or Lasso Tags. Information about creating and using third-party data source connectors can be found in the Language Guide. [/p]

Table 1: Lasso Professional Server 8.5 Data Sources

[table][tr][th]Data Source[/th][th]Notes[/th][th] [/th][/tr]

[tr][td]FileMaker Pro[/td][td]Suports FileMaker Pro 4.x, FileMaker Pro Unlimited 5.x and 6.x. [/td][/tr]

[tr][td]FileMaker Server[/td][td]Supports FileMaker Server 7, 8, or 9 Advanced and FileMaker Server 9. [/td][/tr]

[tr][td]JDBC[/td][td]Supports any data source with a compatible JDBC driver. See the JDBC Data Sources section for details about how to install JDBC drivers. [/td][/tr]

[tr][td]MySQL[/td][td]Supports MySQL 3.x, 4.x, or 5.x data sources. [/td][/tr]

[tr][td]ODBC[/td][td]Support any data source with a compatible ODBC driver. See the ODBC Data Sources section for details about how to install ODBC drivers. [/td][/tr]

[tr][td]OpenBase[/td][td]Supports OpenBase data sources. [/td][/tr]

[tr][td]Oracle[/td][td]Supports Oracle data sources. The Oracle "Install Client" libraries must be installed in order to activate this data source. See the Oracle Data Sources section for more information. [/td][/tr]

[tr][td]PostgreSQL[/td][td]Supports PostgreSQL data sources. The PostgreSQL client libraries must be installed in order to activate this data source. See the PostgreSQL Data Sources section for more information. [/td][/tr]

[tr][td]Spotlight[/td][td]Supports searching files on Mac OS X 10.4/10.5 systems only. [/td][/tr]

[tr][td]SQL Server[/td][td]Supports Microsoft SQL Server. The SQL Server client libraries must be installed in order to activate this data source. See the SQL Server Data Sources section for more information. [/td][/tr]

[tr][td]SQLite[/td][td]SQLite is also the internal data source which is used for the storage of Lasso's preferences and security settings. [/td][/tr]

[/table]

Using Lasso Data Source Connectors

[p]Data source connectors allow database actions to be performed via Lasso Web pages and Lasso pages. Database actions can be used in Lasso to search for records in a database that match specific criteria, to navigate through the found set from a database search, to add, update, or delete a record in a database, to fetch schema information about a database, and more. In addition, database actions can be used to execute SQL statements in SQL-compliant databases. [/p]

[p]Interacting with data sources via Lasso Professional Server involves four steps: [/p]

Configuring the data source application or service to accept connections from Lasso Professional Server. This is done in the data source itself, outside of Lasso Site Administration. This chapter describes configuring each data source to accept connections from Lasso Professional Server.
Data sources can be connected to directly by specifying the host parameters directly within inlines. Using this method does not require setting up the data source in Lasso Site Administration and does not require configuring Lasso security. This method is best used when developing sites where some security can be sacrificed for coding efficiency.
Configure Lasso Professional Server to communicate with a data source host. This involves adding the data source connection information in the Setup > Data Sources > Hosts section, and enabling the desired databases in the Setup > Data Sources > Databases section. This chapter describes creating connections with MySQL, FileMaker Pro 4, 5, or 6, FileMaker Server 7, 8, or 9 Advanced, FileMaker Server 9, and JDBC-compliant data sources.
Set up Lasso Security for the data source via the Setup > Security section of Site Administration. This typically involves setting database and table permissions for the [code]AnyUser[/code] group, and then creating custom groups and users if password-protected user access is desired. This is described in the Setting Up Security chapter.
Write Lasso code to interact with the data source. This is covered in the Database Interaction section of the Language Guide.

Viewing Lasso Data Source Connectors

[p]The Setup > Data Sources > Connectors page in Site Administration is where all data source connectors can be listed, viewed, enabled, or disabled. The [code]Connector Listing[/code] panel shows all connectors currently installed to Lasso Professional Server, the status of each connector, and links to the [code]Hosts[/code] page for each connector. [/p]

[p]Data sources which require additional set up will be marked as Unavailable in this listing. Consult the appropriate section of this chapter for details about how to install the additional components necessary to enable the data source. JDBC data sources will not show up unless drivers have been installed in the [code]JDBCDrivers[/code] folder. [/p]

Figure 1: Connectors Page

Updating Data Source Connectors

[p]Selecting a data source connector from the [code]Connectors Listing [/code]panel will show the [code]Connector Detail [/code]panel to the right. The [code]Connector Detail[/code] panel displays the name of the selected data source connector, its status, and the internal name for the connector as it appears in the Lasso Service console. Data source connectors may be enabled or disabled using this panel. JDBC drivers additionally allow the specific driver type to be selected. [/p]

To update details for a selected connector:

Select [code]Enabled[/code] or [code]Disabled[/code] from the [code]Status[/code] pull-down menu to enable or disable the connector.

JDBC Drivers allow the [code]JDBC Driver Class[/code] to be selected. In general a JDBC 2.0 class should be selected if available.

Select the [code]Update[/code] button.

FileMaker Pro Data Sources

[p]Lasso Professional Server supports communication with FileMaker Pro 4, 5, or 6, FileMaker Server 7, 8, or 9 Advanced, or FileMaker Server 9. This section documents how to set up Lasso to communicate with the different versions of FileMaker Pro. See the next section for information about connecting to FileMaker Server. [/p]

[p]Lasso Professional Server communicates with FileMaker Pro 4, 5, or 6 via the Web Companion, which is the built-in Web-publisher for FileMaker Pro. Using Web Companion, FileMaker Pro may be set up to run on the same machine as Lasso Professional Server or on a separate machine. [/p]

Requirements

[p]FileMaker Pro 4.x, FileMaker Pro 5.x Unlimited, or FileMaker Pro 6.0 Unlimited for Windows or Mac OS X with the latest version of Web Companion activated and configured. FileMaker Server connectivity is documented in the next section. See the table below for all versions of FileMaker Pro’s Web Companion certified to work with Lasso Professional Server. [/p]

Table 2: Supported Versions of FileMaker Pro’s Web Companion

[table][tr][th]FileMaker Version[/th][th]Web Companion Version[/th][th] [/th][/tr]

[tr][td]FileMaker Pro 4.x[/td][td]4.0v3 [/td][/tr]

[tr][td]FileMaker Pro Unlimited 5.0v3[/td][td]5.0v6 [/td][/tr]

[tr][td]FileMaker Pro Unlimited 5.5v2[/td][td]5.5v4 [/td][/tr]

[tr][td]FileMaker Pro Unlimited 6.0v4[/td][td]6.0v2 [/td][/tr]

[tr][td]FileMaker Server 7 Advanced[/td][td]See Next Section [/td][/tr]

[tr][td]FileMaker Server 8 Advanced[/td][td]See Next Section [/td][/tr]

[tr][td]FileMaker Server 9[/td][td]See Next Section [/td][/tr]

[tr][td]FileMaker Server 9 Advanced[/td][td]See Next Section [/td][/tr]

[/table]

[p]The latest updates to FileMaker Pro’s products can be downloaded from FileMaker, Inc.’s Web site at: [/p]

[pre]http://www.FileMaker.com/ [/pre]

[note][b]FileMaker [/b][b]Licensing Note[/b]: In accordance with the FileMaker Pro license agreement, only FileMaker Pro Unlimited 5 or 6 may be used with Lasso Professional Server. However, The regular version of FileMaker Pro 4, 5, or 6 may be used with Lasso Studio or Lasso Developer. For more information on the FileMaker Pro license agreement, see FileMaker’s Web site at[code] http://www.filemaker.com[/code]. [/note]

Configuring FileMaker Pro 4, 5, or 6

[p]This section describes setting up FileMaker Pro 4, 5, or 6 Web Companion for use with Lasso Professional Server. [/p]

To configure FileMaker Pro Web Companion:

In FileMaker Pro, select Edit > Preferences > Application from the menu bar (FileMaker Pro > Preferences > Application in Mac OS X versions).

Select the [code]Plug-Ins[/code] tab.

Under [code]Enabled Plug-Ins[/code], check the box next to [code]Web Companion[/code]. This makes the [code]Configure…[/code] button appear at the bottom of the window.

Select the [code]Configure… [/code]button.

Enter the TCP/IP port through which Web Companion will communicate with Lasso Professional Server in the [code]TCP/IP[/code] field.
[note][b]Security [/b][b]Note[/b]: Communication with the Web Companion in FileMaker Pro 5.x and 6.x can be limited to a single IP address via checking the [code]Restrict access to IP address(es)[/code] box and entering the IP address of the machine that is running Lasso Professional Server. This is highly recommended for maximum data source security. [/note]

Select [code]OK[/code].

[p]Web Companion is now configured to communicate from the FileMaker Pro application via the TCP/IP port specified. Sharing via Web Companion also needs to be enabled for each FileMaker Pro database that is to be used with Lasso Professional Server. [/p]

To enable databases to be shared via Web Companion:

In FileMaker Pro, open the database to be used with Lasso Professional Server.

Select File > Sharing from the menu bar.

Check the box next to [code]Web Companion[/code].

Select [code]OK. [/code]The FileMaker Pro database is now ready for use with Lasso Professional Server.

For proper performance, database-level security must be aligned between FileMaker Pro and Lasso Professional Server. This involves setting a master password for a FileMaker Pro database, and then entering the password in Site Administration. Follow the procedure below for setting a master password for a FileMaker Pro database, which is then provided to Lasso Professional Server.

To set a master password for a FileMaker Pro database:

In FileMaker Pro, select File > Access Privileges > Passwords from the menu bar.

Under[code] Privileges[/code], check [code]Access the entire file[/code].

In the [code]Available commands[/code] pull-down menu, select [code]Normal[/code].

Enter a password in the [code]Password[/code] field.

Select [code]Create[/code].

Select [code]Done[/code].

[p]The above steps will prevent any user from accessing a FileMaker Pro database directly without knowing the correct password. The FileMaker Pro host may now be configured in the [code]Setup [/code]section of Site Administration. [/p]

FileMaker Pro Configuration Guidelines

[p]For proper performance, adhere to the following guidelines. [/p]

All Lasso-enabled FileMaker Pro databases must be open in FileMaker Pro before a Lasso action can be performed on the database.
All Lasso-enabled FileMaker Pro databases must be in [code]Browse[/code] mode.
All Lasso-enabled FileMaker Pro databases may be in either single-user or multi-user mode in FileMaker Pro.
All Lasso-enabled FileMaker Pro databases should be opened with the master password if passwords have been established, specifically so that all necessary fields, layouts, scripts, and functions (browsing, printing, exporting, creating records, etc.) are available.
FileMaker Pro database security in Lasso Professional Server is only as secure as the Web Companion setup. It is possible for Web browsers to communicate directly with the Web Companion, which circumvents Lasso security. It is strongly recommended that database-level restrictions be enabled within FileMaker Pro, and user-level security be controlled by Lasso Professional Server security. Additionally, it is strongly recommended that only a single IP address is permitted to access Web Companion which represents the machine on which Lasso Professional Server operates.
For tips on optimizing performance for FileMaker Pro databases, see the FileMaker Pro Data Sources chapter in the Lasso 8.5 Language Guide.

Creating Connections to FileMaker Pro Hosts

[p]The Setup > Data Sources > Hosts[code] [/code]page in Site Administration displays all data source hosts for a selected data source connector, and allows the administrator to add new hosts. A host is a computer that serves the databases with which Lasso Professional Server communicates. [/p]

Figure 2: FileMaker Pro Hosts Page

[p]There are two FileMaker connectors in Lasso Professional Server. Lasso Connector for FileMaker Pro enables communication with FileMaker Pro versions 4, 5, or 6. Lasso Connector for FileMaker SA enables communication with FileMaker Server 7, 8, or 9 Advanced and FileMaker Server 9. [/p]

Adding Data Source Hosts

[p]Selecting the [code]Add Host[/code] button shows the [code]Add Host [/code]panel to the right. The [code]Add Host [/code]page allows the administrator to add a new FileMaker data source host to a selected data source connector. [/p]

To add a new FileMaker Pro host:

[p]Make sure that the proper data source is selected for the version of FileMaker Pro that is to be accessed. [/p]

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host in the [code]IP Address/Host Name [/code]field. This is the address of the machine running the data source application.

Enter the TCP/IP port of the data source application or service of the host in the [code]Port[/code] field. See the FileMaker Pro documentation for information on where to find or set this in the data source. This is commonly [code]591[/code] for the FileMaker Pro Web Companion.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username for the host in the [code]Host Username[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this username by default. If the host does not require a username, then leave this field blank.
[note][b]Note: [/b]No username should be entered for a FileMaker Pro host if [code]FileMaker Pro Access Privileges[/code] is used as the security method for the FileMaker Pro data source. [/note]

Enter a password for the host in the[code] Host Password[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this password by default. If the host does not require a password, then leave this field blank.

Select the [code]Add Host [/code]button.

[p]Once the host is added, the new host appears in the [code]Hosts Listing[/code] panel to the left. There, one can select the[code] List Databases[/code] link, or select the [code]Databases[/code] tab to view the databases in the specified host. [/p]

Updating and Deleting Host Settings

[p]Selecting the name of a host shows its details in the [code]Host Detail [/code]panel, which appears to the right. In the [code]Host Detail[/code] panel, the administrator can update or delete the selected host by selecting the [code]Update[/code] or [code]Delete[/code] button. [/p]

Table Batch Change

[p]The table batch change option allows the encoding of every table on the current host to be changed at once. Select a new encoding from the pop-up menu or select [code]Other…[/code] and type a custom encoding choice. After clicking perform every table on the current host will be set to the specified encoding. [/p]

[note][b]Note: [/b]New tables which are added to databases on the host will not be set to the selected encoding. It may be necessary to adjust the encoding of each new table after they are added or to use the batch change option after new tables are added. [/note]

Specifying FileMaker Pro Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access a FileMaker Pro data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access a FileMaker Pro host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]fmremotedatasource[/code]. The [code]-Name[/code] should be specified as the URL of the machine hosting FileMaker Pro. The [code]-Port[/code] is optional and defaults to [code]591[/code]. The [code]-Username[/code] and [code]-Password[/code] should be specified. [/p]

[p]For example, the following code shows how a connection to a FileMaker Pro database hosted on the same machine as Lasso might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’fmremotedatasource’,
-Name=’127.0.0.1’,
-Port=591,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

Enabling FileMaker Pro Databases

[p]The last required step in configuring a data source connection is to enable the newly-connected databases in Site Administration. This provides a method for the Lasso site administrator to designate which databases can and cannot be used with Lasso. [/p]

[p]The section on [code]Database Preferences[/code] which follows the individual data source sections includes instructions for enabling database and modifying database preferences. For information on setting table, and field preferences, see the corresponding sections at the end of this chapter. For information on setting up required Lasso data source permissions for users, see the Setting Up Security chapter. [/p]

FileMaker Server Data Sources

[p]Lasso Professional Server communicates with FileMaker Server 7, 8, or 9 Advanced and FileMaker Server 9 through the built-in XML interface. Lasso cannot communicate with any other products in the FileMaker 7, 8 or 9 product line such as FileMaker Pro. [/p]

Requirements

[p]FileMaker Server 9 for Windows or Mac OS X (or FileMaker Server 9 Advanced). Or, FileMaker Server Advanced 7 or 8 for Windows or Mac OS X. The Web Publishing Engine must be installed and each database must be configured according to the instructions in the following section. [/p]

[p]The latest versions of FileMaker Server can be downloaded from FileMaker, Inc.’s Web site at: [/p]

[pre]http://www.FileMaker.com [/pre]

Configuring FileMaker Server 9 for Use With Lasso

[p]This section describes setting up FileMaker Server 9 or FileMaker Server 9 Advanced for use with Lasso Professional Server. [/p]

To configure FileMaker Server 9:

[p]Follow the instructions included with FileMaker Server 9 carefully. Setting up FileMaker Server 9 is considerably easier than setting up earlier versions of Filemaker Server. [/p]

Make sure that the Web Serving options are turned on and that the XML interface is enabled.
The databases which are to be accessed by Lasso must be in the FileMaker Server [code]Data/Databases[/code] folder and must be [code]Open[/code] within FIleMaker Server.
Each database to be accessed by Lasso must have the [code]fmxml[/code] keyword added to the Extended Privileges section of the Accounts & Privileges dialog box. The username and password which are entered into Lasso Administration must use a Privilege Set which has access to this extended privilege.
FileMaker Pro database security in Lasso Professional Server is only as secure as the Publishing Engine setup. It is possible for Web browsers to communicate directly with the Publishing Engine, which circumvents Lasso security. It is strongly recommended that the security features of FIleMaker Pro be used to secure accessible databases.
It is strongly recommended that only a single IP address is permitted to access the Publishing Engine which represents the machine on which Lasso Professional Server operates.
For tips on optimizing performance for FileMaker databases, see Chapter 10: FileMaker Data Sources in the Lasso Language Guide.

Configuring FileMaker Server 7 or 8 Advanced for Use With Lasso

[p]This section describes setting up FileMaker Server Advanced for use with Lasso Professional Server. [/p]

To configure FileMaker Server Advanced:

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

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.
Ensure XML Publishing is turned on in the Web Publishing Administration Console.
The databases which are to be accessed by Lasso must be in the FileMaker Server [code]Data/Databases[/code] folder and must be [code]Open[/code] within FIleMaker Server.
Each database to be accessed by Lasso must have the [code]fmxml[/code] keyword added to the Extended Privileges section of the Accounts & Privileges dialog box. The username and password which are entered into Lasso Administration must use a Privilege Set which has access to this extended privilege.
FileMaker Pro database security in Lasso Professional Server is only as secure as the Publishing Engine setup. It is possible for Web browsers to communicate directly with the Publishing Engine, which circumvents Lasso security. It is strongly recommended that the security features of FIleMaker Pro be used to secure accessible databases.
It is strongly recommended that only a single IP address is permitted to access the Publishing Engine which represents the machine on which Lasso Professional Server operates.
For tips on optimizing performance for FileMaker databases, see Chapter 10: FileMaker Data Sources in the Lasso Language Guide.

Creating Connections to FileMaker Server Hosts

[p]The Setup > Data Sources > Hosts[code] [/code]page in Lasso Administration displays all data source hosts for a selected data source connector, and allows the administrator to add new hosts. A host is a computer that serves the databases with which Lasso Professional Server communicates. [/p]

Figure 3: FileMaker Server Hosts Page

Adding Data Source Hosts

To add a new FileMaker Server host:

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host in the [code]IP Address/Host Name [/code]field. This is the address of the machine running the data source application.

Enter the TCP/IP port of the data source application or service of the host in the [code]Port[/code] field. See the FileMaker Server documentation for information on where to find or set this in the data source. This is commonly [code]80[/code] for FileMaker Server.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username for the host in the [code]Host Username[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this username by default. If the host does not require a username, then leave this field blank.

Enter a password for the host in the [code]Host Password[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this password by default. If the host does not require a password, then leave this field blank.

Select the [code]Add Host [/code]button.

Updating and Deleting Host Settings

Specifying FileMaker Server Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access a FileMaker Server data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access a FileMaker Server host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]fmserveradvanceddatasource[/code]. The [code]-Name[/code] should be specified as the URL of the machine hosting FileMaker Server. The [code]-Port[/code] is optional and defaults to [code]80[/code]. The [code]-Username[/code] and [code]-Password[/code] should be specified. [/p]

[p]For example, the following code shows how a connection to a FileMaker Server data source hosted on the same machine as Lasso might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’fmserveradvanceddatasource’,
-Name=’127.0.0.1’,
-Port=80,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

Enabling FileMaker Server Databases

JDBC Data Sources

[p]Lasso Professional Server communicates with JDBC data sources via JDBC drivers, which are made available for a data source either by the manufacturer or by a third party. In order for a data source to be JDBC-compliant, JDBC connectivity must be featured in the data source, and a JDBC driver must be available. Popular JDBC-compliant data sources include Microsoft SQL Server, Sybase, DB2, and Frontbase, as well as many others. For more information on JDBC connectivity for a particular data source, see the data source documentation or contact the data source manufacturer. [/p]

[p]Lasso Professional Server functions as its own JDBC driver manager. Once JDBC connectivity is installed or is available for a data source, the JDBC driver is loaded in Lasso Professional Server to allow Lasso to connect. Lasso Professional Server uses the JDBC 3.0 industry-standard API, and can connect to any databases using drivers that are certified as JDBC 1.0, JDBC 2.0, or JDBC 3.0 compatible. [/p]

[note][b]JRE [/b][b]Version Note[/b]: Lasso Professional Server can use JDBC drivers that are version 2.0 or higher provided that the JRE installed to the operating system also supports them. Java 1.4.x standard installations or greater on all operating systems instantly support JDBC 2.0. [/note]

Requirements

[p]The following requirements must be fulfilled in order for a data source to communicate with Lasso Professional Server via JDBC: [/p]

Installation of JDBC-compatible data source with any required patches or components for JDBC connectivity to a machine accessible by Lasso Professional Server.
JDBC driver compatible with data source.
Documentation for JDBC driver. This should contain information on what kind of connection URL information is required for Lasso Professional Server to connect via JDBC. This information varies from data source to data source.
Installation of JRE 1.4 on the machine running Lasso Professional Server (Windows only). See the Configuring on Windows chapter for more information.

Setting Up JDBC Data Sources

[p]The following procedure describes setting up a generic JDBC data source for use with Lasso Professional Server. These procedures may vary depending on the data source, and not every data source will require additional component installations to use JDBC. The documentation for the JDBC driver is important in determining what connection information is needed to allow Lasso to connect to the JDBC-compliant data source using that JDBC driver. [/p]

[note][b]Important: [/b]To ease the process of setting up specific JDBC data sources, LassoSoft Support Central articles are available that provide instructions on how to set up specific JDBC data sources to communicate with Lasso Professional Server. These articles are available at [code]http://support.lassosoft.com[/code]. [/note]

To set up a data source to communicate with Lasso Professional Server via JDBC:

Install any patches or components required for JDBC connectivity to the data source. See the data source documentation or contact the data source manufacturer for more information.

Start the data source application or service. The data source must be running to be accessed by Lasso Professional Server.

Lasso Professional Server will require access privileges to the data source for any or all levels of security (database-level, table-level, field-level, etc.). This can be achieved by creating a new user in the data source for use by Lasso, or by allowing Lasso to use an existing user account for data access. This user information will be used in the Setup > Data Sources > Hosts section of Site Administration.

Install all required JDBC drivers for the data source to the [code]JDBCDrivers[/code] folder in the Lasso Professional Server application folder on the machine running Lasso Service. Any support libraries required by the data source can be placed in the [code]JavaLibraries[/code] folder.

Restart Lasso Service. During startup, Lasso Service scans the [code]JDBCDrivers[/code] folder and lists all valid JDBC drivers in the Setup > Data Sources > Connectors page of Site Administration. Lasso Professional Server acts as its own JDBC driver manager, and each JDBC driver represents a data source connector.

Select the desired class from the connector using the [def]Setup > Data Sources > Connectors[/def] section of Site Administration. Lasso will select the most compatible class by default.

Create a connection to a JDBC data source host using the Setup > Data Sources > Hosts section of Site Administration. This will involve entering a connection URL that contains the host name and other information required by the JDBC driver. The JDBC driver documentation should specify how the connection URL should be formatted.

Data Source Hosts

[p]The Setup > Data Sources > Hosts[code] [/code]page in Site Administration displays all data source hosts for a selected JDBC data source connector, and allows the administrator to add new hosts. The [code]Host Listing[/code] panel shows a listing of all the hosts available for the current connector. [/p]

Figure 5: JDBC Hosts Page

[p]Unique [code]Add Host [/code]and [code]Host Detail [/code]panels will be displayed depending on the JDBC data source. These panels allow the administrator to enter additional information that may be required to connect to specific JDBC data sources via specific JDBC drivers. [/p]

[p]Both the [code]Add Host[/code] and [code]Host Detail[/code] panels display the following fields for most JDBC data source hosts: [/p]

Name – This is the name of the host that will be displayed in the [code]Hosts Listing[/code] panel. The properties of this field are the same as for other hosts.
Connection URL – The connection URL contains the host name or IP address and TCP/IP port of the JDBC data source host in addition to any URL prefixes required by the JDBC data source. Instructions for what the connection URL (or connection string) should look like for a particular JDBC data source should be documented in the JDBC driver documentation. A common connection URL format is[code] jdbc:company:product://address:port[/code].
Status – Specifies whether the host is [code]Enabled[/code] or [code]Disabled[/code] in Lasso Professional Server. The properties of this field are the same as for other hosts.
Host Username – The username for the host. The properties of this field are the same as for other hosts.
Host Password – The password for the host. The properties of this field are the same as for other hosts.
Host Schema – The schema for the host, which specifies what tables and fields will be available for the databases in the host. This pull-down menu applies only to JDBC data sources that support named schema ownership (e.g. Frontbase, Sybase). If your data source does not support named schema ownership, then only a value of [code](None)[/code] will be shown.

[note][b]Additional [/b][b]Fields Note[/b]: Additional host property fields may appear depending on the JDBC driver. Consult the JDBC driver documentation for instructions on how to use these additional property fields if they appear. [/note]

[p]A JDBC data source host may be added or updated by entering the information above in the [code]Add Host[/code] or[code] Host Detail[/code] panel, and selecting the [code]Add Host[/code] or [code]Update Host[/code] button, respectively. [/p]

[note][b]Configuration [/b][b]Tip[/b]: See the LassoSoft Support Central Archives at [code]http://support.lassosoft.com[/code] for specific configuration instructions for JDBC data sources that are known to work with Lasso Professional Server. [/note]

Enabling JDBC Databases

MySQL Data Sources

[p]This section provides instructions for setting up a MySQL data source for use with Lasso Professional Server via Lasso Connector for MySQL. A MySQL service running on a host machine is able to communicate with Lasso Professional Server via a TCP/IP port. This can be on the same machine as Lasso Professional Server, or on a separate machine. For more information on MySQL, see the following URL: [/p]

[pre]http://www.mysql.com [/pre]

Requirements

[p]Lasso Professional Server is certified to work with default installations of MySQL 3.23, MySQL 4.x, and MySQL 5.x (release versions). Newer versions or modified versions of MySQL may work with Lasso Professional Server, but are not officially supported. [/p]

[p]For Lasso Professional Server to successfully communicate with a MySQL host, the following requirements must be met. [/p]

The MySQL service must be running.
MySQL must be assigned a port with no conflicts in which to communicate. For standard versions of MySQL, this is port [code]3306[/code] by default.
Access privileges must be assigned in MySQL to the machine running Lasso Professional Server for any or all levels (database-level, table-level, column-level, etc.) of security.

[note][b]Note: [/b]Links to third party distributions and tools are provided for your convenience and were accurate when this manual was written. LassoSoft cannot be responsible for the availability or suitability of software downloaded from third party Web sites. [/note]

Configuring MySQL Hosts For Use With Lasso

[p]MySQL is operated via a command-line interface application, which is normally located in the [code]bin[/code] directory of the MySQL installation on the client machine. For information on how to use this, consult the MySQL documentation. [/p]

[p]Security for MySQL data sources can be set at any level (MySQL-level, database-level, table-level, etc.). All permissions for all levels of security need to be given to Lasso Professional Server for unrestricted operation. This involves setting a new user and password for Lasso Professional Server in MySQL, then entering the username and password in Site Administration. Follow the procedure below for granting all permissions to Lasso Professional Server in MySQL using the MySQL command line utility. [/p]

To grant all permissions to Lasso Professional Server in MySQL:

In the command line utility, log in to MySQL as the root user by entering the following command:

[pre]mysql -u root -p your_password; [/pre]

Replace [code]your_password[/code] with the root password for MySQL specified during the MySQL installation. For instructions on how to set or change the MySQL root password, see the MySQL documentation.

Enter the following to assign a username and password for Lasso Professional Server to gain access to all levels of security in MySQL.

[pre]GRANT ALL ON *.* TO Username@Hostname IDENTIFIED BY "Password"; [/pre]

Replace[code] Username[/code] and [code]Password[/code] with the username and password values you wish to assign to Lasso Professional Server. These will be used in the Setup > Data Sources > Databases section in Site Administration, as described in the Setting Up Data Sources chapter. Replace [code]Hostname[/code] with the host name or IP address of the machine on which Lasso Professional Server is installed. This will restrict the access of this username and password to the machine running Lasso Professional Server for added security. [code]ALL[/code] represents all MySQL action permissions (e.g. [code]INSERT[/code], [code]SELECT[/code], [code]DELETE[/code], etc.), and the *.* characters represent all levels of security in MySQL (e.g. database-level, table-level, etc.).
Lasso Professional Server now has all appropriate permissions to communicate with MySQL, and the MySQL host may now be configured in the [code]Setup [/code]section of Site Administration. See the Setting Up Data Sources chapter for more information.

[note][b]Note: [/b]It is possible to assign limited privileges to Lasso Professional Server one at a time by replacing [code]ALL[/code] with an individual permission (e.g. [code]INSERT[/code], [code]SELECT[/code], [code]DELETE[/code]), and replacing [code]*.*[/code] with a specific database or table name. This will restrict the functionality of Lasso Professional Server to the privileges that are assigned to it. For example, giving Lasso Professional Server only the [code]SELECT[/code] privilege will allow a MySQL database to be searched using Lasso, but records cannot be added, updated, or deleted using Lasso. [/note]

Creating Connections to MySQL Hosts

Figure 6: MySQL Hosts Page

Adding MySQL Hosts

[p]Selecting the [code]Add Host[/code] button shows the [code]Add Host [/code]panel to the right. The [code]Add Host [/code]page allows the administrator to add a new MySQL data source host to a selected data source connector. [/p]

To add a new MySQL host:

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host in the [code]IP Address/Host Name [/code]field. This is the address of the machine running the data source application.

Enter the TCP/IP port of the data source application or service of the host in the [code]Port[/code] field. This is commonly [code]3306[/code] for MySQL data sources.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username for the host in the [code]Host Username[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this username.

Enter a password for the host in the[code] Host Password[/code] field. Lasso Professional Server will connect to the data source and all databases therein using this password.

Select the [code]Add Host [/code]button.

MySQL 4.1/5.x Character Sets

[p]The option to use MySQL 4.1/5.x character sets allows Lasso to insert data into MySQL tables using the native encoding specified when the table is constructed. This option should only be used on MySQL 4.1/5.x data sources. [/p]

[p]When this option is on the encoding for every table in the MySQL host should be set to UTF-8 for highest fidelity. Lasso will transmit UTF-8 to the remote host and then instruct MySQL to convert that data to the native encoding appropriate for the destination table. [/p]

[note][b]Note: [/b]Versions of Lasso prior to 8.1 always connected to MySQL using an [code]ISO-8859-1[/code] character set. Data stored in this way can be retrieved by Lasso, but cannot be retrieved by other MySQL clients without performing a character set translation. The [code]Use MySQL 4.1/5.x Character Sets[/code] option should be left off until the data within the existing tables can be fixed. [/note]

Updating and Deleting Host Settings

Specifying MySQL Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access a MySQL data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access a MySQL host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]mysqlds[/code]. The [code]-Name[/code] should be specified as the URL of the machine hosting MySQL. The [code]-Port[/code] is optional and defaults to [code]3306[/code]. The [code]-Username[/code] and [code]-Password[/code] should be specified. [/p]

[p]For example, the following code shows how a connection to a MySQL data source hosted on the same machine as Lasso might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’mysqlds’,
-Name=’127.0.0.1’,
-Port=3306,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

Enabling MySQL Databases

ODBC Data Sources

[p]This section provides instructions for setting up data source through ODBC for use with Lasso Professional Server. An ODBC driver set up as a system data source (System DSN) is required in order to access the data source through Lasso. The data source can be on the same machine as Lasso Professional Server or on a separate machine. For more information on ODBC, see the documentation included with your operating system: [/p]

Requirements

[p]Lasso’s ODBC data source requires a compatible ODBC driver which has been configured as a System DSN in the ODBC control panel. [/p]

Windows – ODBC data sources are configured using the ODBC Data Source Administrator which is normally accessed through Settings > Control Panels > Administrative Tools > Data Sources (ODBC). Lasso can access data sources configured as System DSNs.
Mac OS X – ODBC data sources are configured using the ODBC Administrator which is can be found in the Applications/Utilities folder. Lasso can access data sources configured as System DSNs.
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 which allows configuration of those drivers. Lasso can access data sources configured as System DSNs.

Configuring ODBC Hosts For Use With Lasso

[p]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: [/p]

Install your ODBC driver using the provided installer or instructions.

Create a System DSN in the ODBC administration application (see above). Note that the System DSN name, username, and password which are configured here will need to be entered in Lasso.

Locate and configure the [code]SQL.ini[/code] file for your driver. 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.

Follow the steps below to add the data source to Lasso.

Adding ODBC Hosts to Lasso

Figure 7: ODBC Hosts Page

[p]The [code]Host Listing[/code] panel shows a listing of all the hosts available for the current connector. Selecting[code] Enable All [/code]will enable all hosts for the selected connector, while selecting [code]Disable All[/code] will disable all data source hosts for the selected connector. Selecting the name of a host shows its details in the [code]Host Detail [/code]panel, which appears to the right. In the [code]Host Detail[/code] panel, the administrator can update or delete the selected host by selecting the [code]Update[/code] or [code]Delete[/code] button. [/p]

To add a new ODBC host:

Select the [code]Add Host[/code] button to show the [code]Add Host [/code]panel to the right.

In the [code]Add Host[/code] panel, enter the name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired. Often this will be the same as the System DSN.

Enter the System DNS of the in the the [code]Data Source Name [/code]field. For example, a connection to the System DSN [code]ExampleDatabase[/code] might appear as follows..

[pre][code]ExampleDatabase[/code] [/pre]

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username and password for the host in the [code]Host Username[/code] and [code]Password[/code] fields. Lasso Professional Server will connect to the data source and all databases therein using this username and password.

Select the [code]Add Host [/code]button.

Specifying ODBC Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access an ODBC data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access an ODBC host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]odbc[/code]. The [code]-Name[/code] should be specified as the System DSN, e.g. exampledatabase. The [code]-Username[/code] and [code]-Password[/code] should be specified if required by the ODBC driver. [/p]

[p]For example, the following code shows how a connection to the same ODBC data source which was configured above might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’odbc’,
-Name=’exampledatabase’,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

OpenBase Data Sources

[p]This section provides instructions for setting up an OpenBase data source for use with Lasso Professional Server. An OpenBase service running on a host machine is able to communicate with Lasso Professional Server via a TCP/IP port. The data source can be on the same machine as Lasso Professional Server or on a separate machine. For more information on OpenBase, see the following URL: [/p]

[pre]http://www.openbase.com/ [/pre]

Requirements

[p]Lasso Professional Server has been tested with the following versions of this data source. [/p]

OpenBase SQL 9.1

Configuring OpenBase Hosts For Use With Lasso

[p]The OpenBase database server must be configured with a user which has access to all of the databases, tables, and other resources which will be published through Lasso. Consult the OpenBase documentation for help configuring OpenBase’s built-in security. [/p]

Adding OpenBase Hosts to Lasso

Figure 8: OpenBase Hosts Page

To add a new OpenBase host:

Select the [code]Add Host[/code] button to show the [code]Add Host [/code]panel to the right.

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host and the database name in the [code]Connection URL [/code]field. For example, a connection to the machine [code]openbase.example.com[/code] to access [code]mydatabase[/code] might appear as follows..

[pre]openbase.exampe.com/mydatabase [/pre]

The [code]Additional Properties[/code] field can be left blank.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username and password for the host in the [code]Host Username[/code] and [code]Password[/code] fields. Lasso Professional Server will connect to the data source and all databases therein using this username and password.

Select the [code]Add Host [/code]button.

Specifying OpenBase Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access an OpenBase data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access an OpenBase host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]openbaseds[/code]. The [code]-Name[/code] should be specified the same as the [code]Connection URL[/code] described above, e.g. openbase.exampe.com/mydatabase. The [code]-Username[/code] and [code]-Password[/code] must be specified. [/p]

[p]For example, the following code shows how a connection to the same OpenBase database server which was configured above might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’openbaseds’,
-Name=’openbase.example.com/mydatabase’,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

Oracle Data Sources

[p]This section provides instructions for setting up an Oracle data source for use with Lasso Professional Server. An Oracle service running on a host machine is able to communicate with Lasso Professional Server via a TCP/IP port. The data source can be on the same machine as Lasso Professional Server or on a separate machine. For more information on Oracle, see the following URL: [/p]

[pre]http://www.oracle.com/ [/pre]

Requirements

[p]Lasso Professional Server has been tested with the following versions of this data source. [/p]

Oracle Database 10g

[note][b]Note: [/b]A universal build of the Oracle client library for Intel-based Macintosh installations was not available when Lasso 8.5 was released. The Oracle data source will not function on these machines until Oracle releases a universal version of the instant client. [/note]

[p]Lasso’s Oracle data source requires the Oracle “Instant Client” to be installed in order to access Oracle hosted on another machine. If Lasso is hosted on the same machine as Lasso then this is not necessary. The instant client can be downloaded from the following Web site. Make sure to download just the basic instant client files rather than the complete Oracle 10g client or database installation. [/p]

[pre]http://download.oracle.com/ [/pre]

Windows – Decompress the “Instant Client” download and copy all the [code].dll[/code] files within directly into the Lasso Professional 8 application folder. Restart Lasso Service in order to access the Oracle data source.
Mac OS X – Decompress the instant client. Download the following installer and copy the included [code].pkg[/code] file into the “Instant Client” folder (i.e. [code]instantclient10_1[/code]). Run the installer by double clicking on the [code].pkg[/code] file. This installer copies the libraries provided by Oracle into their proper locations within the OS.

[pre]http://www.kyngchaos.com/files/software/unixport/OCIinstall.zip [/pre]

After running the installer open a Terminal window and copy and paste the following commands. The [code]sudo[/code] command will require that you enter your password in order to continue. This command creates links so that Lasso can find the Oracle libraries at the location where the installer placed them.

[pre]sudo ln -sf /lusr/local/oracle/lib/* /usr/local/lib/ [/pre]

Linux – Decompress the instant client and follow the instructions on the Web site to place the files within the library search path for Lasso.

Configuring Oracle Hosts For Use With Lasso

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

[pre]http://www.oracle.com/technology/getting-started/index.html [/pre]

Adding Oracle Hosts to Lasso

[note][b]Important: [/b]If the client libraries for Oracle have not been installed properly then a warning message will appear on this page. Consult the Requirements section above for instructions to install the Oracle client libraries which Lasso requires. [/note]

Figure 9: Oracle Hosts Page

To add a new Oracle host:

Select the [code]Add Host[/code] button to show the [code]Add Host [/code]panel to the right.

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter two slashes, the IP address or domain name of the host, the port (defaults to 1521), and the name of the database which is to be accessed in the [code]Connection URL [/code]field. For example, a connection to the machine [code]oracle.example.com[/code] on the standard port to access a database named [code]mydatabase[/code] might appear as follows..

[pre]//oracle.example.com:1521/mydatabase [/pre]

The [code]Additional Properties[/code] field can be left blank.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username and password for the host in the [code]Host Username[/code] and [code]Password[/code] fields. Lasso Professional Server will connect to the data source and all databases therein using this username and password.

Select the [code]Add Host [/code]button.

Specifying Oracle Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access an Oracle data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access an Oracle host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]oracle[/code]. The [code]-Name[/code] should be specified the same as the [code]Connection URL[/code] described above, e.g. oracle.example.com:1521/mydatabase. The [code]-Username[/code] and [code]-Password[/code] must be specified. [/p]

[p]For example, the following code shows how a connection to the same Oracle database server which was configured above might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’oracle’,
-Name=’//oracle.example.com:1521/mydatabase’,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

PostgreSQL Data Sources

[p]This section provides instructions for setting up a PostgreSQL data source for use with Lasso Professional Server. A PostgreSQL service running on a host machine is able to communicate with Lasso Professional Server via a TCP/IP port. The data source can be on the same machine as Lasso Professional Server or on a separate machine. For more information on PostgreSQL, see the following URL: [/p]

[pre]http://www.postgresql.org/ [/pre]

Requirements

[p]Lasso Professional Server has been tested with the following versions of this data source. [/p]

PostgreSQL 8.x

[p]On any platform it is necessary to install PostgreSQL on the Lasso machine, even in order to access a copy of PostgreSQL hosted on another machine. Distributions of PostgreSQL for Windows and Linux can be found at the PostgreSQL Web site. [/p]

[pre]http://www.postgresql.org/ [/pre]

Windows – this URL to download the latest version of the ODBC driver for PostgreSQL. This download includes binary versions of the libraries which Lasso requires in order to access PostgreSQL.

[pre]http://www.postgresql.org/ftp/odbc/versions/dll/ [/pre]

Decompress the zip archive and copy all of the included [code].dll[/code] files into the Lasso Professional 8 application folder and restart Lasso Service.
Mac OS X – Mac OS X binaries can be found at one of the following Web sites. The latter includes a universal build of PostgreSQL for Intel-based Macintosh computers.

[pre]http://www.entropy.ch/software/macosx/postgresql/ [/pre]

[pre]http://www.kyngchaos.com/software/unixport/ [/pre]

After running the installer open a Terminal window and copy and paste the following command. The [code]sudo[/code] command will require that you enter your password in order to continue. This command creates links so that Lasso can find the PostgreSQL libraries at the location where the installer placed them.

[pre]sudo ln -s /usr/local/pgsql/lib/libpq.dylib . [/pre]

Linux – Download the PostgreSQL database and build the client libraries. Follow the instructions on the Web site to place the files within the library search path for Lasso.

Configuring PostgreSQL Hosts For Use With Lasso

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

[pre]http://www.postgresql.org/docs/manuals/ [/pre]

Adding PostgreSQL Hosts to Lasso

[note][b]Important: [/b]If the client libraries for PostgreSQL have not been installed properly then a warning message will appear on this page. Consult the Requirements section above for instructions to install the PostgreSQL client libraries which Lasso requires. [/note]

Figure 10: PostgreSQL Hosts Page

To add a new PostgreSQL host:

Select the [code]Add Host[/code] button to show the [code]Add Host [/code]panel to the right.

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host. For example, a connection to the machine [code]postgresql.example.com[/code] might appear as follows..

[pre]postgresql.example.com [/pre]

The [code]Additional Properties[/code] field can be left blank.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username and password for the host in the [code]Host Username[/code] and [code]Password[/code] fields. Lasso Professional Server will connect to the data source and all databases therein using this username and password.

Select the [code]Add Host [/code]button.

Specifying PostgreSQL Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access an PostgreSQL data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access a PostgreSQL host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]postgresql[/code]. The [code]-Name[/code] should be specified the same as the [code]Connection URL[/code] described above, e.g. postgresql.example.com. The [code]-Username[/code] and [code]-Password[/code] must be specified. [/p]

[p]For example, the following code shows how a connection to the same PostgreSQL database server which was configured above might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’postgresql’,
-Name=’postgresql.example.com’,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

Spotlight Data Source

[p]The Spotlight data source is used to search the file system on machines with Mac OS X 10.4/10.5. This data source is not supported on earlier versions of Mac OS X or on Windows or Linux. [/p]

[p]The Spotlight data source has one pre-defined host and several databases which represent different metadata sets that can be searched. The Lasso Site Administrator must enable the metadata sets which they want to make available for searching. [/p]

[p]Consult the Other Data Sources chapter of the Lasso Language Guide for full details about how to perform searches using the Spotlight data source. [/p]

[p]Apple’s developer information page about Spotlight provides a conceptual background to how Spotlight works, what types of metadata are indexed, and how to run Spotlight queries from the command line. [/p]

[pre]http://developer.apple.com/macosx/spotlight.html [/pre]

Requirements

[p]The Spotlight data source requires Mac OS X 10.4/10.5. The Spotlight data source can only be used to search for files and folders which have been indexed by the Spotlight indexer. [/p]

[p]Sites will only be able to return Spotlight search results for files contained within their Web server root unless the Server Administrator has granted the site permission to access files outside the root and appropriate file permissions have been granted to the user who is performing the search. [/p]

Enabling Spotlight Metadata Sets (Databases)

[p]Lasso defines four Spotlight metadata sets as databases within Lasso Site Administration. A set will also be defined for each volume which is mounted on the machine hosting Lasso Service. Each set must be enabled and appropriate permissions assigned to it before they can be searched by the site user. Some of the sets also require file permissions to be configured. Each metadata set can be enabled by visiting the Site > Data Sources > Databases section of Lasso Site Administration. [/p]

Spotlight_Site – This set corresponds to the Web site root. Once this “database” is enabled it can be searched by any Lasso use who has permission to access it. No additional file permissions are required.
Spotlight_User – This set corresponds to the Lasso Professional 8 application folder. The site must be assigned permission to access files outside of the Web server root and the Lasso user must have permission to access those files in order to use this set.
Spotlight_Computer – This set corresponds to the root of the machine hosting Lasso Service. The site must be assigned permission to access files outside of the Web server root and the Lasso user must have permission to access those files in order to use this set. Note that the search results may contain files which the Lasso user is not capable of manipulating through the file tags.
Spotlight_Network – This set corresponds to the local area network of the machine hosting Lasso Service. The site must be assigned permission to access files outside of the Web server root and the Lasso user must have permission to access those files in order to use this set.
/Volumes/MacintoshHD – Each local hard disk, mounted CD or DVD, mounted disk image, and file server will appear as a separate “database” with the name [code]/Volumes/[/code] followed by the volume name.

SQL Server Data Sources

[p]This section provides instructions for setting up a Microsoft SQL Server data source for use with Lasso Professional Server. A Microsoft SQL Server service running on a Windows host machine is able to communicate with Lasso Professional Server via a TCP/IP port. The data source can be on the same machine as Lasso Professional Server or on a separate machine. For more information on Microsoft SQL Server, see the following URL: [/p]

[pre]http://www.microsoft.com/sql/ [/pre]

Requirements

[p]Lasso Professional Server has been tested with the following versions of this data source. [/p]

Microsoft SQL Server 2005

[p]On Mac OS X and Linux it is necessary to install a set of client libraries in order to access Microsoft SQL Server. [/p]

Windows – Windows machines should already have the necessary client libraries for SQL Server installed.
Mac OS X – Install the FreeTDS libraries in order to access Microsoft SQL Server. The data source will show up as unavailable unless these libraries are installed. The Mac OS X libraries can be found at HMUG. Follow the instructions on the Web page to install the libraries. The Mac OS X developer tools are required and can be found on the Mac OS X install DVD or downloaded direct from Apple.

[pre]http://www.hmug.org/pub/MacOS_X/BSD/Libraries/System/freetds/ [/pre]

Linux – Install the FreeTDS libraries in order to access Microsoft SQL Server. The data source will show up as unavailable unless these libraries are installed. The Linux libraries are available directly from FreeTDS.

[pre]http://www.freetds.org/ [/pre]

Configuring SQL Server Hosts For Use With Lasso

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

[pre]http://www.microsoft.com/sql/ [/pre]

Adding SQL Server Hosts to Lasso

[note][b]Important: [/b]If the client libraries for SQL Server have not been installed properly then a warning message will appear on this page. Consult the Requirements section above for instructions to install the SQL Server client libraries which Lasso requires. [/note]

Figure 11: SQL Server Hosts Page

To add a new SQL Server host:

Select the [code]Add Host[/code] button to show the [code]Add Host [/code]panel to the right.

In the [code]Add Host[/code] panel, enter a name for the host in the [code]Name[/code] field. This will be displayed in the [code]Hosts Listing[/code] panel for the host, and can be as descriptive as desired.

Enter the IP address or domain name of the host. For example, a connection to the machine [code]sqlserver.example.com[/code] for the database [code]mydatabase[/code] might appear as follows..

[pre]sqlserver.example.commydatabase [/pre]

The [code]Additional Properties[/code] field can be left blank.

Select [code]Enabled[/code] from the [code]Status[/code] pull-down menu to enable the host.

Enter a username and password for the host in the [code]Host Username[/code] and [code]Password[/code] fields. Lasso Professional Server will connect to the data source and all databases therein using this username and password.

Select the [code]Add Host [/code]button.

Specifying SQL Server Hosts in Inlines

[p]Setting up a data source host and properly configuring Lasso security is the best way to ensure that access to the data source is fully 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 which access an PostgreSQL data source host. See the Lasso Language Guide for full details about inline host specification. [/p]

To access a SQL Server host directly in an inline:

[p]The [code]-Host[/code] parameter can be used to specify all of the connection parameters for a data source host. The [code]-Host[/code] array should contain the following elements. The [code]-Datasource[/code] should be specified as [code]sqlserver[/code]. The [code]-Name[/code] should be specified the same as the [code]Connection URL[/code] described above, e.g. sqlserver.example.commydatabase. The [code]-Username[/code] and [code]-Password[/code] must be specified. [/p]

[p]For example, the following code shows how a connection to the same SQL Server database server which was configured above might appear. [/p]

[pre][Inline: -FindAll,
-Host=(Array:
-Datasource=’sqlserver’,
-Name=’sqlserver.example.commydatabase’,
-Username=’lassouser’,
-Password=’mysecretpassword’),
-Database=’mydatabase’,
-Table=’example’]
[Found_Count]
[/inline] [/pre]

SQLite Data Sources

[p]Lasso Professional Server comes with an embedded high-performance data source called SQLite. This data source is used to store Lasso’s internal site preferences and security settings. [/p]

[p]SQLite is installed, enabled, and pre-configured within Lasso Professional Server by default. No further set up or installation of SQLite is required. [/p]

Using SQLite Databases

[p]SQLite databases are stored in the [code]SQLiteDBs[/code] folder within each site’s folder. By default this folder contains databases that are required for Lasso Professional Server to function, as documented in the Using Site Administration chapter. [/p]

[p]Custom databases may be created and added to this folder three different ways: [/p]

Creating custom SQLite databases using [code]DatabaseBrowser.lassoapp[/code], which is described in the Building and Browsing Databases chapter.
Issuing SQL statements via the Utility > SQL section of Site Administration. For more information on issuing SQL statements, see the Site Administration Utilities chapter.
Creating SQlite databases using a third-party tool and moving them into the [code]SQLiteDBs[/code] folder.

Enabling Custom SQLite Databases

[p]Once custom SQLite databases are created or moved to the [code]SQLiteDBs[/code] folder, they can be used in Lasso code or tested using the [code]Browse[/code] section of the [code]DatabaseBrowser.LassoApp[/code] tool. [/p]

[p]For information on setting up required Lasso data source permissions for users, see the Setting Up Security chapter. For information on setting database, table, and field preferences, see the remaining sections in this chapter. [/p]

Other Data Sources

[p]Third-party data sources can be installed in Lasso Professional Server and accessed using the same tools for the built-in data sources and JDBC data sources. Third-party data sources are installed differently depending on their type. Consult the documentation for the data source connector for full details. [/p]

LCAPI data source modules are installed in [code]LassoModules[/code]. They usually have an extension of [code].dylib[/code], [code].dll[/code], or [code].so[/code].
LJAPI data source modules are installed in [code]LassoModules[/code]. They usually have an extension of [code].jar[/code] or [code].class[/code]. Any Java libraries required by the module can be installed in [code]JavaLibraries[/code].
Custom data source modules are installed in [code]LassoStartup[/code]. They usually have an extension of [code].Lasso[/code] or [code].LassoApp[/code].

[p]After a third-party data source is installed Lasso Service must be restarted. The data source will then show up in the [def]Setup > Data Sources > Connectors[/def] section of Site Administration. Consult the documentation for the data source connector for details about how it should be configured. [/p]

Database Preferences

[p]The [code]Databases[/code] page lists all databases within a selected data source host. From here, the administrator can enable or disable selected databases, update database settings details, or list tables within a database. [/p]

Figure 12: Databases Preferences Page

[p]The [code]Databases Listing[/code] panel shows a listing of all the databases available for the current host. The administrator can switch hosts by selecting a new host from the [code]Hosts[/code] pull-down menu. [/p]

Enabling Databases

[p]Each database must be enabled before it can be used with Lasso. Databases can be enabled in three ways. [/p]

Select a connector and host from the pop-up menus at the top of the Database Listing panel. Select the Enable button next to each database which you want to access through Lasso.
Selecting [code]Enable All [/code]will enable all databases shown for the selected host, while selecting [code]Disable All[/code] will disable all databases for the selected host. Selecting [code]Enable All New[/code] will enable all [code]New[/code] databases for the selected host (see the following section for a description of all database status types). Selecting the [code]Refresh[/code] button updates the [code]Database Lising[/code] panel with the current list of available databases.
Selecting each database name will show the Database Detail panel. See the next section for details about how to modify individual database preferences.

[note][b]Note: [/b]Always select the [code]Refresh[/code] button in the [code]Databases Listing[/code] panel to view all databases that have been newly opened or loaded in a data source host. [/note]

Updating Database Preferences

[p]Selecting a database in the [code]Database Listing[/code] panel will show its details in the [code]Database Detail[/code] panel, which appears to the right. The following settings can be updated: [/p]

[def]Alias[/def] – Allows an alternate name to be set for a selected database without changing the actual file name. This is the database name that should be used in Lasso pages, and is the only name that will be associated with that database by Lasso. The alias is the same as the file name by default.
[def]Status[/def] – Specifies whether or not a database is accessible by Lasso Professional Server. An [code]Enabled[/code] or [code]Disabled[/code] status designates that the database has been actively enabled or disabled in Site Administration. Databases are [code]New[/code] if the database is open or accessible by the database application at the specified host, but have not yet been enabled or disabled in Site Administration. Databases are [code]Not Available[/code] if they exist in Site Administration but are no longer available to the current host. Calling from within a solution any database that has a [code]New[/code], [code]Disabled[/code], or [code]Not Available[/code] status will produce an error.
[def]Database Username[/def] – (FileMaker Only) The username required for Lasso to connect to the database. This username will only be used if [code]Always Connect with Host Username/Password[/code] is set to [code]No[/code].
[def]Database Password[/def] – (FileMaker Only) The password required for Lasso to connect to the database. This username will only be used if [code]Always Connect with Host Username/Password[/code] is set to [code]No[/code].
[def]Always Connect with Host Username/Password[/def] – (FileMaker Only) Specifies whether or not the host-level username and password will be used when connecting to the database. Selecting [code]Yes[/code] uses the username and password in the [code]Hosts[/code] page. Selecting [code]No[/code] uses the username and password set above.
[def]SQL Stop Words (SQL Databases Only)[/def] – Allows the administrator to establish a list of words that should not be allowed in SQL statements. For example if [code]Delete[/code] is listed as a SQL stop word then no SQL statement containing [code]Delete[/code] will be executed on the specified database. Instead, a Lasso Security error will result.

Updating Database Preferences

[p]The administrator can update the database status by entering or selecting an appropriate value for each field, and then selecting the [code]Update[/code] button. [/p]

Deleting Database Preferences

[p]Selecting the [code]Delete Settings[/code] button in the [code]Database Detail[/code] panel will remove the database settings from Site Administration, including all permissions, aliases, and options granted to the database, and its corresponding tables and fields. [/p]

[note][b]Database [/b][b]Schema Note[/b]: If a database’s schema is significantly altered, the recommended method for updating Site Administration is to delete the database settings in the [code]Database Detail[/code] panel, and then refresh the [code]Databases Listing[/code] panel. Then, all options for the new database schema may be set again in Site Administration. [/note]

Current Permissions

[p]The [code]Group Permissions Listing[/code] shows which groups have permission to perform actions in the selected table, and which actions are allowed or denied. These can be changed within the[code] Groups[/code] section, which is discussed later in this chapter. [/p]

Table Batch Change

[p]The table batch change option allows the encoding of every table on the current database to be changed at once. Select a new encoding from the pop-up menu or select [code]Other…[/code] and type a custom encoding choice. After clicking perform every table on the current database will be set to the specified encoding. [/p]

[note][b]Note: [/b]New tables which are added to the database will not be set to the selected encoding. It may be necessary to adjust the encoding of each new table after they are added or to use the batch change option after new tables are added. [/note]

Aliases

[p]Aliases allow databases and tables to be given unique names within Site Administration that are separate from the actual database and table names for each data source. This allows multiple databases with the same names from different hosts to be accessed by Lasso. [/p]

[p]The schema of databases can be hidden using aliases, hiding the names of the actual files that contain the database data. On a shared Web server, each database could be given a name specific to the client who has access to the database. [/p]

[p]Web site portability can be enhanced using aliases. For example, a site which is being transitional from a FileMaker database [code]Contacts.fp3 [/code]to a SQLite database [code]Contacts [/code]could use an alias on [code]Contacts [/code]so it still appears under the name [code]Contacts.fp3[/code]. The alias allows the back-end database to be changed without modifying all of the Lasso pages that reference a given database name. [/p]

[p]Once a database or table alias is established, the aliased name must always be used for access to the database through custom Lasso pages. Using a combination of the real name and aliased name is not possible. [/p]

[p]If a new host is added to Site Administration and some of the databases have the same names as databases already configured within Site Administration, then the new databases will have aliases created automatically which add an asterisk to their name so it is unique. For example, if a host is added with a [code]Contacts [/code]database then it will be added with an alias as [code]Contacts* [/code]since its name conflicts with the internal [code]Contacts [/code]database. [/p]

[note][b]Note: [/b]Aliases are created with an incremental number of asterisks depending on the number of databases with the same name that exist in Site Administration. [/note]

Table Preferences

[p]The [code]Tables [/code]page lists all tables within a selected database. From here, the administrator can enable or disable selected tables, update table details, or list fields within a table. [/p]

Figure 13: Table Preferences Page

[p]The [code]Tables Listing [/code]panel shows a listing of all the tables available for the current database. The current connector, host, and database are displayed by default. The administrator can switch databases by selecting a new database from the [code]Database[/code] pull-down menu. [/p]

[p]Selecting the name of a table shows its details in the [code]Table Detail[/code] panel, which appears to the right. Selecting [code]Enable All [/code]will enable all tables shown for the selected database, while selecting [code]Disable All[/code] will disable all tables shown for the selected database. Selecting [code]Enable All New [/code]will enable all tables for the selected database with the status [code]New[/code]. Selecting the[code] Refresh[/code] button updates the [code]Tables [/code]page with the most current information. [/p]

Updating Table Status

[p]Selecting a database in the [code]Tables Listing[/code] panel will show its details in the [code]Tables Detail[/code] panel, which appears to the right. Here, the administrator can update the selected table by modifying its details in the [code]Tables Detail[/code] panel, and then selecting the [code]Update[/code] button. The following table details can be updated: [/p]

[def]Alias [/def]–[def] [/def]Allows an alternate name to be set for a selected table without changing the actual file name. This is the table name that should be used in Lasso pages, and is the only name that will be associated with that table by Lasso. The alias is the same as the file name by default.
[def]Status [/def]–[def] [/def]Specifies whether or not a table is enabled for use with Lasso Professional Server. Calling a disabled table from within a solution will produce an error.
[def]Encoding [/def]–[def] [/def]Specifies the character encoding that will be used for all data stored in each field in the table. This can be [code]ISO8859-1[/code] (default), or [code]UTF-8[/code] (Unicode).

Updating Table Preferences

[p]The administrator can update the table status by entering or selecting an appropriate value for each field and selecting the [code]Update[/code] button. [/p]

Deleting Table Preferences

[p]Selecting the [code]Delete Settings[/code] button will remove the table settings from Site Administration, including all permissions, aliases, and options granted to the table, and its corresponding fields. [/p]

[p]Current Permissions [/p]

[p]The [code]Group Permissions Listing[/code] shows which groups have permission to perform actions in this table, and which actions are allowed or denied. These can be changed within the [code]Groups[/code] section, which is discussed later in this chapter. [/p]

Field Preferences

[p]The [code]Fields [/code]page lists all fields within a selected table. From here, the administrator can enable or disable selected fields, update field details, or list fields within a table. [/p]

Figure 14: Field Preferences Page

[p]The [code]Fields Listing[/code] panel shows a listing of all the fields available for the current table. The current connector, host, database, and table are displayed by default. One can switch tables by selecting a new table from the [code]Table [/code]pull-down menu. Selecting [code]Enable All[/code] will enable all fields for the selected table, while selecting [code]Disable All[/code] will disable all fields for the selected table. [/p]

Updating Field Status

[p]Selecting a field in the[code] Tables Listing[/code] panel will show its details in the [code]Field Detail [/code]panel, which appears to the right. The administrator can update the field status by entering or selecting an appropriate value for each field and selecting the [code]Update[/code] button. [/p]

[p]The following field preferences can be set: [/p]

[def]Status[/def] – Specifies whether or not a field is accessible by Lasso Professional Server. Calling a disabled field from within a solution will produce an error.
[def]Allow Searching on this Field[/def] – Specifies whether or not a field can be searched or viewed by users. Fields can be restricted from being searched by setting this preference to [code]No[/code]. The default is [code]Yes [/code]so that all fields which are [code]Enabled [/code]can be used within searches. This preference can be useful to restrict searches to only indexed fields.
[def]Allow this Field to be Modified[/def] – Specifies whether or not a field can be updated by users. Fields can be made read-only by setting this preference to [code]No[/code]. The default is [code]Yes [/code]so that all fields can be modified provided appropriate permissions are granted for the database and table.
[def]Require an Exact Match on this Field[/def] – Specifies whether or not an exact value is required when searching, deleting, or updating records. Exact matches are case-sensitive. The equals operator must be used with that field when it is present in a search action. In addition, if any field is defined as exact match, then the [code]-FindAll [/code]and [code]-Random [/code]actions cannot be used with that database. See the section on Record-Level Security below for more information.
[def]Filter Search Values for this Field[/def] – Allows the administrator to insert Lasso code that is executed every time a -[code]Search[/code] action is performed on the selected field. The code can alter the search request or return a custom error message if the search request does not meet the specified criteria. For more information on filters, see the Field Filters section below.
•[code] [/code][def]Filter Add Values for this Field[/def] – Allows the administrator to insert Lasso code that is executed every time a -[code]Add[/code] action is performed on the selected field. For more information on filters, see the Field Filters section below.
•[def] [/def][def]Filter Update Values for this Field[/def] – Allows the administrator to insert Lasso code that is executed every time an -[code]Update[/code] action is performed on the selected field. For more information on filters, see the Field Filters section below.

Field Filters

[p]Field filters allow Lasso code to be performed when specified Lasso actions are performed. This can be useful for preventing certain types of data from being submitted to specified fields. For example, you can set up profanity filters, or filters to prevent operations which may adversely affect the performance or integrity of your databases (e.g. lengthy search or sort requests). [/p]

[p]Field filters can be attached to a specific field for the search, add, or update actions. When the specified action is performed, the code within the field filter will be executed, modifying the value of the data being submitted for the field, or returning a security error. [/p]

[p]The code within each field filter can access the value for the field in the variable [code]Filter_Field[/code]. The value for this variable at the end of the Lasso code will be the value that is submitted to the data source connector for the action. What follows are examples of various useful field filters. [/p]

To use an included file as a filter:

[p]Specify an [code][Include][/code] tag within any type of field filter. The code in the included file will be processed for each field that references the file. This allows the same code to be used for many different field filters. The following tag would include a file named [code]Add_Filter.lasso [/code]in the [code]Filters [/code]folder at the root of the Web serving folder. [/p]

[pre][Include: '/Filters/Add_Filter.lasso'] [/pre]

To use a custom tag as a filter:

[p]Define a custom tag within a Lasso page in [code]LassoStartup[/code]. The custom tag can be called within a filter the same as any Lasso tags. The following example calls a custom tag[code] [MySearchFilter[/code]] with the [code]Filter_Field [/code]variable as a parameter. It then stores the value of the custom tag back into the [code]Filter_Field [/code]variable. [/p]

[pre][Variable: 'Filter_Field' = (MySearchFilter: 'Filter_Field')] [/pre]

To ensure a value is provided when adding a record:

[p]Insert the following code into an add filter in order to ensure that a value is always entered for a specific field. If this code were entered for the field [code]Full_Name, [/code]then the value [code]John Doe [/code]would be inserted whenever the field was left blank in the [code][Inline][/code] that performed the add. [/p]

[pre][If: (Variable: '[code]Filter_Field'[/code]) == '']
[Variable: '[code]Filter_Field'[/code]= 'John Doe']
[/If] [/pre]

To ensure a minimum length for search parameters:

[p]Insert the following code into a search filter in order to ensure that the value being searched for is alphanumeric, and contains at least three characters. If the search parameter does not meet these criteria, then a field restriction error is reported and the search is not performed. [/p]

[pre][If: (Variable: '[code]Filter_Field'[/code])->Length >= 3]
[Loop: (Variable: '[code]Filter_Field'[/code])->Length]
[If: !(String_IsAlphaNumeric: (Variable: '[code]Filter_Field'[/code])->(Get: Loop_Count))]
[Error_SetErrorCode: (Error_FieldRestriction: -ErrorCode)]
[Error_SetErrorMessage: (Error_FieldRestriction)]
[/If]
[/Loop]
[Else]
[Error_SetErrorCode: (Error_FieldRestriction: -ErrorCode)]
[Error_SetErrorMessage: (Error_FieldRestriction)]
[/If] [/pre]

To filter profanity from added or updated records:

[p]Insert the following code into a file named [code]Profanity_Filter.lasso [/code]at the root of the Web serving folder. Call this file within the add and update filters for any fields which need to be checked for profanity as follows. [/p]

[pre][Include: '/Profanifty_Filter.lasso'] [/pre]

[p]The profanity filter will take a list of banned words and quickly scan through the submitted field values, replacing any of them with the string [code]&#@$!.[/code] The list of banned words is defined in the array [code]Banned_Words[/code] and is strictly G-rated for demonstration purposes. [/p]

[pre][Variable: 'Banned_Words' = (Array: 'idiot', 'weirdo', 'moron')]
[Loop: (Variable: 'Banned_Words')->Size]
[Variable: 'Filter_Field' = (String_Replace: (Variable: 'Filter_Field'),
-Find=(Variable: 'Banned_Words')->(Get: Loop_Count),
-Replace='&#@$!']
[/Loop] [/pre]

[p]This filter can be easily adapted to forbid banned words from being submitted by returning a field restriction error. [/p]

To deny search requests with a slow sort request:

[p]In some databases such as FileMaker Pro, a sort request on an unindexed or related field can significantly impact the ability of the application to serve additional database requests. The following filter returns a field restriction error if a search request is entered that requests a sort on the [code]Grand_Total [/code]field. This filter should be entered as a search filter. [/p]

[pre][Sort_Arguments]
[If: (Sort_FieldItem) == 'Grand_Total']
[Error_SetErrorCode: (Error_FieldRestriction: -ErrorCode)]
[Error_SetErrorMessage: (Error_FieldRestriction)]
[/If]
[/Sort_Arguments] [/pre]

[p]This filter can be easily adapted to check search criteria or other sort criteria, and to deny the search request if any undesirable actions are attempted.[/p]