Solved

Store serialized objects in MySQL

Posted on 2002-07-11
3
202 Views
Last Modified: 2010-03-31
Hi!

I've been stuck with this one all afternoon and I'm in bit of a rush (it has to be done by tomorrow so the points are set accordingly :). I have two methods in a Week class, which implements serializable:

public byte[] toByteArray()
public static synchronized Week fromByteArray(byte[] bytearray)

toByteArray(), which works for sure, returns the serialized object as an array of bytes and fromByteArray should rebuild the object (don't know yet if it actually works).

The problem I have is when I try to write it to a mysql database. Here is the method which should perform the writing:

  public static synchronized void saveBaseWeek ( String userName, Week week ) {
    // Prepare a statement to insert binary data
    String sql = "";
    PreparedStatement pstmt = null;
    byte[] bytearray = null;
     
    bytearray = new byte[week.toByteArray().length];
    bytearray = week.toByteArray();

    //sql = "USE ?; INSERT INTO ? ( ?,?) VALUES ( ?,? );";
    sql = "USE " + Constants.APPLICATION.DATABASE.DATABASE_NAME + "; INSERT INTO " + Constants.APPLICATION.DATABASE.BASEWEEK_TABLE_NAME + " ( " + Constants.APPLICATION.DATABASE.USERNAME_COL_NAME + "," + Constants.APPLICATION.DATABASE.BASEWEEK_COL_NAME + " ) VALUES ( ?,? );";
    try {
      pstmt = myCon.prepareStatement(sql);
      // Set values for the prepared statement
      //pstmt.setString(1, Constants.APPLICATION.DATABASE.DATABASE_NAME);
      //pstmt.setString(2, Constants.APPLICATION.DATABASE.BASEWEEK_TABLE_NAME);
      //pstmt.setString(3, Constants.APPLICATION.DATABASE.BASEWEEK_COL_NAME);
      //pstmt.setString(4, Constants.APPLICATION.DATABASE.USERNAME_COL_NAME);
      pstmt.setString(1, userName);
      pstmt.setBytes (2, bytearray);
    } catch (Exception ex) {ex.printStackTrace();}

    // Insert the data
    try {
      pstmt.execute();
      pstmt.close();
    } catch (Exception ex) {ex.printStackTrace();}
   
  }
 

A part of the stacktrace thrown by pstmt.execute() reads

STACKTRACE >>>>>>
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near '; INSERT INTO Baseweek ( Username,BaseweekObject ) VALUES ( 'r','’\0sr\08com.n' at line 1
     at org.gjt.mm.mysql.MysqlIO.sendCommand(MysqlIO.java:497)
     at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(MysqlIO.java:550)
     at org.gjt.mm.mysql.Connection.execSQL(Connection.java:885)
     at org.gjt.mm.mysql.PreparedStatement.execute(PreparedStatement.java:1093)
<<<<<< STACKTRACE


The table's description is:

mysql> describe baseweek;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| BaseweekObject | blob        | YES  |     | NULL    |       |
| Username       | varchar(15) |      | PRI |         |       |
+----------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>





Any suggestions on what I'm missing?


Regards,

Marko
0
Comment
Question by:_marko_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
girionis earned 300 total points
ID: 7146373
 Can you set the database you use when you connect to the db server and then only send the INSERT statement to the db server? It might not like the ; in front of the INSERT statement.
0
 
LVL 2

Author Comment

by:_marko_
ID: 7146429
It worked!!! Unbelievable!!! I have used the USE-statement in other queries, but they have not been prepared statements so I didn't think it would make a difference.

Hehe, now I can go to the beach instead of having to do any more bug hunting :)


Thank you girionis very, very much!


Regards,

Marko
0
 
LVL 35

Expert Comment

by:girionis
ID: 7146515
 You can never be sure with computers... One thing that is working fine in one machine might not work at all in another.

  Thank you for the points, I am glad I helped :-)

  P.s. Enjoy the beach.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question