VBA run-time error when passing dates to MySQL query

Question

From Excel VBA i connect to MySQL to fetch my_stored_procedure. Working fine except that when I use date filter as parameters to pass to MySQL SP i receive a run-time error.

The date filters are entered by the user in two cells as start-date and end-date. The cells are defined defined as ranges dateFrom and dateTo.

So I'm using these ranges to pass on to mySql rs, but VBA throws back:

Run-time error '-2147217900 (80040e14)'

You have an error in MySQL syntax; check the manual that corresponds to your MYSQL server version... 'my_stored_procedure '2015-10-01', '2015-10-30'' at line 1.

Below is my VBA code:

        Const ConnStr As String = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=0.0.0.0;Database=db;User=user;Password=pw;Option=3;PORT=3306;Connect Timeout=20;"

Function MySqlCommand(strSql As String) As Variant
    Dim conMySQL
    Set conMySQL = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    conMySQL.ConnectionString = ConnStr
    conMySQL.Open
    Set cmd.ActiveConnection = conMySQL
    cmd.CommandText = strSql
    ReturnValue = cmd.Execute
    conMySQL.Close
End Function

Function MySqlRS(strSql As String) As ADODB.Recordset
    Dim conMySQL
    Set conMySQL = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conMySQL.ConnectionString = ConnStr
    conMySQL.Open
    Set rs.ActiveConnection = conMySQL
    rs.Open strSql
    Set MySqlRS = rs
End Function
_____
Public Sub fetchReport()

Dim rs As ADODB.Recordset
    Set rs = MySqlRS("my_stored_procedure '" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "' ")
Dim hd As Integer


    If Not rs.EOF Then
            For hd = 0 To rs.Fields.Count - 1
                Worksheets("data").Cells(1, hd + 1).Value = rs.Fields(hd).Name  'insert column headers
            Next

        Sheets("data").Range("A2").CopyFromRecordset rs  'insert data
        Sheets("data").Range("A1").CurrentRegion.Name = "rsRange"  'set named range for pivot
    End If

ActiveWorkbook.RefreshAll

End Sub

And here is my SP syntax:

CREATE DEFINER=`user`@`%` PROCEDURE `my_stored_procedure`
(
in fromDate date,
in toDate date
)
BEGIN
SELECT ...
WHERE dateColumn >= fromDate AND dateColumn <= toDate 
END

Any help is appreciated.

Joel

Is that all your code? or there is more? I am looking for a command string and an execute method — user5412293
– user5412293, Commented Nov 2, 2015 at 21:19
This is the way you need to work with queries to data base Connection String > Connection > Command > Record Set. You are missing to create a command pass the store procedure and execute. — user5412293
– user5412293, Commented Nov 2, 2015 at 21:33
thank you. Where would i add it and what? Just to add, the code was working just fine until i added the date queries. Set rs = MySqlRS("my_stored_procedure") — joell
– joell, Commented Nov 2, 2015 at 21:39

Drew · Accepted Answer · 2015-11-03 00:07:04Z

1

I hope this is helpful. Been a long time since I have done this.

Schema

create table myTable
(   id int auto_increment primary key,
    dateColumn date not null,
    stuff varchar(100) not null
);
insert myTable(dateColumn,stuff) values
('2014-12-21','dogs'),('2015-02-21','frogs'),('2015-07-21','snakes'),('2015-12-21','cats');

Stored Proc

drop procedure if exists `my_stored_procedure`;
DELIMITER $$
CREATE PROCEDURE `my_stored_procedure`
(
    in fromDate date,
    in toDate date
)
BEGIN
    SELECT * 
    from myTable
    WHERE dateColumn >= fromDate AND dateColumn <= toDate; 
END
$$
DELIMITER ;

VBA

Function MySqlStoredProcRS(strSql As String) As ADODB.Recordset
    Dim conMySQL
    Set conMySQL = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conMySQL.ConnectionString = ConnStr ' not shown but like yours in Op Question
    conMySQL.Open
    Set rs.ActiveConnection = conMySQL
    rs.CursorLocation = adUseClient
    rs.Open strSql, conMySQL, , , adCmdText
    Set MySqlStoredProcRS = rs
End Function
Public Sub fetchReport()

Dim rs As ADODB.Recordset
Dim sql As String
sql = "call my_stored_procedure ('" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "')"
Set rs = MySqlStoredProcRS(sql)
Dim hd As Integer


    If Not rs.EOF Then
            For hd = 0 To rs.Fields.Count - 1
                Worksheets("data").Cells(1, hd + 1).Value = rs.Fields(hd).Name  'insert column headers
            Next

        Sheets("data").Range("A2").CopyFromRecordset rs  'insert data
        Sheets("data").Range("A1").CurrentRegion.Name = "rsRange"  'set named range for pivot
    End If

