Visual Basic —

What About SQL?

SQL (or other DML in which the data source is very obscure) has to be kept somewhere. Often it is scattered through a system-perhaps bundled into each business object. This is clearly not a good policy. What if the data source changes in type or structure? What if the type of DML changes? The maintenance burden can be high. At minimum, even if the SQL is easy to find, every business object must be recompiled. The TMS answer is something called a SQL ROOS (Resource Only Object Server). At TMS we use these as central, easy-to-replace-and-modify repositories for business rule SQL, or anything that is textual (error messages, interface text etc.). Internally the mechanism used is a very standard Windows one, the resource file (RES) that holds a string table. The ROOS makes calls to this string table, using appropriate off-sets to pull out the correct text, and load the methods parameters for return to a calling business server. Many Windows programs use these resource files, either compiled into them, or often compiled and linked to as separate DLLs. Visual Basic has a restriction in this regard: we can only include one RES file per project. So to avoid this, and to make string maintenance straightforward without having to recompile a whole application, we do put our RES files into separate DLLs, as illustrated in Figure 12-3. In this way many DLLs, and therefore many RES files, can be referenced and used in the same project. The Commenter add-in also uses a RES file for loading pictures onto Visual Basic's Edit toolbar.

Figure 12-3 Using multiple ROOS's in a system.

For a given request, the SQL ROOS might return the following information:

  1. The SQL statement (including "?" placeholders for parameters)
  2. The number of parameters being used in the SQL statement
  3. The type (input, output, input/output) of each parameter
  4. The kind of query being done (straight select, prepared statement, stored procedure, multiple resultset, insert, update, delete, or other action/batch query)

It makes sense to have a ROOS template project-although some things might change from ROOS to ROOS (mainly the contents of the RES file), the bulk of the code will be exactly the same. Also remember that if you use a ROOS template project, change the base address for each ROOS DLL (and indeed each DLL) you intend to deploy on the same machine. If you have missed this step, check out the DLL Base Address entry on the Compile tab of the Project Properties dialog box, and the associated Help.

So when a business object server has to execute one of its methods that requires retrieval of remote data via a business rule, it uses the SQL ROOS to provide that rule (in the form of SQL which typically calls a stored procedure). It then adds any data parameters, and uses the retrieved SQL and the data parameters as parameter arguments on a method call to the data access server. The data access server in turn submits the SQL and returns a notification of success/failure, any returned parameters or a resultset (depending on the kind of operation being undertaken) to the business object server. If necessary, the business object server returns any data to its base client. (See Figure 12-4.)

Figure 12-4 Using a SQL ROOS