java.sql.SQLException: Incorrect syntax near '@P1'.

UPDATED: 24 February 2014
Scenario: 
We were calling MSSQL Stored Procedure using hibernate. My colleague called me to check out error and even I was clueless. It was working fine at database level. And while checking out calling code it seems alright but it thrown an error as follow.

org.hibernate.exception.SQLGrammarException: could not execute query
  at Incorrect syntax near '@P1'..()
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
  at could not execute query.(Native Method)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.loader.Loader.doList(Loader.java:2223)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
  ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P1'.
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  ... 50 more

Solution:
My colleague forgot to put comma , after each parameter. Follow below queries one with error and other without.

EXEC MY_STORED_PROC :PARAM1 :PARAM2 /* Query that throwing error because of missing comma. */
EXEC MY_STORED_PROC :PARAM1, :PARAM2 /* Query that works fine. */

0 comments :


How to configure Subdomain in Tomcat for localhost?

UPDATED: 22 February 2014
In my previous article How to configure Subdomain in Tomcat 7? , I wrote about configuring subdomain on live server with linux environment. Today I'll show how you can configure sub-domain in Apache in local system with Netbeans and without Netbeans environment.



Configure sub-domain using Netbeans environment. 
Netbeans executable bundle has Apache Tomcat server in it. You can install Apache tomcat separately add it to Netbeans. So lets check out how to create sub-domain...

Step 1: Open Services tab in Netbeans. If you can't find click on Window > Services (Ctrl + 5) .

Step 2: Navigate to Servers > Apache Tomcat > Edit server.xml


Step 3: Find an Engine tag and add following line with in Engine tag.
<!-- Changes required in following code. In this example (cdn_2b48cl2gf97hytt58p8g.mydomain.com) is my sub-domain and directory.
        1. appBase: sub-domain directory location in webapps.
        2. name: sub-domain name you want. like(cdn_2b48cl2gf97hytt58p8g.mydomain.com, m.mydomain.com)
        3. docBase: Absolute path/location of your directory. -->
<Host appBase="webapps/cdn_2b48cl2gf97hytt58p8g" autoDeploy="false" name="cdn_2b48cl2gf97hytt58p8g.mydomain.com" unpackWARs="true">
      <Context antiJARLocking="true" docBase="D:\Path_to_project\build\web\cdn_2b48cl2gf97hytt58p8g" path=""/>
</Host>

How to forward domain request using hosts file?
Using hosts file you can forward any domain request to any IP address or hosts. We'll forward sub-domain request to localhost.

Note: For this example I used windows platform. Use sudo vim /etc/hosts command in terminal for linux and mac OR http://www.howtogeek.com/howto/27350/beginner-geek-how-to-edit-your-hosts-file/

Step 1: Navigate to folder C:\Windows\System32\drivers\etc.

Step 2: Open hosts file using any editor like notepad and add line like below. So now your sub-domain will point to localhost.
127.0.0.1 cdn_2b48cl2gf97hytt58p8g.mydomain.com

How to setup sub-domain in Tomcat without Netbeans environment?
I gave you example to create sub-domain using Netbeans but technically you are using Netbeans as an editor nothing else. All you need to do is open server.xml file of your tomcat server in any editor like notepad follow same steps above and you are done.

Finally you need to restart your server and you are done!. Now you can access your sub-domain by url like cdn_2b48cl2gf97hytt58p8g.mydomain.com:8084/. You need to specify your tomcat server port. 

0 comments :


java.lang.NoClassDefFoundError: org/bouncycastle/asn1/ASN1ObjectIdentifier

UPDATED: 20 February 2014
For those who don't know, Bouncycastle is lightweight cryptography API.

Scenario:
We created applet that uses bouncycastle classes to generate digital certificate. My colleague worked on bouncy castle and it was working fine so far. I created new method in applet and signed it with code signing certificate in my machine. I uploaded applet on test server and it thrown error given below.  

