As the administrator, you can add a custom message on the Connection Manager dialog. The message appears whenever a connection is made to the mart from one of the client applications (CA ERwin Data Modeler or CA ERwin Data Modeler Navigator Edition). This custom message appears after you are authenticated for connection to the desired mart, but before the connection dialog closes.
A sample stored procedure is provided for each supported database in the Samples\Stored Procedures folder. You can modify the sample to change the text or a custom procedure can be written with logic to determine the database user ID and lookup table for an appropriate message to appear. The message can be up to 1000 characters long and the procedure should return 4 separate strings each a maximum of 250 characters in length.
Example: Stored procedure for returning a custom message at connection for a Microsoft SQL Server or Sybase database
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.m7x_Get_Privacy_Message'))
DROP PROCEDURE dbo.m7x_Get_Privacy_Message
go
CREATE PROCEDURE dbo.m7x_Get_Privacy_Message
@string1 varchar(250) output,
@string2 varchar(250) output,
@string3 varchar(250) output,
@string4 varchar(250) output
AS
BEGIN
--Declare
-- Ensure to initialize strings to avoid un-necessary results
SELECT @string1 = '',
@string2 = '',
@string3 = '',
@string4 = ''
-- Add custom code here for extra validations
/* Formatted message would go here. Ensure that the content of the message does not exceed 1000 chars. Failure to do so results in truncation */
Select @string1 = 'This stored procedure will be implemented by the customer based on their current requirements. Depending on the DBMS additional validations can be made by the end user to suit their privacy requirements.'
Select @string2 = Char(13) + Char(10) + 'Currently the procedure can return up to 1000 characters. Customer responsible for limiting each of the return strings to <= 250 chars, other wise there could be unexpected errors returned by server.'
Select @string3 = ' Use native DBB functions for special ASCII characters like CRFL, LF, TAB etc.,'
Select @string4 = Char(13) + Char(10) + 'Prior to exiting the proc make sure to limit the strings to 250 chars'
-- Safety check to limit 250 chars
SELECT @string1 = left(@string1, 250),
@string2 = left(@string2, 250),
@string3 = left(@string3, 250),
@string4 = left(@string4, 250)
END
go
GRANT ALL ON m7x_Get_Privacy_Message TO PUBLIC
go
Example: Stored procedure for returning a custom message at connection for an Oracle database
CREATE OR REPLACE PROCEDURE m7x_Get_Privacy_Message (
p$string1 IN OUT varchar2,
p$string2 IN OUT varchar2,
p$string3 IN OUT varchar2,
p$string4 IN OUT varchar2,
p$gen_err_code IN OUT NUMBER
)
AS
-- Declarations here
BEGIN
-- Ensure the return parameter is set to Zero for success
p$gen_err_code := 0;
p$string1 := '';
p$string2 := '';
p$string3 := '';
p$string4 := '';
-- Add custom code here for extra validations
-- Formatted message would go here. Ensure that the content of the message does not exceed 1000 chars. Failure to do so results in truncation */
p$string1 := 'This stored procedure will be implemented by the customer based on their current requirements. Depending on the DBMS additional validations can be made by the end user to suit their privacy requirements.';
p$string2 := Chr(13) || Chr(10) || 'Customer responsible for limiting each of the return strings to <= 250 chars, otherwise there could be unexpected errors returned by server.';
p$string3 := ' Use native DB functions for special ASCII characters like CRFL, LF, TAB etc.,';
p$string4 := Chr(13) || Chr(10) || 'Prior to exiting the proc make sure to limit the strings to 250 chars';
-- Safety check to limit 250 chars
p$string1 := SubStr(p$string1, 1, 250);
p$string2 := SubStr(p$string2, 1, 250);
p$string3 := SubStr(p$string3, 1, 250);
p$string4 := SubStr(p$string4, 1, 250);
RETURN;
END m7x_Get_Privacy_Message;
/
DROP PUBLIC SYNONYM m7x_Get_Privacy_Message
CREATE PUBLIC SYNONYM m7x_Get_Privacy_Message FOR m7x_Get_Privacy_Message
GRANT ALL ON m7x_Get_Privacy_Message TO PUBLIC
/
| Copyright © 2011 CA. All rights reserved. | Email CA Technologies about this topic |