stored procedure

[jill han <jh...@bynum.com>]

I could find the answers at 
http://wiki.apache.org/db-torque/FrequentlyAskedQuestions

however, the stored procedure is one of oracle object type which sits in
db server. It is not bound to a specific objectPeer as said.
When I wrote a program to run as the sample instructed, the program will
encounter org.apache.torque.TorqueException: java.sql.SQLException:
ORA-00900: invalid SQL statement.

Here is the program code:
....
String sql = "exec doLogin(" + clientName + ", " + username + ", " +
password + ")";
List results = TurbineUserPeer.executeQuery(sql);

Here is the oracle procedure:
doLogin(client in varchar2, username in varchar2, pass in varchar2)
is
uname varchar2(32);
pswd varchar2(32);

BEGIN
  select login_name, password_value into uname, pswd
  from turbine_user
  where login_name = username and password_value = pass and 
        client_id in 
        (select object_id from client where upper(name) like
upper(client));
     DBMS_OUTPUT.PUT_LINE('client name = ' || client || '; login name =
' || uname || '; password = ' || pswd);
  Exception
	  WHEN NO_DATA_FOUND THEN  
	   DBMS_OUTPUT.PUT_LINE('incorrect login!');
END;

Your help is appreciated as always.

Jill

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: stored procedure

[Thomas Vandahl <tv...@apache.org>]

jill han wrote:
> String sql = "exec doLogin(" + clientName + ", " + username + ", " +
> password + ")";

My guess would be that clientName, username and password are strings
which need to be quoted in the SQL statement, like

String sql = "exec doLogin('" + clientName + "', '" + username + "', '"
+ password + "')";

Bye, Thomas.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org