INF: SQL Query Constant Substitution Feature

MORE INFORMATION

Query constant substitution facilitates staging and versioning. Constants defined in the registry can be used in SQL queries. For example, you can define a table name as either a store-specific constant or as a Merchant Server level constant in the registry. This constant can then be used in the SQL queries stored in your store_sql table. To create your own constant, use Merchant Server's constants key in the registry, either at the store level, or at the server level.

The following example demonstrates how to use this feature:

1. Run the following SQL statements in the database that contains Clockped's tables in ISQL/w (if you are using Microsoft SQL Server) or in your RDBMS query tool:

         create table test(col1 int)
         insert test values(10)
   						

2. Add a new key named 'Test' and a string named 'mytable' with a value of 'test' to the following registry key:

         HKEY_LOCAL_MACHINE\Software\Microsoft\MerchantServer\Stores\ 
         Clockped\Constants
   						

3. Run the following SQL query in the database that contains Clockped's tables in ISQL/w (if you are using Microsoft SQL Server) or in your RDBMS query tool:

         insert clockped_sql values('querysubst','select * from #mytable')
   						

   Note that "##" will be converted into "#" in query strings. Also, if your query string contains #xxx (where xxx is equal to one of the constants), the #xxx value will be replaced with that constant's value. In the SQL query above, '#mytable' will be replaced by 'test.'
4. Add the following code to welcome.html in the Clockped\Login \Templates directory:

         <br>Testing the constant 'mytable' in a query:<br>
         [fetchrows results "querysubst"]
         There are [value results.count]  row(s) in "[value const.mytable]"
         table<br>
         and the first row is:[eachcolvalue results]
         [value results.colvalue]
         [/eachcolvalue]<br><br>
   
         Testing the constant 'mytable' in tfetchcrows:<br>
         [tfetchrows results2 const.mytable]
         There are [value results2.count]  row(s) in "[value const.mytable]"
         table<br>
         and the first row is:[eachcolvalue results2]
         [value results2.colvalue]
         [/eachcolvalue]<br>
   						

5. Run the following URL in your browser to check the results:

         http://<computer name or IP address>/prd.i/pgen/clockped
         /0L/welcome.html