Sometimes ,we receive a request to develop a report to change data in a database. We can see several ways to do this, but we don’t recommend you use any of those methods.

We believe that every report should be able to be reprinted and reprocessed using the same data and getting the same results. If a report changes that database during processing, it is a volatile report and reruns cannot be handled in the usual way.

For this reason, we have not developed a User Function Library (UFL) that can update SQL databases. There are UFLs that do this, but we prefer a more robust and repeatable production or ad-hoc solution. We have developed a better way to combine the two steps of a report and a database modification.

In another article, we suggested you use a report to build a SQL Update statement, and then export this to a text file. Then paste the text output into a SQL tool and apply the update your database in a second step.

You can now automate this by combining our Disk Output UFL with our cViewSQL utility. Use a formula to build the SQL Updates you need in your database. Then use cViewSQL to process these lines. Breaking the task into two steps makes it a lot easier to develop, test, and maintain.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.