executeUpdate blocked

Question

When the code comes to the step "pstmt.executeUpdate()" it freezes and blocked and I didn't receive any SQL exception

This is works:

SQL = "INSERT INTO Procedure (file_path,id) VALUES ('/test/file_test.pdf',512);";
pstmt = con.prepareStatement(SQL,Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();

This doesn't work! and I don't receive any exception and it is blocked:

SQL = "INSERT INTO Procedure (file_path,id) VALUES (?,?);";
pstmt = con.prepareStatement(SQL,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, "/test/file_test.pdf");
pstmt.setInt(2, 512);
pstmt.executeUpdate();

I don't understand why my code is blocked when I use the next solution (I mean setString and setInt).

What is your SQL database (e.g. MySQL, Oracle, SQL Server) ? — Tim Biegeleisen, Oct 25 '19 at 08:37
hi @TimBiegeleisen thank you for your feedback my SQL DB is SQL SERVER — radhwen hrizi, Oct 25 '19 at 08:38
hi @deHaar thank you for your feedback, the first solution works fine but the second doesn't works and it is blocked and no SQL exception catched — radhwen hrizi, Oct 25 '19 at 08:41
Bizarre. You could try to locate the point of failure by tracking sql statements issued by the [jdbc layer](https://stackoverflow.com/questions/27060563/enable-logging-for-sql-statements-when-using-jdbc) and [the database](https://stackoverflow.com/questions/303994/log-all-queries-in-mysql). @deHaar's suggestion was, that you issue SELECTs with the same parameter handling to see, whether they get processed or not. — Curiosa Globunznik, Oct 25 '19 at 08:43
Try to put it in a try/catch and see which error you get and use your debugger — J. Adam, Oct 25 '19 at 08:45
Have you tried to add the enclosing apostrophes to the second option? Like `pstmt.setString(1, "'/test/file_test.pdf'");`? I know it shouldn't make a difference, but maybe worth a try. — deHaar, Oct 25 '19 at 08:45
@deHaar that would be a different string. About the suggestion of J.Adam: does the code run in a thread without proper exception handling? — Curiosa Globunznik, Oct 25 '19 at 08:46
Maybe [this answer](https://stackoverflow.com/a/4224729/1616627) to a different question gives a hint? — Roger Gustavsson, Oct 25 '19 at 08:48
@deHaar I tried you solution but doesn't works i still face the same issue — radhwen hrizi, Oct 25 '19 at 08:54
Which version of SQL Server are you using, which version of Java and which version of the Microsoft SQL Server JDBC driver? — Mark Rotteveel, Oct 25 '19 at 09:01
Can you tell us more about your connection setup? There is a possibility that your first statement executes and creates a lock on 'Procedure'. If the second statement runs in a different transaction and the first is never commited, you might be running into a simple database lock. — Simon, Oct 25 '19 at 09:20
hi @Simon when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? — radhwen hrizi, Oct 25 '19 at 11:00
hi @RogerGustavsson when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? — radhwen hrizi, Oct 25 '19 at 11:03

score 0 · Accepted Answer · edited Oct 25 '19 at 13:40

0

procedure is a reserved keyword in SQL. May be it is causing due to this. Change the Procedure table name to something else and try.

You can also try by change Procedure to `Procedure`.

I hope this works for you.

edited Oct 25 '19 at 13:40

pascal

3,287
1
17
35

answered Oct 25 '19 at 08:57

user1773579

122
1
9

If this were the problem, the OP would receive an exception, and would not be blocked. – Mark Rotteveel Oct 25 '19 at 09:00
I don't think that is the problem here, but it is definitely a(nother) problematic thing. OP should rename the table, I agree, but still this doesn't answer the question and would be better suited as a comment. – deHaar Oct 25 '19 at 09:03
Actually I don't have permission to add comment. So I commented as answer. – user1773579 Oct 25 '19 at 09:14
Procedure it was just example I put it my real table is Allegati_Procedure – radhwen hrizi Oct 25 '19 at 09:21
hi @MarkRotteveel when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? – radhwen hrizi Oct 25 '19 at 11:00
hi @user1773579 when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? – radhwen hrizi Oct 25 '19 at 11:02
hi @deHaar when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? – radhwen hrizi Oct 25 '19 at 11:02
Does that explicit key exist in the *other table*? If not, the insert would violate the foreign key constraint, most likely... – deHaar Oct 25 '19 at 11:08
hi @deHaar Yes this foreign key id=512 exist in the other table (as primary key ) – radhwen hrizi Oct 25 '19 at 12:50
OK, then it's not the problem ;-) but it must be something about that value if you can insert the file path without it using both possibilities. – deHaar Oct 25 '19 at 13:04
hi @deHaar but if i tried this query INSERT INTO Procedure (file_path,id) VALUES ('test/file.pdf',512) in Microsoft SQL Server Mangement Studio it works fine but through JAVA still not works – radhwen hrizi Oct 25 '19 at 13:06
hello guys bug found and fixed Cause of the Bug : for example let us say we have 2 table B[Id, othercolumn] and A[Id, pathfile,fk_id_B] I started a start transaction (UPDATE records id=512 ) on the table B but i forget to close the transaction (connection.commit();connection.close()) before i stared other start a transaction on A so causes this freeze because the record (id=512) still used by the previews transaction Solution: before start the transaction on A I close the transaction on B thank you Guys for helps and particiaption :) – radhwen hrizi Oct 25 '19 at 15:07

score 0 · Answer 2 · answered Oct 25 '19 at 09:05

0

Using Try/Catch and the debugger will give you more information about the problem. I had a similair problem in the past and this is how i solved it. I'm not sure what the problem was but try this out

public void getCar(String carName) {
String[] key = {"PRIMARY_KEY_COLUMN"};
try(Connection con = super.getConnection()) {
    String queryOne = "INSERT INTO Procedure (file_path,id) VALUES (?,?)";;
    try(PreparedStatement pstmt = con.prepareStatement(queryOne, key)){
        pstmt.setString(1, "/test/file_test.pdf");
        pstmt.setInt(2, 512);
        pstmt.executeUpdate();
        int generatedKey = getGeneratedKey(pstmt));
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
        // Do somthing if connection is set but exception thrown
    }
} catch (SQLException e) {
    e.printStackTrace();
    // Do somthing else if connection couldn't be set
}
}

private int getGeneratedKey(PreparedStatement pstmt) throws SQLException {
    int generatedKey = 0;
    ResultSet rs = pstmt.getGeneratedKeys();
    if (rs.next()) {
        generatedKey = rs.getInt(1);
    }
    rs.close();
    return generatedKey;
}

answered Oct 25 '19 at 09:05

J. Adam

1,457
3
20
38

when I try to ignore the foreign key it woks fine FROM "INSERT INTO Procedure (file_path,id) VALUES (?,?)" to "INSERT INTO Procedure (file_path) VALUES (?)"; the Id it is a foreign key which is the id of other table (in my case i used 512 ) i don't why doesn't works if I tried to use the foreign key Id=512? – radhwen hrizi Oct 25 '19 at 11:00
@radhwenhrizi try to execute the query directly and see what happens – J. Adam Oct 25 '19 at 13:08
Yes it works if i tried directly, but through JAVA not works – radhwen hrizi Oct 25 '19 at 13:42
hello guys bug found and fixed Cause of the Bug : for example let us say we have 2 table B[Id, othercolumn] and A[Id, pathfile,fk_id_B] I started a start transaction (UPDATE records id=512 ) on the table B but i forget to close the transaction (connection.commit();connection.close()) before i stared other start a transaction on A so causes this freeze because the record (id=512) still used by the previews transaction Solution: before start the transaction on A I close the transaction on B thank you Guys for helps and particiaption :) – radhwen hrizi Oct 25 '19 at 15:08

score 0 · Answer 3 · answered Oct 25 '19 at 15:09

hello guys bug found and fixed

Cause of the Bug : for example let us say we have 2 table B[Id, othercolumn] and A[Id, pathfile,fk_id_B] I started a start transaction (UPDATE records id=512 ) on the table B but i forget to close the transaction (connection.commit();connection.close()) before i stared other start a transaction on A so causes this freeze because the record (id=512) still used by the previews transaction Solution: before start the transaction on A I close the transaction on B

thank you Guys for helps and participation :)

executeUpdate blocked

3 Answers3