• Internet
• 27.08.2019
• EN

Timothy Davis: Adventures in Form Handling

written by Timothy Davis, 3. August 2011
 

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.

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)
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
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;

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.

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

About Tim

I'm Tim Davis. My primary professional interests are web, web content, and mobile. Currently I lead services team centered around OpenText Web Site Management (FKA: RedDot) for the Americas.

Source: Adventures in Form Handling

© copyright 2011 by Timothy Davis