PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Enterprise Database Development in Sybase Workspace Release 1.5

Enterprise Database Development in Sybase Workspace Release 1.5

The diagram below shows the system architecture for this application (Note: The main focus is on the database back-end side of the application architecture and design).

The back-office shipping application is cleanly separated from the online customer-facing order processing application through the use of two separate domains in the application - Ordering and Shipping. These domain boundaries, and the data integration between the domains, is achieved by using Sybase Real-Time Data Services. This enables non-intrusive propagation and routing of the necessary Ordering data to the Shipping domain without impacting the online transaction processing performance. The Shipping confirmation processing is separated by using ASE Real-time Messaging to send the shipping confirmation data.

The following screenshots illustrate how Sybase WorkSpace simplifies back-end development. Database deployment, debugging, and testing features integrated into Sybase WorkSpace aren't shown here. (Figure 1)

Define database objects from application database models
Developers using Sybase WorkSpace can build conceptual and physical data models of the application databases. The screenshot below shows the database model created for the Order Entry database. Once the model is final, the corresponding schema can be generated and browsed on the database server from within Sybase WorkSpace. (Figure 2)

Create a replication environment for database recovery and disaster planning
Sybase WorkSpace helps in creating a model for database disaster recovery planning using the Information Liquidity Model (ILM). The following screenshot shows the ILM model used to define replication from the primary database to the standby database using Replication Server and also to propagate appropriate primary database activity to the Shipping database using RepConnector. SQL code (foreground picture) generated from the model is executed on the database and replication servers to set up the desired replication.

Sybase WorkSpace 1.5 now supports direct connection to the Replication Server and wizards for replication artefacts such as table and database replication definitions, publications, and subscriptions. (Figure 3)

Create database stored procedures and triggers to implement database logic
The Sybase WorkSpace SQL development environment offers powerful editing and debugging capabilities for database objects. The Sybase WorkSpace screenshot below shows a stored procedure object under development in the Order Entry application database.

Sybase WorkSpace 1.5 now supports the full lifecycle of database temp tables. (Figure 4) Note that in the previously mentioned stored procedure, an ASE temp table is being used. When saving and debugging such a stored procedure, WorkSpace detects the use of the temp table and prompts for the creation command that's saved as an annotation in the code (as shown in previous screenshot). This helps developers streamline their work by eliminating the extra work required to create those temp tables. At debug time, WorkSpace can show the contents of those temp tables as they come into existence. The following screenshot shows the temp table dialog displayed when a stored procedure being saved has reference to the temp table. Once annotation is added, the temp table dialog isn't shown. (Figure 5)

Build and execute complex query using the Visual SQL feature in the database editor
SQL code development in Sybase WorkSpace is complemented by several creation wizards such as Visual SQL where developers can build, preview, and execute the query before it's brought into the stored procedure, trigger, SQL script, etc. The Sybase WorkSpace screenshot below shows the Visual SQL Builder with database tables, columns, and the query being built for the stored procedure. (Figure 6)

Debugging ASE stored procedures and triggers
Sybase WorkSpace has a built-in database debugging environment for stored procedures and triggers. For the ASA database, user-defined functions and event handlers are also supported. Developers have multiple options to debug such objects including attaching them to an external application. The Database Debug perspective layout shown below has special views for debugging tasks such as stepping, setting breakpoints, variable watch and modification, examining results, viewing and editing referenced tables data, viewing temp table data, and attaching to external clients, among other things.

Debugging triggers is supported and enhanced through the launch configuration options to associate DML statements. The referenced tables view can show inserted and deleted data (it requires one extra line of code). (Figure 7)

Build a Complex JMS SQL Query for sending Shipping confirmation data (XML) to the e-mail Notification Application
Developers using Sybase ASE real-time messaging features and XML would find generating messaging SQL a simple and easy task with the Sybase WorkSpace Real-time Messaging Wizard. This wizard presents appropriate information about the messaging endpoints and lets developers create appropriate messages and their supported system and user-defined properties. The screenshot below shows the confirmation message being created to send from the Shipping Database to the JMS endpoint. (Figure 8)

Expose the database services based on existing logic such as stored procedures and SQL commands
For developers writing code that interacts with database servers, Sybase WorkSpace eliminates the JDBC API complexities and frees developers from the details of result set handling by providing a database service. The database service is used to expose stored procedures and SQL commands, which enables their consumption in other applications such as business processes, EJBs, portals, and web applications. The database service automatically detects result sets and developers can further customize those result set objects for the service consumers. The following screenshot shows a database service built from the stored procedure for sending Shipping confirmation messages. (Figure 9)

Sybase WorkSpace represents the next generation in development technology designed to maximize the efficiency of creating new applications of all types. This article highlighted the end-to-end database development capabilities in Sybase WorkSpace and its support for varied database-related infrastructures such as Sybase ASE, Sybase ASA, Sybase IQ, Replication Server, RepConnector, and MobiLink. WorkSpace delivers a unified environment that simplifies database development and optimizes associated development activities with features such as powerful database editing and debugging, code generation, support for Web Services, and setting up data movement between databases.

More Stories By Samir Nigam

Samir Nigam is director of engineering in the information technology and solutions group in Sybase. He is currently looking after Data Management and Data Integration tools in Sybase Workspace - Sybase's next generation unified development environment built on Eclipse. Samir holds a Masters in Computer Engineering from the University of Cincinnati.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.