Syntax

   @SQL(<connectionID>,<sqlQuery>[,<resultList>{}]) 
   
Arguments

   <connectionID> 
   
      Name of database connection on which to execute query.
      
      Range: String  
   
   <sqlQuery>  
   
      Fully-formed SQL query to pose to database.
      
      Range: String  
   
   <resultList>{} 
   
      An attribute list to hold the results of the query, if there are any.
   
      Range: String  
   
Configuration

   The @SQL function accepts the following configuration line:

      SQL <connectionID> [<keyword> <value>]+ 
      
   with the following keywords:

   SERVER_TYPE 
   
      The type of server being used. For this FME release, the valid values are 
      ODBC, ORACLE, ORACLE8I, EXCEL, MDB, and MSSQL_ADO. When an MDB or Excel 
      server is used, the server name is the name of the MDB or Excel file.
   
      Required.

      Range: String  
      
   SERVER_NAME 
   
      The name of the server hosting the database. For ODBC servers, this is the 
      ODBC name of the data source. For ORACLE and ORACLE8I servers, this is the 
      SQLNet service name.
   
      Required.
      
      Range: String  
      
   USER_NAME   
   
      The user name that accesses the database. Some database sources ignore 
      this value.
      
      Optional.
   
      Range: String  
      
   PASSWORD 
   
      The password that accesses the database. Some database sources ignore this 
      value.
   
      Optional.
   
      Range: String  
      
   USE_SSPI 
   
      For SQL Server, set this to YES to use Windows Authentication instead of 
      username and password.
   
      Optional.
   
      Range: YES or NO  
      
   DB_NAME  
   
      The name of the database. Some database sources ignore this value.
   
      Optional.
   
      Range: String  
      
   PORT  
   
      The TCP/IP port that accesses the remote database. Some database sources 
      ignore this value.

      Optional.
   
      Range: String  

   The SQL configuration line defines a database connection for a future @SQL 
   call. Every invocation of @SQL references a connection to a data source.

   Some parameters are optional as far as the SQL configuration line is 
   concerned. For example, ODBC may require only the SERVER_NAME parameter 
   unless a user name and password were assigned to the data source, in which 
   case the USER_NAME and PASSWORD parameters would also be required.
      
Description

   This function enables the FME to execute arbitrary database commands through 
   a connection to the database. Currently ODBC, MDB, Excel, ORACLE and any FME 
   Objects accessible databases are supported. FME Objects enabled databases 
   include PostGIS/PostgreSQL, SDE 3.x, GeoMedia SQL Server, DB2, and SQL 
   Server.

   The purpose of the command is to provide access to Structured Query Language 
   (SQL) statements which perform database updates or queries. An SQL query can 
   result in zero or more results being returned. For example, a query to remove 
   a table does not return any results, but a query to find rows of a table 
   matching a certain set of criteria could return many results.

   The <resultList> parameter of the @SQL call specifies an attribute list to 
   hold the results of the query, if there are any. It must be specified as a 
   proper attribute list name, complete with a trailing {}. The results are 
   stored on the feature as attributes in the form <listName>{n}.<attrName>, 
   where n is the result number, starting at 0, and <attrName> is the name of a 
   column in the returned table.

      Tip: It is possible for an SQL query to return results from multiple 
      tables. When writing results to the feature's attributes, a special 
      attribute named <listName>{n}.SQLResultTableNum is created to specify the 
      index of the returned table, starting at 0, for result n. Normally, this 
      attribute always has a value of 0. This situation may occur, for example, 
      if an update triggers queries on several tables.

   If the <resultList> is not specified, then any query results will be stored 
   directly on the feature. The SQL column name is used as the FME attribute 
   name. If multiple rows are returned, the values from the last row will be 
   stored on the feature.

   Multiple statements may be separated by semicolons. An individual statement 
   may be preceded with a hyphen, indicating that errors should be ignored.

   If the query returns any values of a date type, they are stored in the 
   feature in YYYYMMDD format. In addition, they are also stored in an attribute 
   with a .full suffix in the format YYYYMMDDHHMMSS.

      Tip: When performing an SQL query on an Excel database, the table name 
      must be suffixed with a '$' and surrounded with square brackets in order 
      for the query to execute properly. This is due to limitations of the Excel 
      ODBC driver and how it handles system tables.

   Error Handling

   If an error is encountered while performing an SQL query, it will be logged 
   to the log file. If @SQL is being used as an attribute function, its return 
   value will be the same error message. @SQL's return value will be an empty 
   string when no error occurs.