Coder Forum

SQL Code

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
ALTER  PROCEDURE [dbo].[Recipient_EnumerateVE] (
	@Pending INT = 0,
	@JobPrefixId INT = 101,
	@JobId INT = 727,
	@RecipientId INT = 0,
	@FailedOnly INT = 0
)
 
AS
 
/* Description : Return list of recipients based on JobId and other supplied
 *		 parameters.  VENALI EXPRESS FLAVOUR
 * In          : @Pending - Return only recipients for jobs in a pending state
 *		 @JobPrefixId - Identifier of gateway submitting job
 *		 @JobId - Identifier of job whose recipients are to be returned
 *		 @RecipientId - Identifier of recipient to return
 *		 @FailedOnly - Return only those recipients with a completed
 *		 status of failed.
 * Out	       : Two recordsets:
 *			Summary Totals for job, listing successful, failed and
 *			pending recipients.  Values returned are based only on
 *			the JobId and do not reflect effects of other parameters.
 *			Recipient List, return all recipients that meet the
 *			selection parameters provided.
 * Notes       : Based on the selection criteria, return the list of recipients for
 *		 given job.  Create summary totals are return those as well.
 * History     :
 *
 * Date       Who Action    Notes
 * --------------------------------------------------------------------------
 * 13/07/2011 MAS Created
 */
 
DECLARE	@ReturnCode INT,
	@RecordCount INT,
	@JobSuccessCount INT,
	@JobFailureCount INT,
	@JobPendingCount INT,
	@SystemJobId INT,
	@MessageStringShort varchar(32),
	@MessageStringLong varchar(128)
 
