Mono - OSX - ODBC

Question

I've set up system DSNs, which I can use from other ODBC apps (e.g. iQueryODBC), but in mono, I get "Data source name not found and n" (sic).

I am using "DSN=myodbc" for the connection string, via the connection string builder.

OSX 10.4

Latest Mono packages - 2.4.2.3.

Anyone ever got ODBC working on Mono/ OSX?

(Oh - for what it is worth - and I am fairly certain it is not relevant - the DSN is for MySql 5 driver.)

Full code:

     public static void Main (string[] args)
 {
  OdbcConnectionStringBuilder csb = new OdbcConnectionStringBuilder();
  csb.Dsn = args[0];
  DataSet d = GetDataSet(csb.ConnectionString , "SELECT * FROM tbl");
  Console.WriteLine (d.Tables.Count);
 }

 public static DataSet GetDataSet(string connectionString, string queryString)
 {
  Console.WriteLine("GetDataSetFromAdapter(" + connectionString + ")");
  DataSet dataSet = new DataSet();
     using (OdbcConnection connection = new OdbcConnection(connectionString))
     {
        OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, connection);
         // Open the connection and fill the DataSet.
         try
         {
             connection.Open();
             adapter.Fill(dataSet);
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.ToString());
         }
     }
     return dataSet;
 }

Answer 1

In the mono website are a example may can help you.

ODBC-Mono.Net

Answer 2

I think I have seen your post on the Mono mailing list about this but was too busy to respond at the time.

I have used both the ODBC interface and the native connector to connect to MySQL databases for Mono on Mac OS X, Linux, Windows and Solaris (and only ever had issues with an old build of Mono under Solaris).

Because of a problem I encountered with a production environment I switched to using the native MySQL connector some time ago though (I downloaded and built it using Mono Develop without issue).

This is an example of how I used the ODBC interface:

string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" 
    + "SERVER=localhost;" 
    + "DATABASE=myDatabase;"
    + "UID=root;"
    + "PASSWORD=p4ssw0rd;";

// Connect to database using ODBC Driver
OdbcConnection dbConnection = new OdbcConnection(connectionString);
dbConnection.Open();

// Execute SQL using the ODBC interface
OdbcCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = sql
OdbcDataReader dbReader = dbCommand.ExecuteReader();

// Get the result and put it into a hashtable (as an example)
ArrayList arrayList = new ArrayList();
int i = 0;
while(dbReader.Read()) {
    arrayList.Add(new Hashtable());
    Hashtable hashtable = (Hashtable) arrayList[i];
    for (int j = 0; j < dbReader.FieldCount; j++) {
     hashtable.Add(dbReader.GetName( j ).ToString(), dbReader.GetValue( j ));
     }
i++;
}

// Free up resources
dbReader.Close();
dbReader = null;
dbCommand.Dispose();
dbCommand = null;

// Close DB Connection
dbConnection.Close();

If the example does not work (for some non-obvious reason) comment and let me know and I will test it and/or provide a tested example of using the native MySQL provider if that would be helpful.

Answer 3

Thanks for the advice, but 'fraid it does't work - Connection.Open fails, exactly as before, with "Data source name not found..." It thinks the DSN doesn't exist, but... it does, because other apps (non Mono) can use it. I've dived into the Mono source code, for OdbcConnection.Open, but it is just a wrapper for some native code, which I don't have the source for.

Note that I am using totally standard, generic code - a classic 'example' so it is not a question (I'm fairly certain) of not knowing HOW to do it - I am doing it correctly, but it just doesn't work as per the examples elsewhere on the net, because the Odbc connection is asking for a named DSN, and is being told by the native code that the DSN doesn't exist.

Note also that using a DSN-less connection produces EXACTLY the same error - the native code provider still looks for a matching DSN, which (obviously, in this case) it doesn't find because there isn't a DSN called "DATABASE=myDatabase...".

I'm guessing this is OSX 10.4 related, so... I'm maybe not going to find an answer.

I also tried the native MySql connector, but it failed to build. Again, may be 10.4 related. Maybe this is the excuse I need to upgrade... which would (of course!) require a new MacBook... mmmm... shiny!!

Thanks again for the advice, chaps.

Answer 4

For the benefit of anyone else driving by this, you CAN compile the native MySql connector on 10.4. The "sln" which you download from MySql.com contains a lot of other stuff which isn't required, and doesn't compile! But don't be put off - just keep removing projects from the solution until it compiles, and then grab MySql.Data.DLL to add to your project. (You don't have to add it to the GAC - just put it in the "bin" directory of your project, and reference it by file.)

@Iain - thanks for your advice again. I have explained to the Mrs that "a nice man on the internet" said that the only solution is a new MacBook Pro and she is thinking about it!