Editing Database Views and Stored Procedures


“Ninety percent of the time you just need to make a simple modification,” the Redmond Magazine reviewer wrote in the introduction to the recent review that selected DatabaseSpy as Redmond Roundup Champion among database tools. DatabaseSpy lets you make those quick updates with its intuitive interface that is consistent across multiple database types. For the other ten percent of the time, DatabaseSpy can also be an appropriate tool for more advanced database maintenance tasks, such as creating or altering database views and stored procedures. Let’s take a look . . . The DatabaseSpy 2008 Online Browser lets you explore views and procedures by navigating and expanding them the same way you can explore tables in the hierarchical display of the Online Browser helper window. DatabaseSpy edit views and stored procedures

Edit Database Views

When you select an existing database view in the Online Browser window, the right-click context menu offers options to generate new statements in the SQL Editor that can quickly get you started editing a view, or to assist creating a new one. DatabaseSpy edit views and stored procedures For instance, when you generate a create statement for an existing view, DatabaseSpy displays the formatted and color-coded view definition for immediate access in a new SQL Editor window. DatabaseSpy edit views and stored procedures

Create a New Database View

You can modify the statement any way you like in the SQL Editor. As an example, let’s assume you’ve been assigned to create a company phone directory view. All the data you need is already contained in the employees view – plus a lot more! You can start by saving a copy of the employee view with a new name. Simply edit the database view name, execute your statement, and the new database view is created. DatabaseSpy edit views and stored procedures As you refresh the database connection in the Online Browser window, the new view is immediately visible and available for access. DatabaseSpy edit views and stored procedures

Alter a Database View

You can choose the Alter selection from the right-click context menu to edit any database view, including the new phone directory view. You can revise the existing view to make the changes you want, taking advantage of all the DatabaseSpy SQL Editor functionality, including color coding, automatic formatting, and even auto-completion. DatabaseSpy edit views and stored procedures When your edits are complete, the SQL Editor Execute button runs the alter statement and modifies the view in the database. DatabaseSpy edit views and stored procedures To make access to the new phone directory view even easier for the HR department, you can save a select statement for the view in a SQL file and add it to the HR manager’s DatabaseSpy Project menu. Altova DatabaseSpy edit views and stored procedures

Use SQL Refactoring to Create a View

If you don’t have an existing view to use as a template, DatabaseSpy 2008 offers a convenient alternative. The SQL Refactoring menu includes an option to convert any select query to a create view statement. The default view name is even highlighted so you can immediately assign a more relevant name. Altova DatabaseSpy edit views and stored procedures
DatabaseSpy edit views and stored procedures

Edit Stored Procedures

The DatabaseSpy 2008 Online Browser also lets you edit stored procedures in your database. You can highlight any stored procedure, then can expand your selection to explore it. Or, use the right-click context menu to generate SQL statements for operations available to act on stored procedures. Altova DatabaseSpy edit views and stored procedures
You can edit stored procedures in the DatabaseSpy SQL Editor, and execute your revised statements using the same techniques described above for database views. DatabaseSpy edit views and stored procedures DatabaseSpy edit views and stored procedures

Execute Stored Procedures

The Execute option in the DatabaseSpy 2008 context menu for stored procedures builds a time-saving template for an execution script for the stored procedure. Altova DatabaseSpy edit views and stored procedures
For procedures that require parameters, all you have to do is set the parameter values, then it’s just one click to execute the stored procedure and view the results. Altova DatabaseSpy edit views and stored procedures

Edit User-defined Functions

DatabaseSpy 2008 even lets you navigate and edit user-defined functions stored in your database, starting from the same convenient right-click context menu in the Online Browser. Altova DatabaseSpy edit views and stored procedures Try this for yourself with a free trial of Altova DatabaseSpy.

Tags: ,

Case Study: Wrycan, Fitz & Floyd, MarketLive


wrycan Fitz and Floyd is a leader in design and manufacture of hand painted ceramic gift ware. In 2007, they approached Wrycan, an Altova partner focused on content-centric XML expertise and related software development, for help creating a solution that would allow Fitz and Floyd to interface their existing CRM system to their new Web-based storefront application from MarketLive, the leader in e-commerce software solutions. Fitz and Floyd had already purchased a license for the Altova MissionKit software suite, so Wrycan was able to jump right in and start mapping data from Fitz and Floyd’s Oracle database to MarketLive’s proprietary schema using Altova MapForce. Wrycan assigned the project to a Principal Consultant, who had plenty of previous experience with XML technologies (including XSLT and XML Schema) as well as with large-scale databases, but who had never before used MapForce, Altova’s data conversion, transformation, and integration tool.

The Challenge

