Mysql ODBC Select command with parameter - not working

Question

please, can anybody explain why this code is not working? Connection to mysql is made via mysql odbc driver (latest). Parameter in Select command is not recognized.

I also tried to replace @param1 in Select command:

Select product_id from product where model = ?

code still not working.

Sub Main()

    Dim DBCONT As New Odbc.OdbcConnection
    Dim strConn As String = "DSN=MyDSN"
    DBCONT.ConnectionString = strConn
    DBCONT.Open()

    Dim cmd As New Odbc.OdbcCommand
    With cmd
        .CommandText = "SELECT product_id FROM products WHERE model = @param"
        .Connection = DBCONT
    End With

    Dim param1 As Odbc.OdbcParameter
    param1 = cmd.CreateParameter()
    With param1
        .ParameterName = "@param"
        .OdbcType = Odbc.OdbcType.VarChar
        .Size = 30
        .Value = "TESTVALUE"
    End With


    Dim reader As Odbc.OdbcDataReader
    reader = cmd.ExecuteReader

    Console.WriteLine(cmd.CommandText.ToString)
    'this line displays "Select product_id from products where model = @param"
    'instead of  "Select product_id from products where model = "TESTVALUE"..
    'WHY??

    While reader.Read
        Console.WriteLine(reader(0))
        Console.WriteLine()
    End While
    Console.ReadLine()

    DBCONT.Close()
    reader = Nothing
    cmd = Nothing

End Sub

I don't think that MySQL supports using "@" as a parameter prefix except via Connector/Net. I don't use it much so I could be wrong but I think the standard for MySQL is to prefix the parameter name with a colon in the SQL code and then omit that when setting the parameter name. — jmcilhinney, Oct 27 '18 at 12:52
There is a native provider for MySql available as a Nuget package. Forget ODBC and install the Nuget. Then reference MySql.Data.MySqlClient. This provider has no problem with parameters starting with @. — Mary, Oct 27 '18 at 17:29

score 0 · Answer 1 · answered Oct 27 '18 at 12:59

0

Where it says:

".CommandText = "SELECT product_id FROM products WHERE model = @param"

Change it to:

".CommandText = "SELECT product_id FROM products WHERE model = '@param'"

(I've put ' ' around the @param, keep in mind this is different to the " " that is surrounding this)

answered Oct 27 '18 at 12:59

But putting single quotes around it would make it literal text, would it not? – jmcilhinney Oct 27 '18 at 13:19
.CommandText = "SELECT product_id FROM products WHERE model = '@param'" the code console.writeline(cmd.commandtext) displays SELECT product_id FROM products WHERE model = '@param' – js1980 Oct 27 '18 at 13:49

score 0 · Answer 2 · answered Oct 27 '18 at 13:19

0

I'm not 100% sure but I'll post this as an answer anyway because I think it's correct:

Dim cmd As New Odbc.OdbcCommand
With cmd
    .CommandText = "SELECT product_id FROM products WHERE model = :param"
    .Connection = DBCONT
End With

Dim param1 As Odbc.OdbcParameter
param1 = cmd.CreateParameter()
With param1
    .ParameterName = "param"
    .OdbcType = Odbc.OdbcType.VarChar
    .Size = 30
    .Value = "TESTVALUE"
End With

answered Oct 27 '18 at 13:19

jmcilhinney

50,448
5
26
46

`cmd.Prepare()`, have you tried that? Also I think you use `?` in place where the params go. – Trevor Oct 27 '18 at 13:45
What about `?`...? – Trevor Oct 27 '18 at 13:49
no way.... -> ERROR [07001] [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.23-23]The number of parameter markers is not equal to he number of parameters provided' – js1980 Oct 27 '18 at 14:00
According to [this thread](https://stackoverflow.com/questions/18082840/how-to-bind-parameters-via-odbc-c) the question mark is the way to go. Maybe try that and then, if it doesn't work, edit your question to show us the exact code that you tried. – jmcilhinney Oct 27 '18 at 14:30

score -1 · Accepted Answer · answered Oct 27 '18 at 18:34

Thanks for your help. This code is already working:

Sub Main()

Dim DBCONT As New Odbc.OdbcConnection
Dim strConn As String = "DSN=MyDSN"
DBCONT.ConnectionString = strConn
DBCONT.Open()

Dim cmd As New Odbc.OdbcCommand
With cmd
    .CommandText = "SELECT product_id FROM products WHERE model LIKE ?"
    //it seems it is important to add paramater right after commandtext
    .Parameters.Add("@param", OdbcType.VarChar).Value = "%" + "TESTVALUE" + "%"
    .Connection = DBCONT
End With

Dim reader As Odbc.OdbcDataReader
reader = cmd.ExecuteReader
Console.WriteLine(cmd.CommandText.ToString)
//it should display SELECT product_id FROM products WHERE model LIKE ?
While reader.Read
    Console.WriteLine(reader(0))
    Console.WriteLine()
End While
Console.ReadLine()

DBCONT.Close()
reader = Nothing
cmd = Nothing

End Sub

Mysql ODBC Select command with parameter - not working

3 Answers3