Archive for October, 2011

New release: SQL Pretty Printer Version 3.2.8

Friday, October 14th, 2011
  • Introduce 14 new format options in this version and there are more than 90 format options in SQL Pretty Printer.
  • Fix a bug that menu and toolbar disappeared in vs2010 after first installation.
  • About 2 times faster when format some big SQL scripts.
  • Fix a bug that can’t format join clause correctly in this sql: 
    SELECT
  *
FROM
  S1
  natural JOIN W3
  LEFT OUTER JOIN S2
    ON S1.S = S2.S
  • Improve format result if there is a comment after WHERE keyword directly.
  • Lots of minor bugs fixed.

Posted in release, sql pretty printer | No Comments »

How to add syntax colored and well formatted SQL in your wordpress blog

Tuesday, October 4th, 2011

People always post SQL code into blogs and forums need a tool to help them to prepare dull monochrome, badly-formatted SQL code for HTML-based publication in a nice clean style.

SQL Pretty Printer is able to output HTML in both <code> and <pre> blocks in a convenient way with code like this:

	<div><span style="font-family: Courier New; font-size: 10pt;">
	<span style="color: blue; ">WITH</span> <span style="color: maroon; ">mycte</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span>
	<br/>     <span style="color: blue; ">AS</span> <span style="color: maroon; ">(</span><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">=</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: red; ">'hello'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'a'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span>
	<br/>         <span style="color: blue; ">UNION</span> <span style="color: blue; ">ALL</span>
	<br/>         <span style="color: blue; ">SELECT</span> <span style="color: fuchsia; font-style: italic; ">CONVERT</span><span style="color: maroon; ">(</span><span style="color: black; font-style: italic; ">VARCHAR</span><span style="color: maroon; ">(</span><span style="color: black; ">1000</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span> <span style="color: maroon; ">x</span> <span style="color: silver; ">+</span> <span style="color: red; ">'b'</span><span style="color: maroon; ">)</span>
	<br/>         <span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/>         <span style="color: blue; ">WHERE</span>  <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span> <span style="color: silver; "><</span> <span style="color: black; ">10</span><span style="color: maroon; ">)</span>
	<br/><span style="color: blue; ">SELECT</span> <span style="color: maroon; ">x</span>
	<br/><span style="color: blue; ">FROM</span>   <span style="color: maroon; ">mycte</span>
	<br/><span style="color: blue; ">ORDER</span>  <span style="color: blue; ">BY</span> <span style="color: fuchsia; font-style: italic; ">Len</span><span style="color: maroon; ">(</span><span style="color: maroon; ">x</span><span style="color: maroon; ">)</span><span style="color: silver; ">,</span>
	<br/>          <span style="color: maroon; ">x</span><span style="color: silver; ">;</span> 
	</span></div>

You can copy and embedded above HTML code into your blog to get a colored SQL like this:

WITH mycte(x)
     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘a’)
         FROM   mycte
         WHERE  Len(x) < 10
         UNION ALL
         SELECT CONVERT(VARCHAR(1000), x + ‘b’)
         FROM   mycte
         WHERE  Len(x) < 10)
SELECT x
FROM   mycte
ORDER  BY Len(x),
          x; 

But in wordpress, the output will be looked like this that add additional lines.

WITH mycte(x)

     AS (SELECT x = CONVERT(VARCHAR(1000), ‘hello’)

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘a’)

         FROM   mycte

         WHERE  Len(x) < 10

         UNION ALL

         SELECT CONVERT(VARCHAR(1000), x + ‘b’)

         FROM   mycte

         WHERE  Len(x) < 10)

SELECT x

FROM   mycte

ORDER  BY Len(x),

          x; 

It seems that editor of wordpress add an additional <br/> tag at the end of each line. this issue can be resolved by changing

function wpautop($pee, $br = 1) {

to

function wpautop($pee, $br = 0) {

in wp-includes/formatting.php

Posted in formatting, sql, wordpress | No Comments »

case(Capitalization) user defined identifier in SQL statement

Tuesday, October 4th, 2011

SQL Pretty Printer provides the ability to capitalize user defined identifier such as stored procedure name start with sp_ in SQL Server.

name: case_prefix_identitfer

setting path: capitalisation -> Prefix_ident

name: identifier_prefix_list

setting path: capitalisation -> Identifier prefix list

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_prefix_identitfer to uppercase and identifier_prefix_list to sp_,

EXECUTE SP_EXECUTESQL
  @query,
  n’@age int’,
  @age = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case variable name in SQL statement

Tuesday, October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of variable name in SQL statement, this feature helps you to find out variable in SQL statement quickly.

name: case_variable_name, default is no change.

setting path: capitalisation -> Variable

EXECUTE Sp_executesql
  @query,
  n’@age int’,
  @age = 25 

After setting case_variable_name to upper case,

EXECUTE Sp_executesql
  @QUERY,
  n’@age int’,
  @AGE = 25 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case alias name in SQL statement

Tuesday, October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of alias name in SQL statement, this feature helps you to find out alias in SQL statement quickly.

name: case_alias_name, default is no change.

setting path: capitalisation -> Alias

SELECT departmentname,
       Min(salary) AS min_salary
FROM   employees
GROUP  BY departmentname 

After setting case_alias_name to upper case,

SELECT departmentname,
       Min(salary) AS MIN_SALARY
FROM   employees
GROUP  BY departmentname 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case column name in SQL statement

Tuesday, October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of column name in SQL statement, this feature helps you to find out columns in SQL statement quickly.

name: case_column_name, default is no change.

setting path: capitalisation -> Column

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_column_name to upper case,

SELECT DEPARTMENT_ID,
       Min(SALARY)
FROM   employees
GROUP  BY DEPARTMENT_ID 

Posted in format option, sql pretty printer, v238 | No Comments »

Upper case or lower case table name in SQL statement

Tuesday, October 4th, 2011

Unlike some other SQL formatting tools, SQL Pretty Printer able to control the case of table name in SQL statement, this feature helps you to find out tables in SQL statement quickly.

name: case_table_name, default is no change.

setting path: capitalisation -> Table

SELECT department_id,
       Min(salary)
FROM   employees
GROUP  BY department_id 

After setting case_table_name to upper case,

SELECT department_id,
       Min(salary)
FROM   EMPLOYEES
GROUP  BY department_id 

Posted in format option, sql pretty printer, v238 | No Comments »

Case(capitalization) options used when formatting SQL statements

Tuesday, October 4th, 2011

There are 5 case options available while formatting SQL tokens in SQL Pretty Printer.

1. Uppercase(All caps): All letters are capital letters.

2. Lowercase: All letters are lower case.

3. InitCap: Only the first letter of the whole word capitalized.

4. No Change: Case of all letters in the word not changed.

5. InitCapEachWord: Also known also CamelCase or Pascal case, First letter of each word is capitalized.

TCaseOption = (coUppercase,coLowercase,coInitCap,coNoChange,coInitCapEachWord);

Posted in format option, sql pretty printer | No Comments »

Gudu Software 2002-2011  Privacy Policy  Terms of Use  About Us