Fitz and Floyd required a solution that would automatically synchronize data from their Oracle database to MarketLive’s storefront application. It needed to perform the following functions: inventory updates, product updates, and order status updates. This way, when a customer ordered a Fitz and Floyd product via the MarketLive interface, they would be getting real-time information about the company’s inventory. The solution needed to be simple to use, easy to maintain, cost effective, and completed on time, so they could put their new storefront into production promptly. Fitz and Floyd’s existing data was housed in an Oracle 8.0.5 database and was organized according to internal requirements. In order to transform their data into a format that would work with MarketLive’s storefront application, Fitz and Floyd’s data needed to be mapped to MarketLive’s XML Schema. In addition, there needed to be a system in place to track and log any transaction errors that occurred.

The Solution

Because of MapForce’s ease-of-use, the Principal Consultant was able to get started using its intuitive features right away. Wrycan used MapForce to map the transformation from Fitz and Floyd’s Oracle database to the XML Schema definition (XSD) instance provided by MarketLive. Using the database as the source component and the XSD as the target, the following mapping was produced: MapForce mapping transparent In order to map to some XML Schema entities that were not explicitly defined in the original MarketLive schema, Wrycan used Altova XMLSpy’s graphical XML Schema editor to fill in the gaps, adding attributes to the schema that had not previously existed and thus ensuring that all necessary Fitz and Floyd data would be mapped to the MarketLive Web interface. An example of the schema modifications is shown below: XML Schema modifications Wrycan used MapForce’s unique code generation capabilities to automatically produce a Java applet that was used to update Fitz and Floyd’s product, inventory, shipping, and order status information programmatically. This specialized applet was then packaged along with Wrycan’s proprietary Transaction Manager. MapForce made it very easy to update and redeploy the data mapping requirements as they changed throughout the project. Because of MapForce’s ease of use and built-in code-generation capabilities, less technical users can also update the data mapping when there are changes.

Simple Web-based Transaction Manager

Utilizing open source Java technologies such as Apache Tomcat and Quartz Enterprise Job Scheduler, Wrycan was able to create a simple transaction manager that allowed the transactions handled by the MapForce-generated, Java-based data integration applet to be scheduled, processed, and logged. The Transaction Manager is a custom software application made specifically for Fitz and Floyd by Wrycan, but built in such a way that it can be reused for future clients. It consists of several components:

  • User interface – allows the integration of MapForce-generated Java code
  • FTP interface – adds the ability for files to be downloaded for transformation from Oracle database format to the eCommerce platform XML format or vice versa
  • Scheduler – allows the automation of the data migration
  • Reporter – stores transaction results in XML files accessible in the user interface and also has the ability send emails in case of exceptions

