This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Help for Dynamically changing sort by field and order by clause, in db2 procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help for Dynamically changing sort by field and order by clause, in db2 procedure" Watch "Help for Dynamically changing sort by field and order by clause, in db2 procedure" New topic
Author

Help for Dynamically changing sort by field and order by clause, in db2 procedure

Biju moozhikkara
Greenhorn

Joined: Oct 21, 2005
Posts: 20
Hello,

i have requirement where i have to do order by on the field that i give as input to the store procedure.Also i have to change the sorting direction, ASC or DESC

folowing is the stored proc i tried.Although its error free and getting deployed .the result returned on execution is,not sorted as per the input i gave.

CREATE PROCEDURE SP_TESTPAGE (
IN current_sort_field VARCHAR(64),
IN current_sort_order VARCHAR(64),
IN sso_id VARCHAR(64)
)
DYNAMIC RESULT SETS 1
P1: BEGIN

-- Declare cursors
DECLARE cursor CURSOR WITH RETURN FOR
SELECT SESSION_ID, FROM_DATE, DESCRIPTION FROM TACTP00.PAGINATION
ORDER BY current_sort_field ;


OPEN cursor;
--END CASE;
END P1

The another aproach i tried was to put seperate select query with order by on each cloumn with a declared cursor with it.And open only one cursor based on the check whether current_sort_field(input to stored proc) is column1 or column2.This works fine, but this will bring up lots of if conditons in the stored proc as the number of columns in the table increases. It would be great if i can make the first approach work.

Can any body help

Regards

Biju
Arulanand Dayalan
Ranch Hand

Joined: Aug 10, 2005
Posts: 124
You Can use Dynamic Sql. Build the Query using the input parameters. Use EXECUTE IMMEDIATE to execute it.

Hope this Helps.

Thanks and Regards,
Arul.
Biju moozhikkara
Greenhorn

Joined: Oct 21, 2005
Posts: 20
Can any body provide me with Any sample working code or tutorial for dynamic sql in db2 procedures.


Regards
Biju M
Tracy Nelson
Greenhorn

Joined: Sep 29, 2005
Posts: 12
Dynamic SQL is the opposite of stored procedures. What you do is to use a StringBuffer to create your SELECT statement, appending your SORT BY and ORDER BY clauses. Create a PreparedStatment, passing your StringBuffer as the statement to execute. Then just call executeQuery().

If your shop requires that all SQL be performed via stored procedures, then you should talk to your DBA and see if they have any suggestions. Otherwise, you might want to write a small utility that goes out to the database, grabs the metadata for the table you're querying, and generates the stored procedure with the IF statements for every column. At least that way, you don't have to go in and update the SP by hand every time the table schema changes.


Protect me from the things I want!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help for Dynamically changing sort by field and order by clause, in db2 procedure