How to call a routine in query

Five Stars

How to call a routine in query

Hello, I want to perform a select request on an access database like this:

"SELECT TABLENAME.Something

FROM TABLENAME

WHERE TABLENAME.Something > routineName()

ORDER BY TABLENAME.Something ASC"

 

I've tried different return type (string and integer) routines and syntaxes like:

"...>"+ routineWhoReturnAString() +"ORDER BY..."

but i always get a null pointer exception or an invalid schema name exception.

Thank you !!!

 

EDIT : My nullpointereexception comes from a condition in an "if" link even though it was said to be an error in the input componant.

The correct syntax in the query, if the routine returns a String, is:

"SELECT TABLENAME.Something

FROM TABLENAME

WHERE TABLENAME.Something > "+ class.method() +"

ORDER BY TABLENAME.Something ASC"

 

NOTE: Using a global variable works great too.


Accepted Solutions
Sixteen Stars

Re: How to call a routine in query

Try this....

 

"SELECT TABLENAME.Something
FROM TABLENAME
WHERE TABLENAME.Something > " + routines.classname.methodName() + " 
ORDER BY TABLENAME.Something ASC"

Routines are just classes. The package is "routines", the class name is the routine name and then you have the method name. I'm assuming your method is returning a number (as a string or an int), so it can be expressed as above.

 

The best way to test dynamically created SQL is to create it in a tJava and output to the console using a System.out call. This will allow you to see what you are generating and test it in a SQL query analyzer.


All Replies
Forteen Stars TRF
Forteen Stars

Re: How to call a routine in query

I'm afraid you need to put the routine's result into a global variable, then reuse this variable into your query


TRF
Sixteen Stars

Re: How to call a routine in query

Try this....

 

"SELECT TABLENAME.Something
FROM TABLENAME
WHERE TABLENAME.Something > " + routines.classname.methodName() + " 
ORDER BY TABLENAME.Something ASC"

Routines are just classes. The package is "routines", the class name is the routine name and then you have the method name. I'm assuming your method is returning a number (as a string or an int), so it can be expressed as above.

 

The best way to test dynamically created SQL is to create it in a tJava and output to the console using a System.out call. This will allow you to see what you are generating and test it in a SQL query analyzer.