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
    .ExecuteQueries
End With

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

How can I do to solve this error
Thank's
fgo36Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
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
Cn.Open
   
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)
    .Parameters.Refresh
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

0
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
http://forums.mysql.com/read.php?98,123762,123824#msg-123824
http://forums.mysql.com/read.php?98,373032,373057#msg-373057
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
SHOW WARNINGS;
USE `car`;
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

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

  DECLARE result varchar(100) DEFAULT '';

  SET result = '*NOT_EXIST';

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

END

0
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
Cn.Open
SQL = "SELECT embedded_software_version_Get('" & pieces & "','" & proc & "')"
Set lRst = Cn.Execute(SQL, , adCmdText)
sqlGetVersion = lRst.Fields(0)

ExecuteSQLCommand_Exit:
 Set lRst = Nothing
 Set lCmd = Nothing
 Cn.Close
 Exit Function
End Function


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fgo36Author Commented:
I found solution myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.