Misys Tiger via ODBC via C#

By: Johnathon Wright on: November 07, 2012

The code below constitutes my various attempts to connect to Misys Tiger via ODBC using C#. Feel free to skip to the word "WiN" to skip the failures.

h3. Warning

This code below is not inteded to be production-worthy. I just wanted to see data. Excuse the slop.

h2. Questions and Answers

Q. How do I create a connection to the various ODBC connections available?

A. see "#WIN" below.

Q. How can I retrieve data from this connection? Some examples found online fail for this connection.

A. see "#WIN" below.

Q. How should I handle the numerous errors that can come from the ODBC connection?

Q. My client's installation of Tiger has many companies. Each company has its own ODBC connection. ( Company0001, Company0002, Company_0012, ... ). Each time I esablish a connection to a new company, a dialog asks the user to authenticate. Can I make it so that the user only has to sign in once?

A. The ODBC connection respected the username and password when embedded in the connection string:


DSN=Company_0001;Uid=username;Pwd=password

h2. ADO.NET with DataAdapter - #FAIL


    public class CompanyListFactory
    {
        public static DataSet Get()
        {
            OdbcConnection _connection = new OdbcConnection("DSN=Company_Shared");
            _connection.Open();

            DataSet ds = new DataSet();
            OdbcDataAdapter query = new OdbcDataAdapter( "select * from root.COMPANY_APC", _connection );
            query.Fill( ds );

            _connection.Close();                
            return( ds );
        }
    }

I get this error:


System.Data.Odbc.OdbcException: ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function at System.Data.Odbc.OdbcDataReader.NextResult(Boolean disposing, Boolean allresults) at System.Data.Odbc.OdbcDataReader.NextResult()

at System.Data.ProviderBase.DataReaderContainer.NextResult()

I suspect this is a valid error because when I change the table name to COMPANY_XYZ I get:


[Transoft][TSODBC][usqlsd]Unknown Table 'root.COMPANY_XYZ'

After reading about ODBC compliance, I suspect the OdbdDataAdapter is issuing commands that include pagination, etc., which perhaps the Transoft driver may not respect.

h2. ODBC Compliance

In a previous, aborted attempt to connect, I got this error:


System.Data.Odbc.OdbcException: ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr

which led me to the understanding that there are levels of ODBC conformance. Microsoft is assuming they are using the "we are awesome" level of ODBC... but it seems more likely that they are using the "bare minimum necessary" level of ODBC. At this point what I want is to go back to the ADODB model... "I send you a select, you send me back raw data" model, rather than the "Let's have handlers and all kinds of levels of abstraction" model.

Update

The "Transoft U/SQL Help Document":ftp://ftp2.transoft.com/pub/CDextras/USQL/documentation/TransoftUSQLHelp.pdf provides a relevant table starting around page 70. This document even provides C# example code on page 113 !

h2. Using COM component, just like the example: #FAIL

    public void UseComComponent()
    {
        ADODB.Connection conn = new ADODB.Connection();
        conn.Open("DSN=Company_Shared");
         String sql = "select 'A' from root.COMPANY_APC"

        ADODB.Recordset rs = new ADODB.Recordset();
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
        rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly);
        object zz = rs.GetRows();

        while( rs.EOF != true )
        {
            Console.Write(rs.DataMember);
            rs.MoveNext();          
        }


        conn.Close();
    }

An example I have been referencing was written in VB, so it used COM components intead of the CLR. I added a reference to the COM component "Microsoft ActiveX Data Objects 2.7 Library" (which the author of that post also used). That gave me ADODB. I was able to get pretty far, but couldn't quite figure out how to get the data out of the reader:

h2. using ADO.NET's DataReader -- #WIN !!!


/* some of these aren't necessary, don't remember which. */ using System using ADODB; using System.Data; using System.Drawing; using System.Windows.Forms; using System.Data; using System.Data.Odbc;

    public void UseAdo()
    {

        OdbcConnection _connection = new OdbcConnection("DSN=Company_Shared");
        _connection.Open();
        OdbcCommand cmd = new OdbcCommand("select 'A' from root.COMPANY_APC", _connection);
        OdbcDataReader reader = cmd.ExecuteReader();
        Console.WriteLine("JW Was Here");
        if(reader.HasRows)
        {
            while(reader.Read())
            {
                string s = reader.GetString(0);
            }
        }

        _connection.Close();    
    }

Even though I had already failed to use ADO.NET, I tried again. This time I found an article that went into great detail about various ways to retrieve data.

h2. Various Errors Experienced, some solved.

h3. Another Computer, Another Error

I built a spike and took it to another computer, running XP instead of Windows 7. I got this error:


ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the ve rsion of ODBC behavior that the application requested (see SQLSetEnvAttr). at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode ) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, Odb cConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOption s options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection own

ingObject)

