MySQL stored procedures?

[wigwug]

I wanted to take advantage of MySQL 5.0’s stored procedures. Unfortunately they do not show up in the task pane data explorer along side the tables. Is this because I am using the 3.51 version of the ODBC driver or is there another reason? How would I go about accessing my procedures?

Thanks in advance.

Karl (WigWug)

 

[Mike Lewis]

Karl,

I don't think stored procedures ever show up in the Data Explorer. You would normally execute them via SQL pass-through. Basically, call SQLEXEC(), passing a command similar to this one:

EXEC MyProcedure My_First_Parameter, My_Second_Parameter

The above syntax works in SQL Server; I assume MySQL would be the same.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[wigwug]

Mike:

Thanks for the heads up! You are always a big help and I appreciate your input.

Karl

 

[wigwug]

Mike:

Still having an issue... I have tried several varients of this but I just can't seem to get it. Any ideas?

THIS.nConnectionHandle = SQLConnect("MySQLTest")
nlnumret = SQLEXEC(THIS.nconnectionhandle , EXECSCRIPT(p1), 'resultcursor')
SQLDISCONNECT(THIS.nConnectionHandle )


Thanks again

 

[Mike Lewis]

Karl,

You definely don't want EXECSCRIPT in there.

Your second param to SQLEXEC() should contain a string like the one I showed you:

EXEC MyProcedure My_First_Parameter, My_Second_Parameter

EXECSCRIPT() is purely a VFP function. The only way that would work with SQLEXEC() is if it is running some code that itself returns a command that you want to send to MySQL. I doubt that's what you want.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[wigwug]

Mike:

Thanks, I found this out after further reading. My latest attempt...

THIS.nConnectionHandle = SQLConnect("MySQLTest")
nlnumret = SQLEXEC(THIS.nconnectionhandle , 'EXEC(p1)', 'outcursor')
SQLDISCONNECT(THIS.nConnectionHandle )

Still does not work.

Thanks again.

 

[Mike Lewis]

Karl,

Is EXEC(p1) valid MySQL syntax? It doesn't look like standard ANSI SQL. Are you sure you don't want EXEC p1 (without the parentheses), where p1 is the name of the stored proc?

Also, it doesn't help much when you say "still doesn't work". What actually happens when you try? What error, if any, are you seeing?



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)