Solved

Delphi XE4 ADO Stored Procedure

Posted on 2013-11-19
3
936 Views
Last Modified: 2013-11-25
Hi, Experts.

I have a Delphi XE4 service program and it is generating an error, but I'm not able to figure out what I'm doing wrong.

Here's my code snippet:

          
if not ADOQuery2.Eof then
          begin
            ADOQuery2.First;
            while not ADOQuery2.Eof do
            begin
              if FLogDebug then
                LogMessage('TCyCalApptSyncThread.ProcessDB Got data for SQLAppointmentId = ' + IntToStr(iSQLId), EVENTLOG_INFORMATION_TYPE, 0, 1);
              sSubject := ADOQuery2.FieldByName('Subject').AsString;
              sBody := ADOQuery2.FieldByName('Body').AsString;
              sLocation := ADOQuery2.FieldByName('Location').AsString;
              sDisposition := ADOQuery2.FieldByName('Disposition').AsString;
              sDispositionRemarks := ADOQuery2.FieldByName('DispositionRemarks').AsString;
              sStaffId := ADOQuery2.FieldByName('StaffId').AsString;
              sTeamId := ADOQuery2.FieldByName('TeamId').AsString;
              sCalendarType := ADOQuery2.FieldByName('CalendarType').AsString;
              sSQLAppointmentClass := ADOQuery2.FieldByName('SQLAppointmentClass').AsString;
              sAddBy := ADOQuery2.FieldByName('AddBy').AsString;
              sEditBy := ADOQuery2.FieldByName('EditBy').AsString;
              sCourtDivision := ADOQuery2.FieldByName('CourtDivision').AsString;
              sEntryId := ADOQuery2.FieldByName('EntryId').AsString;
              sExclusiveTo := ADOQuery2.FieldByName('ExclusiveTo').AsString;
              sModuleCode := ADOQuery2.FieldByName('ModuleCode').AsString;
              sName := ADOQuery2.FieldByName('Name').AsString;
              sPriKey := ADOQuery2.FieldByName('PriKey').AsString;
              sRelKey := ADOQuery2.FieldByName('RelKey').AsString;
              sSecKey := ADOQuery2.FieldByName('SecKey').AsString;
              sSerial := ADOQuery2.FieldByName('Serial').AsString;
              dtStart := ADOQuery2.FieldByName('Start').AsDateTime;
              dtEnd := ADOQuery2.FieldByName('End').AsDateTime;
              dtDispositionDate := ADOQuery2.FieldByName('DispositionDate').AsDateTime;
              dtAddTime := ADOQuery2.FieldByName('AddTime').AsDateTime;
              dtEditTime := ADOQuery2.FieldByName('EditTime').AsDateTime;
              bShowInFileIndex := ADOQuery2.FieldByName('ShowInFileIndex').AsBoolean;
              bIsHistorical := ADOQuery2.FieldByName('IsHistorical').AsBoolean;
              bAlarmPending := ADOQuery2.FieldByName('AlarmPending').AsBoolean;
              bAutoEntry := ADOQuery2.FieldByName('AutoEntry').AsBoolean;
              bCourtCalendar := ADOQuery2.FieldByName('CourtCalendar').AsBoolean;
              iFromCycom := ADOQuery2.FieldByName('FromCycom').AsInteger;
              bManualChange := ADOQuery2.FieldByName('ManualChange').AsBoolean;
              bSubpoena := ADOQuery2.FieldByName('Subpoena').AsBoolean;
              iMatterId := ADOQuery2.FieldByName('MatterId').AsInteger;
              iDocumentId := ADOQuery2.FieldByName('DocumentId').AsInteger;
  //            sDocPath := GetDocPath(iDocumentId);
              sDocPath := '';
              iDocumentListId := ADOQuery2.FieldByName('DocumentListId').AsInteger;
              iPRDefendantId := ADOQuery2.FieldByName('PRDefendantId').AsInteger;
              iAutoCalRoot := ADOQuery2.FieldByName('AutoCalRoot').AsInteger;
              iAutoCalLnk := ADOQuery2.FieldByName('AutoCalLnk').AsInteger;
              iAutoCalType := ADOQuery2.FieldByName('AutoCalType').AsInteger;
              iRuleNo := ADOQuery2.FieldByName('RuleNo').AsInteger;
              bOutlook := ADOQuery2.FieldByName('Outlook').AsBoolean;
              iCompulaw := ADOQuery2.FieldByName('Compulaw').AsInteger;

              SP := TADOStoredProc.Create(nil);
              try
                SP.Connection := ADOConn;
                SP.ProcedureName := 'UpdateCalendar';
                SP.Parameters.CreateParameter('@CAL_ID', ftInteger, pdInput, 10, iId);
                SP.Parameters.CreateParameter('@MATTER_ID', ftInteger, pdInput, 10, iMatterId);
                SP.Parameters.CreateParameter('@DOC_ID', ftInteger, pdInput, 10, iDocumentId);
                SP.Parameters.CreateParameter('@SCHED_DATE', ftDatetime, pdInput, 25, dtStart);
                SP.Parameters.CreateParameter('@SCHED_TIME', ftDatetime, pdInput, 25, dtStart);
                SP.Parameters.CreateParameter('@LOCATION', ftString, pdInput, 200, sLocation);
                SP.Parameters.CreateParameter('@STAFF', ftString, pdInput, 6, sStaffId);
                SP.Parameters.CreateParameter('@TEAM', ftString, pdInput, 6, sTeamId);
                SP.Parameters.CreateParameter('@EVENT', ftString, pdInput, 70, sSubject);
                SP.Parameters.CreateParameter('@CAL_TYPE', ftString, pdInput, 12, sCalendarType);
                SP.Parameters.CreateParameter('@DISPO', ftString, pdInput, 10, sDisposition);
                SP.Parameters.CreateParameter('@COMPL_DATE', ftDatetime, pdInput, 25, dtDispositionDate);
                SP.Parameters.CreateParameter('@DISPO_REM', ftString, pdInput, 250, sDispositionRemarks);
                SP.Parameters.CreateParameter('@NAME', ftString, pdInput, 10, sName);
                SP.Parameters.CreateParameter('@CLASS', ftString, pdInput, 1, Copy(sSqlAppointmentClass, 1, 1));
                SP.Parameters.CreateParameter('@WCLASS', ftString, pdInput, 1, Copy(sSqlAppointmentClass, 1, 1));
                SP.Parameters.CreateParameter('@AUTOCALROOT', ftInteger, pdInput, 10, iAutoCalRoot);
                SP.Parameters.CreateParameter('@AUTOCAL_LNK', ftInteger, pdInput, 10, iAutoCalLnk);
                SP.Parameters.CreateParameter('@PRI_KEY', ftString, pdInput, 10, sPriKey);
                SP.Parameters.CreateParameter('@REL_KEY', ftString, pdInput, 10, sRelKey);
                SP.Parameters.CreateParameter('@SEC_KEY', ftString, pdInput, 10, sSecKey);
                SP.Parameters.CreateParameter('@MODULE', ftString, pdInput, 3, sModuleCode);
                SP.Parameters.CreateParameter('@SERIAL', ftString, pdInput, 10, sSerial);
                SP.Parameters.CreateParameter('@AUTOCAL_TYPE', ftInteger, pdInput, 10, iAutoCalType);
                SP.Parameters.CreateParameter('@RULE_NO', ftInteger, pdInput, 10, iRuleNo);
                SP.Parameters.CreateParameter('@HISTORY', ftBoolean, pdInput, 1, bIsHistorical);
                { this parameter gets sEditBy if editing, or sAddBy if new }
                if iId = 0 then
                  SP.Parameters.CreateParameter('@ADDEDIT_BY', ftString, pdInput, 10, sAddBy)
                else
                  SP.Parameters.CreateParameter('@ADDEDIT_BY', ftString, pdInput, 10, sEditBy);
                SP.Parameters.CreateParameter('@IN_DOCLIST', ftBoolean, pdInput, 1, bShowInFileIndex);
                SP.Parameters.CreateParameter('@DOCLIST_ID', ftInteger, pdInput, 10, iDocumentListId);
                SP.Parameters.CreateParameter('@SUBPOENA', ftBoolean, pdInput, 1, bSubpoena);
                SP.Parameters.CreateParameter('@COURT_DIV', ftString, pdInput, 6, sCourtDivision);
                SP.Parameters.CreateParameter('@COURT_CAL', ftBoolean, pdInput, 1, bCourtCalendar);
                SP.Parameters.CreateParameter('@PRDE_ID', ftInteger, pdInput, 10, iPRDefendantId);
                SP.Parameters.CreateParameter('@ALARM_PENDING', ftBoolean, pdInput, 1, bAlarmPending);
                SP.Parameters.CreateParameter('@AUTO_ENTRY', ftBoolean, pdInput, 1, bAutoEntry);
                SP.Parameters.CreateParameter('@OUTLOOK', ftBoolean, pdInput, 1, bOutlook);
                SP.Parameters.CreateParameter('@ENTRYID', ftString, pdInput, 256, sEntryId);
                SP.Parameters.CreateParameter('@EXCLUSIVE_TO', ftString, pdInput, 3, sExclusiveTo);
                SP.Parameters.CreateParameter('@FROMCYCOM', ftInteger, pdInput, 10, iFromCycom);
                SP.Parameters.CreateParameter('@MANUALCHNG', ftBoolean, pdInput, 1, bManualChange);
                SP.Parameters.CreateParameter('@COMPULAW', ftInteger, pdInput, 10, iCompulaw);
                SP.Parameters.CreateParameter('@ENDDATE', ftDatetime, pdInput, 25, dtEnd);
                SP.Parameters.CreateParameter('@Note', ftMemo, pdInput, Length(sBody), sBody);
                SP.Parameters.CreateParameter('@DocPath', ftString, pdInput, 256, sDocPath);
                if FLogDebug then
                  LogMessage('TCyCalApptSyncThread.ProcessDB UpdateCalendar About to execute', EVENTLOG_INFORMATION_TYPE, 0, 1);
                SP.ExecProc;
                if FLogDebug then
                  LogMessage('TCyCalApptSyncThread.ProcessDB UpdateCalendar executed ok', EVENTLOG_INFORMATION_TYPE, 0, 1);
              finally
                SP.Free;
              end;

