Adventures in Form Handling – II

The next step was generating the query with DS Attributes. Nice thing about SQL is it doesn’t care about line breaks, so you can condense it down to one line. This is good as the XML engine in Delivery Server may not preserve these. I didn’t test conclusively, if you need white-space or formatting for say a REST call you will want to test thoroughly.

This is how I broke down the SQL to ‘templatize’ it for Delivery Server. Each line that needs Delivery Server variables is broken out.

DECLARE @formID as varchar(50) DECLARE @InsertOutputForm table (  form_id varchar(50) );DECLARE @InsertOutputFormFields table( id varchar(50), form_id  varchar(50), name  varchar(50),  type varchar(50)); INSERT INTO dbo.forms (name,type,dsuser) OUTPUT    INSERTED.id as form_id  INTO @InsertOutputForm 
VALUES (N'partnerinfoform', N'form',N'cindy')
SET @formID=(SELECT form_id FROM @InsertOutputForm); INSERT INTO dbo.form_fields (form_id, name, type, data)OUTPUT INSERTED.id, INSERTED.form_id, INSERTED.name, INSERTED.type  INTO @InsertOutputFormFields VALUES 
 (@formID, 'products', 'string', 'products'),
 (@formID, 'solutions', 'string', 'solutions'),
 (@formID, 'successes', 'string', 'successes'),
 (@formID, 'resources', 'string', 'resources'),
 (@formID, 'comments', 'string', 'test'),
 (@formID, 'submit', 'string', ''),
 (@formID, 'step', 'string', '430'),
 (@formID, 'skey', 'string', 'SID-04000407-1F158E8D')
;

Next prepare to drop it into a DS XML file with pseudo inline attribute syntax.

DECLARE @formID as varchar(50) DECLARE @InsertOutputForm table (  form_id varchar(50) );DECLARE @InsertOutputFormFields table( id varchar(50), form_id  varchar(50), name  varchar(50),  type varchar(50)); INSERT INTO dbo.forms (name,type,dsuser) OUTPUT    INSERTED.id as form_id  INTO @InsertOutputForm 
VALUES (N'[#formname#partnerinfoform#]', N'[#formtype#form#]',N'[#rde-fields.user#]')
SET @formID=(SELECT form_id FROM @InsertOutputForm); INSERT INTO dbo.form_fields form_id, name, type, data)OUTPUT INSERTED.id, INSERTED.form_id, INSERTED.name, INSERTED.type  INTO @InsertOutputFormFields VALUES 
 (@formID, '[#fieldname#]', '[#fieldtype#string#]', '[#data#]'),
;
<!-- Split Fields -->
		<rde-dm:attribute mode="write" attribute="request:ft.ssv.fields" value="[#request:_sf_form_fieldnames_#].substring(1)" value-separator="|" />
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment1" value="DECLARE @formID as varchar(50) DECLARE @InsertOutputForm table (  form_id varchar(50) );DECLARE @InsertOutputFormFields table( id varchar(50), form_id  varchar(50), name  varchar(50),  type varchar(50)); INSERT INTO dbo.forms (name,type,dsuser) OUTPUT    INSERTED.id as form_id  INTO @InsertOutputForm " />
		<!-- replace pseudo code -->
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment2" value="VALUES (N'[#request:_sf_form_name_#partnerinfoform#]', N'[#request:formtype#form#]',N'[#user:rde-fields.login#anonymous#]') "/>
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment3" value="SET @formID=(SELECT form_id FROM @InsertOutputForm); INSERT INTO dbo.form_fields (form_id, name, type, data) OUTPUT INSERTED.id, INSERTED.form_id, INSERTED.name, INSERTED.type  INTO @InsertOutputFormFields VALUES " />
		
		<!-- Loop Over Fields -->
		<rde-dm:attribute mode="for-each" attribute="request:ft.ssv.fields" alias="field" tag="fields">
			<rde-dm:attribute mode="condition">
				<rde-dm:constraint>context:field NE ""</rde-dm:constraint>
				<field>
					<!-- Get Field Name -->
					<![CDATA[-]]><rde-dm:attribute mode="read" attribute="context:field"/><![CDATA[:]]><rde-dm:attribute mode="read" attribute="request:[#context:field#]" /><![CDATA[;<br/>]]>
					<rde-dm:attribute mode="write" attribute="request:ft.query.segment4" value="[#request:ft.query.segment4#], (@formID, '[#context:field#].replace(';',', ')', '[#fieldtype#string#]', '[#request:[#context:field#badField#]#].replace(';',', ')')"/>
					
					<rde-dm:attribute mode="write" attribute="request:rdb.columns" value="[#request:rdb.columns#], form_fields.[#context:field#]"/>
					<rde-dm:attribute mode="write" attribute="request:rdb.values" value="[#request:rdb.values#],&quot;[#request:[#context:field#badField#]#].replace(';',', ')&quot;"/>
				</field>
			</rde-dm:attribute>
		</rde-dm:attribute>
		
		<rde-dm:attribute mode="write" attribute="request:ft.rdb.formquery" value="[#request:ft.query.segment1#] [#request:ft.query.segment2#] [#request:ft.query.segment3#] [#request:ft.query.segment4#].trim().substring(1);" value-separator="" />
		<![CDATA[<br/>]]>
		<rde-dm:attribute mode="read" attribute="request:ft.rdb.formquery" /><![CDATA[<br/>]]>
		<!-- TODO: 
			- sql query submit
			- validate
			- add date&time of submit
			- set campaign step code = true
			- 
		-->
		<rde-dm:rdb mode="query" alias="otwsm_supplemental" sql="select count(*) as count from dbo.form_fields;"/><![CDATA[-pre count <br/>]]>
		<rde-dm:rdb mode="update" alias="otwsm_supplemental" sql="[#request:ft.rdb.formquery#]"/>
		<rde-dm:rdb mode="query" alias="otwsm_supplemental" sql="select count(*) as count from dbo.form_fields;"/><![CDATA[- post count<br/>]]>

