?
Solved

Store serialized objects in MySQL

Posted on 2002-07-11
3
Medium Priority
?
203 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 1200 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

Independent Software Vendors: 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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month12 days, 22 hours left to enroll

777 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