Open in new window


The error occurs when it gets to "SP.ExecProc;"  I know this because my debugging line just before it gets successfully posted to the event log, but the one after SP.ExecProc  does not.

The error says:  "Parameter object is improperly defined. Inconsistent or incomplete information was provided"

Here's the actual stored procedure (currently running on SQL Server 2008 R2, but must support SQL 2005 through SQL 2012)

ALTER PROCEDURE [dbo].[UpdateCalendar]
-- Add the parameters for the stored procedure here
@CAL_ID int,
@MATTER_ID int,
@DOC_ID int,
@SCHED_DATE datetime,
@SCHED_TIME datetime,
@LOCATION varchar(200),
@STAFF varchar(6),
@TEAM varchar(6),
@EVENT varchar(70),
@CAL_TYPE varchar(10),
@DISPO varchar(10),
@COMPL_DATE datetime,
@DISPO_REM varchar(250),
@NAME varchar(10),
@CLASS varchar(1),
@WCLASS varchar(1),
@AUTOCALROOT smallint,
@AUTOCAL_LNK int,
@PRI_KEY varchar(10),
@REL_KEY varchar(10),
@SEC_KEY varchar(10),
@MODULE varchar(3),
@SERIAL varchar(10),
@AUTOCAL_TYPE int,
@RULE_NO smallint,
@HISTORY bit,
@ADDEDIT_BY varchar(10),
@IN_DOCLIST bit,
@DOCLIST_ID int,
@SUBPOENA bit,
@COURT_DIV varchar(6),
@COURT_CAL bit,
@PRDE_ID int,
@ALARM_PENDING bit,
@AUTO_ENTRY bit,
@OUTLOOK bit,
@ENTRYID varchar(256),
@EXCLUSIVE_TO varchar(3),
@FROMCYCOM int,
@MANUALCHNG bit,
@COMPULAW integer,
@ENDDATE datetime,
@Note Text,
@DocPath varchar(256)
AS
BEGIN
DECLARE @Doc_List_ID_New INT
SET @Doc_List_ID_New=0
DECLARE @ResultSPs int,@ResultDocListSPs INT
BEGIN TRANSACTION
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF(@CAL_ID=0)
BEGIN
IF(@IN_DOCLIST=0)
BEGIN
SET @Doc_List_ID_New=-1
END
ELSE
BEGIN
EXEC UpdateDocList @MATTER_ID,@DOCLIST_ID,@EVENT,@AddEdit_BY,@DocPath,'',
'',2,'',@MODULE,'',0,1,@Doc_List_ID_New output,@ResultDocListSPs output
--2- Source for Calendar relatd DOCLIST entry
--Prepared by='',IndexClass=''
END
DECLARE @CAL_ID_NEW INT
EXEC PROC_AUTOID @CAL_ID_NEW OUTPUT
-- Insert statements for procedure here
INSERT INTO [CALENDAR]
([CAL_ID]
,[MATTER_ID]
,[DOC_ID]
,[SCHED_DATE]
,[SCHED_TIME]
,[LOCATION]
,[STAFF]
,[TEAM]
,[EVENT]
,[CAL_TYPE]
,[DISPO]
,[COMPL_DATE]
,[DISPO_REM]
,[NAME]
,[CLASS]
,[WCLASS]
,[AUTOCALROOT]
,[AUTOCAL_LNK]
,[PRI_KEY]
,[REL_KEY]
,[SEC_KEY]
,[MODULE]
,[SERIAL]
,[AUTOCAL_TYPE]
,[RULE_NO]
,[HISTORY]
,[ADD_TIME]
,[ADD_BY]
,[IN_DOCLIST]
,[DOCLIST_ID]
,[SUBPOENA]
,[COURT_DIV]
,[COURT_CAL]
,[PRDE_ID]
,[ALARM_PENDING]
,[AUTO_ENTRY]
,[OUTLOOK]
,[ENTRYID]
,[EXCLUSIVE_TO]
,[FROMCYCOM]
,[MANUALCHNG]
,[COMPULAW]
,[ENDDATE])
VALUES
(@CAL_ID_NEW
,@MATTER_ID
,@DOC_ID
,@SCHED_DATE
,@SCHED_TIME
,@LOCATION
,@STAFF
,@TEAM
,@EVENT
,@CAL_TYPE
,@DISPO
,@COMPL_DATE
,@DISPO_REM
,@NAME
,@CLASS
,@WCLASS
,@AUTOCALROOT
,@AUTOCAL_LNK
,@PRI_KEY
,@REL_KEY
,@SEC_KEY
,@MODULE
,@SERIAL
,@AUTOCAL_TYPE
,@RULE_NO
,@HISTORY
,GETDATE()
,@ADDEDIT_BY
,@IN_DOCLIST
,@Doc_List_ID_New
,@SUBPOENA
,@COURT_DIV
,@COURT_CAL
,@PRDE_ID
,@ALARM_PENDING
,@AUTO_ENTRY
,@OUTLOOK
,@ENTRYID
,@EXCLUSIVE_TO
,@FROMCYCOM
,@MANUALCHNG
,@COMPULAW
,@ENDDATE)
IF(@Note IS NOT NULL)
BEGIN
INSERT INTO Memos (TableName, Name, RecNo, Sequence, Notes)
VALUES('CALENDAR', 'DETAIL', @CAL_ID_NEW, 1, @Note)
END
END
ELSE
BEGIN
IF(@IN_DOCLIST=1)
BEGIN
EXEC UpdateDocList @MATTER_ID,@DOCLIST_ID,@EVENT,@AddEdit_BY,@DocPath,'',
'',2,'',@MODULE,'',0,1,@Doc_List_ID_New output,@ResultDocListSPs output
END
ELSE
BEGIN
IF(@DOCLIST_ID>0)
BEGIN
DELETE FROM DOCLIST WHERE MATTER_ID=@MATTER_ID and
DOCLIST_ID=@DOCLIST_ID
END
SET @Doc_List_ID_New=-1
END
UPDATE [CALENDAR]
SET
[DOC_ID] = @DOC_ID
,[SCHED_DATE] = @SCHED_DATE
,[SCHED_TIME] = @SCHED_TIME
,[LOCATION] = @LOCATION
,[STAFF] = @STAFF
,[TEAM] = @TEAM
,[EVENT] = @EVENT
,[CAL_TYPE] = @CAL_TYPE
,[DISPO] = @DISPO
,[COMPL_DATE] = @COMPL_DATE
,[DISPO_REM] = @DISPO_REM
,[NAME] = @NAME
,[CLASS] = @CLASS
,[WCLASS] = @WCLASS
,[AUTOCALROOT] = @AUTOCALROOT
,[AUTOCAL_LNK] = @AUTOCAL_LNK
,[PRI_KEY] = @PRI_KEY
,[REL_KEY] = @REL_KEY
,[SEC_KEY] = @SEC_KEY
,[MODULE] = @MODULE
,[SERIAL] = @SERIAL
,[AUTOCAL_TYPE] = @AUTOCAL_TYPE
,[RULE_NO] = @RULE_NO
,[HISTORY] = @HISTORY
,[EDIT_TIME] = GetDATE()
,[EDIT_BY] = @ADDEDIT_BY
,[IN_DOCLIST] = @IN_DOCLIST
,[DOCLIST_ID] = @DOCLIST_ID
--,[SUBPOENA] = @SUBPOENA
--,[COURT_DIV] = @COURT_DIV
--,[COURT_CAL] = @COURT_CAL
--,[PRDE_ID] = @PRDE_ID
--,[ALARM_PENDING] = @ALARM_PENDING
--,[AUTO_ENTRY] = @AUTO_ENTRY
--,[OUTLOOK] = @OUTLOOK
--,[ENTRYID] = @ENTRYID
--,[EXCLUSIVE_TO] = @EXCLUSIVE_TO
--,[FROMCYCOM] = @FROMCYCOM
--,[MANUALCHNG] = @MANUALCHNG
--,[COMPULAW] = @COMPULAW
,[ENDDATE] = @ENDDATE
WHERE [CAL_ID] = @CAL_ID AND [MATTER_ID] = @MATTER_ID
IF(EXISTS(SELECT RECNO FROM MEMOS WHERE TABLENAME='CALENDAR' AND  Name='DETAIL' AND
RECNO= @CAL_ID))
BEGIN
IF(@Note IS NULL)
BEGIN
SET @Note=''
END
Update Memos SET  Notes=@Note
WHERE  TABLENAME='CALENDAR' AND  Name='DETAIL' AND RECNO= @CAL_ID
END
ELSE
BEGIN
IF(@Note IS NOT NULL)
BEGIN
INSERT INTO Memos (TableName, Name, RecNo, Sequence, Notes)
VALUES('CALENDAR', 'DETAIL', @CAL_ID, 1, @Note)
END
END
END
COMMIT
SET @ResultSPs = 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
SET @ResultSPs = -1
END CATCH
SELECT @ResultSPs  as ResultSPs
END

