Sunday, December 14, 2014

Mysql date format and string parsing!!


Recently I worked on Mysql and need to work on date and time formats and faced one issue where I need to convert from long date format(e.g Fir Dec 12 00:00:00 IST 2014 )  to date format yyyy-mm-dd format. So for doing this, we need to parse the long date format for that I used concat, str_to_date and substring inbuilt Mysql methods.

substring in Mysql: Substring function used to get the substring from given string. For that we need to specify the ranges of the substring. Below is the example

Syntax:

substring(input string,substringStartingPos[,lenghtOfTheString])

Example:

select substring('Hello substring',4) gives  'lo substring' as it starts from 4th position to end of the string

select substring('Hello substring',4,4) gives 'lo s' as it starts from the 4th position and it takes 4 characters as length specified 4

select substring('Hello substring',-4) gives 'ring' as we specified the range four characters rom back(minus specifies from back).

Similarly I have taken the Day, Month and year from the long date pattern format as below.

select substring('Fir Dec 12 00:00:00 IST 2014',-4) gives '2014' which is a year
select substring('Fir Dec 12 00:00:00 IST 2014',4,4) gives 'Dec' which is a month
select substring('Fir Dec 12 00:00:00 IST 2014',9,2) gives '12' which is a day

Concat in Mysql: Concat function will combine more than one string into one string. Below is the syntax and example.

Syntax:

concat(str1,str2,str3,....)

Example:

select concat('Hello',' ', 'World!!') gives 'Hello World!!' as we passed three string to concat function.

Concat string function used to separate the strings with special characters as delimiters.


Coming to our problem of converting string to date format now as we know concat and substring functions. For that we need to use str_to_date inbuilt Mysql function.

str_to_date in Mysql: str_to_date function as name says, it converts string to date format. Its inverse of date_format() function which converts from date to string.


Syntax:

str_to_date(string,dateformat)


Example:
select str_to_date('Dec 12,2014', '%M %e,%Y') gives '2014-12-12' as %M is for month, %e as day and %Y is for Year.  There are many formats for the date, we can check here for more.

And finally the resulted query for converting from long date format to yyyy-mm-dd is below.

select STR_TO_DATE(concat(substring('Fir Dec 12 00:00:00 IST 2014',4,7),',',substring('Fir Dec 12 00:00:00 IST 2014',-4)), '%M %e,%Y') as date

Which will give the date as '2014-12-12' which is required format.


Happy Coding!!


Thursday, November 6, 2014

MySQL installation failure in OS X Yosemite!!

Recently I upgrade my MacBook Pro to latest Mac OS X version Yosemite. And when I try to install mysql,  after completing the installation process , it is showing "The installation failed" message as shown below. I google for it and I found that actually mysql got installed successfully, but it was showing that message.




You can start working only mysql by starting mysql server. And below are the steps to do it.

To start mysql server:

sudo /usr/local/mysql/support-files/mysql.server start

To stop mysql server:
sudo /usr/local/mysql/support-files/mysql.server stop

to login mysql via terminal:
/usr/local/mysql/bin/mysql

Saturday, October 25, 2014

Difference between objectForKey and valueForKey in iOS

You may get this doubt when you work with NSDictionary in iOS. When you use these methods on Dictionaries, there wont be any much difference between objectForKey and valueForKey in iOS. Both methods behaves almost same on dictionaries. But valueForKey used on Key Value Coding (KVC) as its a key value method.

Where to use: objectForKey will work on NSDictionaries and valueForKey will work on NSDictionaries and KVC. On NSDictionaries both returns null if key not found. And valueForKey throws valueForUndefinedKey: exception on KVC if property not found.  Check the examples below for more clarity.

Usage on NSDictionary: We can use objectForKey: and valueForKey:  on dictionaries. Below is the sample code.

    NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:@"king",@"name",@"9985396338",@"phone", nil];
    // using objectForKey method
    NSLog(@"name is %@", [dict objectForKey:@"name"]);
    // displays null as name1 not found in NSDictionary
    NSLog(@"name1 is %@", [dict objectForKey:@"name1"]);

    // using valueForKey method    
    NSLog(@"phone is %@", [dict valueForKey:@"phone"]);
    // displays null as phone1 not found in NSDictionary
    NSLog(@"phone is %@", [dict valueForKey:@"phone1"]);

Usage on KVC: we can't use objectForKey: method on key-value coding. We can use valueForKey: method. Below is the example

