dynamic code generator

I know this may be fairly specific of an application need, but I am in the middle of a project of converting 300+ Adobe LiveCycle documents to html5 and have employed offshore resources to do the pretty conversion, but they don't get my business logic, so I wrote this little script to strip the field type, class, and any select options out of their html so I can stuff it into the document as cfml.  This is all created in coldbox and mssql.

We start with some simple input fields that come back like this

First we do a simple input page

this submits to my handler which does all of the business logic on stripping the code.  This actually sets  the classes and then passes off to SQL.

so we send our variables off too SQL

This comes back and we put it on a simple page like this

Which gives us output like this

So, now we can change our input field names and get our code, we do something very simple in our handler

Now to SQL for making our fields

Now, please note, I created this new udf that is a derivative off of  my list to table that builds options on the select lists.

This returns the my dynamic code in a format like this

Of course you can customize as you need it, but I hope this helps someone in need.



How many lines of code

I was reading today and found a great article from Pinal Dave.  Ever wondered how many lines of code are in your database?

Boom, here you go:

DB_NAME(DB_ID()) [DB_Name],
COUNT(*)   AS Object_Count,
SUM(LinesOfCodeAS LinesOfCode
LEN(definition)- LEN(REPLACE(definition,CHAR(10),'')) AS LinesOfCode,
FROM sys.all_sql_modules a
JOIN sysobjects  s
-- AND xtype IN('TR', 'P', 'FN', 'IF', 'TF', 'V')

Just open your database and run this.  Here are some keys for the results you will receive.


Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure


Hope this helps you out and gives you some ammo when the boss comes telling you what you don't do.

Binary Base64 data in xml field

Jonathan Perret and I were working on our new solution for HTML5 based documents that we can populate with data from the database and save data entered by clients and produce PDF files for government applications.  So, I Jonathan sends m some sample xml telling me my function is broke and it looks something like 

<?xml version="1.0" encoding="UTF-8"?><array><item><struct><fieldname>SIGNATURE1</fieldname><fieldvalue>data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOkAAAAlCAYAAACnHc4sAAAA+UlEQVR4Xu3TQREAAAgCQelf2hr3WBMwK+wcAQJpgaXTCUeAwBmpEhCICxhp/EHiETBSHSAQFzDS+IPEI2CkOkAgLmCk8QeJR8BIdYBAXMBI4w8Sj4CR6gCBuICRxh8kHgEj1QECcQEjjT9IPAJGqgME4gJGGn+QeASMVAcIxAWMNP4g8QgYqQ4QiAsYafxB4hEwUh0gEBcw0viDxCNgpDpAIC5gpPEHiUfASHWAQFzASOMPEo+AkeoAgbiAkcYfJB4BI9UBAnEBI40/SDwCRqoDBOICRhp/kHgEjFQHCMQFjDT+IPEIGKkOEIgLGGn8QeIRMFIdIBAXeImKACagvdwtAAAAAElFTkSuQmCC</fieldvalue></struct></item></array>

I run this and get 

Msg 9402, Level 16, State 1, Procedure Manage_Document, Line 17
XML parsing: line 1, character 38, unable to switch the encoding

After some research, the solution is changing <?xml version="1.0" encoding="UTF-8"?> to <?xml version="1.0" encoding="UTF-16"?> as it needs defined.  This was a fairly simple solution and my proc is now working fine.   Now Jonathan keeps telling me it's broke, he is getting SQL Server Error on his side.  Finally we broke it down, something between ColdFusion and SQL, the binary in the xml causes an issue.   What's the solution you ask?  This was so simple and funny, I couldn't believe it.  I modified the procedure header the following

ALTER PROCEDURE [dbo].[rsp_Manage_Document_New] (@xml xml nvarchar(max))

and added the following:

DECLARE @xml1 xml

SELECT @xml1 = @xml

It won't take it on the input, between the languages, but it allows me to convert.  Now we can save signatures as images in the XML in the database and pass them back to the front end for display and output on our documents.

Native JSon in MSSQL 2016

We have exciting news in the Microsoft Community.  This announcement confirms that MSSQL 2016 will contain native support for JSon


This will allow us to go directly from JQuery to Tables without conversion.  I am excited to see how it goes.

json to xml to table

Many of you may have this issue, my front end developer Jonathan Perret came to me with a problem.  He wanted to quit saving multiple line tables with update data one cell/row at a time.  So, let's look at this sample:

We have all seen forms like this before and don't know what to do with it.  Here is the link to my sample form, http://www.evengame.com/demo/formSubmit.  After you hit submit you will see that it automatically produces both the JSon and XML strings for you.  We have a build in Struct to XML converter that looks like this courtesy of Coldbox:

    <cffunction name="arrayToXML" returnType="string" access="public" output="false" hint="Converts an array into XML with no headers.">
        <cfargument name="data"         type="array"    required="true" hint="The array to convert">
        <cfargument name="useCDATA"      type="boolean"  required="false" default="false" hint="Use CDATA content for ALL values. False by default">
        <cfargument name="rootName"     type="string"   required="true"   default="" hint="The name of the root element, else it defaults to the internal defaults."/>
        var buffer = createObject('java','java.lang.StringBuffer').init('');
        var target = arguments.data;        
        var x = 1;
        var dataLen = arrayLen(target);
        var thisValue = "";
        var rootElement = "array";
        var itemElement = "item";
        // Root Name        
        if( len(arguments.rootName) ){ rootElement = arguments.rootName; }
        //Create Root
        <cfloop from="1" to="#dataLen#" index="x">
            <cfparam name="target[x]" default="_INVALID_">
            <cfif isSimpleValue(target[x]) AND target[x] EQ "_INVALID_">
                <cfset thisValue = "NULL">
                <cfset thisValue = target[x]>
            <cfif NOT isSimpleValue(thisValue)>
                <cfset thisValue = translateValue(arguments, thisValue)>
                <cfset thisValue = safeText(thisValue,arguments.useCDATA)>
            <cfset buffer.append("<#itemElement#>#thisValue#</#itemElement#>")>
        <cfset buffer.append("</#rootElement#>")>
        <cfreturn buffer.toString()>


Now that we have our xml, we can send to MSSQL.  I started  with a user defined function that Pinal Dave for the start on this and modified it so that we can get an index with each delimited item in the list.

    CREATE FUNCTION [dbo].[udf_List2Table]     (    @List VARCHAR(MAX),    @Delim CHAR)
    RETURNS @ParsedList TABLE (item VARCHAR(MAX),sortOrder int)
    DECLARE @item VARCHAR(MAX), @Pos int,@sortOrder int


        SET @sortOrder = 1
        SET @List = LTRIM(RTRIM(@List))+ @Delim
        SET @Pos = CHARINDEX(@Delim, @List, 1)
        WHILE @Pos > 0


                SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
                IF @item <> ''


                    INSERT INTO @ParsedList (item,sortOrder)
                    VALUES (CAST(@item AS VARCHAR(MAX)),@sortOrder)
                    SET @sortOrder = @sortOrder+1


            SET @List = RIGHT(@List, LEN(@List) - @Pos)
            SET @Pos = CHARINDEX(@Delim, @List, 1)




Here are a few sample queries you can run to test this:

    SELECT *
    FROM dbo.udf_List2Table('1,2,3,4',',') ult

    SELECT *
    FROM dbo.udf_List2Table('1[2[3[4','[') ult

    SELECT *
    FROM dbo.udf_List2Table('1/1/1900,2/1/1900,3/1/1900,4/1/1900',',') ult

    SELECT CAST(DATEADD(d,1,ult.item) AS varchar(32)) AS item, ult.sortOrder
    FROM dbo.udf_List2Table('1/1/1900,2/1/1900,3/1/1900,4/1/1900',',') ult

You will get results like this respectively:

  item sortOrder
  1 1
  2 2
  3 3
  4 4
  item sortOrder
  1 1
  2 2
  3 3
  4 4
  item sortOrder
  1/1/1900 1
  2/1/1900 2
  3/1/1900 3
  4/1/1900 4
  item sortOrder
  Jan  2 1900 12:00AM 1
  Feb  2 1900 12:00AM 2
  Mar  2 1900 12:00AM 3
  Apr  2 1900 12:00AM 4

Now that we have the sort order, that becomes our index that we can merge our lists which allows our front end guy to simply dump the data we send them to build the table and make a delimited list of variables for each field on the page.  So, now we can use  the following code to make a more complex table:

DECLARE @xml xml = '

/* Now we declare our temporary table. */

DECLARE @employeeTable TABLE (id int PRIMARY KEY,BusinessEntityID int,VacationHours int,SickLeaveHours int,DepartmentID int)

/* Insert our first delimited xml element */

INSERT INTO @employeeTable (id,BusinessEntityID)
SELECT ult.sortOrder,ult.item
FROM dbo.udf_List2Table(@xml.value('(/struct/BusinessEntityID)[1]', 'VARCHAR(MAX)'),',') ult

/* Now update our table with additional delimited xml elements */

SET a.VacationHours = b.item
FROM @employeeTable a INNER JOIN (
SELECT ult.item,ult.sortOrder
FROM dbo.udf_List2Table(@xml.value('(/struct/VacationHours)[1]', 'VARCHAR(MAX)'),',') ult
) b ON a.id = b.sortOrder

SET a.SickLeaveHours = b.item
FROM @employeeTable a INNER JOIN (
SELECT ult.item,ult.sortOrder
FROM dbo.udf_List2Table(@xml.value('(/struct/SickLeaveHours)[1]', 'VARCHAR(MAX)'),',') ult
) b ON a.id = b.sortOrder

SET a.DepartmentID = b.item
FROM @employeeTable a INNER JOIN (
SELECT ult.item,ult.sortOrder
FROM dbo.udf_List2Table(@xml.value('(/struct/DepartmentID)[1]', 'VARCHAR(MAX)'),',') ult
) b ON a.id = b.sortOrder

FROM @employeeTable

You will get a table like this

  id BusinessEntityID VacationHours SickLeaveHours DepartmentID
  1 220 60 41 10
  2 221 102 72 10
  3 219 94 61 12
  4 218 70 50 9
  5 217 59 71 9
  6 245 44 25 12
  7 249 76 54 10
  8 244 72 59 9
  9 235 62 40 9
  10 236 73 59 10


Now you have a working temporary table that you can manipulate and process as you desire.  With the advent of all of the front end tools that can produce JSon and turn that into xml all as a tool kit, this will allow you to turn that xml into workable tables for updating.

SQL Satruday Houston #408

Thank you for allowing me to present today at SQL Saturday Houston #408 (http://www.sqlsaturday.com/408/eventhome.aspx).  I appreciate all of the great feedback and I will be going through and posting solutions for what you have suggested in my blog over the next few days.  I am going to post the highlights in the next few blogs today.

Houston SQL Server User Group

Was happy to present today for the Houston SQL User Group.  We had some great discussion about the presentation.  Attached is the file set from today's presentation about cursors, temp tables, and pivots.  I know this is an unrealistic example, but here is the source code.  You can find the source code at /__media/Cursors Temp Table Pivot.zip


Steve AllisonWelcome to my Blog.

I am a long time IT person who started programming in 1976 with my father.  I started with Basic on a TI994A, TV on Channel 3 or 4 as the monitor and my cassette player as my hard drive smiley.  Oh for those days when nobody will ever need more than 640 kb of hard disk space, the use of old 5 1/4" floppys.  I don't miss those days at all.   I grew into Microsoft Basic and text files for data in the 80's, ASP, Access, Excel, and Visual Basic in the 90's, and in the 2000's I did MySql and MSSql and moved the front end to Coldfusion platform.  I have moved my apps to web based and build most everything in JQuery, Coldfusion, Coldbox, Contentbox, and MSSql for the database.  I am a member of the Houston Sql Server User Group and the Houston ColdFusion User Group.

My Company is Even Game, and we are "Leveling the playing field through technology,"  We focus on small to mid sized  companies providing them with software as a service for their production needs.  We currently have a small server cluster with Hosting.com which provides our hardware and software requirements for Firewall and closed network environment.

I have been putting off blogging long enough and will start posting some of my day to day trials of a small company Coder, Owner, Developer, DBA, Salesman...  You get the drift.  I look forward to helping you find solutions to problems similar to ones I have found.



I am a long time dba that goes back to the days of text files and cassette tapes and have been through it all. Jonathan Perret and I own a small firm that specializes in small to medium size business solutions with software as a service. Our software is semi-custom to each user to allow for all of the custom nuances that each business requires.


Recent Entries

Entries Search