ASO and MySQL Workbench error

I recently switched over to A Small Orange as a host provider and have been happy with their services so far. My websites are running faster than they did before. But now I run into a small issue. I use MySQL Workbenchto work on databases. It’s a great software. But I get an error when I try to connect to a database remotely.

error

I did a few searches and found a post regarding the skip-networking entry in the config file. Unfortunately, I’m in a shared environment and do not have access to that file. I have contacted the ASO tech support. Hope they can find a solution. I never had this issue with other host provider.

MSSQL Driver for PHP5.3

Before using PHP5.3, I never had to worry about MSSQL support. PHP included a dll file to use with MSSQL Server 2000 and up. This is no longer true if you decide to upgrade to PHP5.3.x. For months I looked for ways to connect to our MSSQL Server using PHP5.3.x. I finally found the driver and it works. It’s called SQLSRV and you can download it from http://sqlsrvphp.codeplex.com. It’s easy to install, just read the CHM file.

  • Run the exe to unpack it
  • Rename the folder to Microsoft SQL Server Driver for PHP (optional)
  • Move this to your %Program Files% directory (optional)
  • Inside that directory you will find several dll files. It supports both NTS (Non Thread Safe) and TS (Thread Safe). You also need to choose between VC6 or VC9 compiler. This will determine which file will be used.
  • Copy that file to %Installation Directory%PHPext (assuming you are using the ext directory for your extensions.
  • Edit your php.ini. In the extensions section add the following extension=php_sqlsrv_53_ts_vc6.dll – or whatever file you copied to your ext directory.
  • Restart your web server and that should be it.

Here’s a quick sample code to get you started.

<?php

$servername = "SQLSERVER_NAME_OR_IP";
$uid = "db_username";
$pwd = "db_passwd";
$connectionInfo = array (
"UID"=>$uid,
"PWD"=>$pwd,
"Database"=>"database_name"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) {
echo "ERROR: DB Connection";
die(print_r(sqlsrv_errors(), true));

}
$stmt = sqlsrv_query($conn, "SELECT * FROM table");

if ($stmt) {
while ($row = sqlsrv_fetch_array($stmt)) {
echo $row[0].'<br />';
}
}

?>

That should do it. I have only tested this on Windows 7 Professional (32bit), MSSQL 2005 (32bit) Standard on a remote Windows 2008 Server Standard R2, PHP5.3.3, Apache2.2.15, and driver version 1.1 (version 2 is now available) . Make sure your MSSQL server can accept remote connections if it is on a different server than your web server. The above instructions shouldn’t be too different on other versions.

If you have a phpinfo() page, you will see the image below.

sqlsrv

Hope this helps you get started. Happy coding.

Windows 2008 and MSSQL Server 2005 not allowing remote connections

If you think you checked every setting in MSSQL Server that allows remote connections, you may have missed the Windows firewall setting. I have installed MSSQL 2005 many times on Windows 2000 Server, 2003, and 2008 and never ran into the remote connection problem. What’s happening is I’m unable to connection using the Management Studio on another machine into the server.

  • Checked the Configuration Tools/SQL Server Configuration Manager
  • Checked SQL Browser service is running
  • Pinged the IP address
  • Checked the Surface Area Configurations and made sure it allowed local and remote connections using TCP/IP only

It turns out that Windows firewall didn’t add MSSQL to the exceptions list. So here’s how you do it. Run firewall.cpl to bring up the Windows Firewall settings. On the left click on “Allow a program through Windows Firewall”. Make sure you are on the “Exceptions” tab and click on “Add program…”. If it’s not on the list, browse for the sqlservr.exe. You will find this in [INSTALL_DIR]MSSQL.1MSSQLBinnsqlservr.exe.

I never had this issue before. Not sure what caused it. Maybe Windows 2008 SP2. The versions I’m using are Windows Server 2008 RC2 and MSSQL Server 2005 Standard.

Debian5 Lenny and MySQL Server 5.1.x

I am running Debian5 Lenny which is running Virtualmin and Webmin. The default configurations install MySQL server 5.0.51. MySQL’s website has version 5.1.x. I wanted to update the version on my Debian box. Unfortunately, Debian considers MySQL 5.0.51 as the stable version, while version 5.1.49 is considered unstable. You can install it by updating your /etc/apt/sources.list file. Add the following lines and save.

deb http://ftp.de.debian.org/debian/ sid main
deb-src http://ftp.de.debian.org/debian/ sid main

After saving the file, update the apt-get.

apt-get update

You can continue to use apt-get and install the newer version of MySQL server or use the Webmin web gui. You can search for it under System/Software Packages, then search for “mysql-server”. You will see the newer version. Before you install it, make sure you have backed up your database, it will remove the old one.

It took some time to find the information. I had to browse different websites and piece together the information. It’s funny how something this common, is hard to find a solution to on one web page. I hope this helps Linux noobs like me out there. Do keep in mind that Debian considers this version of MySQL unstable.

Connect to MSSQL 2005 using PHP

Here’s a couple of quick tips to connecting to a MSSQL 2005 server using PHP on a Windows webserver. You will need to enable the php_mssql.dll in your php.ini file.

Here’s the code to connect to the server.

<?php

$conn = mssql_connect('my_server', 'db_username', 'db_password');
if (!$conn) { die('ERROR: Unable to connect to the database.'); }
mssql_select_db('database_name');

?>

Here is a helpful function I found that will help escape strings. I found this at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php.

<?php

function ms_escape_string($data) {
//if ( !isset($data) or empty($data) ) return ";
if ( !isset($data) or empty($data) ) return """;         // modified to handle empty $data
if ( is_numeric($data) ) return $data;

$non_displayables = array(
  '/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
  '/%1[0-9a-f]/', // url encoded 16-31
  '/[x00-x08]/', // 00-08
  '/x0b/', // 11
  '/x0c/', // 12
  '/[x0e-x1f]/' // 14-31
);
foreach ( $non_displayables as $regex ) {
  $data = preg_replace( $regex, ", $data );
  $data = str_replace("'", """, $data );
  //return $data;
  return "'".$data."'";        // modified to handle empty $data
}

