New HL7 Online Training Available

HL7 is an international standard for transmitting clinical and administrative information within healthcare domains such as hospitals, pharmaceutical, medical device, and insurance entities.

Altova has just released a new MapForce course as part of its line of free online training modules. This course focuses on HL7 integration using both the EDI (version 2.x) and XML (version 3.x) formats. Each of the three modules include step-by-step tutorials, evaluation exercises, and a chance to work with real HL7 files and functions.

Go on, join the EHR revolution! Download a free 30-day trial of MapForce, go to our training page, and check out powerful HL7 mapping from Altova!

Ride the UML Heat Wave

Get the Most Advanced UML Features - And Save 50%, 70%, or Even 90% Over Competing Tools!

For a limited time we’re offering special savings on Altova UModel. Combining a rich visual interface with superior usability features, UModel also includes high-end code engineering functionality to empower you with the maximum benefits of UML software development.

Starting at just $149, Altova UModel delivers the most advanced UML modeling features in the industry - at a fraction of the cost of other UML tools.

The same powerful functionality and ease of use that you've experienced in other Altova tools is packed into an affordable, intuitive UML modeling tool. UModel supports:

* All 14 UML 2.3 diagrams

* SysML and BPMN

* Code gen and reverse engineering of Java, C#, and VB .NET

* Round-trip engineering

* XMI import / export

* Visual Studio and Eclipse integration

* 32- and 64-bit versions

* And much more

Cool off with an additional 20% discount Enter code JULY10 during checkout in the Altova Online Shop for 20% off UModel. Act fast - this sizzling offer expires July 31, 2010.

*Savings calculated by comparing published single-user prices of Sparx Enterprise Architect Engineering Edition, Magicdraw Professional Edition, and IBM Rational Software Architect Standard Edition including Rational Modeling Extension for Microsoft .NET, with equivalent UModel Enterprise Edition configured with comparable bundled software maintenance plans.

Using Altova Tools to Work with XML Data in the SQL Azure Cloud

In an earlier post we discussed connecting to Microsoft SQL Azure databases with Altova DatabaseSpy and demonstrated database schema comparison and content comparison between a local database and the same database migrated to SQL Azure.

In this post we will use a different method to migrate an existing table to SQL Azure and show you some tricks you can do with XML in the cloud. We started by creating a new database schema in SQL Azure. Then we created a DatabaseSpy project with a connection to a local copy of SQL Server Express running the AdventureWorks sample database, and a second connection to our new SQL Azure schema.

The AdventureWorks database contains a table called JobCandidate with some XML data we will use for a model for our SQL Azure XML contents.

We can generate a CREATE statement for the existing table to use as a basis for the SQL Azure version.

We need to modify this statement to execute in our SQL Azure database. In addition to changing the database and schema names, we will remove the foreign key constraint to the Employee table, since our new database doesn’t contain a table with that name.

Also, SQL Azure does not support the CONTENT keyword, so we will remove that as well.

After making sure the Properties window for the revised CREATE statement points to the SQL Azure database, we can execute the statement. When we refresh the database and expand our view in the Online Browser helper window, we can see the new empty table.

A data comparison between the existing table and the new one will allow us to create a script to migrate data into our new table in the SQL Azure cloud. This is similar to the data comparison we wrote about in our previous post on SQL Azure, except instead of merging data directly, we will save the merge script.

Our first attempt to run the merger script failed, throwing an error message that SQL Azure cannot insert values into the new table when IDENTITY_INSERT is set to OFF. We can add a line to the merge script to SET INDENTITY_INSERT ON and re-execute:

Next, we can run a SELECT query to view the data that was successfully uploaded.

The DatabaseSpy Data Inspector window lets us more easily examine the contents of a wide column, and is ideal to use for XML documents stored in the Resume column of the new JobCandidate table.

Editing XML Data with XMLSpy

If you need to revise, edit, update, or validate XML data in a SQL Azure database, Altova XMLSpy provides more robust XML editing features than DatabaseSpy. We can connect to our SQL Azure database from XMLSpy and run a SELECT query from the XMLSpy Database Query window. XMLSpy lets us open any XML row for direct editing, with access to advanced XML editing functionality.

Of course all the familiar features of the XMLSpy text view and grid view are available.

After your edits are complete, the XMLSpy File / Save menu option saves the revised XML document to the same row of the JobCandidate table in the SQL Azure database in the cloud.

Parsing XML Data with XQuery

You can also apply the XMLSpy XQuery editor, with its built-in knowledge of XQuery syntax and context-sensitive entry helpers to build XQuery statements that parse the XML data in your SQL Azure database. The XQuery statement below extracts and returns the home addresses from the XML resumes where JobCandidateID is less than 7.

The XQuery statement can be executed in the Database Query window, with results immediately available to work with in XMLSpy.

Of course the XQuery result can also be edited in Text view or in Grid view.

