Rick DeBay wrote:

> 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

Hello Rick, look I am not really an expert in Firebird , actually this
is the first time I create a huge application using FB, but I will
show you how I do when I have input parameters and output parameters (I
found this out trying and testing), I have the book JDBC and Java, and
there they really say to do the way you are doing, but that didnt work
for me, then I did this way and it works fine for me:

This SP has 14 input parameters and 1 output, so what I do is
perform as it had just ouput parameters, I dont use CallableStatement.
PreparedStatement p = c.prepareStatement("SELECT * FROM
SP_SET_SALVAR_DOCUMENTO_RECEBER(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
p.setString(1, tipo_recebimento);
p.setString(2, id_tomador);
p.setString(3, documento_origem);
p.setString(4,documento_gerado);
p.setBigDecimal(5, perc_desconto);
p.setBigDecimal(6, perc_juros);
p.setBigDecimal(7, valor_desconto);
p.setBigDecimal(8, valor_receber);
p.setBigDecimal(9, valor_total);
p.setDate(10,data_emissao);
p.setTime(11,hora_emissao);
p.setDate(12, data_vencimento);
p.setString(13, descricao);
p.setString(14, boleto);

ResultSet rs = p.executeQuery();
if(rs.next())
{
numero_gerado = rs.getInt("DOCUMENTO_GERADO");
}
rs.close();
p.close();

As you can see this is a SET so I am saving data and I am also
retrieving data at once. This works fine for me.
I hope I could help you.