// .h file
@interface valueForKeyTest : NSObject
@property NSString *name,*phone;
@end

// .m file
@implementation valueForKeyTest
@synthesize name, phone;
@end

// main.m file
#import "valueForKeyTest.h"
int main(int argc, const char * argv[]) {
    valueForKeyTest *obj = [[valueForKeyTest alloc] init];

    obj.name = @"king";
    obj.phone = @"9985396338";
    
    // valueForKey on accessor methods
    NSLog(@"name is %@", [obj valueForKey:@"name"]);
    NSLog(@"phone is %@", [obj valueForKey:@"phone"]);

    // this will throw valueForUndefinedKey: as name1 is not a property
    NSLog(@"name is %@", [obj valueForKey:@"name1"]);
}



Key Value Coding: A key is unique value, which identifies the property value of the object. A key generally accessor method or instance variable in the object, Check KVC for more info. KVC has below key value methods.


  • setValue:forKey:
  • valueForKey:
  • setValue:forKeyPath:
  • valueForKeyPath: 
  • setValuesForKeysWithDictionary:
  • dictionaryWithValuesForKeys:


Wednesday, October 15, 2014

tree load error - jasperserver


When you are working with Jasper reporting server, you often face tree load error for the jasper server. I faced this error lot of times and I could not find the root cause for this. But we can fix this problem by clearing the cookies in the browser. 
Below are the steps to clear the cookies for the jasper server in the firefox browser.

  • Go to setttings/Preferences in the firefox browser
  • Go to Privacy tab
  • Click on 'remove individual cookies'
  • Search for localhost or jasper server ip address. See sample image below (for me its local host)
  • Click on 'Remove cookie' button (remove until all localhost cookies are removed)



I have specified for FireFox browser, if you are using other browsers like Chrome, IE you need to follow similar process to clear the individual cookies.

Unable to load metadata for VDB name - Jasper 5.6


When we use Virtual Data Source (VDS) in Jasper Reporting Server 5.6, we will face 'Unable to load metadata for VDB name' error. This is due to unable to handle the relation/mapping between the databases with Foreign keys. We need to specify it manually. Below are the sequence of steps for doing that.
  • Open the file applicationContext-VirtualDatasource.xml 
    • Path jasperreports-server-5.6/apache-tomcat/webapps/jasperserver-pro/WEB-INF
  • Search for translatorConfigList property in the file
  • Add property importPropertyMap after translatorConfigList (check below for property code)
  • Restart the server and it will work.

Below is the property code you need to add in the file:

<property name="importPropertyMap">
  <map>
    <entry key="dw">
      <map>
        <entry key="importer.importKeys" value="false"/>
        <entry key="importer.importForeignKeys" value="false"/>
        <entry key="importer.importIndexes" value="false"/>
        <entry key="importer.importStatistics" value="false"/>
      </map>
    </entry>
  </map>
</property>

In the above code, key value is 'dw' is a name of the database which we set while creating the virtual data source

P.S: In Jasper server 5.5 , you need to restart the server for the most of the cases if you face this error.

Friday, August 22, 2014

Find Vicinal words in python



Recently I came to know about the vicinal words. As python is good for string processing. So thoought  of writing code for finding vicinal words in python. Below is the code to find vicinal words in python.

Vicinal: Vicinals are words where every letter in the word has an alphabetic neighbour. That means, if the word contains a 'C', it also contains either 'B' or 'D'. Check here for more details.


import string

def find_vicinals(input_words):
    vicinal = []
    non_vicinal = []
    #take words one by one
    for word in input_words:
        count = 0
        #logic to compare each char with all for their neighbours
        if len(word) > 1:
            for c1 in word.lower():
                for c2 in word.lower():
                    if c2 == 'a' and ord(c1)+1 == ord(c2) or ord(c2) == ord('z'):
                        count += 1
                        break;
                    elif c2 == 'z' and ord(c2) == ord('a') or ord(c2)-1 == ord(c2):
                        count += 1
                        break;
                    elif ord(c1)+1 == ord(c2) or ord(c1)-1 == ord(c2):
                        count += 1
                        break
            #if match count n word len are same
            #then its vicinal
            if count == len(word):
                vicinal.append(word)
            elif not count:
                non_vicinal.append(word)
        else:
            non_vicinal.append(word)
    #print if list not empty
    if vicinal.__len__():
        print "Vicinals: ", ' '.join(vicinal)
    if non_vicinal.__len__():
        print "Non-vicinals: ", ' '.join(non_vicinal)

