Highlighted
Absent Member.
Absent Member.
1583 views

[archive] Creating System Dsn's dynamically

[Migrated content. Thread originally posted on 14 March 2005]

Hello,

I have a situation where I need to create System DSN's using the AccuODBC. I don't need a UI in fact I don't want to use the UI to create the System DSN. I need to have my application that will dynamically create the System dsn base on parameters that are passed to it and use the newly created DSN. After it has completed its task then Delete it again. We are using the .Net Framework and either VB.net or C#.net. Any Ideas or suggestion would be appreciated.


Thanks
0 Likes
3 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Creating System Dsn's dynamically

The simplest way would be to use a DSN less connection string like this:

Driver={AcuODBC Driver};DBQ=C:\ACUDATA\XFD;FILE PREFIX=C:\ACUDATA\FILES;FILE SUFFIX=FIL;

but it doesn't seem to work with the AcuODBC driver. In .NET you get the error:

Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [08001] [AcuCorp, Inc.][AcuODBC Driver]Please specify an XFD (data dictionary) directory.
at System.Data.Odbc.OdbcConnection.Open()

So, next option is to create the sytem DSN in the registry. The Registry key is HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
If you look at this key on your PC you will find subkey entries for your AcuODBC DSN's. Within those DSN subkeys are values and their respective data. In your .NET program use the same values, but just change the data to fit the needs of the user. Use the Microsoft.Win32 namespace for reading/writing/deleting in the registry.

Example here:
http://www.thecodeproject.com/csharp/modifyregistry.asp
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Creating System Dsn's dynamically

Thanks yes...I have to use a System DSN...I came just found another example as follows:


using System;
using System.Runtime.InteropServices;

    ///
    /// The ODBC Library class uses the ODBCCP32.dll API to create and remove ODBC System DSN's
    ///
    public class OdbcLib
    {

        #region Private constants
            //Module Constants used in creating DSN's
            private const string ODBC_INI_DSN_KEY_NAME = "DSN=";
            private const string ODBC_INI_FILE_PREFIX_NAME = "\0File Prefix=";
            private const string ODBC_INI_UID_NAME = "\0Uid=";
            private const string ODBC_INI_PWD_NAME = "\0pwd=";
            private const string ODBC_INI_DBQ_NAME = "\0DBQ=";
            private const string ODBC_INI_ACUODBC_DRIVER = "AcuODBC Driver";    //The Default Driver
            private const string ODBC_INI_READONLY = "\0Read Only=";
        
        #endregion

        #region Constructors
            ///
            /// The constructor.
            ///
            public OdbcLib()
            {
            }
        #endregion

        #region Public methods
        ///
        /// Creates a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool AddUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 1, OdbcDriverName + "\0",
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_UID_NAME + "Admin" + ODBC_INI_PWD_NAME + "" + ODBC_INI_DBQ_NAME + DBPath + "\0");
        }
        
        ///
        /// Modify a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// Path to database
        /// True if successfull
        public bool EditUserDSN(string OdbcDriverName, string DSName, string DBPath)
        {
            return SQLConfigDataSource((IntPtr)0, 2, OdbcDriverName + "\0",
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_UID_NAME + "Admin" + ODBC_INI_PWD_NAME + "" + ODBC_INI_DBQ_NAME + DBPath + "\0");

        }

        ///
        /// Remove a User DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// True if successfull
        public bool RemoveUserDSN(string OdbcDriverName, string DSName)
        {
            return SQLConfigDataSource((IntPtr)0, 3, OdbcDriverName + "\0",
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_UID_NAME + "Admin" + ODBC_INI_PWD_NAME + "" + ODBC_INI_DBQ_NAME + "\0");
        }


        ///
        /// Add a System DSN.
        ///
        /// Name of the ODBC Driver
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {
            return SQLConfigDataSource((IntPtr)0, 4, OdbcDriverName,
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_FILE_PREFIX_NAME + FilePrefix + ODBC_INI_DBQ_NAME + DBQ + ODBC_INI_READONLY + "FALSE" + "\0");

        }

        ///
        /// Add a System DSN.
        ///
        /// Name of the DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool AddSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {
            return AddSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix,DBQ);

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string OdbcDriverName, string DSName, string FilePrefix ,string DBQ)
        {

            return SQLConfigDataSource((IntPtr)0, 5, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_FILE_PREFIX_NAME + FilePrefix + ODBC_INI_DBQ_NAME + DBQ + "\0");

        }

        ///
        /// Modify a System DSN
        ///
        /// Name of DSN
        /// FilePrefix
        /// DBQ
        /// True if successfull
        public bool EditSystemDSN(string DSName, string FilePrefix ,string DBQ)
        {

            return EditSystemDSN(ODBC_INI_ACUODBC_DRIVER, DSName,FilePrefix, DBQ);

        }
        
        ///
        /// Remove a System DSN
        ///
        /// Name of the ODBC Driver
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string OdbcDriverName, string DSName)
        {

            return SQLConfigDataSource((IntPtr)0, 6, ODBC_INI_ACUODBC_DRIVER,
                ODBC_INI_DSN_KEY_NAME + DSName + ODBC_INI_FILE_PREFIX_NAME + ODBC_INI_DBQ_NAME + "\0");

        }

        ///
        /// Remove a System DSN
        ///
        /// Name of DSN
        /// True if successfull
        public  bool RemoveSystemDSN(string DSName)
        {

            return RemoveSystemDSN(ODBC_INI_ACUODBC_DRIVER,DSName);

        }
        #endregion

        #region Win32 API Import
        ///
        /// Win32 API Import
        ///
        [DllImport("ODBCCP32.dll")]
        private static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);
        #endregion
    
    }



Thanks
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Creating System Dsn's dynamically

Looks good, does it work for you? Hopefully it works correctly with different versions of ODBC.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.