Hi all, Im trying to create a custom web application that will communicate via MSMQ using XML based on the commerce portal components. Is it neccessary for me to purchase the commerce portal granules even if I do not intend to use commerce portal? I intend to use it simply for receiving an XML message then sending back an XML reply. On a similar note, Im sure that Im not the only one that has tried this sort of thing before. Does anyone have any tips or documents that deals with creating custom applications using this method? I have all the commerce portal docs and course materials, but this focuses entirely on standard commerce portal. Any tips would be greatly appreciated. Im going to be using Coldfusion MX to communicate with MSMQ. Thanks in advance. Kind Regards, Edward Peggs
Hi Edward I am in the same proces, I have build everything outside the Commerce portal components, I use MS Visual Studio.Net and the web app is build with ASP.NET, it sends an XML document, via MSMQ and waits for a response XML document. On the Navision side, I have a codeunit that listens on the MSMQ (see the examples for the communication component), retrieves data from Navision and creates an XML document from it and sends it back as a reply. It works. It takes a bit more to do the integration than using C/ODBC etc. but I can now execute Navision logic from the web app, which greatly reduces the complexity. I still have some technical issues, with Navision giving errors on passing DOMDocuments to the Stream object of the message, but I hope to get these solved soon. Performance wise it looks good, once the Navision logic is cached.
Hi Edward, If you plan on developing a website that can be accessed from the internet and communicates with Navision you should consider setting up a second database (SQL or other) that holds some of the data that is used by the website, such as item records, customer records etc. The reason for having this second database is that you certainly do not want to handle all communication with Navision through the MSMQ unless the website is very small and will not have many concurrent users. Even though you could read some data from Navision directly using ADO or ODBC by executing SQL statements directly towards the Navision database from the website, it is not a good idea for an external website since by doing that you expose your Navision database to the web. If the website is accessed from the internet it is a potential security whole. If you do not want to use Commerce Portal you should however consider using the same architecture with having a second SQL database that is synchronized from Navision through MSMQ. Complex data queries and “private” information (such as invoice details etc.) are processed by the website communicating directly with Navision through MSMQ and other data queries are executed directly towards the second SQL database. By using this architecture your performance will be better than if only MSMQ was used and security a lot tighter then if data queries where executed directly towards the Navision database. Take a look at the MSMQ Triggers which should make the development of the synchronization components a bit easier. If you don’t intend to use the Commerce Portal objects in Navision or other components provided by CP you do not need to by it. You might have to license one or more NAS:es though, depending on your needs. Hope this helps /Bruno
Thankyou for your suggestions. Its certainly given me food for thought! I know now that what Im trying to achieve is possible and has been done before. We may or may not go down the route of synchronisation, or we may find it sufficient to wait for XML responses, its difficult to say which is best at this stage. For your info, Im hoping to be able to create a framework for outputting formatted information from a custom Navision module. Things such as summarised customer satisfaction results, scheduling diaries etc., that would be of interest to our business partners. Regards, Edward.
Edward: We have a similar goal. We intend to use a Java Webserver at the front end and push the XML to the NAS which would activate the custom codeunits in Native DB. Any ideas, advice, clues, pitfalls, samples, etc. would be wonderful. I am in the infancy of this project and working with the NAS. We are still gathering requirements at this point. Thanks in advance. Owen
Hi Owen, Im only just starting work on the above project now, but I have created working prototypes from which I have found out a few things. 1) Design a flexible XML schema for which to send and receive messages beforehand. That way, you can write a single handling unit within Navision that will process the request and route it to the right destination. 2) The sort of application Im using MSMQ for requires that a response be sent back ie. it uses OutMsg.SendWaitForReply(x) on the outgoing message. If so, make sure you use 2 computers, or at least run 2 instances of navision on 1 computer. This is because SendWaitForReply(x) will not allow Navision to do anything until it receives a reply or times out, therefore the receiving codeunit isnt able to process the incoming message, doh! Took me an afternoon to figure that one out! 3) During testing, theres no need to use NAS. Its all the same really, and within Attain is easier to debug and make changes (though it is a pain having to close & reopen company every time you make a change). Thats all I can think of at the moment, if you have any other questions feel free to message me. Ive found that the speed is quick enough to not require a replicated DB, but like Bruno (above msg) says, if you expect really high volume stuff, you may want to look at using XML to synchronise the databases. Regards, Edward.
Edward - we are just starting to consider such a project, and will require real-time interaction with a .NET application. What did you end up using as your method of transferring information back and forth. We have looked recently at a Digital Vantage Point Transaction Manager which handles XML translations, but it’s cost is seeming prohibitive. Any comments on your experience over the last 8 months would be appreciated.
We have built a live solution which has good performance, it runs ASP.Net. The architecture is: 1) ASP.Net reads info from a SQL database, if write transactions is needed it sends xml to NAS, it runs Navision logic and uses ado to write immidiately to the SQL Server, it then returns an ok flag to the asp.net app, which rereads the sql data (Navision logic could have updated related records and fields) 2) To detect changes in Navision, a single insance codeunit is started with nas, which scans a transaction log table, which logs all changes (like change log does), it then reads the navision record and writes it to SQL database. Entries to the transaction log is done for relevant tables by handling the insert/modify/delete/rename trigers where appropriate (or change log could be used)
Hi, the system suggested by mjanum sounds like it would be more scalable than what we did. However, ours was always going to be a fairly small scale solution, and so far, we have had 20 or so concurrent users on it so far without noticeable slowdown. The number of users is not excessive but the results are pleasing so far anyways. The company this was for was already running a Macromedia Coldfusion based content management system for their website and intranet so the solution was in part written using Coldfusion CFML and plugged in. The way it works: The definition of input forms, datasets and reports are setup in Navision. From these definitions, Navision will periodically generate a parameters xml file and upload it to the webserver. The parameters file contains data which coldfusion uses to generate input and filter forms. It also contains instructions on how to format the msmq message so that Navision can recognise it when it returns over the wire. To give an idea what I mean, I have copied a small chunk from the actual params file. report The notifications by district show the number of notifications of Insurance department jobs received for specific districts. This can be filtered by customer and also by date period. Select Customer No CustomerNo ffCustomerNo 200 Select Customer Group CustomerGroup ffCustomerGroup 200 input Notification Date ffNotificationDate 200 The example shows coldfusion how to create a page that: i) is a report (including help instructions) ii) displays input for 3 filters before report is displayed (and their width, type, validation and positioning) So a web user sees a coldfusion generated form and fills in the data. This is sent to Navision which parameterises the data into a temporary table (parameter/value pairs). This request is routed to the correct destination based on the report name and business logic runs. Response is sent back and then xsl stylesheets parse the returned xml displaying it for the user. Things to note: 1) MSMQ has a 4Mb limit per message. Any more and a system has to be devised to fragment the message and sent it in multiple parts. We are aware of this but havent reached this limitation. When running a data request or report, only part of the result set is returned. For example, if you wanted to display an item list, it would be displayed as page 1 of x, and as they request data, only a page’s worth is returned. In navision, this is quite easily done with rec.NEXT(x). 2) The ability to display reports are a definite plus point to doing things this way. Although you have to modify existing reports to generate xml output (easy to do this. Theres a library of navision functions so all that has to be done is something like AddDataItem(x) or AddField(x) within the report). 3) Data tables are generated in a similar fashion to Emil Romien’s xml exporter (http://www.mibuso.com/dlinfo.asp?FileID=285) I did one myself and was surprised to see that such a module already existed. Actually his is better than mine so I think I will integrate his one into the system when I release the system as an open source project. 4) Security etc is provided by the content management system, so I cannot detail it here. 5) Most of the logic and enhancements are done within Navision. For example, if I need to create a new report, it only requires changes to be made to navision (rather than for example having to rewrite the report from data held in a sql server table). The coldfusion and xsl stuff is quite static, which is good for us because it means theres no need to muck about with many different technologies. 6) Im quite disappointed with the lack of flexibility of some of the xsl stylesheets. Im currently debating about changing it so that something else parses the xml, or xsl only does part of the work. Not sure yet. 7) Navision errors were (and still are to an extent) a nuisance. Before we went live, filters input by the web user were passed as-is to navision and applied to the tables directly. This was great because it was possible to use navision style filter requests (eg. 01/01/04…01/03/04). Unfortunately, an incorrect filter caused an error. The result of this was that a msmq response was not generated and the web user got a message after 30 seconds saying communication with navision was not successful. To try and correct this, the received parameters are now read into a temporary table and checked to make sure its valid. Detected errors cause a response to be generated telling the user they made a mistake. I suppose at some point, I ought to enhance it so that msmq ack’s the request when it arrives (before processing) but haven’t had time yet. 8) When the VPN link between the webserver and NAS dies, so does the portal. This is where mjanum’s solution would be loads better as I suppose his solution would keep on working and sync when the link is back online. In my case, all the forms carry on working, but any request to navision would not complete (time out) because the communication is synchronous. This would make the solution unsuitable for mission critical solutions (which ours isnt) such as accepting credit card payments. Future Plans Future plans include extending the output to allow cross references. So for example, a report that contains a field called “customer No.”, clicking on it will show a detail card for that customer (just like using the navision client). There are currently hyperlinks attached to certain fields, but these were hardcoded per report. Lastly, Im hoping to be able to release the above as an open-source project in a month or 2. Ive still got a few things I would like to tidy up and enhance (eg. integrating the xml exporter). I will inform people here when I do so. Ok hope this was helpful! Regards, edd