#read the line
s = raw_input("Line: ")
# check length n proceed if not zero
while len(s):
    input_words = s.split(' ')
    input_words = ' '.join(s.split())
    for c in string.punctuation:
        input_words= input_words.replace(c,"")
    find_vicinals(input_words.split(' '))
    # read line again
    s = raw_input("Line: ")

Sunday, May 25, 2014

How to convert UUID to String in Python!!!



              A universally unique identifier (UUID) is an identifier standard used in software construction, standardized by the Open Software Foundation (OSF) as part of the Distributed Computing Environment (DCE). Check here for more details.

             I recently faced a  scenario where I need to use string format of the UUID value. I searched in google and could not find proper answer. Finally I got the solution and here it is. After Creating a UUID, we can get different different values depending on the format. For example, we can get int, hex and byte values of UUID. Similarly using urn (Uniform resource name), we can get the string value of the UUID as shown below.

>>> uid = uuid.uuid1() #creating UUID
>>> uid 
UUID('52f8e1ba-e3ac-11e3-8232-a82066136178')
>>> uid.int #int format of UUID
110288963624339905056441828728831697272L
>>> uid.hex  #hexa format of UUID
'52f8e1bae3ac11e38232a82066136178'
>>> uid.bytes  #bytes format of UUID
'R\xf8\xe1\xba\xe3\xac\x11\xe3\x822\xa8 f\x13ax'
>>> uid.urn #uniform resource name
'urn:uuid:52f8e1ba-e3ac-11e3-8232-a82066136178'
>>> 

From the above example uid.urn returns string value 'urn:uuid:52f8e1ba-e3ac-11e3-8232-a82066136178'. But we need only value and we dont need first nine characters 'urn:uuid:', so we can skip those 9 characters to get the string value as shown below.

>>> uid_str = uid.urn
>>> str = uid_str[9:]
>>> str
'52f8e1ba-e3ac-11e3-8232-a82066136178'
>>> 

Happy Coding!!!

Thursday, May 1, 2014

Program to find binary value using bitwise operator in C!!!


/*************************************************
* This program finds the binary value of the given integer value
* Used bitwise operators to find binary value 
* Below is the logic to find binary value
* 1. Need to skip right most 0'1 until 1 comes 
* 2. After that, do AND operation with MASK value  print 1 on true else 0
* 3. Shift one bit left
* 4. Repeat step 2 Until Int size completes
*
*
* Int size is generally 4 bytes which is 32 bits
* MASK value for Int is in hexa (0x80000000)
* Above mask value is taken, because we need to mask Most significant bit (31st bit)
*
*************************************************/

#define INT_SIZE 32
#define MASK  (0x80000000) 
#include&amp;lt;stdio.h&amp;gt;
void findBinary(int num)
{
 int pos;
 int i=0;
 int flag = 0;
 
 //finding location from right side
 //so that we can ignore zeros
 //e.g: 0000100 same as 100, ignore 0000
 // ANDing 
 for(i=0;i&amp;lt;INT_SIZE;i++)
 {
  if(((num&amp;lt;&amp;lt;i) &amp; MASK))
  {
   if(!flag)
   {
    pos = i;
    break;
   }
  }
 }
 printf("\nBinary value is ");
 for(i=pos;i&amp;lt;INT_SIZE;i++)
 {
  if(((num&amp;lt;&amp;lt;i) &amp; MASK))
   printf("1");
  else
   printf("0");
 }
 printf("\n");

}
int main()
{
 int x = 0;
 printf("Enter input value\n");
 scanf("%d",&amp;x);
 findBinary(x);
}
OutPut:
Enter input value
256
Binary value is 100000000

Enter input value
234
Binary value is 11101010

Happy Coding!!!

Monday, April 28, 2014

Ubuntu - How to install guest addition feature in virtual box!!



When we install any virtual OS using virtual box, transfering or moving data from host OS to virtual OS and vice-versa is a common thing. By default Virtual box wont allow this feature in ubuntu. We can achive this by installing Guest Additions. Below are the advantages and how to install Guest Additions in Virtual box Ubuntu.


  • To make full screen
  • To transfer files from host to virtual OS and vice-versa
  • Copy paste from host to virtual OS and vice-versa