Unfortunately I quickly learned I should have RTFM’d. I ran straight into a blocker.


Unexpected error occurs:This statement isn't allowed=DECLARE @formID as varchar(50) DECLARE @InsertOutputForm table ( form_id varchar(50) )

The RDB DynaMent limits the types of commands that can be executed. Thus I seemed out of luck. Except their is the statement mode which I don’t believe I’ve used before. It will let you run unrestricted SQL statements. If you are running simpler queries do use ‘query’ or ‘update’ to help prevent SQL Injection.

<!-- Split Fields -->
		<rde-dm:attribute mode="write" attribute="request:ft.ssv.fields" value="[#request:_sf_form_fieldnames_#].substring(1)" value-separator="|" />
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment1" value="DECLARE @formID as varchar(50) DECLARE @InsertOutputForm table (  form_id varchar(50) );DECLARE @InsertOutputFormFields table( id varchar(50), form_id  varchar(50), name  varchar(50),  type varchar(50)); INSERT INTO dbo.forms (name,type,dsuser) OUTPUT    INSERTED.id as form_id  INTO @InsertOutputForm " />
		<!-- replace pseudo code -->
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment2" value="VALUES (N'[#request:_sf_form_name_#partnerinfoform#]', N'[#request:formtype#form#]',N'[#user:rde-fields.login#anonymous#]') "/>
		<rde-dm:attribute mode="write" attribute="request:ft.query.segment3" value="SET @formID=(SELECT form_id FROM @InsertOutputForm); INSERT INTO dbo.form_fields (form_id, name, type, data) OUTPUT INSERTED.id, INSERTED.form_id, INSERTED.name, INSERTED.type  INTO @InsertOutputFormFields VALUES " />
		
		<!-- Loop Over Fields -->
		<rde-dm:attribute mode="for-each" attribute="request:ft.ssv.fields" alias="field" tag="fields">
			<rde-dm:attribute mode="condition">
				<rde-dm:constraint>context:field NE ""</rde-dm:constraint>
				<field>
					<!-- Get Field Name -->
					<![CDATA[-]]><rde-dm:attribute mode="read" attribute="context:field"/><![CDATA[:]]><rde-dm:attribute mode="read" attribute="request:[#context:field#]" /><![CDATA[;<br/>]]>
					<rde-dm:attribute mode="write" attribute="request:ft.query.segment4" value="[#request:ft.query.segment4#], (@formID, '[#context:field#].replace(';',', ')', '[#fieldtype#string#]', '[#request:[#context:field#badField#]#].replace(';',', ')')"/>
					
					<rde-dm:attribute mode="write" attribute="request:rdb.columns" value="[#request:rdb.columns#], form_fields.[#context:field#]"/>
					<rde-dm:attribute mode="write" attribute="request:rdb.values" value="[#request:rdb.values#],&quot;[#request:[#context:field#badField#]#].replace(';',', ')&quot;"/>
				</field>
			</rde-dm:attribute>
		</rde-dm:attribute>
		
		<rde-dm:attribute mode="write" attribute="request:ft.rdb.formquery" value="[#request:ft.query.segment1#] [#request:ft.query.segment2#] [#request:ft.query.segment3#] [#request:ft.query.segment4#].trim().substring(1);" value-separator="" />
		<![CDATA[<br/>]]>
		<rde-dm:attribute mode="read" attribute="request:ft.rdb.formquery" /><![CDATA[<br/>]]>
		
		<rde-dm:rdb mode="query" alias="otwsm_supplemental" sql="select count(*) as count from dbo.form_fields;"/><![CDATA[-pre count <br/>]]>
		<rde-dm:rdb mode="statement" alias="otwsm_supplemental" sql="[#request:ft.rdb.formquery#] SELECT id, form_id, name, type FROM @InsertOutputFormFields;"/>
		<rde-dm:rdb mode="query" alias="otwsm_supplemental" sql="select count(*) as count from dbo.form_fields;"/><![CDATA[- post count<br/>]]>
		<!-- TODO: 
			- add date&time of submit
		-->
		<rde-dm:attribute mode="read" attribute="request:step" /><![CDATA[-step<br/>]]>
		<rde-dm:attribute mode="write" op="set" attribute="user:campaign.step[#request:step#]" value="true"/> Set to True
		<rde-dm:attribute mode="read" attribute="user:campaign.step[#request:step#]" /><![CDATA[-step<br/>]]>
	<rde-dm:attribute mode="condition">
		<rde-dm:constraint>(request:redirect-target NE '') AND (request:debug NE 'true')</rde-dm:constraint>
			Redirect Now!<!-- redirect somewhere -->
			<rde-dm:process mode="redirect" type="http" url="http://10.25.0.51/demo/en/partner.htm" >
				<rde-dm:include content="[#request:redirect-target#content/en/index.htm#]"/>
			</rde-dm:process>
	</rde-dm:attribute>

