Creating Tables on SQL Systems

Creating Tables on SQL Systems
==============================

On non-SQL systems, tables are automatically created the first time they are referenced. However, on a SQL system we need to ensure that the user has the correct permissions before creating a table. This means that the tables must be created as part of the installation process.

Below is some example code which can be called to create tables with their Dexterity Auto Stored Procedures and grant access to both. This method does not need the creation of an AutoGrant Stored procedure to work as it grants access with pass through SQL commands.

These examples are assuming that the tables you have created have been added to a new table group of the appropriate series. If you are using Series 3rd party, you must also create entries in the SY_Pathnames table to ensure that the new tables are created in the correct datbases.

NOTE: In the script examples below, please replace the SYSTEM_TABLE_NAME placeholder with the name of your System Series table (to be created in the DYNAMICS database) and/or the COMPANY_TABLE_NAME placeholder with the name of your Company series (or Financial, Sales, Purchasing, Inventory, Payroll series) table (to be created in the current Company database).

Please create the SQLSaUser() and Grant_Table_Access() global functions first so that they can be used by the global procedures.


{ Global Function: SQLSaUser }

function returns boolean OUT_Access;

OUT_Access = false;
if 'SQL Server' of globals = 0 then
abort script;
end if;

if SQLSaUser' of globals
OUT_Access = true;
end if;

{ The following is using the new v8.00 functions }
if syUserInRole('User ID' of globals, ROLE_SYSADMIN) or
(syUserIsDBO ('User ID' of globals, 'Intercompany ID' of globals) and syUserIsDBO ('User ID' of globals, SQL_SYSTEM_DBNAME)) then
OUT_Access = true;
end if;



{ Global Function: Grant_Table_Access }

{ Grant Table Access for SQL }

function returns boolean OUT_Success;
in integer l_AltDictID;
in string IN_Table_Name;
in string IN_DB_Name;

local long l_context;
local long l_error;
local string l_physical_name;

pragma(disable warning LiteralStringUsed);

if 'SQL Server' of globals = 0 then
set OUT_Success to true;
abort script;
end if;

set l_physical_name to getPhysicalName(IN_Table_Name,l_AltDictID) of form XTableInformation;

if SQL_Connect(l_context) <> OKAY then
set OUT_Success to false;
abort script;
end if;

set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "use " + IN_DB_Name) = OKAY then
if SQL_ExecCmd(l_context) <> OKAY then
set OUT_Success to false;
abort script;
end if;
else
set OUT_Success to false;
abort script;
end if;


set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "grant all on " + l_physical_name + " to DYNGRP ") <> OKAY then
set l_error to SQL_ExecCmd(l_context);
if l_error <> OKAY then
set OUT_Success to false;
abort script;
end if;
end if;


set OUT_Success to false;

{ Now send off the big SQL Statement for finding all of the stored procedures }
set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "DECLARE @command varchar(255) ") = OKAY then
if SQL_CmdAppend(l_context, "DECLARE TheCursor CURSOR for ") = OKAY then
if SQL_CmdAppend(l_context, " select 'grant all on '+ rtrim(name) + ' to DYNGRP' ") = OKAY then
if SQL_CmdAppend(l_context, " from sysobjects where name like 'zDP_"+l_physical_name+"%' ") = OKAY then
if SQL_CmdAppend(l_context, "set NOCOUNT on ") = OKAY then
if SQL_CmdAppend(l_context, "open TheCursor ") = OKAY then
if SQL_CmdAppend(l_context, "FETCH NEXT FROM TheCursor INTO @command ") = OKAY then
if SQL_CmdAppend(l_context, "while(@@fetch_status <> -1) begin ") = OKAY then
if SQL_CmdAppend(l_context, " if (@@fetch_status <> -2) begin ") = OKAY then
if SQL_CmdAppend(l_context, " exec (@command) ") = OKAY then
if SQL_CmdAppend(l_context, " end ") = OKAY then
if SQL_CmdAppend(l_context, " FETCH NEXT FROM TheCursor INTO @command ") = OKAY then
if SQL_CmdAppend(l_context, "end ") = OKAY then
if SQL_CmdAppend(l_context, "DEALLOCATE TheCursor ") = OKAY then
if SQL_CmdAppend(l_context, "set NOCOUNT off ") = OKAY then
set l_error to SQL_ExecCmd(l_context);
if l_error <> OKAY then
set OUT_Success to false;
abort script;
else
set OUT_Success to true;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;


if SQL_Terminate(l_context) <> OKAY then
end if;

pragma(enable warning LiteralStringUsed);



{ Global Procedure: Create_System_Tables }

if 'SQL Server' of globals = 0 then
abort script;
end if;

if not SQLSaUser() then
abort script;
end if;

Table_SetCreateMode(true); {Turns on create ability}
open table SYSTEM_TABLE_NAME
close table SYSTEM_TABLE_NAME
Grant_Table_Access(Runtime_GetCurrentProductID(), technicalname(table SYSTEM_TABLE_NAME), SQL_SYSTEM_DBNAME);

{ Repeat for other System Tables}

Table_SetCreateMode(true); {Turns off create ability}



{ Global Procedure: Create_Company_Tables }

if 'SQL Server' of globals = 0 then
abort script;
end if;

if not SQLSaUser() then
abort script;
end if;

Table_SetCreateMode(true); {Turns on create ability}
open table COMPANY_TABLE_NAME
close table COMPANY_TABLE_NAME
Grant_Table_Access(Runtime_GetCurrentProductID(), technicalname(table COMPANY_TABLE_NAME), 'Intercompany ID' of globals);

{ Repeat for other Company Tables}

Table_SetCreateMode(true); {Turns off create ability}


David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions
http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.