We help IT Professionals succeed at work.

Retruning parameters from stored procedure to Delphi using FIBplus

Stephen Lappin
on
2,131 Views
Last Modified: 2013-12-09
I have a stored procedure in Firebird that returns two parameters:

CREATE PROCEDURE OPDUTY_LOGON_LOGOFF_TIME(
  INDIVIDUAL_IDENT CHAR(6) CHARACTER SET ASCII,
  QUEUEEVENT_IDENT BIGINT)
RETURNS(
  OPDUTYSTARTTIME TIMESTAMP,
  OPDUTYENDTIME TIMESTAMP)
AS....

But when I call it from Delphi 2005 using FIBPlus 6.7.0, they return value of zero. When I run it in Firebird, it functions correctly. I have used a SQL monitor to check that it is being called, and that the correct values for the input parameters are supplied. Can anyone help me on this?

    try
      dtmUpdateCalculationDatasetModule.stpOpDutyLogonLogoffTime.
        ParamByName('INDIVIDUAL_IDENT').AsString := uclIndividual;
      dtmUpdateCalculationDatasetModule.stpOpDutyLogonLogoffTime.
        ParamByName('QUEUEEVENT_IDENT').AsInt64 := uclQueueEvent;
      dtmUpdateCalculationDatasetModule.stpOpDutyLogonLogoffTime.
        ExecProc;
      OpDutyStartTime := dtmUpdateCalculationDatasetModule.
        stpOpDutyLogonLogoffTime.ParamByName('OPDUTYSTARTTIME').AsDateTime;
      OpDutyEndTime := dtmUpdateCalculationDatasetModule.
        stpOpDutyLogonLogoffTime.ParamByName('OPDUTYENDTIME').AsDateTime;
    finally
      dtmUpdateCalculationDatasetModule.stpOpDutyLogonLogoffTime.Close;

Comment
Watch Question

Principal Operations Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mike LittlewoodEngineer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Stephen LappinSenior Technologist

Author

Commented:
Thanks for the advice so far, experts. Unfortunately, it is not improving results.

I have changed my FIBPlus component so that it is executing a query, but the values returned are still NULL (again, it works when running the SELECT in Firebird directly - 1 row fetched.).

SQL Montitor shows the query being executed:
Application: eOPMUpdateManager.exe
Operation: Execute

SELECT
    OPDUTYSTARTTIME,
    OPDUTYENDTIME
FROM
    OPDUTY_LOGON_LOGOFF_TIME(?INDIVIDUAL_IDENT,
    ?QUEUEEVENT_IDENT)

  INDIVIDUAL_IDENT = '400609'
  QUEUEEVENT_IDENT = 6

This is an extract from Delphi showing it being called:

    with dtmUpdateCalculationDatasetModule do
    begin
      with qryOpDutyLogonLogoffTime do
      begin
        try
          ParamByName('INDIVIDUAL_IDENT').AsString := uclIndividual;
          ParamByName('QUEUEEVENT_IDENT').AsInt64 := uclQueueEvent;
          ExecQuery;
          OpDutyStartTime := FieldByName('OPDUTYSTARTTIME').AsDateTime;
          OpDutyEndTime := FieldByName('OPDUTYENDTIME').AsDateTime;
        finally
          Close;
          Free;
        end;
      end;
    end;

Any further suggestions?
Stephen LappinSenior Technologist

Author

Commented:
I should add the following are the results of evalutating variables when running in debug mode:

dtmUpdateCalculationDatasetModule.qryOpDutyLogonLogoffTime.Fields[0]
= (0, True, 'OPDUTYSTARTTIME', $196B950, 0, 1, $F69044, $174FDD0, False, False, Unassigned, '', 510, 0, 8, 0, False, False, True, 0, 0, False, nil, nil)

dtmUpdateCalculationDatasetModule.qryOpDutyLogonLogoffTime.Fields[1]
= (1, True, 'OPDUTYENDTIME', $196B950, 0, 1, $F690DC, $174FDD0, False, False, Unassigned, '', 510, 0, 8, 0, False, False, True, 0, 0, False, nil, nil)
Nick UpsonPrincipal Operations Engineer
CERTIFIED EXPERT

Commented:
Please show the code of the stored procedure
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Stephen LappinSenior Technologist

Author

Commented:
I tried changing teh component to TpFITDataset and using Open insteand if ExecQuery, but got the same results. Full definition of stored proc below.

CREATE PROCEDURE OPDUTY_LOGON_LOGOFF_TIME(
  INDIVIDUAL_IDENT CHAR(6) CHARACTER SET ASCII,
  QUEUEEVENT_IDENT BIGINT)
RETURNS(
  OPDUTYSTARTTIME TIMESTAMP,
  OPDUTYENDTIME TIMESTAMP)
