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

supreme clothing said: I simply wished to say thanks again. I am not sure what I might have carried out in the absence of those creative concepts shown by you relating to such topic. It actually was a real hard case in my opinion, but finding out the expert form you processed that took me to cry over delight. I am just happy for your help and in addition hope that you really know what an amazing job you have been undertaking teaching most people thru a site. I am certain you have never got to know all of us. supreme clothing

off white clothing said: I happen to be writing to make you know what a really good experience my daughter developed visiting your site. She figured out a wide variety of issues, which include what it's like to have a great helping character to have many more completely have an understanding of specified problematic things. You truly did more than our expected results. Thank you for rendering these informative, dependable, educational and even cool thoughts on this topic to Gloria. off white clothing

kyrie 5 said: I want to voice my affection for your kind-heartedness giving support to folks that absolutely need guidance on that niche. Your personal dedication to getting the message all around had been unbelievably significant and have truly empowered regular people just like me to reach their goals. Your new useful suggestions signifies this much a person like me and extremely more to my peers. Regards; from everyone of us. kyrie 5

calvin klein underwear said: I truly wanted to write down a small message to be able to express gratitude to you for the stunning tips and hints you are sharing here. My time intensive internet research has finally been rewarded with reputable facts and strategies to exchange with my family members. I 'd assume that we site visitors actually are undeniably blessed to dwell in a good community with so many outstanding individuals with very beneficial hints. I feel pretty lucky to have discovered your web pages and look forward to many more pleasurable moments reading here. Thanks a lot once more for a lot of things. calvin klein underwear

golden goose said: Thanks so much for providing individuals with an extremely breathtaking possiblity to read from this web site. It really is very nice and also packed with a great time for me and my office fellow workers to search your web site a minimum of thrice a week to read the newest issues you have. And of course, I'm so at all times pleased with all the extraordinary strategies served by you. Certain 3 tips on this page are ultimately the most efficient we have had. golden goose

stone island said: Needed to post you one very small note to help give many thanks again for all the extraordinary things you have shared above. This has been simply pretty open-handed of you in giving unreservedly what a lot of folks would have sold as an e book to help with making some bucks on their own, mostly given that you might well have tried it if you ever considered necessary. The thoughts likewise served to become great way to be aware that most people have the identical passion really like my personal own to know lots more on the topic of this problem. I think there are lots of more fun occasions in the future for those who looked at your blog. stone island

lebron shoes said: I together with my friends came reviewing the best procedures on your web page and so at once developed a horrible suspicion I never expressed respect to the site owner for them. Those guys are already as a result warmed to learn them and now have actually been using those things. Thank you for really being quite kind and for picking varieties of magnificent information most people are really desperate to know about. Our sincere apologies for not expressing appreciation to sooner. lebron shoes

chrome hearts online store said: Thanks so much for providing individuals with a very nice possiblity to discover important secrets from this site. It's always so lovely and also full of amusement for me personally and my office acquaintances to visit your site particularly three times in one week to read the latest issues you have. And definitely, I am certainly motivated concerning the incredible creative ideas you serve. Selected 1 ideas in this posting are surely the best we have had. chrome hearts online store

bape hoodie said: I truly wanted to write down a word to be able to say thanks to you for these lovely solutions you are giving at this site. My rather long internet search has at the end of the day been compensated with really good information to share with my great friends. I 'd claim that we site visitors actually are really endowed to live in a wonderful network with very many lovely professionals with insightful secrets. I feel quite fortunate to have seen your site and look forward to some more thrilling minutes reading here. Thank you once again for a lot of things. bape hoodie

kd shoes said: My wife and i felt really delighted when Peter could carry out his investigations because of the ideas he gained from your own web page. It's not at all simplistic to just happen to be giving away guides that other folks might have been selling. Therefore we discover we've got the website owner to thank because of that. The illustrations you have made, the simple blog navigation, the friendships you will assist to foster - it's got everything astonishing, and it's helping our son and our family understand that situation is thrilling, which is highly vital. Thanks for the whole thing! kd shoes

supreme clothing said: I happen to be writing to make you be aware of what a helpful experience my friend's daughter enjoyed viewing your web page. She figured out so many things, not to mention what it's like to have an incredible giving mood to get many people with ease learn a number of tricky matters. You undoubtedly did more than our expectations. Thank you for coming up with those interesting, healthy, educational not to mention easy tips on your topic to Sandra. supreme clothing

