Example of return value for Visual Basic stored functions

I have a Mysql 5.1.46 stored function which is working well in Mysql and C# applications.
I want to use it in Visual Basic and it cann't work (use of ODBC 5.1.8)
The function,
Mystr = Get_sw("123456", "prune")
takes 2 varchar parameters and must return a Varchar
I configure the parameters like this

With My_Obj_DB
    .Queries ("Get_sw")
    .DefParameter adParamReturnValue, strMsg, adVarChar
    .DefParameter adParamInput, "123456", adVarChar
    .DefParameter adParamInput, "prune", adVarChar
End With

and the following error is returned
"Error in SQL syntax"
'_latin1"= call Get_sw(_latin1'123456',_latin1" at line 1

How can I do to solve this error
Who is Participating?
fgo36Author Commented:
The solution is to use a direct command:

Option Explicit
Public Function sqlGetVersion(pieces As String, proc As String) As String
Dim Cn As ADODB.Connection
Dim lCmd As ADODB.Command
Dim lRst As ADODB.Recordset
Dim Result As String
Dim SQL As String
Set Cn = New ADODB.Connection
Set lCmd = New ADODB.Command
Set lRst = New ADODB.Recordset

Cn.CursorLocation = adUseClient
Cn.ConnectionString = ConnectionString_dev
SQL = "SELECT embedded_software_version_Get('" & pieces & "','" & proc & "')"
Set lRst = Cn.Execute(SQL, , adCmdText)
sqlGetVersion = lRst.Fields(0)

 Set lRst = Nothing
 Set lCmd = Nothing
 Exit Function
End Function

Robberbaron (robr)Commented:
have you tried using ADODB . this wraps the conenction in a newer interface  http://dev.mysql.com/doc/refman/5.0/fr/ado--rs-addnew.html

then the calls to the db are a little different.  for my use, the result is returned as a recordset,
Dim bRevOK As Boolean
    Dim cn_Doc As ADODB.Connection, cmd_Doc As ADODB.Command
    Dim rs_Rev As ADODB.Recordset
    Set cn_Doc = New ADODB.Connection
    Set cmd_Doc = New ADODB.Command
    Set rs_Rev = New ADODB.Recordset
    cn_Doc.ConnectionTimeout = IDoc_conn_ConnectionTimeout
    cn_Doc.CommandTimeout = IDoc_conn_CommandTimeout
    cn_Doc.CursorLocation = IDoc_conn_CursorLocation
    cn_Doc.Open IDoc_Conn_ConnectionString, IDoc_conn_RuntimeUserName, IDoc_conn_RuntimePassword
    cmd_Doc.ActiveConnection = cn_Doc
    cmd_Doc.CommandType = adCmdText
    ClearCommandParameters cmd_Doc
    cmd_Doc.CommandType = adCmdStoredProc
    rs_Rev.CursorType = adOpenForwardOnly  'adOpenStatic
    rs_Rev.CursorLocation = adUseClient
    rs_Rev.LockType = adLockReadOnly
    cmd_Doc.Parameters.Append cmd_Doc.CreateParameter("ProjectId", adVarChar, adParamInput, 10, sProjId)
    cmd_Doc.Parameters.Append cmd_Doc.CreateParameter("DocNo", adInteger, adParamInput, 4, Null)
    cmd_Doc.Parameters.Append cmd_Doc.CreateParameter("DocId", adVarChar, adParamInput, 50, sDocId)
    cmd_Doc.Parameters.Append cmd_Doc.CreateParameter("TestRev", adVarChar, adParamInput, 5, sRevNew)
    'now get the actual documents
    cmd_Doc.CommandText = "GetProjDocsLatest"  'name of stored proceedure
    Set rs_Rev = cmd_Doc.Execute
    ClearCommandParameters cmd_Doc
    bRevOK = False

Open in new window

fgo36Author Commented:
I have no problem for using a Stored procedure, I just need to have a response with a stored function.
I have try many possiblities but no correct answer, always error.

Hereafter my last try

' Server Win 2003 32Bits, Client Win XP SP 3
' Mysql 5.1.46,
' ODBC Connector 5.1.8
' ConnectionString
' DRIVER={MySql ODBC 5.1 Driver};SERVER=sv;DATABASE=db;UID=us;PWD=pw;CHARSET=UTF8;OPTION=272646155

Set Cn = New ADODB.Connection
Set lCmd = New ADODB.Command
Cn.CursorLocation = adUseClient
Cn.ConnectionString = ConnectionString
lCmd.ActiveConnection = Cn
With lCmd
    .CommandType = adCmdStoredProc
    .CommandText = "get_embedded_software_version"
    .Parameters.append .CreateParameter("@result", adVarChar, adParamReturnValue, 40)
    .Parameters.append .CreateParameter("@par_event_name", adVarChar, adParamInput, 100)
    .Parameters.append .CreateParameter("@par_event_software", adVarChar, adParamInput, 100)
End With
lCmd(1) = "3"
lCmd(2) = "eagle"

lCmd.Execute Options:=adExecuteNoRecords

' ERROR: [MySQL][ODBC 5.1 Driver][mysqld-5.1.46-community]You have an error in your SQL syntax;
'   check the manual that corresponds to your MySQL server version for the right syntax to use
'   near ''' = call get_embedded_software_version('3', 'eagle')' at line 1

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Robberbaron (robr)Commented:
it looks ok generally.

http://www.codeguru.com/forum/archive/index.php/t-182960.html is similar but doesnt use @result (ie just "result")

this appears to be the problem as MySQL seems to be trying to assign the result of the call to nothing.
'' = call get_embedded_software_version('3', 'eagle')

the SP does return a varchar ?   should it be char(8) or numeric ?  (unlikely)

Possible bug but this was v3.5?? see http://bugs.mysql.com/bug.php?id=15635
fgo36Author Commented:
My problem is the same, the .commandText is = to { ? = call Get_emxxx(?, ?) } just before execute.

I tried with different parameters without succcess.
I use stored proc in the same program with no error but I want to use stored func.

Here a simple SQL function that I try, with no success
USE `car`;
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------

CREATE DEFINER=`root`@`localhost` FUNCTION `Get_param`(
  par_event_name varchar(20)
) RETURNS varchar(100) CHARSET latin1

  DECLARE result varchar(100) DEFAULT '';

  SET result = '*NOT_EXIST';

  SELECT description INTO result FROM customers
        WHERE name = par_event_name;
  RETURN result;


fgo36Author Commented:
I found solution myself
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.