Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delphi XE4 ADO Stored Procedure

Posted on 2013-11-19
3
Medium Priority
?
1,039 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

971 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