SQLGetInfo - How to use this function

Question

I have developed a c# application which connects to many types of database servers like Sql,Oracle,Mysql etc..Connection was established using ODBC.

I need to find the server type (DBMS type) for which the connection was established, as the user only enters the DSN name.

After several hours of surfing, I found that my requirement is possible only through this function SQLGetInfo.But I don't know how to deal with this in C#.

even have checked this Link1.

from the above link, I found that ODBC API only gives DataBase name and DataSource Name.But I need to find the Datatbase type like whether it's a SQL connection or Oracle connection or mySqlConnection.

Is it possible to get the DBMS type from DSN name..?

Note: I don't want to read it from a registry setting.I tried this and it works, but I'm having permission issues..!

Answer 1

This will absolutely do the trick. Here is my implementation to hook OdbcConnection GetInfoStringUnhandled function. God we love reflection, I know I am legend ;)

public enum SQL_INFO
{
    DATA_SOURCE_NAME,
    DRIVER_NAME,
    DRIVER_VER,
    ODBC_VER,
    SERVER_NAME,
    SEARCH_PATTERN_ESCAPE,
    DBMS_NAME,
    DBMS_VER,
    IDENTIFIER_CASE,
    IDENTIFIER_QUOTE_CHAR,
    CATALOG_NAME_SEPARATOR,
    DRIVER_ODBC_VER,
    GROUP_BY,
    KEYWORDS,
    ORDER_BY_COLUMNS_IN_SELECT,
    QUOTED_IDENTIFIER_CASE,
    SQL_OJ_CAPABILITIES_30,
    SQL_SQL92_RELATIONAL_JOIN_OPERATORS,
    SQL_OJ_CAPABILITIES_20

}

public static string GetInfoStringUnhandled(OdbcConnection ocn, SQL_INFO info)
{
    MethodInfo GetInfoStringUnhandled = ocn.GetType().GetMethods(BindingFlags.NonPublic | BindingFlags.Instance).First(c => c.Name == "GetInfoStringUnhandled");
    ParameterInfo SQL_INFO =
        GetInfoStringUnhandled.GetParameters()
            .First(c => (c.ParameterType.ToString() == "System.Data.Odbc.ODBC32+SQL_INFO"));
    Array EnumValues = SQL_INFO.ParameterType.GetEnumValues();
    foreach (var enumval in EnumValues) {
        if (enumval.ToString() == info.ToString()) {
            return Convert.ToString(GetInfoStringUnhandled.Invoke(ocn, new object[] { enumval }));
        }
    }
    return string.Empty;
}

private static void Main(string[] args)
{
    OdbcConnection ocn = new OdbcConnection("DSN=GENESIS");
    ocn.Open();
    Console.WriteLine(GetInfoStringUnhandled(ocn, SQL_INFO.DBMS_VER) + " " +
                      GetInfoStringUnhandled(ocn, SQL_INFO.DBMS_NAME));
}

The best documentation I have found, explaining the 47 possibilities to use SQLGetInfo is here https://mariadb.com/kb/en/sql-99/sqlgetinfo/

Nevertheless, OdbcConnection has just integrated an enum with 19 possibilities. The following is an disassembled enum of SQL_INFO of System.Data.Odbc.ODBC32:

public enum SQL_INFO : ushort
{
    DATA_SOURCE_NAME = (ushort)2,
    DRIVER_NAME = (ushort)6,
    DRIVER_VER = (ushort)7,
    ODBC_VER = (ushort)10,
    SERVER_NAME = (ushort)13,
    SEARCH_PATTERN_ESCAPE = (ushort)14,
    DBMS_NAME = (ushort)17,
    DBMS_VER = (ushort)18,
    IDENTIFIER_CASE = (ushort)28,
    IDENTIFIER_QUOTE_CHAR = (ushort)29,
    CATALOG_NAME_SEPARATOR = (ushort)41,
    DRIVER_ODBC_VER = (ushort)77,
    GROUP_BY = (ushort)88,
    KEYWORDS = (ushort)89,
    ORDER_BY_COLUMNS_IN_SELECT = (ushort)90,
    QUOTED_IDENTIFIER_CASE = (ushort)93,
    SQL_OJ_CAPABILITIES_30 = (ushort)115,
    SQL_SQL92_RELATIONAL_JOIN_OPERATORS = (ushort)161,
    SQL_OJ_CAPABILITIES_20 = (ushort)65003
}

As you see, you can simply Invoke the GetInfoStringUnhandled method with a (ushort) casted integer of the info you need. This is an example:

public static string GetInfoStringUnhandled(OdbcConnection ocn, ushort info)
{
    MethodInfo GetInfoStringUnhandled = ocn.GetType().GetMethods(BindingFlags.NonPublic | BindingFlags.Instance).First(c => c.Name == "GetInfoStringUnhandled");
    return Convert.ToString(GetInfoStringUnhandled.Invoke(ocn, new object[] { (ushort)info }));            
}

public static void Main(string[] args)
{
    OdbcConnection ocn = new OdbcConnection("DSN=GENESIS");
    ocn.Open();
    Console.WriteLine(GetInfoStringUnhandled(ocn, (ushort)10003)); //SQL_CATALOG_NAME returns Y
}

