Solved

Delphi XE4 ADO Stored Procedure

Posted on 2013-11-19
3
916 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
Comment Utility
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
Comment Utility
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
Comment Utility
I found the answer.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now