TechieDan

I’ve been using mysql for quite some time, and ever then after I shifted to a work place which uses msSQL, I felt that I’ve neglected mySQL for awhile. Basically one main question I had previously and just recently asked by my colleague is to how to change the root user password?

Here I will teach you what I usually use which is the mysql command line.

Login to mysql server using the following command prompt

$ mysqladmin -u root -p

In mysql prompt, select database mysql

mysql> use mysql;

Now update your root password by putting a new password in the NEWPASSWORD part

mysql> update user set password=PASSWORD(”NEWPASSWORD”) where User=’root’;

Finally you’ve completed the step. Oh one more final step.

mysql> flush privileges;

Now try quit the mysql command line, and relogin again using the new password. Congrats, your root has a new password

Coding have never been the same!!! Some bad habits when writing codes which are redundant

String[] special = {”a1″,”a2″,”a3″,”a4″};  

Connection con = null;  

PreparedStatement stmt = null;  

PreparedStatement stmt2 = null;  

ResultSet rs = null;  

ResultSet rs2 = null;  

Class.forName (”com.mysql.jdbc.Driver”)  

conn = DriverManager.getConnection (url, userName, password);  

String SQL_QUERY = ” SELECT count(*) FROM tblABC WHERE ” +
” name_tennis LIKE = ? “;  

String SQL_QUERY2 = ” SELECT count(*) FROM tblABC WHERE ” +
” name_swimwear LIKE = ? “;  

stmt = con.prepareStatement(SQL_QUERY);  

stmt2 = con.prepareStatement(SQL_QUERY2);  

for (int i=0;i<special[i].length;i++) {  

      stmt.setString(1,special[i]);  

      rs = stmt.executeQuery();  

      if (rs.next()) {  

         /* More work here */  

      }  

}  

for (int i=0;ii<special[i].length;i++) {  

stmt2.setString(1,special[i]);  

      rs2 = stmt2.executeQuery();  

      if (rs2.next()) {  

         /* More work here */  

      }  

}

The above is a very bad example of coding. It might work, and run smoothly but it’s really bad coding. What one should do is to not to let the code be redundant. For example, put both statements into one the loop.

for (int i=0;i<special[i].length;i++) {  

      stmt.setString(1,special[i]);  

      rs = stmt.executeQuery();  

      if (rs.next()) {  

         /* More work here */  

      }      stmt2.setString(1,special[i]);  

      rs2 = stmt2.executeQuery();  

      if (rs2.next()) {  

         /* More work here */  

      }  

}

What’s the use of this? Well, iif you have an object or rather a Bean to set into, you don’t have to loop and reloop. All can be done in the same loop. Well, hope this brings about some bad habits of coding java.

You’re stuck in a new place, with no Internet connection and you have no extra additional softwares to install for GUI for mySQL updates and stuffs. Now then suddenly the client asked you to backup his database using the UNIX server. You, as a fresh database administrator suddenly felt lost and don’t know what to do.

If only everyone have learnt the basics of command line prompt, one will not have panic like the above scenario. How to backup your mysql database using command line prompt.

Windows Environment for simplicity

C:\> [MYSQL HOME]\bin\ mysqldump -u[user] -p [databasename] > something.ddl

Yes, a single line and it’s all done.

What does this line meant?

The rest is pretty understandable
Example if I have a username ‘root’ for the database and password ‘abc’ with the database name called db and I wanted to save all the data onto a file name data.ddl, this is what I would write below.

If you have any questions pertaining this issue, do drop a line.

mySQL and msSQL has a similar SQL syntax but not the same. To have a grasp of what does this mean, let’s have an exercise to get 10 rows from a table with mySQL.

select * from [tablename] limit 10

Now if you were to run this sql query above on msSQL server you’ll either get a wrong sql query or query not able to compute. So alternative for msSQL is by entering this query instead.

select top 10 * from [tablename]

Simple?? Yes, that’s all there is to it. To those that need further questions answered, there are bound to be helpful people posting or me trying to help people out.