MySQL Stored procs with ASP

tdaustin · June 16th, 2008, 08:15 PM

Hi All,

I am looking at stored procs with mySQL 5 and have managed to create a simple stored proc myself as there doesn't seem to be alot out there with mysql and classic ASP. Im wondering how you pass variables through a stored proc? My simple select works fine. I quick run through would be much appreciated.

Tim :)

Code:

MY PROC
CREATE PROCEDURE `pc_getAccounts`()
    READS SQL DATA
SELECT * FROM accounts

MY ASP Page
[code]

<%

' call stored proc
strSQL = "CALL pc_getAccounts;"
Set objRS = objConn.Execute("call pc_getAccounts()")

Do While not objRs.EOF
    Response.Write objRS("FirstName") & "<BR>"
    Response.Flush()
    objRS.MoveNext
Loop

%>

[/code

TDA

Old Pedant · June 16th, 2008, 09:36 PM

Two ways:

(1) Using ADODB.Command and ADODB.Paremeter objects, which create by calling commandObject.CreateParameter and then add to the command using commandObject.Append

You can check out examples and docs here:
http://msdn.microsoft.com/en-us/library/ms677209(VS.85).aspx
(That's a jump into the middle of the stuff, but perhaps the first thing to see.)

(2) The quick and dirty way.

Code:

CREATE PROC foo 
    @param1 INT,
    @param2 VARCHAR(30)
AS 
   ...

And then invoke it via:

Code:

<%
SQL = "CALL foo( 17, 'zamboni' )"
Set RS = yourConnectionObject.Execute( SQL )
%>

************
CAUTION: That second way is STILL subject to SQL injection! You *NEED* to sanitize all parameters.

So of course the right thing to do is method 1, even if it is a pain.

tdaustin · June 17th, 2008, 12:09 AM

Thanks for the reply.

I will definately look at the correct way to do it, however im still coming to grips with the mySQL part so i will do the short way to get the concepts.

Its definately put me on the right track. However is the stored proceedure a mySQL as i found i need to create variables in a different way. However the funny thing is my store proc works with no errors but shows all results instead of just the 1 record with the id of 17. What have i done wrong?

Apreciate your help :)

Code:

' call stored proc
PersonID = 17
Set objRS =  objConn.Execute("call getAccount("&PersonID&")")

Do While not objRs.EOF
    Response.Write objRS("FirstName") & "<BR>"
    objRS.MoveNext
Loop

MY PROC

Code:

CREATE PROCEDURE `getAccount`(IN PersonID INT)
SELECT * FROM accounts where PersonID = PersonID;

TDA

Old Pedant · June 17th, 2008, 12:35 AM

Might just be because you use the same name!

After all, if there was no parameter in use, this statement *WOULD* be expected to get *ALL* records:
SELECT * FROM accounts where PersonID = PersonID;

Because, indeed, for every record, the value of PersonID *WILL* be the same as the value of ... PersonID!

I've created several MySQL procedures, but I've always carefully avoided overlap of names. So all I can say is that doing so seems to work!

Code:

CREATE PROCEDURE `getAccount`(IN ID INT)
SELECT * FROM accounts where PersonID = ID;

Betcha it then works!

tdaustin · June 17th, 2008, 01:20 AM

haha yep your spot on! Cheers thanks for all your help

Tim :)

TDA