Java Release note: Only digitally signed (Code signing) Applet will work 2014 onward
basic: exception: java.lang.NoClassDefFoundError: org/bouncycastle/asn1/ASN1ObjectIdentifier.
java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/bouncycastle/asn1/ASN1ObjectIdentifier
 at com.sun.deploy.uitoolkit.impl.awt.AWTAppletAdapter.instantiateApplet(Unknown Source)
 at sun.plugin2.applet.Plugin2Manager.initAppletAdapter(Unknown Source)
 at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoClassDefFoundError: org/bouncycastle/asn1/ASN1ObjectIdentifier
 at java.lang.Class.getDeclaredConstructors0(Native Method)
 at java.lang.Class.privateGetDeclaredConstructors(Unknown Source)
 at java.lang.Class.getConstructor0(Unknown Source)
 at java.lang.Class.newInstance(Unknown Source)
 at com.sun.deploy.uitoolkit.impl.awt.AWTAppletAdapter$1.run(Unknown Source)
 at java.awt.event.InvocationEvent.dispatch(Unknown Source)
 at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
 at java.awt.EventQueue.access$200(Unknown Source)
 at java.awt.EventQueue$3.run(Unknown Source)
 at java.awt.EventQueue$3.run(Unknown Source)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
 at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
 at java.awt.EventQueue$4.run(Unknown Source)
 at java.awt.EventQueue$4.run(Unknown Source)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
 at java.awt.EventQueue.dispatchEvent(Unknown Source)
 at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
 at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
 at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
 at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
 at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
 at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: org.bouncycastle.asn1.ASN1ObjectIdentifier
 at sun.plugin2.applet.Applet2ClassLoader.findClass(Unknown Source)
 at sun.plugin2.applet.Plugin2ClassLoader.loadClass0(Unknown Source)
 at sun.plugin2.applet.Plugin2ClassLoader.loadClass(Unknown Source)
 at sun.plugin2.applet.Plugin2ClassLoader.loadClass(Unknown Source)
 at java.lang.ClassLoader.loadClass(Unknown Source)
 ... 24 more
Solution:
After trying all options from internet at last we were clueless. I told him to sign applet in your system. He did it and worked fine. Reason is as follow...

We both having 64 bit system. In my system I installed 32 bit java and he has 64 bit java in his system. Thats the problem may be bouncy castle created using 64 bit Java. If you are getting same issue. Try to sign your applet using 64 bit java from
C:\Program Files (x86)\Java\jdk1.6.0_25\bin\jarsigner.exe

0 comments :


How to configure SQL Explorer in Eclipse?

UPDATED: 13 February 2014
Today I came to know one cool plugin of Eclipse. Its SQL Explorer, some of you may aware of this awesome plugin. This article for those who not heard about this plugin ever.

Features: 
  • Connect remote databases.
  • Able to show Tables, Function, View, Stored Procedure, etc...
  • Quick preview of table contains (Table Structure, Foreign keys, Indexes, etc...)
  • Query execution history.

