Archives

How Do I Get Meta Data From MySQL Using PHP?

I know you’ve all asked that question before, right? Well maybe not, but I recently had the need to detect what the DATATYPE for arbitrary columns in arbitrary tables were inside a MySQL database using PHP. Turns out that it isn’t all that hard to get.

The main thing about using this method is that we are simply sending SQL queries to MySQL which instruct it to give us the required information. The first one is to retrieve a list of tables for the currently selected database:

The next time is when we retrieve the columns for the table and the column definitions:

Notice that I looped through the results of the first query (the table names). I then use the value of the table names returned from the query to build the query for extracting the column definitions.

Finally, you might ask why I decided to throw all of the results into an array? I could just as easily (probably more easily) have output the results to the console as I ran through the queries. Well, in my situation I needed to actually store and compare the results at a later time. So that’s what I did! Following is an entire code sample that should work; just fill in your login credentials and have fun!

Calling A Stored Procedure In Java With A Blob Field

Here is the situation. You are writing a program in Java. You need to access data in a Microsoft SQL Server via stored procedure and the data has an XML field. A what field? SQL Server 2005 introduces a new datatype for XML. It is essentially a blob field that you can perform XML operations on. For the Java program though, it doesn’t really care about that functionality and can just access it to write it out to a file (or process it inline).

JDBC Support

First off you are going to need to check your JDBC driver documentation thoroughly for which operations it supports. There also seems to be some variation in the actual implementation. For example, I tried used Microsoft’s own JDBC driver in my project but it caused a strange SQL Server error to be thrown when writing back to an XML field. I could not get around it (at least not in a timely fashion) so I switched to using the JTDS JDBC driver (go open-source!).

The Stored Procedure

Let us say that the stored procedure in SQL Server looks something like this:

Nothing major going on here, it just executes a SELECT statement against a table and returns four fields. The “LargeXMLField” column is the one we are looking at as a Blob (actually a Clob).

Writing The Code

The actual code to access the stored procedure should be nothing new and is not complicated at all. First we need to connect to the database in the standard fashion.

You will want to substitute real values in where the [host], [port] and [database] placeholders are in the database url.

Next we will use the CallableStatement object to execute the stored procedure.

So you see we use the proc.getMoreResults() to iterate through multiple ResultSet objects that may be returned from a stored procedure. We then call proc.getResultSet() to retrieve the ResultSet that has our data. We fetch our data field as a Clob object and use it’s getAsciiStream() method to get an input stream to read from. After that reading from the stream and writing out to another stream is a straightforward task.

How Can I Recover My Lost MySQL root Password?

Many people use the MySQL open source relational database server. Mostly for websites, but I’ve also seen it used elsewhere. One problem that I recently ran into with my local development installation is… “what was my root password again?” Aaaggghh!

OK, sorry. Don’t panic; it can be recovered. A quick survey of the internet and I found this very simple and very useful how-to Recover lost MySQL root password mini-HowTo. Is is very well written and easy to follow… but, it only covers the process from a Linux installation. The basic trick here in either platform is to restart your MySQL server in and tell it to skip the grants table. So here is my addition on the Windoze platform.

Recover Your Lost root Password In MySQL (Windows)

  1. Stop the MySQL server
    1. You can accomplish this by opening the Services control panel applet, finding the MySQL service listing and clicking the Stop icon.
    2. Alternately you can open up the command line and type > net stop MySQL
  2. Start the MySQL server and skip grants
    1. Go to the MySQL installation directory and then the \bin directory under that (in the console)
    2. Type > mysqld-nt --skip-grant-tables
    3. Now the MySQL server should be running wide open (ie. every user that logs in has full access)
  3. Change root password
    1. Now from another console open the mysql client
    2. Run the following SQL query replacing the dots (…) with your new password
  4. Flush the privileges
  5. Quit mysql
  6. Stop MySQL server
    I tried actually doing a CTRL-C in the console that was running mysqld… but that did not work. So I just opened up the handy-dandy task manager, found the mysqld-nt.exe listing and clicked on it and finally clicked the End Process button
  7. Restart MySQL server normally
    1. You can accomplish this by opening the Services control panel applet, finding the MySQL service listing and clicking the Start icon.
    2. Alternately you can open up the command line and type > net start MySQL

There you have it! All should be well in the world once again and you should now be able to login to MySQL with your new root password. If not… umm… reinstall? lol