I got an interesting query from a guy on how to alter a table without using any SQL statements from within Dexterity.

As per the Dexterity Guide, a table cannot be altered from within Dexterity, that means only thru’ Dex coding. If you’re converting tables stored in a SQL database, you must use pass-through SQL to perform the conversion.

Having said that, we can do it from Dexterity but calling a SQL Stored Procedure which will contain the necessary SQL Statements for that table alteration.

1. Make the necessary table changes on Dex Dictionary.

2. Write a SQL Stored Procedure like below (The SQL SP is written on SQL Server 2005 DB and uses SQL 2005 syntaxes):

/* Drop the SP if it exists */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Custom_Tables_Alteration]‘) AND type in (N’P‘, N’PC‘))
DROP PROCEDURE [dbo].[Custom_Tables_Alteration]
GO

/* Create the SP */
CREATE PROCEDURE Custom_Tables_Alteration
AS
BEGIN
/* This statement will move the records from the actual table that needs to be altered to a Temp Table */
SELECT * INTO [custom_table_temp] FROM [custom_table]

/* This statement will truncate the table before it is being altered and then alter the table based on the changes that you have done on Dex Dictionary */
TRUNCATE TABLE [custom_table]
ALTER TABLE [custom_table]… … …

/* This statement will move back the records from the Temp table to the Altered Table */
INSERT INTO [custom_table]([field1], [field2], …, [fieldn]) SELECT [field1], [field2], …, [fieldn] FROM [custom_table_temp]

/* This statement will grant the necessary privileges to the DYNGRP Role */
GRANT SELECT, INSERT, UPDATE, DELETE ON [custom_table] TO DYNGRP
END
GO

3. Write a Dex Trigger on a Successful Company Login, to execute this SQL Stored Procedure, by using call sproc statement. NOTE: This has to be executed only if the user logged on is SA. And we should design this in such a way that it does not alter the table as and when you log on to GP as SA. That’s very important. Well, there are so many ways using which we can validate this.

So that’s it. We are good to go with the new table definition.

Now the points that we need to understand:
1. We should take care of the relevant Table Alteration on both Dex Dictionary and SQL.
2. We should take care of the removed fields and the data which were stored on that field before this alteration.
3. If a field is added to the table and is defined as NOT NULL, then we should make sure that we include that field on the INSERT INTO … SELECT … statement in the above stored procedure and assign a relevant value (either a ZERO or an empty string ”, not NULL STRING).

This is just one way of doing the Table Alterations. There can be more. I would update this post as and when I get something better than this.

Vaidy