MySQL Forums
Forum List  »  Microsoft Access

Stored Procedure with Prepared Statements to build SQL
Posted by: Daniel Hofer
Date: February 27, 2012 02:17PM

This is a stored Procedure workaround to the limitation that an ADODB recordset cannot be assigned as an ACCDB Access report recordsource.

Private Sub Report_Open(Cancel As Integer)
Dim cmdStr As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn, strServer As String

Set cmd = New ADODB.Command

strServer = TempVars![MySQLServer]
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=x; SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB\HDB.dsn;"

With cmd
.ActiveConnection = strConn
.CommandText = "CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '%');"
.CommandType = adCmdText
End With

Set rs = cmd.Execute

' the following shows the recordset has data
Do While Not rs.EOF
Debug.Print rs!COMPANY
rs.MoveNext
Loop

' the following line will not work
Me.Recordset = rs

Set cmd = Nothing
Set rs = Nothing

End Sub
-----------------------------------------------------------------------------------

Work Around:
The following stored procedure is used to fill a MySQL table with records
for the Access report to use as a recordsource.

DELIMITER $$

CREATE PROCEDURE `sp_tempviewinvoicesummary`(invdatestart TEXT, invdateend TEXT, sproj TEXT)
BEGIN

DECLARE spj TEXT;

-- Note: '\'' below is 'single quote'
-- if sproj = '%', want to include records with sproj = null
IF sproj = '%' then
SET spj = CONCAT(' AND (v.sproj LIKE ', '\'', sproj, '\'', ' OR v.sproj IS NULL) ');
ELSEIF INSTR(UPPER(sproj), 'NOT NULL') > 0 then
SET spj = ' AND v.sproj IS NOT NULL';
ELSEIF INSTR(UPPER(sproj), 'NULL') > 0 then
SET spj = ' AND v.sproj IS NULL';
ELSE
SET spj = CONCAT(' AND v.sproj LIKE ', '\'', sproj, '\'');
end if;

SET @str = CONCAT('CREATE TABLE tempviewinvoicesummary
SELECT * FROM viewinvoicesummary AS v
WHERE v.invdate >= \'',invdatestart, '\'',
' AND v.invdate <= \'', invdateend, '\'', spj,
' ORDER BY v.invdate DESC, v.invoiceno DESC;');

-- SELECT @str; used while testing, to check the sql string that was built

BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;
DROP TABLE tempviewinvoicesummary;
END;

PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

Call sp_tempviewinvoicesummary from Access:

Private Sub Report_Open(Cancel As Integer)
Dim cmdStr As String
Dim cmd As ADODB.Command
Dim strConn, strServer As String

Set cmd = New ADODB.Command

strServer = TempVars![MySQLServer]
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=x; SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB\HDB.dsn;"

'cmdStr is built in a criteria form [actual report has many criteria fields] see samples below
cmdStr = TempVars![rptCriteriaCommand]

With cmd
.ActiveConnection = strConn
.CommandText = cmdStr
.CommandType = adCmdText
.Execute
End With

Set cmd = Nothing

End Sub

Four example calls, sproj is a LONG but can be null:

CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '%'); show sproj non-null and null records
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', '3112162'); show sproj matching records
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', 'NULL'); show records with sproj IS NULL
CALL sp_tempviewinvoicesummary('2012-01-01', '2012-2-27', 'NOT NULL'); show records with sproj IS NOT NULL

The table needs to be dropped when the report is closed. Only 1 person can run this report at a time
unless the stored procedure created table name is made unique for each Call.

It's easier and cleaner to just use a pass through query, but I'm trying to build functionality into MySQL
so that it can be used in another front end.

I entertain any comments about improvements.

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure with Prepared Statements to build SQL
3782
February 27, 2012 02:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.