More than one C/OBDC connection open

Dear all, I’m connecting a Navision Financials 2.60F database to a web site through C/ODBC 2.60F. Everything works fine, except when I try to open another connection besides the one I have already opened. I use this to fill through a kind of “look-up” window a list that contains data from another company in the DB. This is really needed, but I can’t seem to be able to this, because the C/ODBC always crashes. I have defined a C/ODBC connection (system DNS) for each company in the DB. Any thoughts besides “No, there’s no way to do that!” would be appreciatted [;)] Thanks. The ASP code and the error produced are below (the ASP crashes in line “conn2.open”). <% Set conn1 = server.createobject(“adodb.connection”) conn1.Open “DSN=Navision1;uid=super;pwd=super” conn1.close set conn1 = nothing Set conn2 = server.createobject(“adodb.connection”) conn2.Open “DSN=Navision2;uid=super;pwd=super” conn2.close set conn2 = nothing %> Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D) [Navision Software a/s][Navision Financials ODBC Driver]Data source rejected establishment of connection

I am exerpiencing a similar problem. Trying to use .NET connection string with an OleDBConnection with the 2.60 driver. It states the the C/ODBC is not registered - and you cannot register the codbc.dll with regsvr32.exe. I would appreciate any ideas on the subject.

Hi. I’m not using .net. I’m using “plain” asp in my project. I was told that I couldn’t open several connections. Sometimes (rarelly) it changes the connection (opens another company) and retrieves data from that company, but it crashes later when trying to go back to the original connection (main company). [xx(] This is obviously a bug, and I’d like to hear from people who succeeded in doing this. Tell me it is possible! [:(] Thanks P.S. Is it too hard to push Navision to make a fix to c/odbc?

Hello, I have not tried, but what about using two different users to log on e.g. super1 for conn1 and super2 for conn2?

The ODBC manager reuses ODBC connections, even if ODBC connection pooling is not enabled. It assumes that everything is thread-safe, C/ODBC is not. Actually C/ODBC is “process-unsafe” when it comes to 2 connections, because of C/ODBC DLL shared data segment. This means if you want to connect to multiple companies, or use multiple connections(each will use a database session), then you will have to use 2 different processes(or 2 different applications). In Windows, 1 application can have multiple processes, and each process can have multiple threads. Only threads have code to execute. When Windows loads a DLL with a “shared” data segment, it will make it shared within the same process only, and no other. All threads in that process will see that same “shared” data segment. If another process loads that DLL, that process will have it’s own different copy of the C/ODBC DLL “shared” data segment. Because you are using 1 program to communicate with C/ODBC, you are using 1 process by default. This means it’s impossible to make 2 connections with 1 process. You have to unshare the C/ODBC data segment. How do you unshare the C/ODBC data segment? You have 2 solutions: 1 - Use the function CreateProcess, to run another process. 2 - Make another application all together and communicate with it. This would mean that it will be using a different process. The quickest way is to use “Easysoft ODBC-ODBC Bridge” at http://www.easysoft.com this program uses CreateProcess as mentioned above to solve this issue. See this link about Navision related FAQ:


http://www.easysoft.com/products/9999/faq_answer.phtml?ID=93&product=2002

Another way is to implement this is by making an ActiveX EXE as a DCOM server, perhaps in VB. You should not use “thread per object” option in VB, because you don’t want multi-thread, but multi-process(application). Use “Thread pool”, 1 thread. Configure DCOM so it runs one server per each client connection, or make 2 different DCOM servers, COMP1.EXE & COMP2.EXE. You can then connect thousands of web users to each one of these servers and they all would use no more that 2 database connections. One per company. When you start using DCOM, some unusual issues would appear. This is related to ADO marshaling and translation. Specifically when it comes to date fields translation when the value in the database is blank date. Versions 2.60E and above translate marshalled blank dates to 1/1/1753(the zero date). Versions below 2.60E cannot use DCOM or out-of-process ADO Marshalling when SELECT returns a recordset with a date field that is blank(Your query will work if you add WHERE mydate > {d ‘01-01-0001’} but will return less records). Versions below 2.60E have to go with MS SQL option if DCOM is needed, or use “Easysoft ODBC-ODBC Bridge”. Also, for the reason above, MS RDS(Remote Data Service) will not work. It works with MS SQL server fine, but not with C/ODBC and Navision native database. Another solution altogether, is to use the Microsoft SQL option, you will be using Microsoft’s own ODBC driver, which is not as buggy. It uses 0 database sessions I think. However I don’t have experience with this. Useful MS KB articles: HOWTO: Create a DCOM Client/Server Application http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161837 HOWTO: Create a DCOM Client/Server with Events by Using Visual Basic http://support.microsoft.com/default.aspx?scid=kb;en-us;Q267836

I can highly recommend the Easysoft solution. We have seen it supporting up to 20 concurrent connections which serve information to our webserver. Email me directly if you have further questions about Easysoft’s bridge.

Thank you all for your replies. I’d like to know more about Shaman’s first approach: the use of function createprocess. It would be very good if someone could post some code sample. Thanks

Hi, When you make an ActiveX EXE server and connect to it from VB/ASP, VB/ASP would talk to COM/DCOM, which in turn would use CreateProcess internally to run the ActiveX EXE server. See my comments above. I recommend that you study the Coffee2 sample that comes with Visual Studio 6. See below: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vb98/html/vbsmpcoffeecoffeecoffee.asp The sample itself can be downloaded from here: http://download.microsoft.com/download/vstudio60pro/Utility/6.0/W98NT42KMeXP/EN-US/vs6samples.exe Also, read the documentation here, which show you how the sample works step by step: Creating an ActiveX EXE Component: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vb98/html/vbsmpcoffeecoffeecoffee.asp The “Coffee” sample is done in 2 ways: “Coffee” and “Coffee2”. You should use “Coffee2” sample because it uses call-back. The first “Coffee” sample sends responses to all clients, you can’t make it respond to only one or two clients. It could take you 2-3 weeks to get everything to work… For your ASP project you would make 2 ActiveX EXE servers, one for each company. You also have to make 2 ActiveX DLL or OCX clients(Windows treats OCX as a DLL). The ActiveX EXE servers will have the following objects: DCOMcomp1.exe: oServer1, oConnector1 DCOMcomp2.exe: oServer2, oConnector2 The ActiveX DLL/OCX client will have the following objects: DCOMclient1.dll: oClient1 DCOMclient2.dll: oClient2 oClient talks only to oConnector, and multiple oConnectors talk to the one and only oServer. oServer is the only one that uses ODBC calls. oClient should never talk to oServer directly. This setup will take advantage of DCOM ability to serialize requests so only one client can access the ODBC driver at a time. There is one single thread that will be talking to ODBC driver, and that is the one used by oServer, preventing lock ups, even when multiple ASP users access ODBC simultaneously. The reason that you should use 2 EXE’s is that you have to have 2 servers. These are basically the same source code, with differenet object names. I do recommend Navision Application Server or Microsoft’s SQL Server over the above method for performance reasons, especially in high volume applications. If you use Microsoft’s SQL Server, then you will be using Microsoft’s SQL ODBC driver, not the buggy C/ODBC driver, and also you don’t have to make ActiveX Client/Server. You just use your ASP code in this case! Useful MS KB articles: INFO: Understanding ADO Marshaling http://support.microsoft.com/default.aspx?scid=kb;en-us;Q248287 INFO: Passing ADO Recordsets in Visual Basic procedures: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q193871 PRB: DCOMCNFG Reports Multiple Copies of DCOM Server: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q180525 INFO: Understanding CreateProcess and Command-line Arguments: http://support.microsoft.com/default.aspx?scid=kb;en-us;175986

Interesting problem, ofcourse the buggy odbc driver is the thing for which you need a workaround (where is the cside oledb driver , maybe msb will publish it RSN… waiting already 2 years) A lot of good workarounds are mentioned, i’d like to add 2 new ideas, affraid i did not test either of them with c/odbc: 1 You could create a new website for your pop-ups and run it in a separate process space (this is a setting on the IIS). Theoretically this would acchieve a separated proces in which odbc is loaded. You could call this site within the same session for security and use parameters in your call to the asp page. 2. Switch connection, you are already displaying data, so maybe you do not need an active “main” connection when displaying the pop-up.

Hi all. Oerly, Interesting approaches! However, I think I’m already doing your idea nr. 2. Check out this code: <% Set conn1 = server.createobject(“adodb.connection”) conn1.Open “DSN=Navision1;uid=super;pwd=super” conn1.close set conn1 = nothing Set conn2 = server.createobject(“adodb.connection”) conn2.Open “DSN=Navision2;uid=super;pwd=super” conn2.close set conn2 = nothing %> If I run this, IE will return an error page saying: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D) [Navision Software a/s][Navision Financials ODBC Driver]Data source rejected establishment of connection /teste2.asp, line 7 As you can see, line 7 is when I try to open the 2nd connection. And I close (do I?) the first one! Is it possible that the first connection isn’t properly closed? Or, like some previous posts say, it just isn’t possible that way?.. As for idea nr 1, I selected Default Web Site, properties, and in the Home directory tab, changed the value in the Application protection to High. But the error remains the same… Any thoughts? Thanks Thanks

Hi, This code in VBA (excel macro) works fine: Sub main() Set con1 = CreateObject(“ADODB.Connection”) con1.Open “DSN=Nav1;UID=ICM;PWD=” con1.Close Set con1 = Nothing Set con2 = CreateObject(“ADODB.Connection”) con2.Open “DSN=Nav2;UID=ICM;PWD=” con2.Close Set con2 = Nothing End Sub Aren’t you just running out of users on your Navision server ? Maybe clearing 1st connection to Navision takes some time…

Hi. Thanks for the reply. I modified the code so that between the closing down of the first connection and the opening of the second one the asp page would wait 10 seconds - I did this with a timer. I think this amount of time is more than enough. Even though, the error remains.[xx(] My code and Oerli’s are identical. Perhaps VBA in Excel manages things in a different way than in ASP. But this is just a guess… Anyway, thank you all for your replies.

Hi, This thread above encounters exactly the problems I come accross using Cognos PowerPLay Transformer. In this standard application we use multiple ODBC connections to get data out of multiple Navision (2.6) companies. So all the solutions mentioned in this thread are based on VBcoding or ASP. Does anyone know solutions for solving this problem for standard applications?? Any contribution is welcome Jack