That is the working result of a form handler to work with the “universal” storage tables handling a SmartForm post.

Adventures in Form Handling

In part this week I’m working on a form handling setup. I created a SmartForm and started about writing it’s input to DB via Delivery Server.  I decided to make a more generic handler. We have some tools around for processing (client side validation, server side validation, JCaptcha) but no universal storage mechanism at hand. Conceptually this same processing used in tandem with SmartForm, forms created by dragging and dropping from panels in SmartEdit, a template form, or an external or legacy form. I’ve decided to just store this in a couple of tables as it will allow flexibility to integrate with other applications later on.

Goal:

Create a Delivery Server form handler to store M forms with N form fields per form. (ok I don’t really expect it to scale unlimited)

DDL:

First I created some tables. here they are.

MS SQL 2008 DB Diagram

USE [otwsm_supplemental]
GO

/****** Object:  Table [dbo].[forms]    Script Date: 08/03/2011 09:24:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[forms](
	[id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[type] [nvarchar](50) NOT NULL,
	[dsuser] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_forms] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[forms] ADD  CONSTRAINT [DF_forms_id]  DEFAULT (newid()) FOR [id]
GO

ALTER TABLE [dbo].[forms] ADD  CONSTRAINT [DF_forms_type]  DEFAULT (N'form') FOR [type]
GO

ALTER TABLE [dbo].[forms] ADD  CONSTRAINT [DF_forms_user]  DEFAULT (N'anonymous') FOR [dsuser]
GO

USE [otwsm_supplemental]
GO

/****** Object:  Table [dbo].[form_fields]    Script Date: 08/03/2011 09:24:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[form_fields](
	[id] [uniqueidentifier] NOT NULL,
	[form_id] [uniqueidentifier] NOT NULL,
	[name] [nvarchar](50) NULL,
	[type] [nvarchar](50) NULL,
	[data] [nvarchar](max) NULL,
 CONSTRAINT [PK_form_fields] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[form_fields]  WITH CHECK ADD  CONSTRAINT [FK_form_fields_forms] FOREIGN KEY([form_id])
REFERENCES [dbo].[forms] ([id])
GO

ALTER TABLE [dbo].[form_fields] CHECK CONSTRAINT [FK_form_fields_forms]
GO

ALTER TABLE [dbo].[form_fields] ADD  CONSTRAINT [DF_form_fields_id]  DEFAULT (newid()) FOR [id]
GO

ALTER TABLE [dbo].[form_fields] ADD  CONSTRAINT [DF_form_fields_type]  DEFAULT (N'string') FOR [type]
GO

SQL:

I decided I wanted to insert from a form post with one interaction between DS and SQL.  For now I decided not to make a prepared statement even though they are supported in v10.1.

(If there are better ways to do the insert let me know in the comments)

DECLARE @formID as varchar(50)
DECLARE @InsertOutputForm table

(

  form_id varchar(50)

);

DECLARE @InsertOutputFormFields table

(

  id varchar(50),
  form_id  varchar(50),
  name  varchar(50),
  type varchar(50)

);

INSERT INTO dbo.forms (name,type,dsuser)

OUTPUT

    INSERTED.id as form_id

  INTO @InsertOutputForm

VALUES (N'test', N'partnerinfoform',N'cindy')

SET @formID=(SELECT form_id FROM @InsertOutputForm);

INSERT INTO dbo.form_fields (form_id, name, type, data)

OUTPUT

    INSERTED.id, INSERTED.form_id, INSERTED.name, INSERTED.type

  INTO @InsertOutputFormFields

 VALUES (@formID, 'products', 'string', 'products'),
(@formID, 'solutions', 'string', 'solutions'),
(@formID, 'successes', 'string', 'successes'),
(@formID, 'resources', 'string', 'resources'),
(@formID, 'comments', 'string', 'test'),
(@formID, 'submit', 'string', ''),
(@formID, 'step', 'string', '430'),
(@formID, 'skey', 'string', 'SID-04000407-1F158E8D');

SELECT id, form_id, name, type FROM @InsertOutputFormFields;

RESULTS:

After struggles with getting to know the output clause and the proper variable syntax I finally got MS SQL 2008 to do all the work.

MS SQL 2008 result set
Query Result

Next step. Writing finishing DS page logic to format the query.