aspose file tools*
The moose likes JDBC and the fly likes SQL: Searching a database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL: Searching a database" Watch "SQL: Searching a database" New topic
Author

SQL: Searching a database

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Hello

I've got strange problem! I'm trying to search for items that expire in a particular month... this month is selected by the user...

this is the SQL i've got so far (for the sake of testing, it doesnt take an input from the user)


But for some reason this SQL does not return anything! ItemExpiryDate in my ms access table is set to 'Text' as recommended by Microsoft.com...
By the way, when i put the full date e.g. '2006-12-04', it works fine and does exactly what i want...

Can anyone spot a mistake somewhere?

thank you
HannaH
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by H Melua:
Hello

I've got strange problem! I'm trying to search for items that expire in a particular month... this month is selected by the user...

this is the SQL i've got so far (for the sake of testing, it doesnt take an input from the user)


But for some reason this SQL does not return anything! ItemExpiryDate in my ms access table is set to 'Text' as recommended by Microsoft.com...
By the way, when i put the full date e.g. '2006-12-04', it works fine and does exactly what i want...

Can anyone spot a mistake somewhere?

thank you
HannaH



I don't think that recommendation to store a date as text is very good advice. Better to keep it as a date, in my opinion, and take advantage of methods that use dates.



I'd recommend NOT using "SELECT *" - type out exactly the columns that you want to bring back. Also, use PreparedStatement to escape those java.sql.Dates properly for you.


%
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
hello Michael

you see, Ms access gives me an "incompatible type error" if i keep it as date and try to insert a java.sql.date to it! i looked up a solution for the error in microsoft.com and thats the recommended solution; i didnt see that error since i changed it!

i completely agree with you, but i dont think i can change it in this case
[ January 14, 2006: Message edited by: H Melua ]
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by H Melua:
hello Michael

you see, Ms access gives me an "incompatible type error" if i keep it as date and try to insert a java.sql.date to it! i looked up a solution for the error in microsoft.com and thats the recommended solution; i didnt see that error since i changed it!

i completely agree with you, but i dont think i can change it in this case

[ January 14, 2006: Message edited by: H Melua ]


No, there's something else wrong. It's perfectly correct to have a DATE column and to insert a java.sql.Date into it. You did something else wrong.

I can run this class and insert a Date into Access:



Try it and see. The Person.mdb is pretty easy - just one table named PERSON with NAME and BIRTHDAY columns.
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
That code needs a change. Do it like this:

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
oh my lord, i tried for sooo long and nothing would work!!

thank you so much for that, i moved from statements to prepared statements and everything worked fine! i read somewhere yesterday, that statements cause trouble when inserting dates, it might not be true all the time, but it did the trick!

thanks again, your code saved me alot

HannaH
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by H Melua:
oh my lord, i tried for sooo long and nothing would work!!

thank you so much for that, i moved from statements to prepared statements and everything worked fine! i read somewhere yesterday, that statements cause trouble when inserting dates, it might not be true all the time, but it did the trick!

thanks again, your code saved me alot

HannaH


Yes, PreparedStatements are the correct, portable way to do it. Glad it helped.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL: Searching a database
 
Similar Threads
Calendar weirdness...
Switch with Boolean ???
Hibernate date in criteria
Using two column functions to return a time span
SQL Show Previous Month Date Question