The answer is to drop the {} braces from the query. Are the braces part
of the specification, or just common usage (Oracle and MS-SQL for
starters)? I couldn't find anything that documented this.

-----Original Message-----
From: Rick DeBay
Sent: Tuesday, August 10, 2004 2:55 PM
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] How to call a stored procedure?

I get the error 'Unknown keyword ? for escaped syntax' so I'm obviously
doing something wrong. How do you call a stored procedure with
firebird?

cstmt = c.prepareCall("{?=CALL INVOICE_ALL_CLAIMS(?,?)}");
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.setString(2,invoice);
cstmt.setDate(3, sqlDate(closeDate) );
/*ResultSet rs =*/ cstmt.executeQuery();
invoice_count = cstmt.getInt(1);

CREATE PROCEDURE INVOICE_ALL_CLAIMS (
INVOICE_NO VARCHAR(10),
INVOICE_DT DATE)
returns (
INV_COUNT Integer)
AS
DECLARE VARIABLE ACCOUNT VARCHAR(15);
DECLARE VARIABLE PHARM DECIMAL(7,0);
BEGIN

FOR SELECT DISTINCT
ACCOUNTID,
SRVPROVID
FROM CLAIMSPAIDREVERSED
WHERE INVOICE IS NULL AND DATESBM <= :INVOICE_DT
INTO
:ACCOUNT,
:PHARM
DO BEGIN
INSERT INTO INVOICE (INVOICE_NO, ACCOUNT, PHARMACY)
VALUES (:INVOICE_NO, :ACCOUNT, :PHARM);

UPDATE CLAIMSPAIDREVERSED
SET INVOICE = :INVOICE_NO
WHERE
ACCOUNTID = :ACCOUNT AND SRVPROVID = :PHARM AND
INVOICE IS NULL AND DATESBM <= :INVOICE_DT;
END

SELECT COUNT(INVOICE) FROM CLAIMSPAIDREVERSED
WHERE INVOICE = :INVOICE_NO
INTO :INV_COUNT;
END

Rick DeBay
Senior Software Developer
RxStrategies.net




Yahoo! Groups Links