Drawback: (Or may be I'm wrong)
  • Can't execute Stored Procedure, Function, etc... from SQL Explorer

Prerequisite:
Note: Download from original site in-case you face some issue for your eclipse environment.

Step 1: Download and extract sqlexplorer_plugin-3.6.1_SR6.zip file. Merge it with eclipse `features` and `plugins` folder.

Step 2: Restart eclipse.

Step 3: Go to Window > Preferences > SQL Explorer and follow step as below image and click Edit.

SQL Explorer

Step 4: Click on Extra Class Path > Add JARs and select `sqljdbc-1.2.0.jar` from location you downloaded at starting of this article. Then click on List Drivers. Click OK on both popup window.

SQL Explorer

Step 5: Now you are good to go. All You need to open perspective window in eclipse. Go to Window > Open Perspective > Other > SQL Explorer.

Step 6: You'll have SQL Explorer window open in your eclipse. In left pan you'll have connection tab. Right click in that tab area create new connection.

Step 7: Provide your connection details in popup window and click ok.

SQL Explorer

That's it. We are done with SQL Explorer setup. Enjoy your new SQL editor. Open below image in new tab to check out SQL Explorer in Eclipse.

SQL Explorer

0 comments :


MSSQL list of useful queries - Part 1

UPDATED: 12 February 2014
Database is backbone of software. Its more important then any other thing in software industries. Its like gold mine of knowledge of how people working around with their data(Information). All top software companies Google, Facebook, etc... are nothing without database. Programming language could be anything what matter is database.

1. Find tables which contains specific column name.
SELECT  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%id%' ;

2. List columns of table and related properties (Default value, Type, etc...)
SELECT [COLUMN_NAME], *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'user_master'

3. Drop Primary key constraint.
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = 'ALTER TABLE table_name DROP CONSTRAINT ' + name + ';'
FROM sys.key_constraints
WHERE [type] = 'PK'
AND [parent_object_id] = OBJECT_ID('table_name');

EXEC sp_executeSQL @sql;

4. ISNULL to replace with value.
To replace value when you are expecting value could be NULL.
DECLARE @NullValue INT = NULL;
/* ISNULL(@variable, ReplaceWithValue) */
SET @NullValue = ISNULL(@NullValue,10)
PRINT @NullValue

5. ISNUMERIC to check value is numeric or not.
DECLARE @NullValue INT = NULL;
/* ISNULL(@variable, ReplaceWithValue) */
SET @NullValue = ISNULL(@NullValue,10)
/* ISNUMERIC(@variable) */
PRINT ISNUMERIC(@NullValue)

6. To check that Index is used (seek) or not for particular table.
/* Change database name and table_name in where condition */
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
  si.name AS IndexName, 
  sc.Name AS ColumnName,
  sic.Index_ID, 
  sis.user_seeks, 
  sis.user_scans, 
  sis.user_lookups, 
  sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si 
 ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic 
 ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc 
 ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('database_name') AND sis.OBJECT_ID = OBJECT_ID('table_name');

0 comments :


Date [java.util.Date] conversion from Java to SQL

UPDATED: 06 February 2014
Datatype Conversion is silent part of any project, yet very important. You'd like to read importance of Datatype Conversion [http://en.wikipedia.org/wiki/Cluster_(spacecraft)]

Case Scenario:
  • Pass current date (java.util.Date) to database stored procedure in varchar / string format.
  • Convert string date to datetime at database level. 
  • Process our logic and return date in string format.
  • Get date in string format at java.
  • Convert string date in java to java.util.Date

java.util.Date > String > datetime(MSSQL Database) > String > java.util.Date

Step 1: We'll create sample stored procedure to test our conversion.
IF (OBJECT_ID('SP_DateTime') IS NOT NULL)
  DROP PROCEDURE SP_DateTime
GO
CREATE PROCEDURE SP_DateTime @DateTimeString VARCHAR(100)
AS
BEGIN
 /**
  * @author javaQuery
  * @test EXEC SP_DateTime '2012-12-15 16:30'
  */
 DECLARE @v_Datetime DATETIME;
 SET @v_Datetime = CONVERT(varchar,@DateTimeString, 120);
 /* Compare dates and other business logic */
 SELECT CAST(@v_Datetime AS VARCHAR(100));
END

Step 2. Calling above stored procedure from Java (Hibernate: How To Call Stored Procedure In Hibernate).
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class SP_DATETIME {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration c = new Configuration();
        c.configure("hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory sf = c.buildSessionFactory();
        Session session = sf.openSession();

        /* Call Stored Procedure */
        Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_DateTime :param1");
        callStoredProcedure_MSSQL.setString("param1", String.valueOf(new Timestamp(new Date().getTime())));

        /* Get return value */
        List customResult = callStoredProcedure_MSSQL.list();
        if (customResult != null && !customResult.isEmpty()) {
            SimpleDateFormat formatter = new SimpleDateFormat("MMM dd yyyy hh:mma");
            try {
                Date d = formatter.parse(customResult.get(0));
                System.out.println(d.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        session.close();
    }
}
For other date format check http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Question: Why we choose string rather than timestamp?
Answer: Consider if you have common class that pass call to stored procedure. You don't know which datatype will come as an argument to your method. We'll set every parameter as string.

Question: Why we are returning date as string from Stored Procedure.
Answer: When we get NULL from Database hibernate will try to convert NULL to date and will throw an exception.

0 comments :


MSSQL to MySQL Guide to Migration Part - 2

UPDATED: 01 February 2014
I covered few points in my previous article MSSQL to MySQL Guide to Migration Part - 1. We'll cover few more points for migration. So far we covered important points for basic migration in this article we'll discuss some extended migration points.

1. DENSE_RANK function
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. - Microsoft (http://technet.microsoft.com/en-us/library/ms173825.aspx)

You all may not aware of this function but it exists in MSSQL. This function doesn't exists in MySQL. There are many ways available on the internet but word to wise eliminate duplicate records if possible. Don't use this function if your product support multiple dialect.

2. Database INDEX migration
CLUSTERED INDEX Primary key will do the job in both MSSQL and MySQL.
NON-CLUSTERED INDEX There ain't clause "Non-Clustered" in MySQL. Yet you can assign multiple index in MySQL. Its just matter of syntax.
/* MSSQL */
CREATE NONCLUSTERED INDEX [IndxNc_user_master__MultiCol01] ON [dbo].[user_master] 
(
 [column1] ASC,
 [column2] ASC
);
/* -------------------------------------------------- */
/* MySQL */
ALTER TABLE user_master
ADD INDEX IndxNc_user_master__MultiCol01 
(
column1 ASC,
column2 ASC
);

3. JOIN(concat) two string in MSSQL and MySQL
MSSQL is designed in a way that it will identify your input.

  • If you are using string with `+` then it'll append and prepend for you. 
  • If you are using numeric value with `+` then it'll do summation. 

MySQL you've to use different methods to perform the same.

  • If you are using string use concat('string', column_value, 'string', column_value).
  • If you are using numeric value, its same as MSSQL. Use `+` sign
/* MSSQL numeric value */
SELECT 100 + id FROM user_master;
/* MSSQL String value */
SELECT ':'+Firstname+':' FROM user_master;
/* -------------------------------------------------- */
/* MySQL numeric value */
SELECT 100 + id FROM user_master;
/* MySQL String value */
SELECT concat(':', Firstname, ':') FROM user_master;

4. PRINT message in MSSQL and MySQL
MSSQL provide clause called PRINT to print run time custom message. In MySQL SELECT clause does the job for you.

Warning: PRINT in MSSQL won't effect your output result while you are dealing with hibernate, where in MySQL SELECT clause print message in tabular format so hibernate will use this table as a result. You may get wrong result in hibernate. Read more about hibernate + stored procedure How To Call Stored Procedure In Hibernate
/* MSSQL */
PRINT 'javaQuery';
/* -------------------------------------------------- */
/* MySQL */
SELECT 'javaQuery';

5. Comments
This is not much important however for your consideration.

  • MSSQL: It supports comment block /* comment */ and hyphen --.
  • MySQL: It supports comment block /* comment */ and hyphen following one space -- .
/* MSSQL */
/* MSSQL comments */
/* --SELECT * FROM user_master */
/* -------------------------------------------------- */
/* MySQL */
/* MySQL comments */
/* -- SELECT * FROM user_master */

6. SELECT values into variables
Selecting values in multiple values differ in both the database language. Here sample code is available for both database.

  • In MSSQL you can use `=` operator.
  • In MySQL you can use `INTO` phrase.
/* MSSQL */
DECLARE @VARIABLE1 VARCHAR(100);
DECLARE @VARIABLE2 VARCHAR(100);

SELECT @VARIABLE1 = Firstname, @VARIABLE2 = Lastname
FROM user_master
WHERE id = 1;
/* -------------------------------------------------- */
/* MySQL */
DECLARE VARIABLE1 VARCHAR(100);
DECLARE VARIABLE2 VARCHAR(100);
 
SELECT Firstname, Lastname INTO VARIABLE1, VARIABLE2 
FROM user_master
WHERE id = 1;

0 comments :