Excel Do, Dynamic Does : Stored Procedures

The XML Is On The Kid

Bob Phillips — Thu, 11 Mar 2010 23:08:00 GMT

I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.

XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a consumer of such, but I still feel it promises far more than it delivers as a technology).

XL-Dennis’ comment gave me the prod I needed to actually do something about this, so I set about converting my tool to using XML files.

This is the structure of the XML I designed, using the same data as my previous posts.

<?xml version="1.0" encoding="utf-8"?>
<database type="Access"
         path="C:\MyDatabases\"
         name=”MyDB.mdb">

   <category name="Get">

       <procedure name="spGetCompanyGoals">

           <SQL code="SELECT SUM(RD.SalesGoal) AS 'Company Sales Goal', " />
           <SQL code="      SUM(RD.BonusGoal) AS 'Company Bonus Goal' " />
           <SQL code="FROM refUsers AS RU " />
           <SQL code=”WHERE LoginID = prmLoginId;" />
       </procedure>

       … more procedures
   </category>

   <category name="Delete">

        <procedure name="spDeleteSalesType">

           <parameter name="prmUpdatedBy"
                      type="VarChar (50)" />

           <parameter name="prmSalesTypeID"
                      type="Integer" />

           <SQL code="UPDATE  refSalesTypes               " />
           <SQL code="SET     Deleted = TRUE,             " />
           <SQL code="        UpdatedBy = prmUpdatedBy,   " />
           <SQL code="        UpdatedOn = Now>             " />
           <SQL code="WHERE   SalesTypeID = prmSalesTypeID;" />
       </procedure>

       … more procedures

       … more categories
   </category>
</database>

There are some distinct advantages to the XML to my mind. These are:

it is more readable
there is less meta-information, such as the various counts, because the XML parser will provide all of that to the code
the XML parser provides a level of validatio
tools such as Altova XMLSpy provide a much better IDE for creating and updating these files than a text editor, as well as validation

There are some disadvantages of course, but the only one that really irks me is having to use & for & and so on. I understand why I have to, but it still rankles a tad.

In the XML design, there were a couple of decisions to be made, around the elements and attributes. Before I embarked upon the design I read the chapter on XML in the second edition of the indispensable Professional Excel Development, and whilst it suggested encapsulating the data in elements rather than attributes I chose to use attributes as I felt it was more readable that way, attributes still need to be within a parent element which loses clarity. In my mind, this

is far simpler than this

           <parameter>
               <name>prmSalesTypeID</name>
               <type>Integer</type>
           </parameter>

Changing the addin was trivial once I had worked out the code for reading the XML file, updating the INI file to my XML format took far more time. The XML version ofthe addin can de downloaded here.

SP Builder is supplied as an Excel 2003 addin, or XLA file, and adds a menu option to the Tools menu with three options, Build SPs, SP Builder List and About.

As you many know, when Excel 2003 workbooks that create commandbars are opened in Excel 2007 or 2010 the menus are accessed via the Addins tab. This is not a satisfactory solution, so the addin tests the Excel version, and if it is 2003 or earlier it builds the commandbars, it is 2007 or 2010 it loads a ‘light’ 2007/2010 addin that adds a group to the Developer tab.

This way, we have a single addin that runs in any version of Excel.

One thing that I found was that I could not insert comments in my file, the parser failed when I had comments. I used what I believe is the correct format for comments, that is

<!—
‘-----------------------
' Check Stored Procedures
'-----------------------
-->

but it only worked when I completely stripped out the comments.

I also created an XSD file for the XML, which I used to play with reading it into Excel, but I see no real use for Excel in this process other than hosting the code, so that is going nowhere. Is there any other use I can use the XSD for? This also points at the next step, take Excel out of the process completely and create a standalone VB application; that would make a nice candidate for me to develop some more .Net skills.

The Kid Grows Up

Bob Phillips — Wed, 03 Mar 2010 12:41:00 GMT

In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO.

As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the stored procedures using the Access GUI, I much prefer to build a script file that can be rerun at any time. This is very much in line with my preferences to autogen as much as possible, and also with me development methodology, where I prefer to allocate design time before ploughing into the functional code.

In the post mentioned above, I said that … you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. This post will cover such a creator app.

In this app, I have a script file that defines all of the SPs, and the application just reads that file and builds the SPs defined therein. I have used an INI file as my SP definition file; I like the flexibility of INI files, the format does not have to be too rigid, and they are easily segmented, and easily read (via code).

The format of my file is as follows

1[spb_App]
2
3   [spb_DB]
4
5       DBType=Access ;could be SQL Server or any other DB
6       DBPath=C:\MyDatabases\
7       DBName=MyDB.mdb
8
9   [spb_Stored_Procs]
10
11       TypeCount=7
12
13       I001=Get
14       I002=Check
15       I003=Insert
16       I004=List
17       I005=Query
18       I006=Table
19       I007=Update
20       I008=Delete
21
22;-----------------------
23; Get Stored Procedures
24;-----------------------
25       [spb_SP_Get]
26
27           SPCount=7
28
29;spGetCompanyGoals
30           [spb_SP_Get_001]
31
32               SPName=spGetCompanyGoals
33
34               [spb_SP_Get_001_Parameters]
35
36                   ParameterCount=1
37
38                  [spb_SP_Get_003_Parameters_001]
39
40                      ParamName=prmLoginID
41                      ParamDataType=VarChar (50)
42
43               [spb_SP_Get_001_SQL]
44
45                   SQLLineCount=4
46
47                   Line001=SELECT SUM(SalesGoal) AS 'Company Sales Goal',
48                   Line002=      SUM(BonusGoal) AS 'Company Bonus Goal'
49                   Line003=FROM refUsers
50                   Line004=WHERE LoginID = prmLoginId;
51etc.

Line 1 isn’t actually used, it is just for completeness.

Lines 3-7 define the database, the type and location. Note that the type is to allow for building SPs in different databases, although we will just discuss Access.

Lines 9-20 define the SP categories, I do this so as to break up the SPs and keep them grouped, for easier maintenance. The Type (Get, Check, etc.) is used as part of the section id for the SP details, as in lines 20, 30, 34, and 38.

Line 27 defines how many SPs are in that group, used as a loop index in the code.

Line 32 is the SP name, used in the code to Drop the SP then Create it anew.

Lines 34-41 defines the parameters. As you can see, there is a ParameterCount specifying how many parameters the SP uses. A definition for each parameter, if applicable, follows, with an incrementing suffix index so that the app can extract each in turn.

Lines 43-50 define the SP code, with a SQLLineCount defining how many lines of SQL are within the SP. In the example above, the SP is very simple, but of course SPs of any complexity can be built.

Lines 29-50 are repeated for each SP within that category.

Lines 22-50 are repeated for each category of SPs.

The category names are not relevant, it can be any name and any number, as long as the sections match up.

Care has to be taken that the definitions are consistent, the category id is correct, the SP index is carried through, the parameter name in the parameter definition is the same as the parameter in the SQL code., and so on.

The SP Builder addin can be downloaded from here. It is unprotected, so the code can be examined, updated as you see fit.

I use this technique for all of my databases, so I have a script file for each, and can easily recreate the database code. As I mentioned, by creating a script file it helps in better design, thinking about the code required rather than diving into the GUI and building as required. This technique could be extended to creating the database, building the tables etc. I have a separate app for this, but have not combined them as I find myself creating the SPs far more often than the database, I find it more convenient to keep as separate applications.