Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delphi XE4 ADO Stored Procedure

Posted on 2013-11-19
3
Medium Priority
?
986 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

705 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