Creating SQL tables

If your integrating application will be using its own tables, it must provide some method of creating them on the SQL server. Some integrating applications use a separate window to create SQL tables, but this isn’t an ideal solution. When creating tables for your integrating application, we recommend that you use a technique similar to that used by the sample integrating application.

Two issues must be solved when creating SQL tables:

• You must be logged into SQL, or an additional SQL Login dialog will be displayed when the tables are created.
• The current SQL user must have create permissions, or an error will occur when tables are created.

To resolve these issues, the sample integrating application creates SQL tables for the current company, only after the user has logged into the system as “DYNSA” or “sa”. This ensures that the no additional login dialog is displayed, and that no creation errors will occur since the administrator always has create privileges. Refer to Database security for more information.

The following focus trigger is used to create SQL tables for the sample integrating application.

local integer l_result;

l_result = Trigger_RegisterProcedure(script Add_Successful_Login_Record, TRIGGER_AFTER_ORIGINAL, script IG_Setup_SQL_Tables);
if l_result <> SY_NOERR then
	warning "Procedure trigger registration for setting up tables failed.";
end if;

The trigger is activated only after the user has logged into a company and to the appropriate SQL database. This prevents the additional login dialog from being displayed.

The following code is the trigger processing procedure that creates the SQL tables. The procedure uses a helper procedure that creates the table and then grants access to the table and the table’s stored procedures. This is described in following section, Granting SQL access privileges. It also calls another helper procedure to bind default values to the columns in each of the tables created. This is described in Assigning default values to columns.

if 'SQL Server' of globals > 0 then
	{Is the system administrator logging in?}
	if ('User ID' of globals = "sa") or ('User ID' of globals = "DYNSA") then

		call IG_Create_Table, "IG_Contact_History_MSTR";
		call IG_Create_Table, "IG_Contact_History_SETP";
		call IG_Create_Table, "IG_Leads_MSTR";
		call IG_Create_Table, "IG_Leads_ROPT";

		call IG_BindSQLDefaults;
	end if;
end if;

Documentation Feedback

---