sudo apt-get update
sudo apt-get install build-essential linux-headers-$(uname -r)
sudo apt-get install virtualbox-ose-guest-x11

Update: In the latest versions of ubuntu, below command will work.

sudo apt-get install virtualbox-guest-x11

Restart ubuntu after running above commands to apply guest additions.

P.S: If you update system software, these guest additions will be removed. Need to install again to use guest additions. I faced same issue. So posting here. I am not sure why guest additions will be removed when you update the software and restart the virtual box Ubuntu.


Happy Learning!!

Tuesday, April 22, 2014

Python - local time to UTC time conversion using strings


Recently I came across a problem of converting local time with offset format to  UTC time format using strings in Python. I did lot of googling and unable to find the solution. So I wrote the code in python and sharing here for others.  Input format for the local time is ISO 8601.

Algorithm to convert from local time stamp with offset to UTC time: 


  1. Check for the format "%Y-%M-%D %H:%M:%z" if matches remove z and return true as its UTC format
  2. Split input value basing on space, so that we will get two items as date and time. if not return false as its invalid format
  3. Split date item from step2 with '-' as date items are separated by '-' and it should return 3 items day, month and year. If not return false
  4. Split time item from step2 with '+' or '-' to separate local time and offset. It should return two items other wise return false
  5. Split local time from step4 with ':' to get three items HH, MM and SS if not return false
  6. Split offset from step4 with ':' to get two offset items HH, MM if not return false
  7. Add MM items from Steps 5 and 6. If MM value is greater than 60 increment HH in local item in step 5 and assign remaining MM to local item in step 5
  8. Add HH items from Steps 5 and 6. If HH value is greater than 24 increment DD in date item in step 3 and assign remaining HH to local item in step 5
  9.  Similarly for Day, Month and same logic applies for leap year as well.
  10. After performing above steps rejoin data and time into one string and return that final string.


Below is the source code in Python:

import datetime
import calendar
import re

HRS_IN_A_DAY = 24
MONTHS_IN_YEAR = 12
MINS_IN_HR = 60
FEB = 2

def is_int(input_value):
    try:
        int(input_value)
        return True
    except ValueError:
        return False

def to_double_digit(value):
    """
    converts to double digit by prefixing zero to the value
    """
    return '0'+str(value) if (value<10) else str(value)

not_found = -1
def convert_local_time_to_utc(l_time_str):
    """
    Validate the input format and if invalid returns false 
    if valid, converts into UTC timestamp and returns new utc timestamp
    """
    try:
        datetime.datetime.strptime(l_time_str,"%Y-%m-%d %H:%M:%Sz")
        print "success"
    except (ValueError, TypeError) as e:
        date_str_items = l_time_str.split(' ')
        if (len(date_str_items)!=2):
            return False
        date_item_list = date_str_items[0].split('-')
        if(len(date_item_list)!=3):
            return False
        time_offset = re.split('-|\+',date_str_items[1])
        if(len(time_offset)!=2):
            return False
        local_time_items = time_offset[0].split(':')
        #print time_offset
        if(len(local_time_items)!=3):
            return False
        offset_items = time_offset[1].split(':')
        if(len(offset_items)!=2):
            return False
        plus_found = l_time_str.find('+')

        # validating for int values as date accepts only int
        if not is_int(date_item_list[0]):
            return False
        year_value = int(date_item_list[0])
        if not is_int(date_item_list[1]):
            return False
        month_value = int(date_item_list[1])
        if not is_int(date_item_list[2]):
            return False
        date_value = int(date_item_list[2])
        if not is_int(local_time_items[0]):
            return False
        local_hh = int(local_time_items[0])
        if not is_int(local_time_items[1]):
            return False
        local_mm = int(local_time_items[1])
        if not is_int(offset_items[0]):
            return False
        off_hh = int(offset_items[0])
        if not is_int(offset_items[1]):
            return False
        off_mm = int(offset_items[1])

        if plus_found == not_found:
            local_mm += off_mm
            if local_mm >= MINS_IN_HR:
                local_hh += 1
                local_mm -= MINS_IN_HR
            local_hh += off_hh
            if local_hh >= HRS_IN_A_DAY:
                date_value+=1
                local_hh-= HRS_IN_A_DAY
                days_in_month = calendar.mdays[month_value]
                if calendar.isleap(year_value) and month_value == FEB:
                    days_in_month += 1
                if date_value > days_in_month:
                    month_value += 1
                    date_value -= days_in_month
                    if month_value > MONTHS_IN_YEAR:
                        year_value += 1
                        month_value -= MONTHS_IN_YEAR
        else:
            local_mm -= off_mm
            if local_mm < 0:
                local_hh -= 1
                local_mm += MINS_IN_HR
            local_hh -= off_hh
            if local_hh < 0:
                date_value -= 1
                local_hh += HRS_IN_A_DAY
                if date_value < 1:
                    month_value -= 1
                    if month_value < 1:
                        month_value += MONTHS_IN_YEAR
                        year_value -= 1
                    date_value += calendar.mdays[month_value]
                    if calendar.isleap(year_value) and month_value == FEB:
                        date_value += 1
        #restoring new values into list
        local_time_items[0] = to_double_digit(local_hh)
        local_time_items[1] = to_double_digit(local_mm)
        date_item_list[0] = to_double_digit(year_value)
        date_item_list[1] = to_double_digit(month_value)
        date_item_list[2] = to_double_digit(date_value)
        #making into timestamp format "YYYY-MM-DD HH:MM:SS"
        final_date = "-".join(date_item_list)+" "+":".join(local_time_items)
        return final_date