golden goose sneakers said: My spouse and i felt very peaceful Peter could conclude his basic research using the precious recommendations he received out of your web page. It's not at all simplistic to simply happen to be handing out tips which some people have been selling. And now we figure out we need the website owner to thank for this. Those illustrations you have made, the straightforward blog menu, the friendships your site aid to promote - it is mostly wonderful, and it's really helping our son in addition to the family recognize that this matter is pleasurable, and that is pretty vital. Thank you for the whole lot! golden goose sneakers

hermes said: I simply wanted to type a simple comment to be able to thank you for all the unique facts you are writing on this site. My prolonged internet research has finally been rewarded with good quality know-how to write about with my good friends. I 'd express that many of us readers actually are rather blessed to live in a really good place with very many perfect people with insightful plans. I feel pretty grateful to have used your weblog and look forward to tons of more cool moments reading here. Thanks a lot once again for all the details. hermes

golden goose sneakers said: I'm also writing to make you be aware of of the beneficial encounter my friend's daughter gained viewing your blog. She noticed a lot of issues, most notably what it's like to have an excellent helping spirit to make a number of people completely fully understand certain extremely tough subject areas. You actually exceeded our desires. I appreciate you for showing those interesting, safe, informative and even fun tips on the topic to Emily. golden goose sneakers

hermes online said: I would like to convey my appreciation for your kind-heartedness supporting individuals who need guidance on this one concept. Your personal dedication to passing the message around had been especially productive and has constantly enabled associates just like me to achieve their targets. Your own informative suggestions indicates a great deal a person like me and extremely more to my peers. Many thanks; from each one of us. hermes online

golden goose said: I have to show thanks to the writer just for rescuing me from such a difficulty. Right after searching throughout the world wide web and seeing strategies that were not productive, I assumed my entire life was done. Living without the presence of approaches to the issues you have solved all through your entire guide is a serious case, and those that would have negatively damaged my entire career if I hadn't noticed your blog. Your own personal mastery and kindness in maneuvering all things was very useful. I'm not sure what I would've done if I hadn't come upon such a subject like this. I can also now look ahead to my future. Thank you so much for this high quality and results-oriented guide. I won't hesitate to recommend your web site to any individual who requires direction on this area. golden goose

supreme clothing said: I needed to write you one bit of remark so as to give many thanks over again on the beautiful pointers you've featured on this site. It is so surprisingly generous of you to deliver without restraint what exactly most of us would've distributed for an ebook to help make some dough for themselves, primarily given that you could possibly have done it if you ever wanted. Those principles in addition served to become a great way to know that other people have similar interest really like my personal own to grasp way more with respect to this problem. I am sure there are numerous more enjoyable opportunities up front for folks who examine your blog post. supreme clothing

yeezy shoes said: I needed to compose you a very small word to finally say thanks again for those striking advice you have contributed on this website. This is simply strangely generous of people like you in giving without restraint precisely what numerous people might have distributed for an e-book to help with making some money on their own, primarily considering the fact that you might well have done it in the event you desired. The inspiring ideas likewise worked as the fantastic way to comprehend other people have the same interest like my very own to grasp many more with regard to this problem. I know there are thousands of more fun situations up front for folks who read through your site. yeezy shoes

yeezy shoes said: Needed to post you a tiny observation to say thank you the moment again for all the splendid basics you've provided in this article. It's wonderfully generous of people like you to give without restraint what exactly most people could possibly have advertised as an e book to help make some cash for their own end, chiefly given that you could have tried it if you considered necessary. The secrets also acted as the good way to comprehend other people online have similar fervor the same as my personal own to figure out somewhat more concerning this problem. I am sure there are numerous more pleasant instances up front for individuals who find out your site. yeezy shoes

kyrie 6 shoes said: Thank you a lot for providing individuals with remarkably superb chance to read from here. It is often very great and as well , stuffed with a great time for me and my office mates to search your web site nearly 3 times in 7 days to study the new stuff you will have. And indeed, I am also always fascinated with the breathtaking information you serve. Selected 4 points in this article are clearly the very best we have had. kyrie 6 shoes

golden goose sneakers said: I wanted to jot down a quick note to be able to thank you for some of the precious information you are placing here. My time-consuming internet research has finally been paid with pleasant facts to go over with my colleagues. I 'd state that that we readers are truly endowed to exist in a fabulous site with very many awesome professionals with interesting hints. I feel quite blessed to have used the web pages and look forward to tons of more cool moments reading here. Thanks again for everything. golden goose sneakers

