Moving from MySQL to PostgreSQL

Using MySQL, MSSQL  for a long time and moving to PostgreSQL would be confusing and tiresome process for some of the developers, as there are some commonly used queries, practices are different in Postgres. Some of these changes seem strange and take sometime to find the existing queries, understand and fix them to make it work again.

Here I’ve compiled some of the changes to get started in fixing the Queries and write new Queries.

MySQL PostgreSQL
Comment Uses Non standard # to begin a comment Line PostgreSQL use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
Quotes Uses ‘ or ” to quote values. in WHERE clause you can use both. This is not ANSI standard for db uses only single quotes for this. in WHERE clause you can only use ‘ single quotes. Double quotes are used to quote system identifiers; field names, table names, etc.
accent/Backtick uses ` (accent mark or backtick) to quote system identifiers This is non standard. so not used/allowed
String Comparison string comparisons are case-insensitive string comparisons are case-sensitive

  • Need to use correct case in queries
  • Need to use case conversion function to search
  • To search / compare case-insensitive use operator, like ILIKE or ~*
Database, table, field and columns names Database, table, field and columns names can be case-sensitive or not, depending on which OS you are using Database, table, field and columns names are case-independent, unless they are created with double-quotes around their name, in which case they are case-sensitive.
Date Handling Both differ most in handling of dates, and the names of functions that handle dates.
Logical Operators uses C-language operators for logic. Ex: ‘foo’ || ‘bar’ means ‘foo’ OR ‘bar’, ‘foo’ && ‘bar’ means ‘foo’ and ‘bar’. This violates database standards and rules in a significant way Follows the standard, uses || for string concatenation Ex: ‘foo’ || ‘bar’ = ‘foobar’.

 

 

Snippet to Dynamically add rows to a table

This code snippet will help you to add rows dynamically in a html table.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Insert Table Row using DOM</title>
<script language="javascript">
function addRow()
{
var tbody = document.getElementById("table1").getElementsByTagName("tbody")[0];
var row = document.createElement("TR");
var cell1 = document.createElement("TD");
var inp1 =? document.createElement("INPUT");
inp1.setAttribute("type","text");
inp1.setAttribute("value","New row");
cell1.appendChild(inp1);
var cell2 = document.createElement("TD");
cell2.innerHTML = "label3";
var cell3 = document.createElement("TD");
cell3.innerHTML = "label4";
row.appendChild(cell1);
row.appendChild(cell2);
row.appendChild(cell3);
tbody.appendChild(row);
//alert(row.innerHTML);
}
</script>
</head>
<body>
<tableid="table1">
<tbody>
<tr>
<td><inputtype=textvalue="Original Row"></td>
<td>label1</td>
<td> label2</td>
</tr>
</tbody>
</table>
<inputtype="button"value="Insert Row"onClick= "addRow();">
</body>
</html>

Most of the code is self explanatory, so am leaving it unexplained. If anybody needs help, post your questions, will help you.

Get computed Width of an HTML Element

This function will help you to get the computed width(actual width) of an HTML Element/Object. For example you may be using a div to show some contents inside that. But want to calculate the actual width of the div after loading the contents inside it. There comes this function to help you.
function getComputedWidth(theElt){
var browserName=navigator.appName;
if (browserName=="Microsoft Internet Explorer"){
var is_ie=true;
} else {
var is_ie=false;
}
if(is_ie){
tmphght = document.getElementById(theElt).offsetWidth;
}
else{
docObj = document.getElementById(theElt);
var tmphght1 = document.defaultView.getComputedStyle(docObj, "").getPropertyValue("width");
tmphght = tmphght1.split('px');
tmphght = tmphght[0];
}
return tmphght;
}
< div id="demo" onclick="alert(getComputedWidth('demo'));">hello there< /div>

To know the computed height of an element see here

Get computed Height of an HTML Element

This function will help you to get the computed height (actual height) of an HTML Element/Object. For example you may be using a div to show some contents inside that. But want to calculate the actual height of the div after loading the contents inside it. There comes this function to help you.


function getComputedHeight(theElt){
var browserName=navigator.appName;
if (browserName=="Microsoft Internet Explorer"){
var is_ie=true;
} else {
var is_ie=false;
}
if(is_ie){
tmphght = document.getElementById(theElt).offsetHeight;
}
else{
docObj = document.getElementById(theElt);
var tmphght1 = document.defaultView.getComputedStyle(docObj, "").getPropertyValue("height");
tmphght = tmphght1.split('px');
tmphght = tmphght[0];
}
return tmphght;
}

< div id=”demo” onclick=”alert(getComputedHeight(‘demo’));”> hello there< /div>
Thanks to Richard A who made the code complete to run as a demo.

To know the computed width of an element see here

Installing Apache,PHP5,MySQL on Ubuntu

Apache
sudo apt-get install apache2

PHP
sudo apt-get install php5
sudo apt-get install libapache2-mod-php5

sudo /etc/init.d/apache2 restart

MySQL
To install the MySQL Server and Client packages.
sudo apt-get install mysql-server mysql-client
This will install the mysql server and client packages.
By default, recent Ubuntu/Debian systems install a MySQL Server from the 5-branch.

Set MySQL Root Password
By default, the root account of the MySQL Server is empty. To set the mysql root password do the following.
sudo mysqladmin -u root -h localhost password ‘mypassword’ #replace the ‘mypassword’ with your password.

Connect to MySQL from PHP / Ruby
Now you have Apache+PHP already installed, and want to connect to MySQL from PHP scritps.
For that you have to install one library, which is used to connect to mysql from PHP. Use this command to install the library.

sudo apt-get install php5-mysql

In case you are using Ruby, use the command
sudo apt-get install libmysql-ruby to connect to MySQL from Ruby.

Note : while restarting the apache if you get the following error
apache2: Could not reliably determine the server’s fully qualified domain name, using 127.0.1.1 for ServerName

then open the /etc/apache2/apache2.conf file and add the following line
as the last line

ServerName “YourSitename or Servername”

save the file and restart the apache by giving the /etc/init.d/apache2 restart command, you will not get the error anymore.

To Install MySQL Query Browser, an GUI for MySQL

sudo apt-get install mysql-query-browser

After installation, to start MySQL Query Browser go to Applications > Programming > MySQL Query Browser.

Install PHP-GD Library

The GD Graphics Library is useful for dynamically creating, manipulating images. You will need to compile PHP with the GD library of image functions for this to work. However Ubuntu (and Debian) comes with package called php5-gd

To install GD for PHP use the following command.

sudo apt-get install php5-gd

I’ve collected the information from net which are required while installing and configuring Apache, PHP, MySQL and given here. Hope this will help for somebody who can get all information at one place.

Cannot load mysql extension. Please check your PHP configuration

Everytime I install PHP, MySQL in windows boxes I used to get the error message “Cannot load mysql extension. Please check your PHP configuration“. This is because i’d forget to do the necessary steps.

While wandering thru sites most of the people were looking for the same issues.So decided to write a simple post with the necessary steps to solve this while installing PHP, MySQL in a linux box.

PHP.INI settings

1. Modify extension_dir = “./” to extension_dir = “c:/PHP/ext”
2. make sure php_mysql.dll file is inside the folder c:/PHP/ext
3. uncomment the line ;extension=php_msql.dll i.e., remove the comma in the line. so that line will look like
extension=php_msql.dll

Others
4. Copy the libmysql.dll file to C:\WINDOWS folder
5. restart Apache Service

Disable Text Selection in a web page

Here is a sample code snippet, which can be used to disable the text selection in a web page.

But selecting text inside input fields, such as Textarea, textbox and Select (combo) box is not restricted.

//get the browser name,version details
function agent(v) { return(Math.max(navigator.userAgent.toLowerCase().indexOf(v),0)); }

//variables to hold the browser names, and versions
var is_ie = false;
var is_ff15 = false;
var is_ff20 = false;
var is_ff30 = false;
if(agent('msie'))
    is_ie = true;
else if(agent('firefox/3.0.0'))
    is_ff30 = true;
else if(agent('firefox/2.0.0'))
    is_ff20 = true;
else if(agent('firefox/1.5'))
    is_ff20 = true;

/* This is the function which Disables Text Selection in the Document*/
function selstart(e){
    //if not ie and !not ie then return
    if (!agent('msie') && !(!agent('msie')))
        return;

    var selobj=(!agent('msie'))? e.target : event.srcElement
    var topelement=(!agent('msie'))? "HTML" : "BODY"

    if(agent('msie')){
        if ( selobj.tagName!=topelement &&
                (selobj.tagName=='SELECT' ||
                selobj.tagName=='TEXTAREA' ||
                selobj.tagName=='INPUT')
              ){
                  selobj.select();
                  return true;
              }
        else
            return false;
    }else{
        document.body.style.MozUserSelect='none';
        document.body.style.userSelect='none';
    }

}
document.onselectstart=selstart;

This function uses another function named agent to get the browser agent name, whether the user’s browser is Firefox or IE or others. And some global variables for storing browser names and versions.

Whenever a select text event (onselectstart) is fired, the function selstart is executed and the text selection inside the web page is restricted. The function will return true to disable text selection.

Javascript function to Validate IP Address (IPv4)

In one of my current project ( Front end for an ISM Device ), I’ve to validate IP Addresses using JavaScript.

And wrote this function fnValidateIPAddress

The function takes an IP Address as input string and returns either true if the input string is a valid IP Address or false if not. This function checks for the Class C IP Addresses only. This function can be changed further to validate different IP Classes.

Continue reading “Javascript function to Validate IP Address (IPv4)”

piqqus.com Invites Available

Anybody want Piqqus.com invite? Let me know will send you an invitation.

For Newbies, Piqqus.com allow you to vote for people’s site in return you?ll get some points in order for you to promote your article in future. A tool to promote your blog and your sites.

piqqus.com is a stumble/diggs/propells exchange website, meaning that you stumble the available opportunity (other people’s topics), earn stumble point. Once you collect enough stumble point, you may create a campaign for your own topic, other people will stumble for you. This feature is for digg.com, propeller.com sites also.

Even piqqus.com is open for registrations, you cannot take part of the campaigns, i.e., you cannot get points,create campaigns for your site,blog, if you have registered without an invitation.

So invitation will give an option to get points earned, and also you can use the points later for promoting your sites,blogs.

Here is an invite http://piqqus.com/….php?r=97..&c=….mohy49c…4674

A Simple Tooltip with Images and Text

As a web programmer we need to show tool tip often in our pages but not in an old fashioned way. Something appealing and subtle to the page layout design.

Mostly we go for available open source scripts which are free. I were also doing the same until I decided to write a simple one when I were in need of customizing a free snippet, which took more time.

Writing a tool tip is quite simple

This is a a simple Tooltip for you web pages with minimal code. Images , Text and HTML code can be shown inside the tooltip

Download the source code Here