none
Get "Input string was not in a correct format" exception when calling OdbcConnection's getSchema("Columns") for Oracle 9i database RRS feed

  • Question

  • Hello,

    I'm trying to retrieve the columns from an Oracle 9i database using OdbcConnection class's getSchema method. While I can retrieve the tables and views just fine with getSchema("tables"), everytime I call getSchema("columns") I get a System.FormatException with message "Input string was not in a correct format".

    I've tried various combinations of the schema string, such as "Columns", "COLUMNS", etc.

    Thanks in advance.

    Reference: http://msdn.microsoft.com/en-us/library/cc668760.aspx

    Monday, February 9, 2009 11:31 PM

All replies

  • Please try to use OracleConnection instead.
    The error message seems to indicate that you're using incorrect query input for target server to understand.

    Hopefully it helps.

    Jou
    Tuesday, February 10, 2009 2:25 AM
    Answerer
  • Edit: Intellisense was unable to find OracleConnection as you suggested, I'm researching to see what I need to do to set it up but certainly appreciate any advices.

    BTW, I can execute .getSchema("Columns") for DB2 database just fine.
    Tuesday, February 10, 2009 4:24 PM
  • I think you didn't reference System.Data.OracleClient.dll and therefore you won't get it from intellisense.

    Try to add the following line on you declaration section:
    using System.Data.OracleClient;

    And then add System.Data.OracleClient.dll into your project References.
     
    I assume you want to access an Oracle 9i database and therefore you want to use oracleclient provider accordingly.
    If you want to access other than oracle databases, like db2, using OdbcConnection provider would be fine.

    Hopefully it helps.
    Wednesday, February 11, 2009 12:53 AM
    Answerer
  • Joe,

    Thanks for your help thus far. I was able to successfully connect to the Oracle database using OracleConnection and retrieve tables and columns.

    I'm connected to the Oracle database using Oracle's 9i driver, which would give me the above issue when using OdbcConnection's getSchema("Columns"). Later, however, I was able to successfully configure the Oracle database connection using the Microsoft Oracle driver, and I no longer experience the above getSchema issue. Does anyone know why the Oracle driver supports getSchema with the "Tables" string but not "Columns"?

    Also, what version of Oracle databases are supported by Microsoft's Oracle driver?

    Although OracleConnection class works, I'm unable to use that because I need to be able to support SQL, DB2, and Oracle databases.

    Thanks.
    Wednesday, February 11, 2009 9:00 PM
  • Hi,
        In this case, you may try use System.Data.Common provided feature to fullfil your needs. Please refer to http://msdn.microsoft.com/en-us/library/system.data.common.aspx.
    Using this namespace provided classes will be able to access SQL, DB2 and Oracle databases in one set of code.
    For instance, you may write your code very similar like DBConnection.GetSchema("columns") to get collection of columns from target server.

    Hopefully it helps.
    • Marked as answer by joejou - MSFTEditor Thursday, February 12, 2009 5:19 AM
    • Unmarked as answer by stilz Thursday, February 12, 2009 6:55 PM
    Thursday, February 12, 2009 5:19 AM
    Answerer