45
©2002-2016, Ido Millet, ido@MilletSoftware.com
Page 240
Update a Database After Success (After_Success_SQL)
After Visual CUT successfully exported, printed, or emailed a report, you may want to update
your database to reflect that information. For example, after bursting invoices to customers, you
may want to update the records for these jobs to reflect the date of invoicing or the fact that an
invoice was emailed. Besides providing useful information, these columns may also be used to
avoid duplicate processing by incorporating them into the record selection formula in your
reports.
To update a database after successful processing, you use the After_Success_SQL command
line argument. The argument structure is as follows:
…
"After_Success_SQL:Type>>ODBC DSN>>User ID>>Password>>SQL Statement"
or to trigger multiple statements (each using a different ODBC DSN) repeat the 5 elements after
a ^^^^ delimiter:
"
After_Success_SQL:Type>>DSN1>>User1>>Pass1>>SQL1
^^^^
Type>> DSN2>>User2>>Pass2>>SQL2"
The parameters (after the ":") are separated by a ">>" and are as follows:
1. Type: the type of success step: Burst or Whole. Any other text (Skip) skips processing.
10. ODBC DSN: The ODBC DSN providing access to the target database. Note that the target
database doesn’t have to be the same
as the one used for the report.
11. User ID: Leave blank if no user id is needed to connect to the ODBC DSN
12. Password: Leave blank if no password is needed to connect to the ODBC DSN
13. SQL Statement: the SQL statement to execute. This typically include embedded references
to fields/formulas that Visual CUT would replace with their dynamic values.
NOTE 1: if the SQL statement is blank, it simply gets skipped (no failure message).
Note 2: to specify multiple statements simply separate them with a '; '
For example, the following command line argument
----------------------------------------------------------------
… "After_Success_SQL:Burst>>xTreme>>>>>>Update "JOBS" SET
"Processed" = True, "Date Processed" = Date() WHERE "Job Name" =
'{@Job}'"
----------------------------------------------------------------
Would trigger a SQL statement through the xTreme ODBC DSN, without user id and password,
every time a bursting step is completed successfully. The statement would find the matching
record in the JOBS table by comparing the Job Name column to the value of a {@Job} formula
in the report. If one or more matching records are found, their "Processed" column is set to
TRUE, and their "Date Processed" column is set to the current date.
Note that the syntax above conforms to MS Access. For other databases you may need to adjust
the syntax. For example, MS Access provides a Date() function. MS Access also requires
enclosing table and column names with double quotes, and literal strings with single quotes (as
done around the value of the {@Job} reference).