Open in new window


Any ideas?
0
Comment
Question by:bjones8888
  • 2
3 Comments
 
LVL 19

Expert Comment

by:Thommy
ID: 39661754
I think the problem lies in the data you get from your ADOQuery2 records, containing improper character(s) in one or more fields.

Set your variables sSubject, sBody, sLocation,... to fixed values and check if calling your stored procedure works this way...

sSubject  :='TheSubject';
sBody      :='TheBody';
sLocation:='TheLocation';
...
0
 

Accepted Solution

by:
bjones8888 earned 0 total points
ID: 39664297
I had manually tried executing the stored procedure from SSMS, so I know there was no issue with the stored procedure itself.

And then I found another way to do it, which worked as expected.  So I know it wasn't data related.

The new method uses a TADOCommand instead.  And I like this method better because I don't have to know how long a particular parameter value is, like I do with the CreateParameter option.

cmd := TADOCommand.Create(nil);
try
    cmd.Connection := ADOConn;  // a previously created TADOConnection
    cmd.CommandType := cmdStoredProc;
    cmd.CommandText := 'UpdateCalendar';
    cmd.Parameters.Refresh;
    cmd.Parameters.ParamByName('@CAL_ID').Value := iId;
    cmd.Parameters.ParamByName('@MATTER_ID').Value := iMatterId;
    <...snip...>
    cmd.Execute;
finally
    cmd.Free;
end;

Open in new window

0
 

Author Closing Comment

by:bjones8888
ID: 39674140
I found the answer.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question