[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Store serialized objects in MySQL

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

Technology Partners: 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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

656 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