?>

Here is a sample query to the database.

<?php

$id = ms_escape_string($_GET['id']);    // escape variable
$sql = 'SELECT id FROM table_name WHERE id='.$id.";
$rs = mssql_query($sql);

if (!$rs)
{
  echo 'ERROR: Unable to get records. '.mssql_get_last_message();    // equivalent to mysql_error()
}else{
  while ($row = mssql_fetch_assoc($rs))
  {
    echo 'The id = '.$row['id'].'<br />';
  }
}

?>

Another thing I learned from the article is the equivalent of “mysql_insert_id()”. It is “SELECT @@IDENTITY”. To handle dates and make them friendly to PHP use CONVERT(VARCHAR, [date], 20). This will display it as if you are using date(‘Y-m-d H:i:s) – 24 hour format. So an example would be…

SELECT CONVERT(VARCHAR, getdate(), 20) AS now

I tried to use PDO but was having a hard time getting it to work. I kept getting errors that had something to do with functions. I searched for solutions and tried them but none worked for me. Also, the rowCount() will always return less than or equal to 0.

I had trouble on both sides PHP and Microsoft. This seems to be the best solution that works for me. Hope it helps others.

Allowing network connections to MySQL in OS X Leopard Server

For the past couple of days I’ve been trying to set up one of our XServe running Leopard Server at work for web hosting. I was able to configure the Web, AFP, and SMB properly. The problem I was having was configuring MySQL. It was easy to enable but trying to connect to it from other computers in the same domain was the problem. There’s a checkbox with “Allow network connections” in the Server Admin section of MySQL. So I checked it, save, and restarted it. I still wasn’t able to connect. I checked for firewall settings. I pinged the host and got a response. I couldn’t figure out what was wrong. So I googled it.

The common answer I found was to edit the /etc/mysql/my.cnf file. Well it’s not there but I did find a /etc/my.cnf file. So I tried editing that file and restarting. It didn’t work.

Then I remembered that the php.ini file that Leopard Server is using isn’t in the default location /etc/php.ini, but it ran the /private/etc/php.ini. So I looked in there and found another my.cnf file. I edited the file by adding # in front of skip-networking entry. I restarted it and it finally worked.

So here’s what you need to do:

  • Go into Server Admin and check the Allow network connections and save
  • Comment out the skip-networking entry from /private/etc/my.cnf
  • Add the IP address of the computer you will be accessing MySQL remotely
  • Give it the privileges it needs
  • Restart MySQL

That should allow you to connect to MySQL running on Leopard Server remotely.

MySQL backup and restore databases with views

We recently installed OS X Leopard Server at work. I had to take MySQL databases from our old Tiger Server and restore them onto the new XServes. Both servers are running MySQL Server version 5.0.45 but our Tiger server is running MySQL Client version 5.0.22 while our Leopard servers are running MySQL Client version 5.1.18. According to MySQL’s documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html):

Prior to release 5.0.48, this option did not create valid SQL if the database dump contained views. The recreation of views requires the creation and removal of temporary tables and this option suppressed the removal of those temporary tables. As a workaround, use –compress with the –add-drop-table option and then manually adjust the dump file.

I tried the –compress option but it didn’t work. I still had to manually adjust the SQL file. The I tried the GUI tool from MySQL, http://dev.mysql.com/downloads/gui-tools/5.0.html (MySQL Administrator).

mysql admin

Once you create the backup, you can restore it using the same application on the newer server. It will even include the views. Once you start using the newer client, you shouldn’t have a problem restoring databases with views from backups made with the mysqldump command.

How to convert MSSQL date format to MySQL

I recently had to transfer over some databases from MSSQL Server 2000 to MySQL. Unfortunately, the date format that MSSQL keeps is MM-DD-YYYY HH:MM:SS am/pm. MySQL uses YYYY-MM-DD HH:MM:SS (24hour format). For example, 8/14/2007 5:00:00 PM (MSSQL format) and 2007-08-14 17:00:00 (MySQL format). In MySQL, we can use the date_format(). But what is the equivalent for MSSQL? That would be the CONVERT(). This is how you would use it.

Here’s the query to run on MSSQL:

SELECT CONVERT(VARCHAR(40),[date_column_name],120) AS new_date_format FROM [table_name]

The “new_date_format” is the column name that will display the MySQL formatted date. This should also work if you have a column that stores datestamp and a column that stores timestamp.

You can find a list of expression values from http://msdn2.microsoft.com/en-us/library/ms187928.aspx. Hope this helps somebody.