Creating Custom SQL

At any stage in your adapter, which runs from top to bottom down the structure tree, you can insert some custom SQL to modify (or make log entries for) the data that has (so far) been written into the compliance database in FlexNet Manager Suite. You may include:

• SQL statements
• Stored procedures.

Any custom SQL is run as part of the same transaction as the import of the external data.

To create custom SQL (connected mode only):

1. Right-click your adapter’s name in the structure tree.
2. In the context menu, select Add New Item, and from the submenu select Custom Query.
3. To give your query a meaningful name (making it easier to read and maintain in the finished adapter XML file):
   1. Right click the new Custom Query entry in the structure tree
   2. Select Rename from the context menu
   3. Provide a new name.
4. Enter your SQL statement into the Query Text field.
   Tip: You may include the keyword [LOG_IMPORT_ID] to return the current value of the ImportID column in the BusinessImportLogSummary table.

   For example:

   • To run a stored procedure called MyStoredProcedure (optionally with parameters), enter

     EXEC MyStoredProcedure Parameter1,...

   • To post an entry in the log for a custom object (which will not otherwise be logged), enter

     Insert into BusinessImportLogObject([ImportID], [ObjectName], [StartDate],
                                     [EndDate], [ObjectType], [Status]) values ([LOG_IMPORT_ID],
                                     'My Custom Object', getdate(),
                                     getdate(),'Custom', 1)

5. In the Timeout field, set the number of seconds that the Business Importer should wait before giving up waiting for a response to the custom SQL statement. The following values have special meaning:
   • -1 means use the default time out for SQL Server
   • 0 means there is no limit and the Business Importer will wait indefinitely for the query to finish.
6. If you wish to write a custom message in the log file as part of this custom SQL, select the Send the internal log ID as a parameter check box. When checked, the @FinImporterLogID parameter value is automatically injected to the stored procedure as it is executed. The value of this parameter is the current record ID from the FinImporterLog table (automatically created).