AS
DECLARE VARIABLE NEXT_QUEUEEVENT_IDENT BIGINT;
DECLARE VARIABLE CURRENT_QUEUEEVENT_IDENT BIGINT;
BEGIN
  /* This procedure takes an INDIVIDUAL's staff number and */
  /* and a QUEUEVENT Ident and returns the correct logon   */
  /* and logoff times for the full operational duty period             */
  /* (taking into account any implicit logoff/logons)      */
 
  CURRENT_QUEUEEVENT_IDENT = QUEUEEVENT_IDENT;
 
  WHILE (CURRENT_QUEUEEVENT_IDENT IS NOT NULL) DO
  BEGIN
    -- First get the start time matching the current queue event and individual
 
    SELECT
          MIN(INDIVIDUALSUITEPOSITION.STARTTIME)
    FROM
          INDIVIDUALSUITEPOSITION
    WHERE
         (INDIVIDUALSUITEPOSITION.INDIVIDUAL = :INDIVIDUAL_IDENT) AND
         (INDIVIDUALSUITEPOSITION.QUEUEEVENTSTART = :CURRENT_QUEUEEVENT_IDENT) OR
         (INDIVIDUALSUITEPOSITION.QUEUEEVENTEND = :CURRENT_QUEUEEVENT_IDENT) AND
         (INDIVIDUALSUITEPOSITION.SUITEPOSITION IS NOT NULL)
    INTO
         :OPDUTYSTARTTIME;
       
    -- Next, check if there are any records lower down the chain that match
    -- i.e. records that have a QUEUEEVENTEND that matches our current QUEUEEVENT
   
    NEXT_QUEUEEVENT_IDENT = NULL;
 
    SELECT FIRST 1
           INDIVIDUALSUITEPOSITION.QUEUEEVENTSTART
    FROM
           INDIVIDUALSUITEPOSITION
    WHERE
           (INDIVIDUALSUITEPOSITION.INDIVIDUAL = :INDIVIDUAL_IDENT) AND
           (INDIVIDUALSUITEPOSITION.QUEUEEVENTEND = :CURRENT_QUEUEEVENT_IDENT)
    INTO
        :NEXT_QUEUEEVENT_IDENT;

     -- If we found a record lower down in the chain, the op duty time
     -- will be taken from that record on the next loop iteration.
     -- If we didn't, we'll leave the OPDUTYSTARTTIME value we found earlier
     
     IF (CURRENT_QUEUEEVENT_IDENT = NEXT_QUEUEEVENT_IDENT) THEN
        CURRENT_QUEUEEVENT_IDENT = NULL;
     ELSE
         CURRENT_QUEUEEVENT_IDENT = NEXT_QUEUEEVENT_IDENT;
  END
 
 
     -- Now get the Op Duty end time for this QUEUEEVENT
     
     -- Reset the Current QUEUEEVENT_IDENT to the original one
     
  CURRENT_QUEUEEVENT_IDENT = QUEUEEVENT_IDENT;
 
  WHILE (CURRENT_QUEUEEVENT_IDENT IS NOT NULL) DO
  BEGIN
    -- First get the end time matching the current queue event and individual

    SELECT
          MAX(INDIVIDUALSUITEPOSITION.ENDTIME)
    FROM
          INDIVIDUALSUITEPOSITION
    WHERE
         (INDIVIDUALSUITEPOSITION.INDIVIDUAL = :INDIVIDUAL_IDENT) AND
         (INDIVIDUALSUITEPOSITION.QUEUEEVENTSTART = :CURRENT_QUEUEEVENT_IDENT) OR
         (INDIVIDUALSUITEPOSITION.QUEUEEVENTEND = :CURRENT_QUEUEEVENT_IDENT) AND
         (INDIVIDUALSUITEPOSITION.SUITEPOSITION IS NOT NULL)
    INTO
         :OPDUTYENDTIME;

    -- Next, check if there are any records lower down the chain that match
    -- i.e. records that have a QUEUEEVENTEND that matches our current QUEUEEVENT

    NEXT_QUEUEEVENT_IDENT = NULL;

    SELECT FIRST 1
           INDIVIDUALSUITEPOSITION.QUEUEEVENTEND
    FROM
           INDIVIDUALSUITEPOSITION
    WHERE
           (INDIVIDUALSUITEPOSITION.INDIVIDUAL = :INDIVIDUAL_IDENT) AND
           (INDIVIDUALSUITEPOSITION.QUEUEEVENTSTART = :CURRENT_QUEUEEVENT_IDENT)
    INTO
        :NEXT_QUEUEEVENT_IDENT;

     -- If we found a record lower down in the chain, the op duty time
     -- will be taken from that record on the next loop iteration.
     -- If we didn't, we'll leave the OPDUTYSTARTTIME value we found earlier

     IF (CURRENT_QUEUEEVENT_IDENT = NEXT_QUEUEEVENT_IDENT) THEN
        CURRENT_QUEUEEVENT_IDENT = NULL;
     ELSE
         CURRENT_QUEUEEVENT_IDENT = NEXT_QUEUEEVENT_IDENT;
  END
 
  SUSPEND;
     
END;
Nick UpsonPrincipal Operations Engineer
CERTIFIED EXPERT

Commented:
a small point you twice have:

    SELECT FIRST 1
           INDIVIDUALSUITEPOSITION.QUEUEEVENTSTART

- select first is meaningless without an order by clause

I suggest you ensure the SP logic is not the problem by having the body just assign fixed values to the output params and do suspend. Then execute as "select * from ..." using your tool of choice and calling it from your code. That will quickly tell you if it's the SP or the program call that is causing the problem
Stephen LappinSenior Technologist

Author

Commented:
Doh! It seems that the query in my stored procedure is returning no data because the data has not been written to the relevant tables yet in the transaction. I didn't write this, and it is complicated code to follow. Grr!

I'll split the points up between you all. Thanks for your assistance.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.