Announcement Announcement Module
Collapse
No announcement yet.
Spring, JDBC and MySQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring, JDBC and MySQL

    I am having several strange problems with SqlUpdate and my MySql database. I have not yet implemented transactions, so I have an ordinary datasource declared and use code like:

    Code:
        public void updateUser(User user) {
        	
              SqlUpdate su = 
                new SqlUpdate(ds, "update user set username = ?, " +
                					"firstname = ?, " +
    			                               "middleinit = ?, " +
                					"lastname = ?, " +
                					"address1 = ?, " +
                					"address2 = ?, " +
                					"city = ?, " +
                					"state = ?, " +
                					"zip = ?, " +
                					"where id = ?");
    
            su.declareParameter(new SqlParameter("username", Types.VARCHAR));
            su.declareParameter(new SqlParameter("firstname", Types.VARCHAR));
            su.declareParameter(new SqlParameter("middleinit", Types.VARCHAR));
            su.declareParameter(new SqlParameter("lastname", Types.VARCHAR));
            su.declareParameter(new SqlParameter("address1", Types.VARCHAR));
            su.declareParameter(new SqlParameter("address2", Types.VARCHAR));
            su.declareParameter(new SqlParameter("city", Types.VARCHAR));
            su.declareParameter(new SqlParameter("state", Types.VARCHAR));
            su.declareParameter(new SqlParameter("zip", Types.VARCHAR));
            su.declareParameter(new SqlParameter("id", Types.INTEGER));
            su.compile();
            
            Object[] oa = new Object[10];
            oa[0] = new String(user.getUsername());
            oa[1] = new String(user.getFirstname());
            oa[2] = new String(user.getMiddleinit());
            oa[3] = new String(user.getLastname());
            oa[4] = new String(user.getAddress1());
            oa[5] = new String(user.getAddress2());
            oa[6] = new String(user.getCity());
            oa[7] = new String(user.getState());
            oa[8] = new String(user.getZip());
            oa[9] = new Integer(user.getId());
            int count = su.update(oa);
        }
    Using the above code the change is made, but when I re-retrieve the list that the user is on, the list cuts off at the user I edited (even when I go into MySql and do a select * - although I can get the record by selecting by id in MySql) and this last time I found I had a corrupted table. I have also done alot of inserts where the new item on the list did not appear until I rebooted the machine. Should I be adding some sort of commit to the above code? Is there something else I'm missing? Should I revert back to the old jdbc and forget Spring? I hate to try to figure out transactions when the basic code does not work.

    Thanks!

  • #2
    Here's some more information. I brought up the list of users and selected edit for one of them. I changed a field and clicked update, which runs the statement in my previous post. When I re-retrieved the list, the list cut off the user I edited and everyone after him. Then I went to MySql and checked the table with the following results:
    mysql 4.1.12a-nt-max> check table User;
    +----------+-------+----------+-------------------------------------------------
    --+
    | Table | Op | Msg_type | Msg_text
    |
    +----------+-------+----------+-------------------------------------------------
    --+
    | cwa.User | check | warning | Size of datafile is: 1360 Should be: 1332
    |
    | cwa.User | check | warning | Found 22 parts Should be: 21 part
    s |
    | cwa.User | check | status | OK
    |
    +----------+-------+----------+-------------------------------------------------
    --+
    3 rows in set (0.02 sec)

    mysql 4.1.12a-nt-max> repair table User
    ->
    -> ;
    +----------+--------+----------+----------------------+
    | Table | Op | Msg_type | Msg_text |
    +----------+--------+----------+----------------------+
    | cwa.User | repair | error | 13 when fixing table |
    | cwa.User | repair | status | Operation failed |
    +----------+--------+----------+----------------------+
    2 rows in set (0.00 sec)

    mysql 4.1.12a-nt-max> check table User;
    +----------+-------+----------+-------------------------------------------------
    --+
    | Table | Op | Msg_type | Msg_text
    |
    +----------+-------+----------+-------------------------------------------------
    --+
    | cwa.User | check | warning | Table is marked as crashed and last repair faile
    d |
    | cwa.User | check | warning | Size of datafile is: 1360 Should be: 1344
    |
    | cwa.User | check | error | Keypointers and record positions doesn't match
    |
    | cwa.User | check | warning | Found 22 parts Should be: 21 part
    s |
    | cwa.User | check | error | Corrupt
    |
    +----------+-------+----------+-------------------------------------------------
    --+
    5 rows in set (0.01 sec)

    mysql 4.1.12a-nt-max> repair table User;
    +----------+--------+----------+----------------------+
    | Table | Op | Msg_type | Msg_text |
    +----------+--------+----------+----------------------+
    | cwa.User | repair | error | 13 when fixing table |
    | cwa.User | repair | status | Operation failed |
    +----------+--------+----------+----------------------+
    2 rows in set (0.01 sec)

    mysql 4.1.12a-nt-max>

    Any ideas?? All of my updates and inserts have had unpredictable results.

    Comment


    • #3
      This does not seem Spring related. Maybe you have some failing hardware or a corrupt filesystem. You could try a MySQL forum to see if they can help pinpointing the cause of the problem.

      Comment


      • #4
        Thanks, I have posted to a MySQL forum. I am also wondering if the 'char' datatypes in the database could be causing trouble - I was using the VARCHAR datatype and Strings for them in my update(?)

        Comment


        • #5
          FYI, I reinstalled MySQL and now everything runs fine...

          Comment

          Working...
          X