JDBC updates using result sets

[Simon Ingram]

Hi folks,

I am trying to update a blob field in a MySQL database using updatable result sets. Should be simple. I have no problem updating my blob using prepared statements or updating other fields on the table. The problem is with the blob! I am going crazy trying to figure this out. Would some smart soul please take a look and tell me what's wrong?

regards and thanks,
Simon

Here is the table:

mysql> show columns from titles;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| TitleId | int(11) | | PRI | 0 | |
| Title | varchar(50) | YES | | NULL | |
| Year | int(11) | YES | | NULL | |
| Price | float | YES | | NULL | |
| URL | varchar(50) | YES | | NULL | |
| Image | blob | YES | | NULL | |
| ByteLength | int(5) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Here is the SQL query:

SELECT TitleID, Title, Image, ByteLength from Titles WHERE TitleID = 11

which should create a single entry in the result set.

Here is the code:

try {
 
    dbConnect = cj.makeConnection();
            System.out.println("connection established");
 
            dbStatement = dbConnect.createStatement(
                                  ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_UPDATABLE);
 
            dbRS = dbStatement.executeQuery(qry_string);
 
            if (!dbRS.next()) System.out.println("record not found");
            else {
                System.out.println("got record");
 
                dbRS.last();
                int rows = dbRS.getRow();
                System.out.println("number of rows in dbRS " + rows);
 
                dbRS.absolute(1);
 
                String title = dbRS.getString("Title");
                System.out.println("Title : " + title);
 

// program fails to get the blob!! null pointer!
 

                blob = dbRS.getBlob("Image");
                System.out.println("got blob " + blob.length());
 
 

} catch (SQLException sqlex) {
        }
 

etc..

And here is the output from the console, which shows that even though I have retrieved the record, the getBlob() method does not retrieve a handle to the blob!

C:\Java\JDBC\classes>java com.ingasimn.EV010 11
SELECT TitleID, Title, Image, ByteLength from Titles WHERE TitleID = 11
connection established
got record
number of rows in dbRS 1
Title : The Sound of Music
java.lang.NullPointerException
at com.ingasimn.EV010.actionPerformed(EV010.java:146)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1764)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.j
ava:1817)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:419)

at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:257)
at javax.swing.AbstractButton.doClick(AbstractButton.java:289)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1113)
at javax.swing.plaf.basic.BasicMenuItemUI$MouseInputHandler.mouseReleased(BasicMen
uItemUI.java:943)
at java.awt.Component.processMouseEvent(Component.java:5134)
at java.awt.Component.processEvent(Component.java:4931)
at java.awt.Container.processEvent(Container.java:1566)
at java.awt.Component.dispatchEventImpl(Component.java:3639)
at java.awt.Container.dispatchEventImpl(Container.java:1623)
at java.awt.Component.dispatchEvent(Component.java:3480)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3450)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3165)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3095)
at java.awt.Container.dispatchEventImpl(Container.java:1609)
at java.awt.Window.dispatchEventImpl(Window.java:1590)
at java.awt.Component.dispatchEvent(Component.java:3480)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:450)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:
197)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:15
0)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:144)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:136)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:99)
null
Connection Closed

[Muhammad Saifuddin]

[B]blob [/B]= dbRS.getBlob("Image");

where you initialize this Object "blog"?

try this

Blob blob = dbRS.getBlob("Image");

make sure the package is already imported in this file. java.sql.Blob;

hope it helps..

[Chad Clites]

Just in case you had not thought of it, have you tried running that command from the MySql command line? Perhaps the record has not been populated as you think it has.

You may also want to consider catching the SQLException. It may tell you something else. As far as I can tell from the information that you have provided, Title has a value, but Image does not, hence the null pointer error. Perhaps there is an error where you insert your blob?

But I am sure that you already figured that out.

[Simon Ingram]

Thanks guys. I tried both suggestions. I have managed to get the thing working but I had to use Streams. Shame really! If anyone can demonstrate how to use Blobs I will be very interested!

regards
Simon