print "Enter the date in the form of \"YYYY-MM-DD HH:MM:SS+HH:MM\" or \"YYYY-MM-DD HH:MM:SS-HH:MM\""
input_date = raw_input("Enter local date:")
while input_date:
    ret_val = convert_local_time_to_utc(input_date)
    if ret_val:
        print "converted UTC is ",ret_val
    else:
        print "Invalid format"
    input_date = raw_input("Enter local date:")

Output:

Enter the date in the form of "YYYY-MM-DD HH:MM:SS+HH:MM" or "YYYY-MM-DD HH:MM:SS-HH:MM"
Enter local date:2000-02-28 22:48:59-05:21
2000-02-29 04:09:59
converted UTC is  2000-02-29 04:09:59
Enter local date:2000-02-28 22:48:59+05:21
2000-02-28 17:27:59
converted UTC is  2000-02-28 17:27:59
Enter local date:sdfd
Invalid format
Enter local date:2000-02-28 22:48:59
Invalid format
Enter local date:
Process finished with exit code 0


Happy Coding!!!


Saturday, April 12, 2014

How to find a leap year in Python!!














How to check leap year in python: Below is python code to find the leap year


def is_leap_year(year):
    """
    below are the conditions for leap year:
    1. divisible by 4
    2. divisible by both 4 and 100
    3. divisible by both 100 and 400
    returns 1 on
    """
    if year%4 == 0:
        if year%100 == 0:
            if year%400 == 0:
                return SUCCESS
            else:
                return FAILED
        else:
            return SUCCESS
    return FAILED

print "Press 0 for exit"
year = input("Enter the year:")
while year:
    if is_leap_year(year):
        print year,"is a leap year"
    else:
        print year,"is not a leap year"
    year = input("Enter the year:")

Output:


Press 0 for exit
Enter the year:2012
2012 is a leap year
Enter the year:1900
1900 is not a leap year
Enter the year:1996
1996 is a leap year
Enter the year:0

You can also use built in python function to find the leap year. Below is the code for that. We need to import calendar module to is isleap() method, which will return True on success and False on fail.

import calendar
print "Press 0 for exit"
year = input("Enter the year:")
while year:
    if calendar.isleap(year):
        print year,"is a leap year"
    else:
        print year,"is not a leap year"
    year = input("Enter the year:")

OutPut:

Press 0 for exit
Enter the year:2012
2012 is a leap year
Enter the year:2014
2014 is not a leap year
Enter the year:1997
1997 is not a leap year
Enter the year:0


Happy Learning!!!

Saturday, April 5, 2014

String simple utility functions in Python

There are many string utility functions which can reduce your code and increase performance.  Below are some of the methods.

To find the no.of occurances of a substring in a string in Python: count string method will return the no.of occurrences of a substring from a string. In the below example , 'M' occurrences are 22 and 'F' occurrences are 19, 'MF'  substring occurrence are 8 and 'FM' substring occurrences are 7

in_str = 'MFMMMFFMMMFFFMMMMMMMFFFFFMMFFMMFMFMMMFFFF'
print in_str.count('M')
print in_str.count('F')
print in_str.count('MF')
print in_str.count('FM')

Appending or concatenating strings in pythonjoin method connects strings with a separator. We can use same method for appending list of strings by using separator as space or empty. But join will work only on iterators like list. Below are some of the examples.

