none
MS SQL to ODBC variable transition RRS feed

  • Question

  • In our organization we have an application that has historically run on MS SQL, but customers have been demanding connection to other databases.  Because of this I am working on a project to port what was developed to connect to MS SQL to ODBC.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    I’ve already mucked around with a lot as we realize this is a major change.  What I find odd is that I’ve come to a point where I’ve changed my data sources from SQL to ODBC.  I’m doing this by backing up in the wizard to connection type, changing to ODBC and pushing final.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    I’ve tried a lot of other ways to save previously written SQL, but this appears to get me the farthest.  The problem I’m having has to do with the parameters and the @variable declarations.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    For instance what gets generated in the code is of a form…

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Imports System.Data.Common

    Imports System.Data.Odbc

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Dim Command As DbCommand = New OdbcCommand()

    Command.Connection = Me.Connection

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Command.CommandText = “SELECT * FROM mytable WHERE toshow = @showable”

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Command.Parameters.Add(New System.Data.Odbc.OdbcParameter("@showable", OdbcType.BigInt, 8, System.Data.ParameterDirection.Input, CType(0,Byte), CType(0,Byte), "toshow", System.Data.DataRowVersion.Current, false, Nothing))

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Me.Adapter.SelectCommand = Command

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Me.Adapter.SelectCommand.Parameters(0).Value = CType(1,Integer)

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Me.Adapter.Fill(someDataTable)

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    The problem is that I get an error as though setting replaceable strings like @showable may not be supported.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    The exception I receive has a message similar to…

    System.Data.Odbc.OdbcException = {"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@ showable’."}

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    By the looks of the error it looks like performing this is permitted since it’s specifying I need to declare the variable, but still I don’t know.  I haven’t been able to find a clear definition of how to perform this in ODBC or if the DataGrid style components support ODBC.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Any lead on this would be greatly appreciated.

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    <!--[if !supportEmptyParas]--> <!--[endif]-->

    Monday, March 17, 2008 9:15 PM

Answers

  • I believe ODBC driver does NOT support named parameters and your queries should specify parameters as question marks, like

     

    SELECT * FROM mytable WHERE toshow = ?

     

    And because you cannot reference parameters by names anymore you should be careful, because you must pass all the parameters in exact same order as they are in your queries.

    I would suggest different approach in a case if your are developing in .NET 2.0 or newer version of .NET. You could use Factory .NET class that hides actual provider-specific implementation from the caller and exposes all ADO.NET functionality to the caller as database-independanr class. You could find more information about factory class here

     

    http://msdn2.microsoft.com/en-us/library/wda6c36e.aspx

     

    http://www.devx.com/dotnet/Article/27297/1954

     

    Tuesday, March 18, 2008 9:59 AM
    Moderator