SET NOCOUNT ON
 
	-- Logging: Start a System Job and log the SP parameters
	SELECT @MessageStringShort = 'Recipient_EnumerateWork<' + convert(varchar, @JobPrefixId) + '-' + convert(varchar, @JobId) + '>'
	EXEC @SystemJobId = [UTIL_BeginSystemJob]
		@JobType = 3,
		@JobTag = @MessageStringShort,
		@OwnerJobPrefixId = @JobPrefixId,
		@OwnerJobId = @JobId,
		@OwnerRecipientId = @RecipientId
 
	SELECT @MessageStringLong = 'Returning recipient list with the following parameters: @Pending = ' + convert(varchar, ISNULL(@Pending, 0)) + ', '
	SELECT @MessageStringLong = @MessageStringLong + '@JobPrefixId = ' + convert(varchar, ISNULL(@JobPrefixId, 0)) + ', '
	SELECT @MessageStringLong = @MessageStringLong + '@JobId = ' + convert(varchar, ISNULL(@JobId, 0)) + ', '
	SELECT @MessageStringLong = @MessageStringLong + '@RecipientId = ' + convert(varchar, ISNULL(@RecipientId, 0)) + ', '
	SELECT @MessageStringLong = @MessageStringLong + '@FailedOnly = ' + convert(varchar, ISNULL(@FailedOnly, 0))
 
	EXEC [dbo].[UTIL_LogSystemJobDetail]
		@JobId = @SystemJobId,
		@JobDetail = @MessageStringLong
 
	-- Create temporary table to hold the selected recipients
	CREATE TABLE #RecptResultSet(JobPrefixId INT NOT NULL, JobId INT NOT NULL, RecipientId INT NOT NULL,
		EventStatusId INT NOT NULL, EventDlvryECD VARCHAR(32) NULL, EventStopDt DATETIME NULL,
		EventFaxNumber VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , EventRecipientName NVARCHAR(64) NULL, EventRecipientCompanyName NVARCHAR(32) NULL,
		EventDlvryAttempts SMALLINT NOT NULL, ErrorSource INT NULL, ErrorCode INT NULL,
		ErrorClass INT NULL, ErrorExtra INT NULL, ErrorDesc VARCHAR(100) NULL, EventStatusDesc VARCHAR(100) NULL,
		EventSubmitDt DATETIME, EventStartDt DATETIME, EventDuration smallint, EventDlvrySpeed VARCHAR(8),
		DlvryServerRecptRecvCSID VARCHAR(39), PageCt int, CoverSubject NVARCHAR(128),UserField1 NVARCHAR(32),EventDlvryCurrentPageCt int,
		RecipTitle nvarchar (32) NULL,RecipFirstName nvarchar (32) NULL,RecipLastName nvarchar (32) NULL,
		RecipAddress1 nvarchar (55) NULL ,RecipAddress2 nvarchar (55) NULL ,RecipCity nvarchar (29) NULL,
		RecipState nvarchar (19) NULL ,RecipZip nvarchar (19) NULL ,RecipCountry nvarchar (35) NULL , RecipVoice nvarchar (34) NULL,
                RecipField1 nvarchar(32) NULL,
		RecipField2 nvarchar(32) NULL, RecipField3 nvarchar(32) NULL, RecipField4 nvarchar(32) NULL, RecipField5 nvarchar(32) NULL,
		RecipField6 nvarchar(32) NULL, RecipField7 nvarchar(32) NULL, RecipField8 nvarchar(32) NULL, RecipField9 nvarchar(32) NULL,
		RecipField10 nvarchar(32) NULL, RecipField11 nvarchar(32) NULL, RecipField12 nvarchar(32) NULL,
        	SenderName nvarchar (64) NULL, SenderCompany nvarchar (34) NULL,SenderAddress1 nvarchar (55) NULL,
                SenderAddress2 nvarchar (55) NULL,SenderCity nvarchar (29) NULL,SenderState nvarchar (19) NULL,SenderZip nvarchar (19) NULL,
                SenderCountry nvarchar (35) NULL,SenderVoice nvarchar (34) NULL,SenderFax nvarchar (34) NULL,
                EventFaxNumberCountryCode VARCHAR(8) NULL , EventFaxNumberCountryName VARCHAR(64) NULL ,
                FailureBRIPErrorDesc VARCHAR(100) NULL, FailureBRIPErrorCode INT NULL,
                FailureReportLongString VARCHAR(23) NULL,
                FailureErrorEmailNotification VARCHAR(1000) NULL, FailureErrorWSResponse VARCHAR(1000) NULL
                )
 
	-- Initialise
	SELECT	@ReturnCode = 0,
		@RecordCount = 0,
		@JobSuccessCount = 0,
		@JobFailureCount = 0,
		@JobPendingCount = 0
 
	-- Get completed as well as pending recipients
	INSERT	#RecptResultSet(JobPrefixId, JobId, RecipientId, EventStatusId, EventDlvryECD, EventStopDt, EventFaxNumber,
		EventRecipientName, EventRecipientCompanyName, 	EventDlvryAttempts, ErrorSource, ErrorCode, ErrorClass,
		ErrorExtra, ErrorDesc, EventStatusDesc, EventSubmitDt, EventStartDt, EventDuration, EventDlvrySpeed,
		DlvryServerRecptRecvCSID, PageCt, CoverSubject, UserField1,EventDlvryCurrentPageCt,
		RecipTitle,RecipFirstName,RecipLastName,RecipAddress1,RecipAddress2,RecipCity,
		RecipState,RecipZip,RecipCountry,RecipVoice,
                RecipField1,RecipField2,RecipField3,RecipField4,RecipField5,RecipField6,
                RecipField7,RecipField8,RecipField9,RecipField10,RecipField11,RecipField12,
        	SenderName,SenderCompany,SenderAddress1,
                SenderAddress2,SenderCity,SenderState,SenderZip,
                SenderCountry,SenderVoice,SenderFax)
                EXEC [dbo].[Recipient_EnumerateWorker] 	@Pending, @JobPrefixId, @JobId, @RecipientId, @FailedOnly, 
                                                        @RecordCount OUTPUT, @JobSuccessCount OUTPUT, @JobFailureCount OUTPUT, @JobPendingCount OUTPUT
 
	CREATE NONCLUSTERED INDEX [idx_RecipientJobId] ON #RecptResultSet
	(
		[JobPrefixId], [JobId], [RecipientId]
	)
 
	EXEC [dbo].[UTIL_LogSystemJobDetail]
		@JobId = @SystemJobId,
		@JobDetail = 'Adding EventFaxNumber details'
 
	-- Get the friendly description of the event status
	UPDATE	r
	SET	EventFaxNumberCountryCode = '1',
                EventFaxNumberCountryName = 'USA/Canada'
	FROM	#RecptResultSet r
        WHERE LEFT(EventFaxNumber,1) = '1'
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
        WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 6
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
        WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 5
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
        WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 4
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
       WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 3
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
        WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 2
 
	UPDATE	r
	SET	EventFaxNumberCountryCode = idc.DialingCode,
                EventFaxNumberCountryName = idc.CountryName
	FROM	#RecptResultSet r
	INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
        WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 1
 
	EXEC [dbo].[UTIL_LogSystemJobDetail]
		@JobId = @SystemJobId,
		@JobDetail = 'Added Failure details'
 
	UPDATE	r
	SET	FailureBRIPErrorDesc = 'Success',
		FailureBRIPErrorCode = 0,
		FailureReportLongString = 'Success'
	FROM	#RecptResultSet r
        WHERE EventStatusId=1
 
	UPDATE	r
	SET	FailureBRIPErrorDesc = em.BRIPErrorDesc,
		FailureBRIPErrorCode = em.BRIPErrorCode,
		FailureReportLongString = em.ReportLongString,
		FailureErrorEmailNotification = em.ErrorEmailNotification,
		FailureErrorWSResponse = em.ErrorWSResponse
	FROM	#RecptResultSet r
	INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
                                       r.ErrorClass=em.ECDErrortype AND
                                       r.ErrorCode=em.ECDErrorCode AND
                                       r.ErrorExtra=em.ECDErrorExtra
        WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
 
	UPDATE	r
	SET	FailureBRIPErrorDesc = em.BRIPErrorDesc,
		FailureBRIPErrorCode = em.BRIPErrorCode,
		FailureReportLongString = em.ReportLongString,
		FailureErrorEmailNotification = em.ErrorEmailNotification,
		FailureErrorWSResponse = em.ErrorWSResponse
	FROM	#RecptResultSet r
	INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
                                       r.ErrorClass=em.ECDErrortype AND
                                       r.ErrorCode=em.ECDErrorCode AND
                                       0=em.ECDErrorExtra
        WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
 
	UPDATE	r
	SET	FailureBRIPErrorDesc = em.BRIPErrorDesc,
		FailureBRIPErrorCode = em.BRIPErrorCode,
		FailureReportLongString = em.ReportLongString,
		FailureErrorEmailNotification = em.ErrorEmailNotification,
		FailureErrorWSResponse = em.ErrorWSResponse
	FROM	#RecptResultSet r
	INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
                                       r.ErrorClass=em.ECDErrortype AND
                                       0=em.ECDErrorCode AND
                                       0=em.ECDErrorExtra
        WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
 
	UPDATE	r
	SET	FailureBRIPErrorDesc = em.BRIPErrorDesc,
		FailureBRIPErrorCode = em.BRIPErrorCode,
		FailureReportLongString = em.ReportLongString,
		FailureErrorEmailNotification = em.ErrorEmailNotification,
		FailureErrorWSResponse = em.ErrorWSResponse
	FROM	#RecptResultSet r
	INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
                                       0=em.ECDErrortype AND
                                       0=em.ECDErrorCode AND
                                       0=em.ECDErrorExtra
        WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
 
	-- Return the summary totals for the Job
	SELECT 	@RecordCount AS [RecordCount],	
		@JobSuccessCount AS [JobSuccessCount],
		@JobFailureCount AS [JobFailureCount],
		@JobPendingCount AS [JobPendingCount]
 
	EXEC [dbo].[UTIL_LogSystemJobDetail]
		@JobId = @SystemJobId,
		@JobDetail = 'Returning recipient list'
 
	-- Return the list of all recipients for the Job
	SELECT	*
	FROM 	#RecptResultSet
	ORDER BY JobPrefixId,
		JobId,
		RecipientId
 
	EXEC [dbo].[UTIL_LogSystemJobDetail]
		@JobId = @SystemJobId,
		@JobDetail = 'Recipient list return complete'
 
	-- Destroy temporary objects
	DROP
	TABLE	#RecptResultSet
 
	EXEC [dbo].[UTIL_EndSystemJob]
		@JobId = @SystemJobId
 