str_list = ['hello','how','are','you']
sentence = " ".join(str_list)
print sentence

OutPut:
hello how are you


str_list = ['hello','how','are','you']
sentence = "123".join(str_list)
print sentence

OutPut:
hello123how123are123you

As we can observe that , join method will take iterator as a input and combines with delimiter or separator and forms the string.


Finding ASCII value of a character in Python: To find the ASCII value of a character, use ord() function. Below is the syntax and example.


ascii_value = ord('a')
print ascii_value 

OutPut: 
97

Happy Reading!!!


Tuesday, March 25, 2014

How to configure JDNI in Japser Reporting Server on Mac!!!


Recently when I try to generate a report from Jasper server, I got the below error and it took me lot of time to solve. So posting here to help others.

org.postgresql.util.PSQLException: ERROR: transaction is read-only Detail

One of the reason for getting this error is setting wrong Data source. Initially I set the data source as JDBC Driver. After doing all the possibilities , tried to change the data source from JDBC to JDNI, and it was worked successfully. Below are the steps to set JDNI drive on Jasper reporting server.
  1. Open context.xml file (path is given below in Mac OS X)
  2. Copy and paste existing resource and edit (given below how to do this) 
  3. Login to the server
  4. Goto view->repository->public->data source
  5. Right click Data Sources -> add resource -> Data Source
  6. Check Type as JNDI data source
  7. Give appropriate Name, ID and description 
  8. Give service name which specified in context.xml file
  9. Save the settings by specifying particular location for later use
  10. Test Connection to check success/failure


in Mac path for context.xml:
/Applications/jasperreports-server-5.5/apache-tomcat/webapps/jasperserver-pro/META-INF/context.xml

Changing resource in context.xml file:

    <Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="jasperdb" password="password" 
        driverClassName="org.postgresql.Driver"
        validationQuery="SELECT 1"
        testOnBorrow="true"
        url="jdbc:postgresql://127.0.0.1:5432/jasperserver?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"
        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

we need to modify name, username, password and url fields in the resource.
name: This will be used while configuring Dat Source
username: user name of your data base
password: Password of your data base
url: url of your data base.

Happy Reading!!!


Saturday, March 22, 2014

function date_trunc("unknown", "unknown") is not unique - SQL


Recently I started working on Japser Studio professional for my new project Cloud to generate the reports. I was very new to all cloud technologies including PostgreSql. I faced lot of problems during this time. below is one of such problem, which kill lot of my time. Here is problem and its solution.

Problem statement: Need to generate usage data reports.

To generate usage data reports, I need to compare the existing timestamp from the table with user given data. For comparing data I used date_trunc()  to get year/month/day etc. Here I was getting above error. Below is the query for that where ts is from test_reports table.

select * from test_reports where (date_trunc('day',ts)


ERROR: function date_trunc("unknown", "unknown") is not unique
  Hint: Could not choose a best candidate function. You may need to add explicit type casts. [SQL State=42725]

This error is due to data type(timestamp) mismatching. By seeing/analysing the error, I found its due to mismatching the time. But no idea how to solve it and googled for it like hell. Luckily our TL has suggested the solution that prepending timestamp to the user input variable.

select * from test_reports where (date_trunc('day',ts)timestamp
 '2014-03-21 14:07:00'))
Jasper studio professional:

Need to create the input parameter and pass that parameter as an argument to the sql query. Here are the steps to create the parameter.


  1. Go to outline window in the Jasper studio
  2. Right click on the Parameters tab and click on "Create Parameter"
  3. Then click on the created parameter and go to the properties window
  4. Change the name to appropriate one
  5. Change the class name to java.sql.timestamp
  6. Change the default expression to new Date() as it will give the current date as default value
  7. Make the isPrompt check box enable for getting the data from user
Now we can able to read the date value from the user into this newly created parameter.

Usage of the parameter in generating reports:

Need to follow below syntax for  using parameter in the reports generation.

$P!{parameter_name}

select * from test_reports where ts < '$P!{user_date}'


If we use same parameter in date_trunc() function, we will face type mismatch error.

select * from test_report where date_trunc('day',update_ts)

To solve that error, you can prepend 'timestamp' which will convert to required type.

select * from test_report where date_trunc('day',update_ts)timestamp
'$P!{user_date}')

Thanks for reading !!!!



Popular Posts