Error while search by date

[Former Member]

hai

below method triggering but getting error,

i not declare any CHAR in any where...

but i assigning the date attribute but it showing error as iam using CHAR.

i think in dictionary table filed is the date datatype.

it is cause may be not matching the sql date and webdynpro date .

my assume is it write, tell me what i need to do .

that is not matching the error is:

"SELECT * FROM "TASK_DETAILS" WHERE "TASK_RECEIPT_DATE" = '2008-09-24' ORDER BY 1,2" contains the semantics error[s]: - 1:34 - type check error: the left hand side >>"TASK_RECEIPT_DATE"<< (DATE) and the right hand side >>'2008-09-24'<< (CHAR) of a comparison operator are not comparable

the method code is as below

if (taskRecDate != null
			&& (taskStatus == null
				&& taskHanByBD == null
				&& taskNo == null
				&& taskType == null
				&& clientName == null)) {
		Date TaskRecDate = Date.valueOf(taskRecDate.toString());
			
	query = query + " TASK_RECEIPT_DATE = '" + TaskRecDate + "'";

			} else if (taskRecDate != null) {
			Date   TaskRecDate = Date.valueOf(taskRecDate.toString());
			
query = query + "AND TASK_RECEIPT_DATE = '" + TaskRecDate + "'";
			}
query = query + " ORDER BY TASK_NUMBER, TASK_TYPE";

[Former Member · Accepted solution]

Hi Eswar

I don't really have much info to go on here... so I'm just going to take a guess.

Have you tried using a PreparedStatement and setting parameters? This way the correct data type is automatically inferred into the SQL statement.

e.g. (assuming receiptDate is defined as the date you would like in your query in a variable of type java.sql.Date)

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TASK_DETAILS WHERE TASK_RECEIPT_DATE = ? ORDER BY 1,2");
stmt.setDate(1, receiptDate);
ResultSet rs = stmt.executeQuery();

See the docs for more info on the PreparedStatement class: [http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html]

Also, if you're using a dictionary project (as it seems you are) then you can use JPA to get this info. Have you looked into this? Might be worth your consideration.

Cheers

Stuart

[Former Member · Accepted solution]

Hai

Your Guess is right .

iam trying on prepared statement, to execute query.

i want to know u mention JPA

what mean this

[Former Member · Accepted solution]

Sorry... I'm a little confused with your response.

Do you mean you've just tried using PreparedStatement and it is now working, or that you were originally using PreparedStatement and it is still not working?

JPA = Java Persistence API

You can find a fair number of docs on this online - just search SDN for "JPA"

Here are a few to get you started:

[Getting Started with Java Persistence API and SAP JPA 1.0|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/40ff8a3d-065a-2910-2f84-a222e03d1f43]

[Basics of the Java Persistence API - Defining and Using Relationships|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/c0ffc32b-0dc9-2910-fd9c-a3e6f4c48e72]

[Basics of the Java Persistence API - Understanding the EntityManager|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/60216821-e789-2910-109b-b6c6e02dce87]

[How to Work with the Sample Application JPAExample01App: A Step by Step Instruction|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/b0c6b369-065a-2910-8f9e-fb6ad535558f]

Cheers

Stuart

[adrian_goerler · Accepted solution]

Hi,

I don't think Eswar is using JPA but plain JDBC.

Eswar,

the original error message you have posted indicates that you are atempting to compare a DATE column with a character literal. In Open SQL, this is not possible. You can compare DATE only with DATE. Moreover, Open SQL does not define a DATE literal. Hence you have to use a parameter as proposed by Stuart.

-Adrian

[Former Member · Accepted solution]

Hi Adrian

I don't think Eswar is using JPA but plain JDBC.

Yes, that's correct - I didn't think he was using it. It was a recommendation that he looks into it as an alternative option.

Cheers

Stuart