Out of Memory: February 2013

Friday, February 8, 2013

Database handling for TimeZones

Today I’ve been working with Postgresql and MySQL trying to figure out how they handle date-time values and timezones. This is actually quite tricky, so I wanted to write it down for later. First, both databases have two different types of columns:

One that stores the date-time without any time zone information
One that stores the date-time with time zone information

For Postgresql, these are:

timestamp without time zone
timestamp with time zone

respectively.

For MySQL, these are:

datetime
timestamp

There are a number of things to consider when dealing with date-time values:

What if the timezone of the server changes?
What if the server moves physical locations thereby indicating a new time zone?
What if the timezone of the database server changes (different than the timezone of the server)?
How does the JDBC driver handle timezones?
How does the database handle timezones?

To figure all of this out, it is important to understand how the date-time value goes from the application, to the database, out of the database and back to the application (for inserts and later selects). Here is how this works for values without timezone information:

Insert

Java creates a java.sql.Timestamp instance. This is stored as UTC
Java sends this value to the JDBC driver in UTC
The JDBC driver sends the value to the database in UTC
The database converts the date-time from UTC to its current timezone setting
The database inserts the value into the column in the current timezone (NOT UTC)

Select

The database selects the value from the column in the system timezone
The database converts the value to UTC
The database sends the UTC value to the JDBC driver
The JDBC driver creates a new java.sql.Timestamp instance with the UTC value

For this scenario, you will run into major issues if the the server or database timezone changes between Insert #5 and Select #1. In this case, the value will not be correct. The only way to make things work correctly for this setup is to ensure no timezone settings ever change or to set all timezones to UTC for everything.

For the types that store timezone information, here is how the data is passed around:

Insert

Java creates a java.sql.Timestamp instance. This is stored as UTC
Java sends this value to the JDBC driver in UTC
The JDBC driver sends the value to the database in UTC
The database calculates the offset between the current timezone and UTC
The database inserts the value as UTC into the column along with the offset it calculated

Select

The database selects the value from the column in UTC along with the offset
The database sends the UTC value to the JDBC driver
The JDBC driver creates a new java.sql.Timestamp instance with the UTC value

Since the JDBC driver only handles UTC values, there is no potential for data mangling here since everything is stored in UTC inside the database. Although the database stores the timezone information along with the date-time value in UTC, it isn’t ever used because the JDBC driver doesn’t care what the original timezone was and ignores that value.

http://brian.pontarelli.com/2011/08/16/database-handling-for-timezones/

Tuesday, February 5, 2013

JSP Variable Accessing in JavaScript

alert("${variable}");

alert("<%=var%>");

or full example

<html> 
<head>
<script language="javascript"> 
function access(){ 
<% String str="Hello World"; %>
var s="<%=str%>"; 
alert(s); 
} 
</script> 
</head> 
<body onload="access()"> 
</body> 
</html>

source

http://stackoverflow.com/questions/4803906/jsp-variable-accessing-in-javascript

Monday, February 4, 2013

Java Static Synchronized [duplicate]

In the case of

public void synchronized f(){...}

The synchronization is per instance of the of enclosing class. This means that multiple threads can callf on different instances of the class.

For

public static void synchronized f(){...}

Only one thread at a time can call that method, regardless of the number of instances of the enclosing class.

Technically, the monitor taken by synchronized in the first example is the of the object instance and the monitor take in the second example is that of the Class object.

Note that, if you have classes of the same name in different ClassLoaders, the do not share the same monitor, but this is a detail you're unlikely to encounter.

http://stackoverflow.com/questions/6443872/java-static-synchronized

Saturday, February 2, 2013

Oracle Equivalent to MySQL INSERT IGNORE?

If you're on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX:

SQL> create table my_table(a number, constraint my_table_pk primary key (a));

Table created.

SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */
  2  into my_table
  3  select 1 from dual
  4  union all
  5  select 1 from dual;

1 row created.

Create Unique Index with IGNORE_DUP_KEY ON/ OFF on SQL Server column

Unique index/ constraint in SQL Server is used to enforce uniqueness over an SQL Column. That is, if a particular column(s) is defined with UNIQUE constraint/ index, user cannot insert duplicate values in that column.

We can have only one NULL value for that column. means, not more than one row can contain NULL value in that column.

While creating a UNIQUE index, we also can specify how to handle duplicate violation. I.e. whether to throw an error, or silently ignore adding a row having a value getting duplicated for that column.

Try following example -

create table #Countries (Id int, Name varchar(20))
GO
ALTER TABLE #Countries
ADD CONSTRAINT IX_Countries
UNIQUE (Name)
WITH (IGNORE_DUP_KEY = ON)
GO
insert into #Countries (Id, Name)
select 1, 'India'
union
select 10, NULL
union
select 11, NULL
union
select 2, 'Australia'
union
select 3, 'India'
GO
select * from #Countries

drop table #Countries

This query inserts only 3 rows in the table

10    NULL
2    Australia
1    India

But it does not throw any error, because we have configured to ignore duplicate rows.

In the same query if you set IGNORE_DUP_KEY =OFF, it will give you following message:

Violation of UNIQUE KEY constraint 'IX_Countries'. Cannot insert duplicate key in object 'dbo.#Countries'.

http://dotnetnsqlcorner.blogspot.com/2012/06/create-unique-index-with-ignoredupkey.html

Out of Memory