Firebird commit doesn't work in Java Application

Question

I'm using a procedure to insert data in my firebird database. This procedure is called in a while command because there is no limit for how many items I can insert, for example:

while (there's itens) {
    query("select * from procedure_insert(id,desc,etc);");
}

This doesn't work, after I finish my insertion not a single item is inserted in the database. My procedure works fine there's no doubt, I believe it's a problem with the class I use to query and commit

Here is my code:

Persistence class:

public class Persistence {

    public static Connection con = Conn.getConn();
    public static Statement stm  = null;

    public static void Conn(){
        try {
            stm  = con.createStatement();
        } catch (SQLException e) {
            //
        }
    }

    public static void Insert(String query) {

        try {
            stm.execute(query);
        } catch (SQLException e) {
            //
        } 
    }

    public static void Commit(){
        try {
            con.commit();
    } catch (SQLException e) {
        //
    }
}

Insert:

while(rs.next()){

    Items item = new Items();
    item.setItem_data(rs.getString("ZPED_ITEM_DATA"));
    item.setItem_desconto(rs.getDouble("ZPED_ITEM_VALOR_V") - rs.getDouble("ZPED_ITEM_DESCONTO"));
    item.setItem_produto(rs.getInt("ZPED_ITEM_PRODUTO"));
    item.setItem_qtd(rs.getDouble("ZPED_ITEM_QTD"));
    item.setItem_valor_v(rs.getDouble("ZPED_ITEM_VALOR_V"));
    item.setPed_cod(rs.getInt("ZPED_COD"));
    ////////////////////////////////////////////////////////////////
    Persistence.Insert("SELECT * FROM PEDIDOS_PROC("+a+","+b+","+p.get(0).getCod_n()+",'"+p.get(0).getData()+"',55,"+item.getItem_produto()+",0,"+item.getItem_qtd()+","+item.getItem_valor_v()+",'A',2);");
    Persistence.Commit();
}

thank you!

I'm using JDBC Jaybird Full 2.2.7

are you really catching and ignoring all these SQLExceptions? Don't do that. At the very, very least, print out some error messages. — Thilo, May 06 '15 at 12:51
Please show us the code for the function `PEDIDOS_PROC`? What does it do? And why do you use a _function_ to insert data? Shouldn't that be a stored procedure? — , May 06 '15 at 12:53
PEDIDOS_PROC is a huge stored procedure made by another person, that procedure its fine, if I query it on terminal it works fine and not a single error is accused when I run my application. Just doesn't work, like I was forgot to commit, what I not... — Matheus Hernandes, May 06 '15 at 13:01
You're swallowing exceptions in your `Persistence` class, so for all you know you do get a lot of exceptions. You also need to show how you create your connection. Also, the way you are concatenating a query is open to SQL injection. You should really use `PreparedStatement` for that (and it might also be the cause of your current problems). — Mark Rotteveel, May 06 '15 at 14:12
And finally, your statement ends in a semi-colon, which isn't actually part of Firebirds SQL grammar (and I keep forgetting whether Jaybird strips them out or not); presence of the semi-colon might cause a syntax error. — Mark Rotteveel, May 06 '15 at 14:17
"*if I query it on terminal it works fine*" - so how exactly do you call that procedure in the terminal? Is that statement any different to the one you use in the Java code? — , May 06 '15 at 14:24
I printed the statement and paste to terminal with exactly the same query executed in Java application. I change _select * from_ to _execute procedure_ and start works — Matheus Hernandes, May 06 '15 at 14:40

score 2 · Accepted Answer · answered May 06 '15 at 14:10

2

is PEDIDOS_PROC selectable procedure? If no you must to execute it

execute procedure PEDIDOS_PROC(params...)

answered May 06 '15 at 14:10

rstrelba

1,838
15
16

2

I am not entirely sure, but I believe the reason this works is because IIRC if you select from a stored procedure (which BTW also works for executable stored procedures), then changes done in the stored procedure are rolled back if that row hasn't been fetched by the client; and you are never processing the result set and so no rows have been processed. If you execute a stored procedure, it can produce one row, and that one row is returned immediately on execute. This is something that I am going to test and document. – Mark Rotteveel May 06 '15 at 14:44
Not sure why.. but now is working, and I know _select * from_ usually execute stored procedures cuz' it works if I run on terminal. – Matheus Hernandes May 06 '15 at 14:52
@acca90 the big difference is that isql will fetch all rows immediately, jaybird doesn't do that. – Mark Rotteveel May 06 '15 at 15:14
A potentially related question is: http://stackoverflow.com/questions/18046737/jaybird-callablestatement-not-executed-until-getting-output-parameter – Mark Rotteveel May 27 '15 at 18:45

Firebird commit doesn't work in Java Application

1 Answers1