longchamp bags said: My husband and i ended up being quite excited Albert could carry out his analysis through the entire ideas he grabbed in your weblog. It is now and again perplexing to simply continually be giving away information and facts which usually the others might have been making money from. We take into account we now have the website owner to be grateful to for this. The type of explanations you've made, the simple web site navigation, the relationships you can help to promote - it's most unbelievable, and it's really making our son and our family understand that idea is excellent, which is certainly unbelievably pressing. Thank you for the whole lot! longchamp bags

hermes handbags said: I want to express my affection for your generosity giving support to individuals who really want help on this particular situation. Your special dedication to getting the message all-around became incredibly beneficial and has constantly made others like me to get to their ambitions. Your helpful information signifies this much a person like me and especially to my office workers. Thank you; from all of us. hermes handbags

balenciaga said: I'm commenting to let you be aware of of the superb encounter my friend's girl encountered going through your site. She came to understand plenty of issues, with the inclusion of what it's like to possess an ideal teaching nature to have many more clearly thoroughly grasp some impossible topics. You actually exceeded my expected results. Thank you for delivering those productive, dependable, edifying and even fun thoughts on that topic to Sandra. balenciaga

balenciaga sneakers said: I must express thanks to the writer for bailing me out of this type of condition. Right after surfing around through the world-wide-web and getting techniques which are not helpful, I thought my life was done. Existing minus the answers to the difficulties you've resolved all through the write-up is a serious case, and those which might have negatively affected my entire career if I hadn't discovered your web blog. Your primary capability and kindness in handling a lot of stuff was crucial. I don't know what I would've done if I had not encountered such a step like this. I'm able to now look ahead to my future. Thanks so much for your high quality and result oriented help. I won't be reluctant to endorse your blog to any individual who would like assistance about this subject. balenciaga sneakers

jordan 11 said: I actually wanted to develop a quick note in order to thank you for all the fantastic recommendations you are giving out on this site. My time intensive internet lookup has finally been honored with good quality knowledge to write about with my colleagues. I would assume that most of us website visitors actually are undeniably fortunate to live in a wonderful network with so many special individuals with helpful ideas. I feel somewhat grateful to have come across your webpage and look forward to so many more entertaining moments reading here. Thank you again for a lot of things. jordan 11

supreme shirt said: I'm also writing to make you know what a notable discovery my friend's girl gained visiting your web page. She discovered a wide variety of things, which include what it's like to possess a wonderful helping character to get others effortlessly fully grasp selected extremely tough things. You truly did more than visitors' expected results. Thanks for coming up with such necessary, trustworthy, educational and as well as cool guidance on that topic to Emily. supreme shirt

kd 10 said: I precisely had to thank you so much once more. I am not sure what I would have gone through without the type of techniques revealed by you relating to that problem. It was a very daunting difficulty in my view, nevertheless being able to view this expert avenue you processed that forced me to weep over happiness. Extremely thankful for your help and trust you comprehend what a powerful job you have been providing instructing many others through your web blog. Most likely you have never got to know all of us. kd 10

bape said: I in addition to my pals ended up examining the excellent guidelines found on your web page then the sudden developed a terrible suspicion I had not expressed respect to the site owner for those strategies. These young men happened to be so excited to see them and have in reality been tapping into them. Thank you for actually being well accommodating as well as for deciding on certain good subjects millions of individuals are really eager to understand about. My very own honest apologies for not expressing gratitude to sooner. bape

kyrie 4 said: I simply needed to say thanks yet again. I do not know what I would have sorted out in the absence of the type of advice revealed by you on such a subject matter. It was actually the frustrating condition in my circumstances, nevertheless spending time with the very expert tactic you managed that made me to leap with joy. Extremely happy for the guidance and thus pray you really know what an amazing job you're undertaking instructing many others via your web blog. Probably you've never encountered any of us. kyrie 4

yeezy 500 said: I in addition to my guys came following the best hints on your website then before long developed a horrible suspicion I never expressed respect to the web blog owner for those strategies. Those people ended up consequently very interested to read them and now have really been tapping into these things. Appreciate your truly being so kind and then for figuring out variety of awesome topics millions of individuals are really desirous to understand about. My very own sincere apologies for not expressing appreciation to sooner. yeezy 500
Back