Java + MS Access and SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Java + MS Access and SQL

Author

Topic

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 13:39:45

Hello all,

I am writing a program in Java which connects to an MS Access Database through JDBC-ODBC. My problem is the following:

I would like to write an SQL statment which basicly does:

SELECT * FROM Table WHERE 0 > Max(Max(Column1, Column2), Column3)

However there is no defined Max function which takes on columns as arguments in MS Access ie I need to define such a function in VBA I pressume? I am not fluent in VBA, can anyone give me an example of such a function and also how to make this function avaliable for reference in the SQL-statement constructed in java.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 13:42:01

Since that is going to match any record where 0 is greater that column1, 2 or 3 how about

where 0 > column1 or 0 > column2 or 0 > column3

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 13:54:19

0 was just an example replace it with any number n. It is the function max I am interrested in; the mentioned SQL-query is just a low-detail example. Sorry for not specifying it clearly enough.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 13:57:14

Same thing applies.

If your variable is greater than any of those columns.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 15:00:33

Again it's not the precise SQL statement , it's the _function_ MAX I am interested in. How to define such a function....

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 15:13:54

create function MyMax (@a int, @b int)
returns int
AS
BEGIN
declare @c int
if @a > @b
set @c = @a
else
set @c = @b
return @c
END

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 15:14:40

select dbo.myMax(dbo.myMax(1,2),3)

3

(1 row(s) affected)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 15:32:15

Thanks your reply. Maybe this is more a question focused vs Java. But how do I define that function so I can reference it in my sql statement. In my code I have something like:

statement.execute(query); where query would be my SELECT statement

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 16:13:59

Sorry that is a SQL Server fuunction.
Try this as an Access function
Function MyMax(a As Double, b As Double) As Double

Dim LWhole As Double

If a > b Then
MyMax = a
Else
MyMax = b
End If

End Function

Or there is a Max function in Java but you can not use it in a SQL staeement.
The only thing you can do in Java is return everything then filter the results with java.lang.Math.max

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 16:28:37

Indeed hence I want to avoid returning all this data. I have defined in the modfule section a function like yours, but when I try reference it in java like this:

....
String test = "SELECT * FROM MyTable WHERE ID = MyFunction()";
....
boolean foundResults = statement.execute(query);
....

and then function is just something random like:

Public Function MyFunction() As Integer
MyFunction = 2
End Function

"Then I get the error function not defined"

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 16:33:25

String test = "SELECT * FROM MyTable WHERE ID = myMax(Column1,Column2)";

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 16:47:54

I am not sure if anything is missing from your last post, but that's just basicly the same. The question is prop really, any idea why it says function not defined, is there something i must do i access to it to reference to the module or something

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-20 : 16:51:49

Shouldn't be. Try the query in access.
If it works then look at your ODBC driver.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

LightRaven
Starting Member

7 Posts

Posted - 2008-02-20 : 18:07:54

The SQL query works fine in Access but not through the JDBC-ODBC bridge - odd

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-21 : 07:56:54

Then again I would ask why do you think you have to have a function.
try where MyVariable > column1 or MyVariable > column2 or MyVariable > column3
It will work through the driver and will accomplish the same task.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources