understanding stored procedures (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

crosscreek -> understanding stored procedures (3/30/2008 13:31:04)

I think I am missing a step, but not quite sure. I am using mysql & ASP in frontpage.

I have the stored procedure "stored" in mysql

For example
(the real query is much longer & has joins, but this is just an example)

Stored procedure sp_ped (myinput Int)
Begin
Select * from tbldog Where tbldog.ID = myinput //
End;

Now I have the code below:

<%@ Language=VBScript %>
<% Option Explicit %>


<!-- #include virtual="/adovbs.inc" -->
<!-- #include file="openconn.inc" -->
The openconn.inc includes
Dim myConn
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.open(Application("XXXX"))

<%


Dim strSQL
myinput = 1
strSQL = "sp_ped myinput"

Set objrs = myconn.execute( strSQL )

I get a syntax error. (I've tried different variations with the myinput, but no luck)

Am I missing a step in the ASP code??? I think I am but not sure.





rdouglass -> RE: understanding stored procedures (3/30/2008 15:03:58)

quote:

strSQL = "sp_ped myinput"


How 'bout this:

strSQL = "sp_ped " & myinput

See, your way you're sending just 'myinput' andnot even a string at that. When you're "inside" the quotes, the stuff is sent literally. When you're "outside" the quotes, ASP interprets the variable and then sends the value of that variable.

That help any?



crosscreek -> RE: understanding stored procedures (3/30/2008 19:17:53)

I did try it the way you quoted at first...I had changed the code to try different things.

Dim SQL, objrs, myinput
myinput = 1
SQL = "sp_Pedi" & myinput

set objrs = myconn.execute( sql )
response.write "debug sql: " & sql & " <br>"

Gave me:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[TCX][MyODBC]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sp_Pedi1' at line 1

/sptest.asp, line 24

I even tried:

myinput = 1
SQL = "sp_Pedi myinput" & myinput


Do I need to open or execute the stored procedure BEFORE I execute in the asp page (in the connection strings somehow)?



rdouglass -> RE: understanding stored procedures (3/30/2008 19:37:32)

quote:

strSQL = "sp_ped " & myinput


Ooops. Sorry, I missed something. How 'bout this way:

strSQL = "EXEC sp_ped " & myinput

This is what jogged my memory:

"Do I need to open or execute the stored procedure "

I'm pretty sure that ought to do it. At least that's the way in SQL Server. Its been a wlittle while since I used mySQL but I don't remember them being that different.

That any better?



crosscreek -> RE: understanding stored procedures (3/30/2008 20:20:10)

Now I get

[TCX][MyODBC]Unknown prepared statement handler (sp_Pedi1) given to EXECUTE

/sptest.asp, line 24

I think that's where I get hung up because alot of the examples I read relate to sql server & not mysql.

That's why I thinking I missing a step somewhere. I can do what I need to do without SP's but I think it would make it easier for me so I don't have to change the queries on each ASP page.



rdouglass -> RE: understanding stored procedures (3/31/2008 11:53:54)

Have you checked their support forum? Sorry, but I'm not really very up on mySQL and that's where I'd go. One last shot at it:

strSQL = "CALL sp_ped (" & myinput & ")"

That any better?

Spooky, what do you think?



crosscreek -> RE: understanding stored procedures (3/31/2008 13:38:37)

I will post their as well. I don't like posting in multiple forums with the same question at the same time.

I googled & found most people asking the question.

I did come up with somthing like this, but I will admit I am not really sure of this procedure...commands are very new to me. So are subs.


This is one way
<%@ Language=VBScript %>
<% 'Option Explicit %>


<!-- #include virtual="/adovbs.inc" -->

<%

Main()

Public Sub Main()

Dim oCommand
Set oCommand = Server.CreateObject("ADODB.Command")

oCommand.ActiveConnection = "DRIVER={MYSQL};SERVER=XXXXX;PORT=3306;OPTION=16384;DATABASE=XXXX;UID=XXXX;PWD=XXXX"
oCommand.CommandText = "sp_pedi"
oCommand.CommandType = adCmdStoredProc

Dim oParameter
Set oParameter = oCommand.CreateParameter("myinput", adInteger, adParamInput, 4, myinput)
oCommand.Parameters.Append oParameter

Dim oRecordset
Set oRecordset = oCommand.Execute

End Sub
%>


CREATE PROCEDURE sp_pedi
(
@myinput int
)

AS
SELECT id, name FROM tbldog WHERE tbldog.id = @myinput
<%

Another way I found is:

<!-- #include virtual="/adovbs.inc" -->
<!-- #include file="myconn.inc" -->

Dim cmd
set cmd = Server.CreateObject("ADODB.Command")
' the connection could vary depending on your sql server.
cmd.ActiveConnection = myconn
cmd.CommandText = "sp_pedi"
cmd.CommandTimeout = 30
cmd.CommandType = 4 'same as adCmdStoredProc
cmd.Prepared = true
' the parametes are parameter in sproc, type, direction, size, value
cmd.Parameters.Append cmd.CreateParameter("@myinput")
cmd.Execute()

But then I am not sure how to execute the SP in the SQL of my asp code


Dim SQL, myin
myin = 1
SQL = "Execute sp_Pedi" & myinput


I understand the theory behind SP's just not how to get them to execute.






Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
6.298828E-02