dbSilentCmdText and stored procedures

[Steve Yetter]

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[Frank Kwong]

there are three types of SP support. Check your manual, the following is one type (on the fly) -

<%
response.write(&#34;<center><h3>Stored Proc on the fly - byroyalty</h3></center>&#34
Set X=Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit=3
X.dbMode=&#34;Grid&#34;
X.dbDSN=&#34;DSN=pubs850; UID=sa; PWD=sa&#34;
X.dbStoredProc=&#34;byroyalty,4,100&#34;
X.aspDB
%>



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[Steve Yetter]

Thanks Frank I am familiar with that property but how do you use the dbSilentCmdText property to call a stored procedure. There are no examples in the manual for doing this. I need to call stored procedures that have parameters and those that do not.

I am trying to use a stored procedure to fill a temporary table and then use the dbSQL property to access the temporary table. This should allow me to filter the table which is something that cannot be done using dbStoredProc to return a result set.


------------
Frank Kwong at 6/5/01 3:55:00 PM

there are three types of SP support. Check your manual, the following is one type (on the fly) -

<%
response.write(&#34;<center><h3>Stored Proc on the fly - byroyalty</h3></center>&#34
Set X=Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit=3
X.dbMode=&#34;Grid&#34;
X.dbDSN=&#34;DSN=pubs850; UID=sa; PWD=sa&#34;
X.dbStoredProc=&#34;byroyalty,4,100&#34;
X.aspDB
%>



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[Frank Kwong]

Steve,

This subject is of some interest.

I have not been able to get to SQL&#39;s temporary table as there is no way to issue an SQL against that.

Silent command is just any valid command that acan be executed. But if you activaye an SP and the SP creates a temporary table. How are you going to issue a query against that using ASP (forget about ASP-db for a while).

Let me know what your findings are.

P.S. The coming versions will have the option of an equivalent of silentcmd &#34;after&#34; the dbSQL is executed as available in Oracle and others.

FK


------------
Steve Yetter at 6/5/01 4:19:26 PM

Thanks Frank I am familiar with that property but how do you use the dbSilentCmdText property to call a stored procedure. There are no examples in the manual for doing this. I need to call stored procedures that have parameters and those that do not.

I am trying to use a stored procedure to fill a temporary table and then use the dbSQL property to access the temporary table. This should allow me to filter the table which is something that cannot be done using dbStoredProc to return a result set.


------------
Frank Kwong at 6/5/01 3:55:00 PM

there are three types of SP support. Check your manual, the following is one type (on the fly) -

<%
response.write(&#34;<center><h3>Stored Proc on the fly - byroyalty</h3></center>&#34
Set X=Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit=3
X.dbMode=&#34;Grid&#34;
X.dbDSN=&#34;DSN=pubs850; UID=sa; PWD=sa&#34;
X.dbStoredProc=&#34;byroyalty,4,100&#34;
X.aspDB
%>



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[John]

Hi Steve,

John here... What about using two calls to ASPdb? The first one calls the Stored Proc and creates the temporary table (which you know the name of - since you passed it as a parameter), and the second call retrieves the data from the temp table and displays it?

John



------------
Frank Kwong at 6/6/01 12:19:54 PM

silentcmd is not a query tool but only a processing tool. It returns no recordset, error or anything. You use it to update/delete/add a record with know SQL paameters. After you say activate an SP and generates a temp table. after the silentcmd ends, so would the SP and the temp table is &#34;gone&#34;. That is why I could not get to it.

FK

------------
Steve Yetter at 6/6/01 9:44:13 AM

Frank, the temporary table is a regular sql table created by the stored procedure using a table name generated by the client and passed into the stored procedure that I want to call using dbSilentCmdText.

Can dbSilentCmdText call a stored procedure that takes a parameter? If so please show me the syntax of that command. An example would help since all the examples in the manual show update sql statements.




------------
Frank Kwong at 6/5/01 9:56:51 PM

Steve,

This subject is of some interest.

I have not been able to get to SQL&#39;s temporary table as there is no way to issue an SQL against that.

Silent command is just any valid command that acan be executed. But if you activaye an SP and the SP creates a temporary table. How are you going to issue a query against that using ASP (forget about ASP-db for a while).

Let me know what your findings are.

P.S. The coming versions will have the option of an equivalent of silentcmd &#34;after&#34; the dbSQL is executed as available in Oracle and others.

FK


------------
Steve Yetter at 6/5/01 4:19:26 PM

Thanks Frank I am familiar with that property but how do you use the dbSilentCmdText property to call a stored procedure. There are no examples in the manual for doing this. I need to call stored procedures that have parameters and those that do not.

I am trying to use a stored procedure to fill a temporary table and then use the dbSQL property to access the temporary table. This should allow me to filter the table which is something that cannot be done using dbStoredProc to return a result set.


------------
Frank Kwong at 6/5/01 3:55:00 PM

there are three types of SP support. Check your manual, the following is one type (on the fly) -

<%
response.write(&#34;<center><h3>Stored Proc on the fly - byroyalty</h3></center>&#34
Set X=Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit=3
X.dbMode=&#34;Grid&#34;
X.dbDSN=&#34;DSN=pubs850; UID=sa; PWD=sa&#34;
X.dbStoredProc=&#34;byroyalty,4,100&#34;
X.aspDB
%>



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[Steve Yetter]

John and Frank thanks for the replies.

First Frank by a temporary table I do not mean one in the tempdb. I am referring to an actual table that would persist accross connections but would have a unique name known only to the client. So if there is a way to call a stored procedure with the dbSilentCmdText I would appreciate knowing the syntax.

John I had hoped to avoid the overhead that I assumed would be involved in calling ASP_DB twice. I would think that the silent command would be less overhead.


------------
John at 6/7/01 1:08:04 AM

Hi Steve,

John here... What about using two calls to ASPdb? The first one calls the Stored Proc and creates the temporary table (which you know the name of - since you passed it as a parameter), and the second call retrieves the data from the temp table and displays it?

John



------------
Frank Kwong at 6/6/01 12:19:54 PM

silentcmd is not a query tool but only a processing tool. It returns no recordset, error or anything. You use it to update/delete/add a record with know SQL paameters. After you say activate an SP and generates a temp table. after the silentcmd ends, so would the SP and the temp table is &#34;gone&#34;. That is why I could not get to it.

FK

------------
Steve Yetter at 6/6/01 9:44:13 AM

Frank, the temporary table is a regular sql table created by the stored procedure using a table name generated by the client and passed into the stored procedure that I want to call using dbSilentCmdText.

Can dbSilentCmdText call a stored procedure that takes a parameter? If so please show me the syntax of that command. An example would help since all the examples in the manual show update sql statements.




------------
Frank Kwong at 6/5/01 9:56:51 PM

Steve,

This subject is of some interest.

I have not been able to get to SQL&#39;s temporary table as there is no way to issue an SQL against that.

Silent command is just any valid command that acan be executed. But if you activaye an SP and the SP creates a temporary table. How are you going to issue a query against that using ASP (forget about ASP-db for a while).

Let me know what your findings are.

P.S. The coming versions will have the option of an equivalent of silentcmd &#34;after&#34; the dbSQL is executed as available in Oracle and others.

FK


------------
Steve Yetter at 6/5/01 4:19:26 PM

Thanks Frank I am familiar with that property but how do you use the dbSilentCmdText property to call a stored procedure. There are no examples in the manual for doing this. I need to call stored procedures that have parameters and those that do not.

I am trying to use a stored procedure to fill a temporary table and then use the dbSQL property to access the temporary table. This should allow me to filter the table which is something that cannot be done using dbStoredProc to return a result set.


------------
Frank Kwong at 6/5/01 3:55:00 PM

there are three types of SP support. Check your manual, the following is one type (on the fly) -

<%
response.write(&#34;<center><h3>Stored Proc on the fly - byroyalty</h3></center>&#34
Set X=Server.CreateObject(&#34;Asp.DB&#34
X.dbUnit=3
X.dbMode=&#34;Grid&#34;
X.dbDSN=&#34;DSN=pubs850; UID=sa; PWD=sa&#34;
X.dbStoredProc=&#34;byroyalty,4,100&#34;
X.aspDB
%>



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.

[Frank Kwong]

Refer to the doc ->
Obj.dbSilentCmdText = &#34;(;,)SQL or SET statements [,Type][,Tout];..repeat&#34;

Type = 4 (for SP)

Use on-the-fly with no parameters. Any problem let me know...

FK



------------
Steve Yetter at 6/5/01 3:42:13 PM

What syntax is correct for calling a stored procedure using this command. An example would be helpful.