ActiveWorkbook.RefreshAll

End Sub

Picture of Output (after clicked Blue thingie)

The Takeaway

I guess explore the changes to the new function MySqlStoredProcRS, the handling of the ADODB.RecordSet, using call, and parentheses wrapping the parameters in the call string.

Good luck.

answered Nov 3, 2015 at 0:07

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

joell Over a year ago

Tnx . Errors out to: PROCEDURE db.my_stored_procedure does not exist.

Drew Over a year ago

then create it. Maybe you have db, db2, db3. What do I know :) Maybe you don't know how to create a stored procedure. Maybe you don't know what database you are in. These are basic things. Stackoverflow is for programming questions, it is not a tutoring site, though it often becomes one. I just don't feel like being a tutor right now.

joell Over a year ago

lol, it does exist in fact, im using it directly thru workbench. weird thing when i remove "call" before the SP i get a different error that syntax isnt correct, same error as in OP.

Drew Over a year ago

we can agree on this pastie, right? ----> pastie.org/10527393

Drew Over a year ago

without call you are doomed. You might as well slap the keyboard and send off that string.

score 0 · Accepted Answer · 2015-11-03 19:06:25Z

0

Hi try this and see how it goes

Option Explicit
Const strCONNECTION As String = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=0.0.0.0;Database=db;User=user;Password=pw;Option=3;PORT=3306;Connect Timeout=20;"


Function GetConnection(ByVal strConnString As String) As ADODB.Connection

    Dim ret As ADODB.Connection

    Set ret = New ADODB.Connection
    ret.Open strConnString

    ' Return the connection object
    Set GetConnection = ret

End Function


Function GetCommand(ByRef oConn As ADODB.Connection, ByVal strCommand As String) As ADODB.Command

    Dim ret As ADODB.Command

    Set ret = New ADODB.Command

    With ret
        Set .ActiveConnection = oConn
        .CommandText = strCommand
        .CommandType = adCmdText
    End With

    ' Return the command
    Set GetCommand = ret

End Function

Function GetRecordSet(ByRef oCommand As ADODB.Command) As ADODB.Recordset
    Set GetRecordSet = oCommand.Execute
End Function


Public Sub fetchReport()

    Dim oCon As ADODB.Connection
    Dim oCom As ADODB.Command
    Dim oRecordSet As ADODB.Recordset
    Dim strCommand As String
    Dim hd As Integer

    ' Create and load the objects
    strCommand = "exec my_stored_procedure '" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "' "

    ' Connection string > Connection > Command > RecordSet
    Set oCon = GetConnection(strCONNECTION)
    Set oCom = GetCommand(oCon, strCommand)
    Set oRecordSet = GetRecordSet(oCom)


    If Not oRecordSet.EOF Then
        For hd = 0 To oRecordSet.Fields.Count - 1
            Worksheets("data").Cells(1, hd + 1).Value = oRecordSet.Fields(hd).Name      'insert column headers
        Next

        Sheets("data").Range("A2").CopyFromRecordset oRecordSet  'insert data
        Sheets("data").Range("A1").CurrentRegion.Name = "rsRange"  'set named range for pivot
    End If

    ActiveWorkbook.RefreshAll

End Sub

I hope this helps :)

edited Nov 3, 2015 at 19:06

answered Nov 2, 2015 at 22:03

user5412293

6 Comments

joell Over a year ago

Thanks Fred. The following compile error, "Variable not defined" at row Set oCon = GetConnection(strCONNECTION)

user5412293 Over a year ago

did you copy all the code? I cannot replicate; its working fine for me.

joell Over a year ago

you right i missed the second row. Now i get "method range of object _global failed" at strCommand = "SP_SalesReport_flat '" & Format(Range("dateFrom"), "yyyy-mm-dd") & "' ,'" & Format(Range("dateTo"), "yyyy-mm-dd") & "' "

user5412293 Over a year ago

make sure that rhe name ranges exist and use the value of the range at the end Range("dateFrom").value Also as per the other post put "Call" in front of the stored procedure name

joell Over a year ago

Not sure why, but after suggested modification i'm getting same error as in OP, regardless of using "call" and .Value: You have an error in MySQL syntax; ... 'my_stored_procedure '2015-10-01', '2015-10-30'' at line 1. Perhaps i need to enable a module/add-in??

|

Collectives™ on Stack Overflow

VBA run-time error when passing dates to MySQL query

2 Answers 2

Schema

Stored Proc

VBA

Picture of Output (after clicked Blue thingie)

The Takeaway

5 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Schema

Stored Proc

VBA

Picture of Output (after clicked Blue thingie)

The Takeaway

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related