I'm passing an integer value into a procedure.  If I understand the
process correctly, I reference that parameter using a ?.  But I'm having
a hard time referencing the parameter value more than once during the
procedure.  For example...

PREPARE CursorStmt FROM
  'select TimeIn, TimeOut
     from PunchTime P
    where EmpID = ?
      and WeekDate = (select IFNULL(Max(WeekDate) THEN CURRENT_DATE
                             ELSE Max(WeekDate))
                        from PunchTime P2
                       where P2.EmpID = P.EmpID)
    order by TimeIn DESC';

OPEN PunchCursor;

IF (ROWCOUNT(PunchCursor) > 0) THEN
  BEGIN
    FETCH FIRST FROM PunchCursor INTO PunchIn, PunchOut;
    IF (PunchOut is NULL) THEN
      UPDATE PunchCursor SET PunchOut = CURRENT_TIME;
    ELSE
      BEGIN
        PREPARE stmt FROM
          'insert into PunchTime (EmpID, WeekDate, TimeIn)
             values ?, CURRENT_DATE, CURRENT_TIME';
        EXECUTE stmt;
      END;
    END IF;
  END;
ELSE
  BEGIN
    PREPARE stmt FROM
      'insert into PunchTime (EmpID, WeekDate, TimeIn)
           values ?, CURRENT_DATE, CURRENT_TIME';
    EXECUTE stmt;
  END;
END IF;

CLOSE PunchCursor;

As you can see, I'm referencing the parameter 4 times during the course
of the procedure.  It compiles OK, but fails when I execute it with a
"Format '%s' invalid or incompatible with argument" error.

So how can I use the same parameter value in both the initial cursor and
in an Insert statement later in the procedure?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

<< I'm passing an integer value into a procedure.  If I understand the
process correctly, I reference that parameter using a ?.  But I'm having a
hard time referencing the parameter value more than once during the
procedure.  For example... >>

What version of EDB are you using ?

As for the issue, you need to include a USING clause when you execute
parameterized DML statements so that EDB has a parameter value to use for
the statement.  Although, they should still work without you setting the
parameters.  Could you send me the tables that you're using so I can track
down the Format() error ?  I suspect that EDB is trying to raise an error
and is not specifying the proper formatting parameters for the error
message.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
>
> What version of EDB are you using ?

I'm using EDB 1.02 Unicode (because of the problems with the 1.03
Unicode version).

> As for the issue, you need to include a USING clause when you execute
> parameterized DML statements so that EDB has a parameter value to use for
> the statement.  Although, they should still work without you setting the
> parameters.  Could you send me the tables that you're using so I can track
> down the Format() error ?  I suspect that EDB is trying to raise an error
> and is not specifying the proper formatting parameters for the error
> message.

It's a big table (20 MB plus a 3MB index).  How do you want me to send it?

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon,

<< It's a big table (20 MB plus a 3MB index).  How do you want me to send
it? >>

Just send it zipped up as an attachment.  It should be small enough then to
clear our 16 MB limit for attachments.

--
Tim Young
Elevate Software
www.elevatesoft.com