SET NOCOUNT OFF
 
	RETURN @ReturnCode
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

PHP Quellcode

<?php
 
$Pending = "0";  
$Prefix = "101";
$JobId = "315";
$RecipientId = "0";
$FailedOnly = "1";
 
// Connect to MSSQL and select the database
mssql_connect('expsq002', 'sa', 'MYPW!');
mssql_select_db('db_data');
 
// Create a new stored prodecure
$stmt = mssql_init('Recipient_Enumerate');
 
//mssql_bind($stmt, '@Pending',$Pending,SQLINT4,false,false,3);
mssql_bind($stmt, '@JobPrefixId',$Prefix,SQLINT4,false,false,3);
mssql_bind($stmt, '@JobId',$JobId,SQLINT4,false,false,3);
//mssql_bind($stmt, '@RecipientId',$RecipientId,SQLINT4,false,false,3);
//mssql_bind($stmt, '@FailedOnly',$FailedOnly,SQLINT4,false,false,3);
 
//mssql_bind($stmt, "@ReturnCode", $lala, SQLVARCHAR, true, true);
$result = mssql_execute($stmt);
$numProds = mssql_num_rows($result); 
while($row = mssql_fetch_row($result))
 
{
var_dump($row);
 
 
}
 
 
 
 
?>

HTML Code

Warning: mssql_execute(): message: Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails. (severity 16) in /var/www/reporting/test.php on line 23 Warning: mssql_execute(): General SQL Server error: Check messages from the SQL Server (severity 16) in /var/www/reporting/test.php on line 23 array(4) { [0]=> int(0) [1]=> int(0) [2]=> int(0) [3]=> int(0) }