OK let say I'm fine with dat.. now I have a problem extracting data from the database.
I wanna run a method whose arguments are String, so I could parse the String into a Date then retrieve it.
since '1970-01-01 21:46:00.000' is recorded in DB, then I assume this is the query would look like :
SELECT tx.* FROM TRANSACTION as tx,USERS as usr
WHERE tx.HOUR_START ='1970-01-01 21:46:00.000'
AND usr.USERNAME = 'Whonai';
that query works perfectly fine.. using query analyzer
now I have two (experimental) methods in my retrieval class :
GetTxtion.java
import net.sf.hibernate.*;
import java.util.*;
import java.io.*;
import java.text.*;
public class GetTxtion
{
protected SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
private Txtion theTrax;
private List traxList;
public GetTxtion() throws IOException,HibernateException
{
Calendar tmp = Calendar.getInstance();
Date dt = null;
try {
dt = sdf.parse("1970-01-01 21:46:00.000");
}
catch (ParseException e) {e.printStackTrace();}
String s = "1970-01-01 21:46:00.000";
byUserAndDate("Whonai",s); byUserAndDate("Whonai",dt); }
public List byUserAndDate(String name, String hrStart) throws HibernateException
{
System.out.println("Retrieving the transaction from a specific User by Date ");
sess = sessFact.openSession();
Transaction tx = null;
List theList;
try
{
tx = sess.beginTransaction();
String[] alias = { "usr","tx" };
Class[] cls = {User.class, Txtion.class};
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr} WHERE {tx}.HOUR_START ='" + hrStart +"' AND {usr}.USERNAME = '"+ name +"' ";
Query theQuery = sess.createSQLQuery(query, alias, cls);
theList = theQuery.list();
if (theList.size() == 0)
{
System.out.println("No transaction");
return null;
}
else
{
System.out.println("Transaction is successfully retrieved");
}
tx.commit();
}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
sess.close();
}
return theList;
}
public List byUserAndDate(String name, Date hrStart) throws HibernateException
{
System.out.println("Retrieving the transaction from a specific User by Date ");
sess = sessFact.openSession();
Transaction tx = null;
List theList;
try
{
tx = sess.beginTransaction();
String[] alias = { "usr","tx" };
Class[] cls = {User.class, Txtion.class};
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr} WHERE {tx}.HOUR_START ='" + hrStart +"' AND {usr}.USERNAME = '"+ name +"' ";
Query theQuery = sess.createSQLQuery(query, alias, cls);
theList = theQuery.list();
if (theList.size() == 0)
{
System.out.println("No transaction");
return null;
}
else
{
System.out.println("Transaction is successfully retrieved");
}
tx.commit();
}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
sess.close();
}
return theList;
}
public static void main(String[] args)
{
try {
new GetTransaction();
} catch (HibernateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
The first method : (using string as date argument)
byUserAndDate("Whonai",s);
would result in these errors: Syntax error converting datetime from character string
Retrieving the transaction from a specific User by Date Hibernate: SELECT tx.ID as ID1_, tx.USER_ID as USER_ID1_, tx.CATEGORY_ID as CATEGORY3_1_, tx.PROJECT_ID as PROJECT_ID1_, tx.LOCATION_ID as LOCATION5_1_, tx.TRANSACTION_DATE as TRANSACT6_1_, tx.HOUR_START as HOUR_START1_, tx.HOUR_END as HOUR_END1_, tx.DESCRIPTION as DESCRIPT9_1_, tx.RATE_VALUE as RATE_VALUE1_, tx.CURRENCY as CURRENCY1_, tx.DURATION as DURATION1_, tx.STATUS as STATUS1_ FROM TRANSACTION tx,USERS usr WHERE tx.HOUR_START ='1970-01-01 21?' AND usr.USERNAME = 'Whonai' 11:13:54,562 WARN JDBCExceptionReporter:38 - SQL Error: 241, SQLState: 22007 11:13:54,562 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string. 11:13:54,578 WARN JDBCExceptionReporter:38 - SQL Error: 241, SQLState: 22007 11:13:54,578 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string. 11:13:54,578 ERROR JDBCExceptionReporter:38 - SQLException occurred java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string.
and the second one :
byUserAndDate("Whonai",dt)
would result the same error..
if I remove the single quote at HOUR_START = ' " +hrStart + " ' ...so the query looks like this :
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr} WHERE {tx}.HOUR_START = "+ hrStart +" AND {usr}.USERNAME = '"+ name +"' ";
they would say invalid parameter binding :
Hibernate: SELECT tx.ID as ID1_, tx.USER_ID as USER_ID1_, tx.CATEGORY_ID as CATEGORY3_1_, tx.PROJECT_ID as PROJECT_ID1_, tx.LOCATION_ID as LOCATION5_1_, tx.TRANSACTION_DATE as TRANSACT6_1_, tx.HOUR_START as HOUR_START1_, tx.HOUR_END as HOUR_END1_, tx.DESCRIPTION as DESCRIPT9_1_, tx.RATE_VALUE as RATE_VALUE1_, tx.CURRENCY as CURRENCY1_, tx.DURATION as DURATION1_, tx.STATUS as STATUS1_ FROM TRANSACTION tx,USERS usr WHERE tx.HOUR_START = Thu Jan 01 21? GMT+07? 1970 AND usr.USERNAME = 'Whonai' 11:21:05,687 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: 07009 11:21:05,687 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s). 11:21:05,703 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: 07009 11:21:05,703 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s). 11:21:05,718 ERROR JDBCExceptionReporter:38 - SQLException occurred java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
any help is appreciated.. thanks in advance.
|