Solved

Store serialized objects in MySQL

Posted on 2002-07-11
3
196 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
topping2 challenge 13 80
Unexpected HttpURLConnection connection behavior 2 84
Basic Java Case or If-Else statement... 3 43
Arrays.asList  VS  ArrayList 4 44
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…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now