The Transaction Manager’s user interface is the point of contact for Fitz and Floyd to control and schedule any data transformations. Because Wrycan wanted to be able to reuse the Transaction Manager, they chose to generate the MapForce code in Java, a platform-independent programming language. (MapForce can also generate application source code in C# and C++.) This code is an integral part of the Transaction Manager, as it dictates the data mapping process, allowing Fitz and Floyd’s internal information to be accessed via the MarketLive interface. The FTP interface is a simple way to manage the transfer and delivery of files from within the Transaction Manager once the MapForce-generated Java applet has transformed the data according to the MarketLive schema. A built-in batch scheduler allows Fitz and Floyd to automate the data migration operations by content type (i.e. order, inventory, product, etc.). Batch jobs The reporting component allows the result of each transaction to be logged in XML. Because of this, if any transaction errors occurred, Wrycan was able to use Altova XMLSpy to analyze and debug the issues.

The Results

Fitz and Floyd now has an easy to use data integration layer that is extensible by adding new MapForce transformations, and they can easily adjust their current transactions. Any updates made to the Fitz and Floyd Oracle database are automatically transferred to the MarketLive application in a format that it can readily understand. Log Details Because the Transaction Manager application is based on platform-independent Java code (generated by MapForce), Wrycan also has a reusable application that can be used as an asset by any online retail company. Wrycan is now able to approach potential clients with a proven data integration layer product that provides job scheduling, email notification, and FTP integration and can utilize any database or schema output via a custom Altova MapForce transformation. When speaking about this project, Dan Ochs, the principal consultant at Wrycan involved with the Fitz and Floyd application stated “MapForce has proven to be an easy-to-use, effective tool for making the data integration and mapping process much easier and faster to implement.” This and many other customer case studies involving Altova solutions are available in the Altova library.

Tags: , , , , ,

Redmond Roundup: Tooling Around in Your Database


RedmondMag.com just published a great article by Peter Varhol, a principal at Technology Strategy Research LLC, an industry analysis and consulting firm. This “Redmond Roundup” discusses common database management tasks and reviews some of the tools that are popular in that space:

Sometimes you don’t need a full-fledged database-management environment. Maybe you just need to go in and add a field, change a table or write an ad hoc query. Certainly you can do all those things in the SQL Server administration tools, writing your own SQL at the command line. Unless you’re a SQL expert, though, that process can be lengthy and error-prone. Having a graphical tool that’s easy to learn and use — and one that you don’t have to use every day to remember how to perform simple actions — is much more productive. Simply point it at the database, click the mouse to tell the tool what to do and your database maintenance is done…

Varhol examines three leading database tools and concludes by awarding Altova’s very own DatabaseSpy Redmond Roundup Champion, noting, “DatabaseSpy is the best overall in terms of range of features.” Redmond Roundup Image source: http://redmondmag.com/features/article.asp?editorialsid=2530

Tags: ,

How to Recommend Improvements or New Features


The most valuable input the Altova team receives is from our customers and potential customers. This feedback shapes our future development plans for improving our tools and adding new features and functionality. We receive this feedback in multiple ways: by interacting with users at industry events, through comments on this blog, through customer case studies, and, most often, through Altova’s Online Support Center. You can use the Support Center to report a problem you’re having, get help with licensing questions, request new features, and more. The form will guide you to the right place to enter your request. As always, we look forward to hearing your feedback!

Tags: ,

Agile Modeling with UModel


Agile development is quickly becoming a leading model in the forward-thinking software community. The agile method seeks to bring development out of the document-heavy rigidity that exists within architecture-centric projects with a flexible and lightweight alternative that focuses heavily on adaptivity and customer communication. The agile model seeks to reduce the vast amounts of paperwork and planning put into many software development projects, shifting the focus to adapt to changing requirements and overall customer satisfaction. The Unified Modeling Language™ (UML®) has long been the de facto industry standard for object oriented software modeling, offering thirteen diagram types to represent three different system views: structure, behavior, and interaction. Altova’s UML modeling tool, UModel, presents an approach to UML that is both iterative and flexible, giving software documentation the ability to adapt and change with each new iteration, and offering customer-facing development teams the opportunity to present compelling application model designs every step of the way.

UML

Adopted as a standard by the Object Management Group (OMG) in 1997, and later formalized as ISO 19805, UML is actually the product of several different prevalent OO modeling languages which emerged in the early 1990s. UML is a graphical language for organizing, analyzing, and planning object-oriented or component-based software projects. The UML 2.1 specification defines thirteen major different diagram types and over one thousand graphical and textual language elements, as well as additional extension mechanisms. Traditionally these diagrams have been used by software developers and project managers as a powerful, standardized planning language to verify application logic and confirm that end-user needs will be met. UML is complex by design, offering a multitude of options for visually detailing software implementations in a wide variety of hierarchical models that can provide representations for every stage and process within the development cycle. Structure Diagrams

  • Class diagram
  • Component diagram
  • Composite structure diagram
  • Deployment diagram
  • Object diagram
  • Package diagram

Behavior Diagrams

  • Activity diagram
  • State machine diagram
  • Use case diagram

Interaction Diagrams

  • Communication diagram
  • Interaction overview diagram
  • Sequence diagram
  • Timing diagram

With this complexity comes a learning curve that can be easily addressed by choosing an intuitive UML modeling tool that includes advanced usability features and seamless graphical representations, as well as the agility to adapt and grow with a software development project. The Agile Manifesto Drafted in early 2001, the Agile Manifesto documents a set of principles for a faster, lighter, and goal-oriented approach to software development that contrast with the traditional waterfall method that has long existed at a majority of technology companies. The ideas behind agile development had been gaining notoriety over many years with the creation of other similar lightweight methodologies, many of which have since been incorporated into the agile family. The manifesto is built on the concept of software development as an iterative process that must be able to quickly adapt to ever-changing requirements and customer needs. The document focuses on:

  • Individuals and interactions over processes and tools
  • Working software over comprehensive documentation
  • Customer collaboration over contract negotiation
  • Responding to change over following a plan

An adherence to these overall goals intends to keep software projects limber and malleable enough to adapt to changing requirements, while keeping developers focused on the quality of their work at every stage of the process. Agile Modeling The agile methodology requires a modeling and documentation process that reflects the fluidity of its founding principles. The agile modeling process is a means to support development projects, addressing interaction and collaboration through the presentation of action plans in a visual format that stakeholders can readily understand, while also being technical enough to provide developers with a basis for their design. Agile modeling focuses on simplicity and the ability to process and handle changing requirements, leading to an incremental approach, where software projects are visually modeled and presented in phases, rather than a traditional model in which all encompassing plans are drawn up at the outset. The Agile Model Driven Development (AMDD) approach dictates a relatively short requirements analysis phase, with successive just-in-time modeling to address project needs at each iteration. Using this method, working software is available for review and testing at a much earlier stage, giving collaborators the opportunity to change requirements as the project evolves. UML and Agile Modeling The widespread adoption of UML as a modeling language stems largely from its ability to express software design in many different ways and at many different stages. In addition, its rapid acceptance as a standard suggests a recognized need for a unified approach to modeling, helping disparate development communities to collaborate over shared projects. As a predecessor to agile methods, UML was developed to address more stringent object-oriented design methods, which have more robust modeling and documentation requirements. However, with the right tool, developers, project managers, and stakeholders can take advantage of this standard modeling language in their agile projects. Agile Modeling with UModel Altova UModel is a full featured UML development tool, supporting all diagram types with additional support for code and documentation generation, reverse engineering, and advanced usability features. Fully compliant with the latest UML specification (2.1.1), UModel is a valuable asset to any form of software development. UModel’s unparalleled flexibility and functionality make it the ideal UML tool for agile modeling, allowing developers and collaborators to take advantage of the trusted UML standard by applying its modeling capabilities to agile methods. UModel UML tool UModel offers advanced usability features that help lessen the UML learning curve, making modeling accessible to all project collaborators. With a focus on versatility in model design, UModel offers a completely customizable interface with color-coded elements to clearly indicate model characteristics. UModel visual elements Users can also add additional customizations to enhance usability and communication that can be automatically applied to single elements, groups, or project-wide. UModel’s rich visual interface enables developers to quickly and easily sketch software designs to communicate all aspects of system architecture. This lightweight approach to UML design melds perfectly with the agile methodology, opening avenues for communication over dynamic project representations. UModel provides additional support for collaboration through support for shared packages, which enable developers to distribute their functional designs to other team members or import designs from other projects for reuse. UModel share package Visual design representations can also easily be saved or printed as images for conceptual review by non-technical contributors. Save UML diagram as image Another compelling feature in UModel that drives inter-project communication as well as customer collaboration is the ease at which developers can create informative use case diagrams. UML use case diagrams tend to be a popular choice in agile modeling because they address one of the most challenging phases of the software development process, the visualization of user interaction. UModel use case diagram representations can be seamlessly illustrated with the help of advanced usability features and sophisticated graphical output. UML use case in UModel UModel also allows developers to auto-generate detailed documentation, including embedded images, in HTML, RTF, or Microsoft® Word. This feature addresses the second point of the Agile Manifesto, enabling teams to focus their efforts on software design, rather than getting buried in overbearing documentation that can sometimes stall project flow. Generate UML documentation UModel’s robust round-trip engineering capabilities provide agile developers with the ability to quickly adapt and respond to change over the course of their project(s). UModel interprets modifications to project source code and synchronizes this with the corresponding UML diagram. UModel supports Java, C#, and Visual Basic, bringing advanced functionality and flexibility to the iterative development process. UML round trip engineering UModel also offers tight integration with the leading integrated development environments, Visual Studio® and Eclipse, giving developers the ability to seamlessly switch between the UML model and code editing windows and see any updates and changes reflected in real-time. UModel’s extensive usability and communication features offers development teams the ability to quickly change and adapt project requirements based on the results of incremental collaboration. Its graphical design interface and intuitive modeling capabilities enable team members to create compelling visual designs that can be easily interpreted by both technical and non-technical stakeholders at every stage of the project. Conclusion The wide acceptance of agile practices signifies a shift from the role-based, waterfall approach that was the norm just a few years ago. Businesses are recognizing that software projects are constantly changing and evolving at every step of the way. Development teams need to be able to manage change, to meet deadlines, and to lower costs. Nothing is quite as effective as the right set of concise diagrams to represent the essence of a software implementation. UML has the capacity to deliver structure to vague and abstract customer requirements, enabling developers to easily conceptualize the task at hand. With its flexible and advanced UML design interface, UModel is an ideal modeling tool for agile development, giving developers an inexpensive, easy-to-use, comprehensive modeling option with robust features for project collaboration and communication. UModel gives users the opportunity to capitalize on the extensive capabilities of the UML standard, but also offers the plasticity required of agile implementations. You can try UModel for free in your next agile development project. This technical brief and other resources are available in the Altova Library.

Tags: , ,

Altova UModel adds Business Process Modeling, Layers, Java 6.0, C# 3.0, and VB 9.0, plus much more in v2008r2


The Altova UML tool for software modeling and application development keeps getting better and better, with recently launched UModel Version 2008 Release 2 adding exciting new features including support for business process diagrams in BPMN notation, code engineering support for Java 6.0, C# 3.0, and Visual Basic 9.0, diagram layers, enhanced auto-completion, and much more. We can’t wait to see how users take advantage of the new layers feature: In UModel 2008 Release 2 you can assign each diagram element to a specific layer and set each layer to be hidden or visible. Just imagine how you could take advantage of layers to build simplified views within complex activity diagrams, state machine diagrams that contain superstates and substates, to identify the roles of different parties in business process diagrams, or in virtually any UML diagram that grows to more than two dozen or so elements!

Tags: , , , , ,