About call Stored procedure in ASP

Database

About call Stored procedure in ASP

I want to get a recordset in asp which is returned from teradata stored procedure.

Create PROCEDURE test() DYNAMIC RESULT SETS 1
BEGIN DECLARE cur1 CURSOR WITH RETURN only FOR
SELECT
col1,col2
from myTable;
OPEN cur1;
END;
-----------------it running well and get a result list correctly by execute "call test()" in SQL Assistant.----------------------------

Then I tried several different ways in ASP to get the result list:

***DB connection using ODBC as:

Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN=myDSN;Database=myDB;Uid=myUsr;Pwd=myPwd;")

1.

Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "test"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute

----------then it returns error says:
test is not a macro.

2.

Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "call test"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute
---------it returns error says:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[NCR][ODBC Teradata Driver][Teradata Database] '{' ('7B'X) is not a valid Teradata SQL token.

3.
Set RS = Server.CreateObject("ADODB.RecordSet")
Rs.open "call test()" , DBConn
Response.Write RS.RecordCount

---------it reuturns error says:
Operation is not allowed when the object is closed.
*I think that means no record list returned, but I tested if "RS IS Nothing", it returns false.

4.
Set RS = Server.CreateObject("ADODB.RecordSet")
Rs.open "SELECT col1,col2 from myTable" , DBConn
Response.Write RS.RecordCount
---------it running well, means at least the DB connection is OK and I also tried to execute Macro, it works, too. But actually, I have some more comlecated logic that must use SP to implement instead of Macro.

So, my question is:
1.How can I call a Teradata Strored Procedure in ASP correctly (not a Macro), and get the returned Recordset?
2.When I try to get the return value in ASP, what's the difference between running a select sql and returnning a cursor from SP?

Thanks very much, it really makes me mad.

10 REPLIES
Teradata Employee

Re: About call Stored procedure in ASP

Hi,

Its been an age i worked on ASP .... but from what i can remember .... try looking into ADODB.Connection object and using that try to execute the command over that connection and use MOVENEXT to move to next row.

Following is the code what i can think of right now, but again its been ages with ASP so you better test it out thoroughly:

conn = server.createobject("adodb.connection")
c.Open Application("dbConn")
set r = c.Execute("CALL SP1()")
do while not r.bof and not r.eof
response.write r(0)
r.movenext
loop

HTH!

Regards,

MAC

Re: About call Stored procedure in ASP

Thanks very much for your reply.

Actually, your code is no error to run, but also no result output. In other words:
set r = c.Execute("CALL SP1()") executed successfully, but empty in RecordSet r.

but it will work fine to execute a Macro by
set r = c.Execute("EXEC myMacro()")

That's why I was puzzled: What's the difference between the result of select SQL and the cursor returned from SP, which related to the same source.

Re: About call Stored procedure in ASP

What's more, I wrote a macro to make sure the DBcommand can execute directly, in which call the previous stored procedure:

create Macro ttt as (
call test();
);

And in ASP :
Set objCmd = Server.CreateObject("ADODB.Command")
With objSPCmd
.ActiveConnection = DBconn
.CommandText = "ttt"
.CommandType = &H0004
End with
Set RS = Server.CreateObject("ADODB.RecordSet")
Set RS = objSPCmd.execute

--------it executed successfully, but still no data in RecordSet
what's wrong?.........

N/A

Re: About call Stored procedure in ASP

Hi ,
Any success ? I am facing the same problem ...

Re: About call Stored procedure in ASP

Hi,

I´ve tried solving this issue all day, until i finally noticed that the option "Return Ouptut Parameters as Resultset" on my dsn was unticked, hence never returning anything through stored procedure calls. I had the same behaviour in Teradata SQL Assistant due to this feature. When i ticked it everything started to work as planned. Hope this is the solution in your case too, i see at least that you are using DSN.

The option is located under "Control panel"->"administrative tools"->ODBC Data Source Administrator-> [The DSN] -> configure -> options.

Regards,
Teradata Employee

Re: About call Stored procedure in ASP

rockett,

Did you ever get your stored procedure to return results to ASP? I am trying to do something very similar in an Excel Macro and getting the same errors.

I have tried changing my ODBC settings as pederfrohm suggested but I don't think that has anything to do with this paritucluar issues since it wasn't necessary to do so in SQL Assistant for me.

Thanks
Enthusiast

Re: About call Stored procedure in ASP

Hello everyone!

I can't create stored procedure which will execute (call) other existing stored procedures.

Can somebody help me?

Senior Supporter

Re: About call Stored procedure in ASP

why do you post this 6 times? Better open one new thread

And without sharing your code you are unlikely to get the right help.

Enthusiast

Re: About call Stored procedure in ASP

I am sorry for it :) 


replace procedure call_all_SP ()                  

begin

call created_SP ('P9', 'P8');

call modified_SP ('P9', 'P8');

call removed_SP ('P9', 'P8);


end;

This procedure created successfully, without any errors. But when I call it there are no results (it is not true):

call ABD.call_all_SP();