|
| |
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
 |
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
< Message edited by rdouglass -- 3/31/2008 12:00:05 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
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.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