Answer 2

Short answer: Don't. Try very hard to find managed equivalents instead. There is no documented way of getting this handle.

Long answer: The SQLGetInfo function's InfoType parameter has 47 possible values. Ref. You can retrieve a regex pattern for quoted identifiers as follows:

DataTable dt = connection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation); 
string quotedIdentifierPattern = (string)dt.Rows[0][DbMetaDataColumnNames.QuotedIdentifierPattern];

This will allow you to recognize but not produce quoted identifiers. It's safe to assume the quote character is indeed one character, though, so you can get it by simply doing:

Regex.Unescape(quotedIdentifierPattern)[0];

(The .Unescape() is necessary since the quote character could be special to regexen and hence escaped.)

Most other uses for SQLInfo() can similarly be solved with .GetSchema(). If you absolutely, positively must use SQLGetInfo() for something, I recommend using the private methods .GetInfoInt16Unhandled(), .GetInfoInt32Unhandled() and ..GetInfoStringUnhandled() on OdbcConnection through reflection. This is subject to breaking without warning, though.

You can get the internal handle through the private .ConnectionHandle member, but this is equally subject to breaking and far less convenient (because you have to write all the unmanaged interop code too).

Use ILSpy or Reflector to get more details on the implementation. Reverse engineering the innards can in many cases point you to a fully managed solution. Ref.

---

OR build on this MSDN sample code to detect the version via different commands, eg

MySQL:        "SELECT version()";
Oracle:         "SELECT @@version, @@version_comment FROM dual";
SQLServer:  "SELECT @@version";

MSDN Sample Code:

using System;
using System.Data;

namespace IDbConnectionSample {
   class Program {
      static void Main(string[] args) {
         IDbConnection connection;

         // First use a SqlClient connection
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(localdb)\V11.0");
         Console.WriteLine("SqlClient\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(local);Integrated Security=true");
         Console.WriteLine("SqlClient\r\n{0}", GetServerVersion(connection));

         // Call the same method using ODBC 
         // NOTE: LocalDB requires the SQL Server 2012 Native Client ODBC driver
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(localdb)\v11.0");
         Console.WriteLine("ODBC\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(local);Trusted_Connection=yes");
         Console.WriteLine("ODBC\r\n{0}", GetServerVersion(connection));

         // Call the same method using OLE DB
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(localdb)\v11.0;Trusted_Connection=yes;");
         Console.WriteLine("OLE DB\r\n{0}", GetServerVersion(connection));
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(local);Trusted_Connection=yes;");
         Console.WriteLine("OLE DB\r\n{0}", GetServerVersion(connection));
         }

      public static string GetServerVersion(IDbConnection connection) {
         // Ensure that the connection is opened (otherwise executing the command will fail)
         ConnectionState originalState = connection.State;
         if (originalState != ConnectionState.Open)
            connection.Open();
         try {
            // Create a command to get the server version 
            IDbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT @@version"; //<- HERE  
            //try out the different commands by passing the CommandText as a parameter
            return (string)command.ExecuteScalar();
         }
         finally {
            // Close the connection if that's how we got it 
            if (originalState == ConnectionState.Closed)
               connection.Close();
         }
      }
   }
}

---

OR you could do something like others suggest, with a little more elegance.

Note: this is a copy / paste job on @FabianStern 's answer - credit to the author. I just made it less procedural and more orthodox as I couldn't stand the cascading Try-Catch's):

protected static DBType GetDBType(string odbcConnStr)
{
 var dbType = DBType.UNSUPPORTED;
 try
 {
  using (var cn = new OdbcConnection(odbcConnStr))
  {
    if (cn.State != ConnectionState.Open) cn.Open();
    dbType = GetDbType(cn, dbType)
    if (dbType > 0) return dbType;

    var sqlVersionQuery = "SELECT version()"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.MYSQL)
    if (dbType > 0) return dbType;

    sqlVersionQuery = "SELECT @@version, @@version_comment FROM dual"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.Oracle)
    if (dbType > 0) return dbType;

    sqlVersionQuery = "SELECT @@version"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.MSSQL)
    if (dbType > 0) return dbType;
  }
 }
 catch(Exception connEx) { }
 return dbType;
}

public enum DBType
{
    UNSUPPORTED = 0,
    MYSQL = 1,
    ORACLE = 2,
    MSSQL = 3,
    JET = 4
}

private static DBType GetDBType(OdbcConnection cn, DBType dbType)
{
  try
  {
    if (cn.Driver == "odbcjt32.dll") dbType = DBType.JET;
  }
  catch(Exception ex) { }
  return dbType;
}

private static DBType GetDbType(OdbcConnection cn, string sqlVersionQuery, DBType dbType)
{
  try
  {
  using (var cmd = cn.CreateCommand()) {
  cmd.CommandText = sqlVersionQuery;
    try
    {
       using (var reader = cmd.ExecuteReader()) 
       {
           if (reader.HasRows) return dbType;
       }
    }
    catch (Exception ex) { }
  }}
  catch (Exception cmdEx) { }           
  }
 return dbType;
}