• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

SQLite Stmt Pointer is Closed Error When Populating JTable

 
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi everyone,

I'm developing a Java Swing application where I display data from an SQLite database in a JTable.
However, I'm encountering the following error when trying to fetch and populate the table data:



Below is the method I'm using to fetch data and update the JTable:



I have verified the database connection and the AxaClients table structure, and the query works when
executed directly in a database tool. However, I keep encountering the stmt pointer is closed error
when this method runs.

Why is sqlite behaving like this? I use the jar bundled version of the database in question.
What is the general solution to this problem? It happens everytime i try to populate my jtable.
Any answer is greatly much appreciated!

Regards,
Robert.


 
Master Rancher
Posts: 5112
38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What statement in your code was executing when the exception happened?  That should show in the stack trace.
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for your reply!

The exception occurs when trying to populate the JTable with data fetched from the database. Specifically, the error happens when executing the line that calls model.addRow(rowData);. This is indicated in the stack trace, which points to that part of the code.

Here’s the relevant part of the stack trace:



It seems like the DefaultTableModel is not properly initialized or there's an issue with the data being added to the table. However, I am not sure why this happens because I do clear the rows beforehand using model.setRowCount(0) and ensure the ResultSet has valid data before adding rows.

Would you have any suggestions on how I can better debug or fix this issue? Could it be related to how the table or DefaultTableModel is configured, or perhaps an issue with the database query?

Thank you for your help!



 
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post the entire stack trace?  It's not obvious (to me) how this and this are related.
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


That is the stacktrace i currently get. It happens when populating a JTable with a hidden first column but that should not be the cause of the problem. Thank you for your reply.
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also, unless I'm not seeing something, the transaction management commands conn.setAutoCommit(false), conn.commit(), conn.rollback(), conn.setAutoCommit(true) don't provide any value in a simple read-only operation, and can just be removed without affecting any behaviour.
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is referring to this line in your code?
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have removed the transaction features completely and yet he still nags about that stmt pointer is closed error. I can't move on because of this error.
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't have an environment to run anything, but you could try moving creating the ResultSet to try-with-resources.  It may not resolve your issue, but the code will be simpler, and might be easier to debug:
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you so much for your replies. I applied try with resources but the problem is still there. I dont know what if you tried using other methods in ResultSet like absolute(int i) afterLast beforeFirst and so on ? But that is over my level.
 
Norm Radder
Master Rancher
Posts: 5112
38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Is Connector a class?  What package is it in?
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Connector is a class where i store my database features.

Also one question? What is the general cause to the error "stmt pointer is closed" in the sqlite database ?
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is the code for the getConnection method in the Connector class



 
Sheriff
Posts: 28371
99
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The API documentation for the next() method of ResultSet says this:

The API docs wrote:When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown. If the result set type is TYPE_FORWARD_ONLY, it is vendor specified whether their JDBC driver implementation will return false or throw an SQLException on a subsequent call to next.


Now, I see that you're using TYPE_FORWARD_ONLY. So it might be that your JDBC driver throws the exception for that reason, instead of politely returning false as one would expect.
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for clearing some dust off the question. What are the alternatives to TYPE_FORWARD_ONLY in sqlite ?
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I ran a test using the same code to read the rows from the table and it executed with without error,

I am using the org.xerial:sqlite-jdbc:3.48.0.0 and Java 17.

 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How is your method being invoked - by an action listener?
 
Paul Clapham
Sheriff
Posts: 28371
99
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Robert Ingmarsson wrote:Thank you for clearing some dust off the question. What are the alternatives to TYPE_FORWARD_ONLY in sqlite ?


The alternative (in my view) is to simply use -- I've often written code to extract data from a table and never used anything other than this version. On the other hand its defaults for the other two parameters are what you used anyway, so it can't hurt to leave out those other two parameters.
 
Saloon Keeper
Posts: 28486
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
TYPE_FORWARD means that you promise that you'll use rs.next() to read each record in a forward direction and not skip around or try to back up. Some JDBC drivers allow those options, some do not.
 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But what does the error mean anyway?



And how do you go about it to prevent it from occuring?
Thank you for all the replies. Still confused.
 
Paul Clapham
Sheriff
Posts: 28371
99
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Show us a program which does nothing but connect to that database and read all of the rows from that table. No other code whatsoever. Does the error still occur? So far we have had committing changes and writing to Swing tables and so on as distractions, which is unhelpful. Just start with the simplest description of the problem and go from there.
 
Norm Radder
Master Rancher
Posts: 5112
38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The getConnection() method can return the same Connection object to multiple callers.  Is there some state in the object that would cause the future uses of it to fail.
 
Tim Holloway
Saloon Keeper
Posts: 28486
210
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A Connection can contain multiple outstanding Statements.

A Statement can contain multiple outstanding ResultSets.

Note that closing a Statement implicitly closes any outstanding open ResultSets and closing a Connection closes outstanding open Statements. But for safety's sake I do try to close ResulttSets and Statements explicitly. And try-with-resources is an excellent way to make sure.
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It means that statement was closed (or was closed indirectly due to the associated connection being closed).  This is the driver source code where the exception is being thrown:My guess it that it is related to using a singleton to share a DB connection.  I was able to reproduce something similar by closing the connection before calling next():

Instead of sharing a connection, try creating a new connection for each interaction with the database, like in my test code.
I don't think there is any real performance disadvantage to creating a new connection each time.

 
Bartender
Posts: 2445
13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Rob,
Do we need to add a finally block to close the connection each time?
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Himai Minh wrote:Hi, Rob,
Do we need to add a finally block to close the connection each time?


When using try-with-resources, the resources (including the connection) will get closed automatically.
 
Ron McLeod
Marshal
Posts: 4699
588
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Himai's comment gave me an idea for a better guess of what might be happening.

In your code, you are using try-with-resources to get a (shared) connection reference from the singleton.When using try-with-resources, the resources (including the connection) will automatically be closed.  This is fine except when you have multiple threads sharing the same connection, and one of the threads finishes having try-with-resources close the connection while the other threads are still using it.

I tried this as a test, and was able to reproduce this kind of problem (notice that the stack trace looks the same as what you shared):

 
Robert Ingmarsson
Ranch Hand
Posts: 214
2
Netbeans IDE PHP Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ron you were right. I used a singleton db connection and the problem has not appeared so far. This is what i used:



I consider this subject closed for now. Thank you all for your kind replies. I can now move on. I love the ranch.

Regards,
Robert!

 
We should throw him a surprise party. It will cheer him up. We can use this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic