Calling SQL stored procedure from statment?

[Richard Vagner]

Someone showed me that you can callable statement like this to the procedure:

stmt = con.createStatement();
rs = stmt.executeQuery("my_storedprocudure '1','newyork',25,'US'")


It actually works! I thought stored procedure only works with something like con.prepareCall("{call my_sp(?,?,?,?,?)}" then pass in the parameters. Will that achieve the same result?

[ May 05, 2005: Message edited by: Richard Vagner ]
[ May 05, 2005: Message edited by: Richard Vagner ]

[Annie Smith]

You could try this:

PreparedStatement pstmt = con.prepareStatement("exec sp(?, ?, ?, ?, ?)");
rs = pstmt.executeQuery();

I guess something like this should work too.

[Avi Abrami]

Richard,
Thanks, that's nice to know. Now if only you'd mention what database and JDBC driver that works for, it may actually prove useful to someone -- because it definitely doesn't work with my Oracle 9i database and Oracle JDBC driver.

In any case, regardless of which (JDBC) interface you use, your database is going to receive text -- which it will try to parse as valid SQL. Remember, "CallableStatement" inherits from "Statement", so I guess it's logical that your system would work.

Good Luck,
Avi.

[Richard Vagner]

Thank you, both of you, very much. The datbase is MS SQL server