And you can save the query result either from the Database Query window or from the XML Editor view.

Find out for yourself how productive you can be by using Altova tools to work with XML data in the SQL Azure cloud ̶ download a free 30-day trial of the Altova MissionKit for Software Architects, an integrated suite that includes XMLSpy, DatabaseSpy, and additional XML, database, and UML tools.

Connecting DatabaseSpy to a SQL Azure Database in the Cloud

Tips and techniques to ease introduction of Microsoft’s cloud-based SQL Azure database into production environments led the topics in June at the Tech-Ed conference in New Orleans.

SQL Azure is built on Microsoft SQL Server technologies and is designed to provide a highly-available and scalable database service hosted by Microsoft in the cloud. Developers who deploy databases in SQL Azure do not have to install, setup, patch, or manage any relational database software, only their own database structure and content. Automatic redundancy and fault tolerance are built-in and no physical administration is required.

You can build a manual connection string and use SQL Server syntax and datatypes to connect DatabaseSpy and other Altova tools to SQL Azure databases to perform typical database development and maintenance tasks.

This blog post makes a connection to a SQL Azure database from DatabaseSpy and demonstrates several typical operations you might want to perform as you migrate an existing database to the cloud.

To retrace these steps on your own you will need a SQL Azure account, or a login and password created by a SQL Azure account holder. For more information on setting up a SQL Azure account, visit the Microsoft SQL Azure home page.

You will also need to install the SQL Server Native Client 10.0 (or later).

SQL Azure does not behave exactly like a local SQL Server database, so we can’t use the Altova SQL Server connection wizard. Instead we will use an ODBC connection.

We won’t illustrate all the details of the process of building a new connection string here. You can paste an existing connection string into the dialog shown above, or, if you need specific instructions for each step to manually create a new string, you can refer to the Tech Note titled Build an ODBC Connection String for SQL Azure on the Altova Web site.

After you connect to SQL Azure the first time, a DatabaseSpy project file lets you save all your connection settings along with frequently-used SQL scripts, database design files, and database comparisons in a convenient bundle to reload later.

The screenshot below shows a new DatabaseSpy project with two databases connected simultaneously, Sakila in MySQL and Sakila in the cloud in SQL Azure.

Microsoft provides a number of conversion tools to help users migrate existing databases to the SQL Azure platform. We used the Microsoft SQL Server Migration Assistant for MySQL to convert our local MySQL Sakila sample database to our SQL Azure account.

DatabasesSpy lets users open multiple connections simultaneously, even to databases of different types. The database comparison functionality of DatabaseSpy makes it an ideal tool to check the results of the Sakila conversion. First we will open a database schema comparison and select a few tables from the MySQL database for the left side of the comparison.

After we select the corresponding tables from SQL Azure version, the tables open in a database schema comparison window.

When we click the green compare button at the top left corner of the window, DatabaseSpy compares the database structures, highlights differences, and generates a summary in the message window.

Some differences represent datatype definitions that vary between databases. For instance, the MySQL type unsigned small int does not have an exact equivalent in SQL Server, so the conversion tool substituted the int type for the film_id column in the film table.

Also, the year datatype assigned to the release_year column in MySQL has been converted to a smallint in SQL Azure. I guess this will make the SQL Azure version of the database more forward-compatible, since it will be able to accommodate films released all the way through the year 32,767, as opposed to 2155, which is the maximum value of the year datatype in MySQL!

We can compare data contained in the two databases via a selection in the right-click context menu, opening the selected tables in a new data comparison window.

The data comparison shows us the contents of the tables are not identical.

When we open the results window, we see that the description column did not migrate successfully.

Looking back at the Database Schema Comparison window, we can see the length of the description column was set to zero. This explains the red arrows that point from the description column in MySQL to the description column in SQL Azure in the Results window. We cannot copy any string of text into a column with a defined length of zero.

Instead, let’s open the SQL Azure version of the film table in a new Design window.

We can increase the size of the description field in the Properties window, and run the resulting change script.

Next, when we re-run the data comparison, we find that the data was converted, but the previously defined field length of zero made the data invisible.

Latency Issues

You can use DatabaseSpy to explore latency issues for the cloud database vs. the local copy.

We saw from the data comparison above that the film tables in the two databases contain 1,000 rows of identical data. We can repeatedly run SELECT statements to retrieve the data from SQL Azure and from the local MySQL database to time the results. The DatabaseSpy SQL Editor message window displays the execution time.

Running the above SELECT statement five consecutive times on the SQL Azure version of the sakila database generated results ranging from 60.632 seconds to 63.851 seconds.

Running a SELECT statement for the same film table in the local MySQL database yielded the following result:

Repeating the test for the local version generated similar times. The takeaway for developers is your database-driven application will likely need to accommodate latency as you move your data to the cloud.

Try your own connection to SQL Azure with a free trial of Altova DatabaseSpy.