Execute a SP using Classic ASP 1

[AndrewWiggins99]

For some reason I cannot execute this Stored procedure VIA ASP.

Here is my code:

<%

dim db_conn

db_conn = "Provider=MSDAORA;Data Source=Pilot;User Id=USer1;Password=SecretPassword;"
set conn = server.createobject("adodb.connection")
set Cmd = Server.CreateObject("ADODB.Command")
'-------------------------------------------------------
conn.open (db_conn)
'-------------------------------------------------------
set rs = Server.CreateObject("ADODB.RecordSet")

sSQL = "exec dbo.return_book_titles(3340, 'Lassie', 61368359)"
set rs = conn.execute(sSQL)

if (rs.bof and rs.eof) then
response.Write "<span class=""error"">No Record Found</span>"
response.End
end if %>

 

[AndrewWiggins99]

Please note I am able to add a simple SELECT statement in the sQL string and it works fine and I am also able to run the SP via PL/SQL however I cannot execute the SP...
I

 

[AndrewWiggins99]

I now encounter this error message. Issue with calling the SP:

Microsoft OLE DB Provider for Oracle error '80040e14'

ORA-00900: invalid SQL statement

 

[xwb]

On ODBC, use call instead of exec. It should be

sSQL = "call dbo.return_book_titles(3340, 'Lassie', ...

Ref

http://www.oracle.com/technetwork/database/windows/odbcfaq-128251.pdf

 

[AndrewWiggins99]

Changed Exec with Call and this surely did the trick..

 

[ElEye]

As a general tip: if your web code permissions don't allow for the execution of a Stored Procedure, a work around is to create a trigger in the database that executes the SP.

For example, if your web code permissions does allow inserting records into a table then use that capability to insert, which executes the SP triggered by the insert. Obviously, you create the table in the DB and add the trigger for the SP in the DB.

The table itself can be a "junk" table. And the trigger that executes the code you desire can include clearing the "junk" table itself to keep things clean.

I've done this to get around permissions issues in ASP and PHP. I've found this method handy, for example, to execute DBMail (SQL Server) even without DBMail access permissions in the web code.

Dave
[

http://flashspot.com

]