Calling stored procedure on remote server 1

[galorin]

I am using Access as a frontend for a MySQL 5.0 database. I have created a stored procedure on the MySQL server, that returns one row consisting of two columns, from a table consisting of nearly 5,000 records. I'll take these 2 fields, and place the values into a form.

I need to run the procedure ~30 times, but that part of the code is the same as elsewhere, My only problem, is I can't figure out how to call the stored procedure, and pass the 4 parameters to the procedure it needs to fetch the minute ammount of data.

From MySQL it's called by

MySQL> Call IsCurrent('FirstName','LastName','Company','Item');

How do I call this stored procedure from within Access?

 

[jrbarnett]

Use an ADODB connection to the server and execute it. Something like:

' More information on Connection strings from [URL unfurl="true"]www.connectionstrings.com[/URL]
' This example MySQL connection string comes from there.
Const DB_CONNECT As String = "Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = "IsCurrent('FirstName','LastName','Company','Item');"
  .CommandType = adCmdStoredProc
  Set rst = .Execute
End With

' Now process your recordset object containing the data

cn.Close
Set cn = Nothing

John

 

[galorin]

It's very close, but not quite there yet. It's moved on to an ODBC error which I will place below the code. The relevant bit of code looks like this (Firstname, Lastname, other variables come from either an array or values from another form):

Const DB_CONNECT As String = "Driver={MySQL ODBC 3.51 Driver};Server=192.168.101.14;Port=3306;Database=contacts;User=remote-root; Password=remote;Option=3;"

Dim cn As ADODB.Connection
Dim cmd As ADODB.command
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
.ConnectionString = DB_CONNECT
.Open
End With

For X = 1 To 33

Set cmd = New ADODB.command

strCMD = ""
strCMD = strCMD & "IsCurrent('" & FirstName & "','" & LastName
strCMD = strCMD & "','" & Company & "','" & Item(X) & "');"
cmd.CommandText = strCMD


With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
Set rst = .Execute
End With

Next X

cn.Close

----------

[MySQL][ODBC 3.51Driver][-Mysql Version-]You have an error
in your mysql syntax; check the manual that corresponds to
your MySQL Version for the right syntax to use near
[red]'{ call IsCurrent('Bob','Goss','Acme Holdings','BBS 1');}' at line 1.[/red]

What's with the curly braces? It looks like the command above is what is getting sent, as opposed to sending the line [blue]call IsCurrent('Bob','Goss','Acme Holdings','BBS 1');[/blue]

What am I missing?

 

[jrbarnett]

You haven't put the following line in:

.CommandType = adCmdStoredProc

It needs to go after

Set cmd = New ADODB.Command

and before

strCMD = ""

John

 

[galorin]

I'll try relocating the line as

set cmd.CommandType = adCmdStoredProc as suggested, Having it here

With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc <------
Set rst = .Execute
End With

may be a bit late in the code?

 

[jrbarnett]

Ah, this is an incompatability between ADODB and MySQL.

Change .CommandType = adCmdStoredProc to

.CommandType = adCmdText

and it should work, but beware that you can't easily pick up output parameter values using this method.

I got this from

http://forums.mysql.com/read.php?98,123762,123824#msg-123824

please read for more information.

John

 

[galorin]

Ta, It's working much better now. All that remains is to display the data in my form.

 

[jrbarnett]

I've got the solution to this in the office, will post tomorrow (at home now).

Basically you:
1. add a textbox for each column in the result set to the detail band of the form, with the name of the column.
2. Loop through the columns in the form and assign the controlsource of the column to the equivalent column of the recordset.

John

 

[galorin]

I'll give that a shot, and am eagerly waiting to see the solution from your office. I know this will work in one place at least. However where this particular stored procedure is being used, I call it multiple times, grab the value, put it into the text box, and do an alert if the value isn't equal to a control value.

Unless I assign values into an array, and call out again in a loop, I can't think of a way to make the text fields keep their proper value, instead of all of them grabbing the last value returned by the stored procedure.

 

[jrbarnett]

1. Add a textbox for each field in the recordset to your form.
Name the textbox after the fieldname in the recordset.

2. Add this code to the procedure, immediately above the cn.Close line:

    Dim intCount As Integer ' Loop for interating controls.

    ' Set the form's recordset to the rst object
    Set Me.Form.Recordset = rst
        
    ' Set the control's ControlSource to the appropriate fieldname
    For intCount = 0 To Me.Form.Recordset.Fields.Count - 1
        Me.Controls(rst.Fields(intCount).Name).ControlSource = Me.Form.Recordset.Fields(intCount).Name
    Next intCount

John