My initial reading of this is that it tried to open a connection and one of the commands issued failed(?). I opened Misys Query on this machine and re-ran it. Successful connection. I closed Misys Query and the program continued to work. My suspicion is that Misys Query is creating ODBC connections that aren't there when the computer first starts. Needs further research.

h3. Can not use DataTable.Load( DataReader_instance )

Interesting sidenote: When I put a breakpoint at DataTable.load( reader ), and then I step past it, I get nothing instead of this error.


ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this func tion at System.Data.Odbc.OdbcDataReader.NextResult(Boolean disposing, Boolean allresults) at System.Data.Odbc.OdbcDataReader.NextResult() at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, Fill

ErrorEventHandler errorHandler)

h2. References

"AllScripts Tiger to MS SQL using GoDaddy Hosting":http://pcnorb.blogspot.com/2010/02/allscripts-tiger-to-ms-sql-to-godaddy.html "ADO.NET code samples":http://msdn.microsoft.com/en-us/library/dw70f090.aspx "ADO.NET for ADO Programmers":http://msdn.microsoft.com/en-us/library/ms973217.aspx "Retrieving Data Using the DataReader":http://msdn.microsoft.com/en-us/library/aa720705.aspx "DSN Connection String Samples":http://www.connectionstrings.com/dsn "Transoft U/SQL Help Document":ftp://ftp2.transoft.com/pub/CDextras/USQL/documentation/TransoftUSQLHelp.pdf





Comments:

supreme said: I just wanted to write down a remark to be able to express gratitude to you for the magnificent techniques you are giving at this website. My prolonged internet search has now been compensated with incredibly good insight to write about with my family. I would tell you that we website visitors actually are very much endowed to be in a useful network with very many wonderful professionals with useful plans. I feel truly blessed to have used your webpage and look forward to really more thrilling moments reading here. Thanks a lot again for all the details. supreme [url=http://www.supremesonlinestore.com]supreme[/url]

authentic jordans said: I want to express my thanks to you for rescuing me from this type of setting. Because of surfing throughout the world wide web and obtaining views which are not beneficial, I was thinking my life was gone. Living minus the approaches to the difficulties you have solved through your report is a serious case, as well as the ones that could have badly damaged my entire career if I hadn't encountered your web blog. Your personal training and kindness in dealing with a lot of things was invaluable. I'm not sure what I would've done if I hadn't encountered such a subject like this. I can at this moment look forward to my future. Thanks so much for the impressive and sensible help. I will not think twice to endorse your blog post to anyone who would like care about this matter. authentic jordans

ferragamo sale said: I not to mention my friends ended up reading the good tricks found on your website then at once I got an awful feeling I never thanked the blog owner for those tips. Most of the men appeared to be very interested to read all of them and have in truth been taking pleasure in these things. Many thanks for really being very thoughtful and for picking variety of really good themes most people are really wanting to discover. Our honest regret for not expressing gratitude to you sooner. ferragamo sale

supreme clothing said: I have to voice my passion for your kindness giving support to those individuals that require help with this particular matter. Your very own dedication to getting the solution up and down became extraordinarily effective and has always allowed somebody much like me to attain their aims. The insightful facts means much to me and somewhat more to my office colleagues. Best wishes; from everyone of us. supreme clothing

lebron shoes said: I definitely wanted to write down a simple comment to be able to say thanks to you for some of the precious points you are giving out at this website. My considerable internet lookup has at the end been paid with good quality suggestions to exchange with my family. I would state that that many of us readers are very blessed to be in a wonderful community with very many awesome individuals with helpful secrets. I feel very grateful to have seen the web site and look forward to some more entertaining moments reading here. Thanks once again for a lot of things. lebron shoes

a bathing ape said: I wanted to send you this little bit of word to thank you so much over again relating to the pleasant knowledge you've contributed above. It is quite shockingly generous with you to present without restraint just what most of us might have distributed for an e-book to earn some cash for their own end, even more so since you might well have done it if you decided. Those pointers as well served as a good way to fully grasp other people online have a similar passion really like my own to understand a great deal more when considering this problem. I'm sure there are some more pleasant opportunities ahead for folks who read through your site. a bathing ape

kobe 11 said: I have to show appreciation to you just for bailing me out of this type of matter. Because of scouting throughout the the web and coming across tricks which are not pleasant, I assumed my life was gone. Being alive without the presence of strategies to the difficulties you've sorted out by way of your main short article is a serious case, and the kind which may have in a wrong way damaged my career if I hadn't encountered the blog. Your own natural talent and kindness in handling the whole thing was very useful. I am not sure what I would have done if I had not come across such a solution like this. I can at this moment look forward to my future. Thanks for your time so much for the skilled and results-oriented help. I will not be reluctant to suggest your site to anyone who needs to have support on this subject matter. kobe 11
Back