[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

MySQL Stored procedure with table name as parameter

I am moving a number of MS Access 2003 applications from MS SQL to MySQL, and I need an example connection string and parametrized stored procedure that is the equivalent of

Set cn = CreateObject("ADODB.Connection")
Set rs =  CreateObject("ADODB.RecordSet")
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=username; password=password; Initial Catalog=MyDB;Data Source=192.168.0.1"
spSQL = "execute spSelectRecords " &  TableName
set rs = cn.Execute spSQL

Where the sproc is "Select * from @TableName

Thanks in advance!

0
jriggin
Asked:
jriggin
  • 6
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You will need to use MySQL prepared statement within the stored procedure for that.
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html
0
 
jrigginAuthor Commented:
Thanks.  I am aware of the MySQL documentation.  I was hoping to save many hours of pursuing those docs and experimentation by getting an example proc that queried a table named as a parameter, and the VB syntax to call it by getting an example from someone who had done it.  I can make a MySQL sproc but haven't been able to successfully call it from VB, so I was trying to get an example.
0
 
Kevin CrossChief Technology OfficerCommented:
You have this in VB.NET, but this is VBA within Access, correct?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Kevin CrossChief Technology OfficerCommented:
Additionally, what are you familiar with within MySQL? Do you know how to create a general procedure and call it? Or are you starting totally from scratch? By the above, it sounded like you can handle the proc side, so maybe you just do not know how to call a MySQL stored procedure. Please be specific on what the question is, i.e., share what you have and the exact error or issues you are having. I can code examples, but you will learn this better if you understand and collaborate on the code.
0
 
jrigginAuthor Commented:
I have written many Transact SQL stored procedures and functions.  I am somewhat familiar with MySQL and have connected to it from classic ASP.  I assume much would be much the same.  I have executed stored procedures in MS SQL from VBA in MS Access 2003.  The code pasted in my question is copied from the application I need to change from MS SQL to MySQL,  and I would like to see an example of passing a table name as a parameter to a MySQL stored procedure in VBA for MS Access 2003.  I would like learn it from a functional example as a code template for repeating this in other parts of the application.

Thanks for your assistance.
0
 
Kevin CrossChief Technology OfficerCommented:
The VBA will look pretty similar. What changes is the procedure call itself and how dynamic SQL works within MySQL. Now to be similar to MS SQL, you can construct a normal SQL string within VBA that does the dynamic substitution of the table name. I will mock up an example, but I guess my point earlier that the only big change is the Prepared Statements in place of Exec(ute) or sp_executesql.
0
 
Kevin CrossChief Technology OfficerCommented:
This is an easy reference for connection strings, but figure I would paste before leaving my iPad:
http://www.connectionstrings.com/mysql#p31

The SQL string would be more like "CALL ProcedureName('" & TableName & "');"

That is pretty much the VBA changes off the top of my head. The stored procedure has the simple code from documentation on using prepared statements. When I get to a computer, I will post the example.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an example procedure using Prepared Statements and a tablename parameter.
USE `test`;
DROP procedure IF EXISTS `spSelectRecords`;

DELIMITER $$

USE `test`$$
CREATE PROCEDURE `spSelectRecords`(IN tablename VARCHAR(255))
BEGIN
   SELECT CONCAT('SELECT * FROM `',REPLACE(tablename,'`',''),'`;') 
   INTO @sql;
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
END
$$

DELIMITER ;

Open in new window


Here is an example call from within MySQL:
CALL spSelectRecords('test');

Open in new window


Here is an example call from VBA within MS Access:
Public Sub SelectMySQLRecords(ByVal tablename As String)
   ' create ADO objects
   Dim cn, rs, spSQL
   Set cn = CreateObject("ADODB.Connection")
   ' since cn.Execute returns an ADODB.RecordSet, this is unnecessary.
   'Set rs = CreateObject("ADODB.RecordSet")
   
   ' do some ADOdb work
   ' http://www.connectionstrings.com/mysql#p31
   cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=test;User=root; Password=pa$$wrd8;Option=3;"
   spSQL = "CALL spSelectRecords('" & tablename & "');"
   Set rs = cn.Execute(spSQL)
   
   ' cleanup ADO objects
   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing
End Sub

Open in new window


Hope that helps!
0
 
jrigginAuthor Commented:
Perfect -- thank you.

I haven't tried it yet but I'll reopen if there's an issue
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now