Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Store serialized objects in MySQL

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 …
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

578 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