Pages

Tuesday, November 30, 2010

PHP substring with UTF-8

You may have noticed strange characters (like �) to appear when you try using the PHP substr function with a UTF8 string. The solution is easy, elegant and core PHP: mb_substr

For example mb_substr("Greek χαρακτήρες",0,10,"UTF8"), will return the first 10 UTF8 characters of our UTF8 string.

For more details see the PHP mb_substr manual.

Wednesday, November 24, 2010

Searching for text in stored procedures and functions in SQL Server

The following query comes in handy when you need to find which user-defined stored procedures or function definitions contain a certain string.


SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%<StringToSearch>%' 


This will return a list of names and definitions of all stored procedures and functions whose definition contains the string <StringToSearch>. If you wish to limit the results to stored procedures only then append the clause:


    AND ROUTINE_TYPE='PROCEDURE'


Likewise, if you wish only functions to be returned then use:


    AND ROUTINE_TYPE='FUNCTION'

Monday, November 22, 2010

Prevent Excel from auto-formatting dates when pasting

A feature of Microsoft Excel that gets pretty annoying at times is that it automatically formats any dates that you paste into it, even non-date strings like 1-1. In order to disable this behavior for a single paste in Excel 2007 follow these steps:

1. Select the cells you want to paste into. Right click, select Format Cells and in the Category list select Text.
2. Copy the text that you want to paste. Instead of simply pasting into the cells click on the arrow below Paste in the Home tab. Click Paste Special, then Text, and finally the OK button.

Friday, November 19, 2010

Write DOM Document to file in Java

There are times when you need to write a Java DOM Document to a file, lets say to save your data. Your Document may represent XML, HTML or any other markup language. Here is a simple function that will do the job:

/**
 * Saves a DOM Document to a file.
* @returns: True on success, false otherwise.
*/
public boolean saveDOMtoFile(Document doc, File myFile)
{
   try{
       TransformerFactory transformerFactory = TransformerFactory.newInstance();
       Transformer transformer = transformerFactory.newTransformer();
       DOMSource source = new DOMSource(doc);
       FileOutputStream out = new FileOutputStream(myFile);
       StreamResult result =  new StreamResult(out);
       transformer.transform(source, result);
       out.close();
  }catch(Exception e){ // You may catch exceptions individually if you wish
      //Something went wrong...
      return false;
 }
  return true;
}

You will also need to import any necessary packages. These should do:


import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import org.w3c.dom.*;
import java.io.*;

Thursday, November 18, 2010

mySQL and UTF-8 character display using PHP

Many developers face character rendering issues when fetching UTF-8 stings from a mySQL database using PHP. Even though the page character encoding is set to UTF-8 and the database collation is also set to utf8_general_ci.

For me the problem was resolved by sending the following query from PHP:
 mysql_query("SET NAMES utf8");

just after using mysql_connect and mysql_select_db PHP functions.

Wednesday, November 17, 2010

Row order in Transact-SQL (SQL Server)

Including a column with the row number in the results of an SQL query is a pretty useful feature and the syntax is a bit hard to remember, so here is an example:


SELECT 
    ROW_NUMBER() OVER (ORDER BY <ColumnName> ASC) AS ROWID
    , * 
FROM <TableName>


This query returns all the rows of table <TableName>, including an additional column ROWID up front. The column <ColumnName> is used for determining the order in which the rows are numbered.

This feature works in SQL Server 2005 and later.