none
Windows ODBC Datasources and .Net System.Data.Odbc: Which driver is used? RRS feed

  • Question

  • 0 vote down
    star

    Hi all,

    I am confused on how to connect to Oracle via ODBC.

    I have created an ODBC datasource named "oracle " in my Control Panel > Administrative Tools, and used the driver "Microsoft ODBC for Oracle".

    In my app.config , I have the following connection strings

    <?xml version="1.0"?>
    <configuration>
      <configSections>
      </configSections>
      <connectionStrings>
        <add name="MappingAssistantGui2.Properties.Settings.maConnectionString"
          connectionString="Dsn=oracle;uid=ma;pwd=ma" providerName="System.Data.Odbc" />
        <add name="MappingAssistantGui2.Properties.Settings.maConnectionStringSqlServer"
          connectionString="Dsn=sqlserver;uid=sli;pwd=slislisli" providerName="System.Data.Odbc" />
        <add name="MappingAssistantGui2.Properties.Settings.maConnectionStringOracle"
          connectionString="Dsn=oracle;uid=ma;pwd=ma" providerName="System.Data.Odbc" />
      </connectionStrings>
      <startup>
        <supportedRuntime version="v2.0.50727" />
      </startup>
    </configuration>

     

     

    In my code I use the connection string maConnectionString (the first one). Apart from my code not working (I receive the following exception: "ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00905: missing keyword"), I am confused on what driver is actually being used underneath:

    1. Microsoft ODBC for Oracle
    2. System.Data.Odbc (is this a driver, or "accessing" classes?)
    3. Are these two the same?
    4. Or are the System.Data.Odbc classes through which I am accessing the driver? I mean, is "Microsoft ODBC for Oracle" an implementation of ODBC access for Oracle, created by Microsoft?
    5. Something else?

    And something else, do I need to have defined an ODBC datasource to be connecting with System.Data.Odbc namespace to a database (whichever of Oracle, SQL Server, MySQL)? Can I not provide all the information needed to connect to an ODBC datasource with System.Data.Odbc through my code at runtime?

    Thanks!

    Thursday, July 2, 2009 1:27 PM

Answers

  • System.Data.Odbc is the .NET library you need to use when working with ODBC drivers. It's not the actual driver but the bridge between ODBC and ADO.NET. It is not database specific and will work with any ODBC compliant database driver.

    The ODBC driver is specified in the ConnectionString property when you use the Connection class of System.Data.Odbc. Below is an example which uses the Microsoft ODBC for Oracle driver:

            Dim ODBCConnection As New System.Data.Odbc.OdbcConnection
    
            ODBCConnection.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
                                                "SERVER=servername;" & _
                                                "UID=userID;PWD=password;"
    
            ODBCConnection.Open()
            '
            '
            '
            ODBCConnection.Close()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 2, 2009 1:45 PM

All replies

  • System.Data.Odbc is the .NET library you need to use when working with ODBC drivers. It's not the actual driver but the bridge between ODBC and ADO.NET. It is not database specific and will work with any ODBC compliant database driver.

    The ODBC driver is specified in the ConnectionString property when you use the Connection class of System.Data.Odbc. Below is an example which uses the Microsoft ODBC for Oracle driver:

            Dim ODBCConnection As New System.Data.Odbc.OdbcConnection
    
            ODBCConnection.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
                                                "SERVER=servername;" & _
                                                "UID=userID;PWD=password;"
    
            ODBCConnection.Open()
            '
            '
            '
            ODBCConnection.Close()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 2, 2009 1:45 PM
  • Thanks Paul, you are helping me once again.

    How about the error error saying that a keyword is missing?

    ERROR [HY000] [Oracle][ODBC][Ora]ORA-00905: missing keyword

    In the

        <add name="MappingAssistantGui2.Properties.Settings.maConnectionString"
          connectionString="Dsn=oracle;uid=ma;pwd=ma" providerName="System.Data.Odbc" />
    
    the "oracle" dsn is the name of the odbc datasource I have defined. The datasource is valid and working OK, I have rin the "Test connection". The datasource is made with the Oracle ODBC driver.

    Any thoughts on that?

    Cheers
    Thursday, July 2, 2009 4:09 PM
  • Where does the error occur in your code?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 2, 2009 6:42 PM
  • It occurs when the FillBy of a tableAdapter is called.

    So, to sum up:
    • I have the app.config pasted above
    • this generates the corresponding Settings.settings file
    When the program starts, Program.cs has  these lines:
    MappingAssistantGui2.Controller.MappingStoreDbManager.Connection = new System.Data.Odbc.OdbcConnection();
    MappingAssistantGui2.Controller.MappingStoreDbManager.Connection.ConnectionString = MappingAssistantGui2.Properties.Settings.Default.maConnectionString;
    And the program crashes the first time a tableadapter fillBy method is called:

    this.dataflowSetTableAdapter.FillBy(maStoreDataSet.DataflowSet);
    and throw this exception:

    System.Data.Odbc.OdbcException was unhandled by user code
      Message="ERROR [HY000] [Oracle][ODBC][Ora]ORA-00905: missing keyword\n"
      Source="SQORA32.DLL"
      ErrorCode=-2146232009
      StackTrace:
           at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
           at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
           at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
           at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
           at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
           at MappingAssistantGui2.MaStoreDataSetTableAdapters.DataflowSetTableAdapter.FillBy(DataflowSetDataTable dataTable) in C:\Documents and Settings\mfr\My Documents\Visual Studio 2008\Projects\ma-gui\MappingAssistantGui2\maTableAdapters\DataflowSetTableAdapter.cs:line 266
           at MappingAssistantGui2.structurePanel.fillTablesBackgroundWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\Documents and Settings\mfr\My Documents\Visual Studio 2008\Projects\ma-gui\MappingAssistantGui2\StructurePanel.cs:line 408
           at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
           at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
      InnerException: 
    
    Any ideas?

    Thanks again
    Friday, July 3, 2009 9:01 AM
  • Can you post the PL/SQL query? It sounds like there is a syntax problem in the SELECT statement.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 3, 2009 9:43 PM
  • SELECT        DATAFLOW.DF_ID, DATAFLOW.DSD_ID, ARTEFACT.ID, ARTEFACT.VERSION, DATAFLOW_CATEGORY.CAT_ID, ARTEFACT.AGENCY, DATAFLOW.MAP_SET_ID, 
                             ARTEFACT_1.ID AS DSD_IDENT, LOCALISED_STRING.TEXT, LOCALISED_STRING.LANGUAGE, MAPPING_SET.ID AS MAPSETIDENT
    FROM            DATAFLOW INNER JOIN
                             DATAFLOW_CATEGORY ON DATAFLOW.DF_ID = DATAFLOW_CATEGORY.DF_ID INNER JOIN
                             ARTEFACT ON DATAFLOW.DF_ID = ARTEFACT.ART_ID INNER JOIN
                             ARTEFACT AS ARTEFACT_1 ON DATAFLOW.DSD_ID = ARTEFACT_1.ART_ID INNER JOIN
                             LOCALISED_STRING ON ARTEFACT.ART_ID = LOCALISED_STRING.ART_ID LEFT OUTER JOIN
                             MAPPING_SET ON DATAFLOW.MAP_SET_ID = MAPPING_SET.MAP_SET_ID
    WHERE        (LOCALISED_STRING.TYPE = 'Name')
    The same works in SQL Server.
    Monday, July 6, 2009 10:07 AM
  • Does it execute properly from SQL Plus?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, July 6, 2009 12:23 PM