When you start to develop mission-critical business applications using your favorite development tool, you should seriously consider iAnywhere's SQL Anywhere database management system to support the data management requirements. For this article I will focus on SQL Anywhere version 10 and PowerBuilder 11.0. Some of the features reviewed may not be supported in previous versions of these software releases. Where possible, I will try and identify features that are only supported in the current release and if there are any alternatives in previous versions.

In Part 1 I discussed the installation of SQL Anywhere, developing a schema, and the Database Painter. In Part 2 I will continue the discussion on the Database Painter as well as tracking database changes.

Database Painter
Tables and Columns
Tables and their associated columns can be easily modified, deleted, and added using the Database Painter interface to the SQL Anywhere database. To add a Table, use the RHMB on the "Table" entry in the tree control on the left side of the painter. From the resulting pop-up menu, select the "New" option and you'll see a pane appear entitled "Column" layout; it should be located in the Database Painter's lower section. This new work area displayed will allow you to tab through and enter your new columns, their data type, length, and even default values. If you would simply like to maintain an existing table and add, delete, or modify columns, select the RHMB on the table name in the DB Painter's tree and then choose the "Alter Table" menu option from the resulting pop-up menu. This will (like the new table request) open the "Column" layout pane, but will populate with information about the current column(s). To add or delete a column, use the RHMB with the mouse positioned just in front of the column name. This should result in a pop-up menu with Insert and Delete column actions for you to choose from.

Once you have the new or modified table changes completed in the associated table structure, move your mouse to the top of the Column layout pane where the headings are displayed and use the RHMB on any of the heading names. PowerBuilder should then display a pop-up menu with the "Save" or "Save Table as" options. You may now select one of these to have the table entity modified or added to the schema by the SQL Anywhere DDL compiler. It should be noted that PowerBuilder never touches your SQL Anywhere database, instead it always interfaces to one of three standard compilers: Data Definition Language (DDL), Data Manipulation Language (DML), or Database Control Language (DCL). All of these various compiler interactions are "brokered" for you automatically by the PowerBuilder Database Painter.

Once a new table has been created, you may wish to add Primary Keys, Indexes, or even Foreign Keys. This is very simple in the Database Painter by using the RHMB on the table name in the tree control or once you have it visible in the object layout pane. At this point a pop-up menu will appear. Select the "New" option and a cascading menu should drop that allows you to create one of the features that I just mentioned (see Figure 1). I won't delve into the details of these DBMS features in this article but I do recommend that you read your DBMS manuals on these topic areas for further detailed information on these features and their ramifications.

As a last comment on handling table changes, the PowerBuilder developer may be restricted from creating new tables/columns or even modifying existing column structures within a table. The PB Database Painter has an interesting option for the developer to interface to the Database Administration personnel. This allows you to provide them with the necessary DDL compiler input for DBA to apply these changes using their favorite database utility. Here is how it works: before pretending to create, modify, or delete an entity, turn on the Database Painters "Activity Log". You can find this option on the lower right-hand side of the Database Painter in the Pane marked "Activity Log." Activate this pane and use the RHMB anywhere within this pane. The resulting pop-up menu should include both a "Start Log" and "Stop Log" menu item. At this time, select the Start Log option to have PowerBuilder automatically record every change to your local SQL Anywhere database, for example, column changes, foreign keys, indexes, etc. When you have completed all your database maintenance tasks, return to the "Activity Log" pane and select the "Stop Log" menu option using the RHMB. The last remaining step will be to export the captured SQL in the log to an ASCII file and send this to your DBA. In older versions of PowerBuilder there was an "Export Log" option but for some reason the Sybase engineers seem to have removed that functionality. To counteract this deficiency, copy the contents of the Activity Log over to something like the MS-Windows NotePad utility and then save this to a .SQL file. The other option is to simply close the Database Painter, which will then prompt you to save the contents of the log file (strange but true).

What I don't like about the latter approach is that I might not be finished with all my database changes at that point. You can get around this little caveat in the newer versions of PowerBuilder by opening a second Database Painter just to log the changes for a given entity. Then, when you close the second instance, you can save the SQL but still continue to work on other in-progress database maintenance items when returning to the first instance of the Database Painter (food for thought).

Views
You can use Sybase Central to create a database "View," but the Database Painter in PowerBuilder gives you an edge by allowing you to access the SQL Painter as you go along your SQL journey. This can be a great time-saver for the RAD developer and for SQL neophytes, a real productivity gain when you need to quickly build more complicated sub-select, outer join, union, etc., queries. To activate this feature, use the RHMB on the tree control in the Database Painter on the "Views" entry. Select "New" from the resulting pop-up menu. This will automatically lead you into the SQL Painter where you can graphically select the entities and SQL options that you require. When you complete the SQL criteria and exit the SQL painter, the "Create View" DDL statement will be returned to the Database Painter where it will prompt you for a name and then send this to SQL Anywhere's DDL compiler to physically create the view entity (see Figure 2).

Another easy technique to create a view is to use the SQL from within a current DataWindow to establish the database view. This can be done by entering the DataWindow Painter and selecting the SQL option to enter the SQL Painter. In the SQL Painter, use the menu item "Design=>Convert to Syntax" to reproduce the actual SQL Syntax that the DataWindow will execute at runtime. Copy this into the MS-Windows Clipboard and exit the SQL/DataWindow painters. Returning to the Database Painter, activate the "ISQL Session" Pane. Within this area, paste the SQL statement from the clipboard of what you discovered using the DataWindow Painter. To create a view from this SQL, add a "Create VIEW ("name") ... DDL header in front of the pasted SQL. Then press the "Execute" command button on your Database Painter toolbar to run the DDL and build the view.

To test the view, expand the "Views" item on the DB Painter's Tree and locate the view you wish to test. Using the RHMB on the view entity, select the "Edit Data=>Grid" menu item from the resulting pop-up menu. This action will execute the actual view entity's SQL and show you the actual result set returned from the SQL Anywhere DBMS. Now is that not easy or what? To utilize the view database entity, all that is required is to build a DataWindow object and request that a view be the data source for the DataWindow's SQL (you'll see this option in the DataWindow Wizard). Select the new view you just created and that's all there is to it. This is a real productivity feature for developers as now you can build dozens of DataWindow objects and never have to construct the same SQL statement again.

Triggers
The PowerBuilder Database Painter is not capable of creating and maintaining Database Triggers, so you will have to use Sybase Central for this activity. Triggers can be most beneficial for ensuring referential integrity, setting default values, copying information (for historical uses), maintaining total counts, etc. In Sybase Central, after you have connected to the target database, use the RHMB on the "Triggers" tree view item and select "New=>Trigger" from the resulting pop-up menu items. The Trigger Wizard will spring into action to help you with your construction endeavors (at least the basics) for the table and action(s) you wish to occur ) (see Figure 3). You may need to visit the SQL Anywhere help file and review the section on Creating Triggers to get more information on the actual coding, but the help is well documented in this area to assist the developer at this point.

Events
Quite often you may require data to be copied from one database to another or even over to another server. Maybe your system requires an audit of new rows within the last hour or to produce picking order information to a warehouse, maybe even back up a database or rebuild indexes, etc., automatically for planned DBA maintenance. These are just some examples of activities that your system may require to be completed on a "timely" basis. For these activities, you can use SQL Anywhere's "Event" processing capabilities.

This feature is only accessible from Sybase Central but very easy to create using the wizard.

To create an Event, launch Sybase Central and connect to your target database. Then use the RHMB on the Event item in the Sybase Central tree and select the "New=>Event" menu item from the resulting pop-up menu. The Event Wizard will be started and will guide you through the process of setting up an event. You may also use PowerBuilder's Database Painter to create an event, but you will have to know the DDL syntax for this. Once you have the event syntax ready though, paste this into the Database Painter's "ISQL Session" pane and press the "execute" toolbar button to create, modify, or delete the event.

You can always view an "Event" from within the PowerBuilder Database Painter by highlighting the event from the tree control in the painter, then RHMB and select the "Properties" menu item from the resulting pop-up menu. The actual event syntax will now appear in the "Property" pane. To modify the event from within PowerBuilder, drag the event from the tree control to the "ISQL Session" pane in the Database Painter (see Figure 4). This will have PowerBuilder converse with the DDL compiler to deliver the exact Event syntax into the work area. The default will be a CREATE statement, so to modify the event, change the verb to ALTER and then modify the command between the Begin and End statements as required. When the modifications are completed, press the Execute button on the Database Painter's toolbar to invoke the DDL compiler (tip: you can then use Sybase Central to confirm the changes if you like).

Stored Procedures
Stored Procedures (SP) are very popular with developers and especially Database Administrators. They can be very useful for various processing tasks that need to be executed within the DBMS server. In the case of SQL Anywhere, the Stored Procedure is also important for performance as it becomes a "compiled" entity within the DBMS repository. By that I mean that a "Plan" is created and the execution mechanism becomes an Assembler program. PowerBuilder developers in the more recent releases have been able to further embrace the use of Stored Procedures due to the fact that a DataWindow can now utilize up to four Stored Procedures. That is to say you can have a different Stored Procedure, one each for: Select, Update, Delete, and Insert. This makes the PB developer very productive as the DataWindow "brokers" the data actions to the various Stored Procedures and the only PowerBuilder commands you need to learn are Retrieve and Update.

To create a Stored Procedure in PowerBuilder is extremely easy. Again, you could use the Sybase Central utility, but with PB's Database Painter in concert with the SQL Painter this task can be made very easy. To start with, open the DB Painter in PB and activate the "ISQL Session" pane. Use the RHMB in the pane's work area and select the "Paste Special=>SQL=Select" from the resulting pop-up menu. This will now activate the SQL painter where you can graphically create any SQL statement you require to activate the desired result set (tip: use the "Preview" button on the SQL Painter's toolbar to test your SQL before returning to the DB painter). Once you are satisfied with the SQL and Result Set being returned by the DBMS, exit the SQL Painter and the final SQL statement will now appear in the ISQL Pane. To this returned statement, add a "Create Procedure Name as ..." in front of the current DDL statement and then press the "execute" button on the DB Painter's toolbar. This should now create the Stored Procedure in the SQL Anywhere repository.

To test the new Stored Procedure that you created using the steps above, use the Tree control in the DB Painter and expand the Stored Procedure section, locate your new Stored Procedure, and with the RHMB, select the "Edit Data=>Grid" menu item from the resulting pop-up menu (see Figure 5). This will have PowerBuilder invoke the Stored Procedure and display the generated result set in the "Results" pane of the Database Painter. You may also use the ISQL session pane and type in an execute command to run the Stored Procedure. For example, "Execute sp_cip_test ( ) ;". For those Stored Procedures that have arguments, you may add these within the brackets after the Stored Procedure name in the execute statement.

The real "icing on the cake," so to speak, for PB developers is when they are required to use the Stored Procedure in an application All they need to do is create a DataWindow using the Stored Procedure as its "data source." This is less than a minute's work in the DataWindow Wizard and the resulting DataWindow Object will execute the Stored Procedure using a "Declare, Prepare, Execute and Fetch loop" that the developer never needs to code (unlike in C++, C#, VB, Java, etc., languages where you need to "code your SQL brains out"). If you have created Stored Procedures for the Delete, Update, and Insert actions, map these in the DataWindow Painter. To do so, use the "Rows=Stored Procedure Update" menu item in the DataWindow Painter. The resulting dialog will allow the developer to map other various Stored Procedures to one of the three actions I just mentioned - including any necessary argument values. Now you have an object that will interact with your SQL Anywhere Stored Procedures effortlessly. Once created, the new Stored Procedure-based DataWindow object can be given to novice developers for easy use in their application.

Database Debugging
This is a weak area in PowerBuilder but the complimentary functionality in Sybase Central for SQL Anywhere is exceptional. If you are experiencing a problem with the Stored Procedure in the Database Painter or the DataWindow Painter of PowerBuilder, launch the Sybase Central utility and once connected to your target database select the problematic Stored Procedure and press the Debug Icon. You can then press the "Breakpoints" icon to set the necessary break and watch points for your Stored Procedure code. Once this has been done, you may use the ISQL facility in Sybase Central to run the Stored Procedure or return to the PB IDE and run the DataWindow or Execute statement in the DB Painter for that Stored Procedure. Either way, when the condition you set is reached, the Sybase Central monitoring of the Stored Procedure will invoke a dialog that will appear indicating that the Sybase Central debugger is now ready to begin a debugging session. If you select to proceed to the debugging stage, Sybase Central will appear in the foreground and you will then be able to introspect the Stored Procedure variables, inputs, outputs, and result set items while stepping forward, backward, over, through, etc., the Stored Procedure code. This facility is a real time-saver for these complicated Stored Procedures, which can be hard to debug, as it places print statements throughout the Stored Procedure code.

It should be noted that once you have completed debugging the Stored Procedure and would like the balance of the Result Set to continue back to the client, you need to exit the "debugging" mode in Sybase Central (see Figure 6). To do this, select the "Design" toolbar icon in Sybase Central. This should now deactivate the bugging session and allow the balance of the result set to be transmitted. Until this happens, however, don't worry that your PB or ISQL session has been frozen in the meantime.

Stored Functions
The use of database functions may also be popular in your organization. Again, the ISQL session pane in the PowerBuilder Database Painter or Sybase Central can be used to create, modify, and delete database functions in the SQL Anywhere repository. This process is identical to the Stored Procedure section above and you'll notice that the DDL is a "Create/Alter FUNCTION name as" versus a "Create/Alter PROCEDURE name as" syntax. The functionality can be very similar to a Stored Procedure except a Stored Function returns a single data element or row versus a complex set of types and rows (like a Result Set) for a Stored Procedure.

Once the Stored Function has been created, you may test the Stored Function in Sybase Central or using PowerBuilder's Database Painter by adding a Select statement like "SELECT "DBA"."fn_cip_test"();" and then pressing the Execute button. Your Stored Function should return a value in the "Result" pane of the Database Painter. Again, if the Stored Function is having some processing problems, return to Sybase Central and set up the appropriate debugging actions. Then run the Stored Function again and walk through the Stored Function code to locate the problematic section of the code when the debug becomes active.

Similar to executing a Stored Procedure, you may add values within the brackets right after the Stored Function name to pass in optional required argument values. In your PowerBuilder application you may also treat a Stored Function like a Stored Procedure and declare it in-line within your PowerScript code. This allows you the maximum flexibility when passing in argument values and directing the result back to a program variable. PowerBuilder's SQL Painter can be called from your PowerScript coding panes to help you with the Declare Function statement. An example script might look like this for the Stored Function execution under program control:

String   ls_xml

DECLARE myFunc PROCEDURE FOR fn_cip_test ( ) ;
Execute myFunc;
FETCH myFunc into :ls_xml;
Close myFunc;

Tracking Database Changes
PowerBuilder has a wonderful feature for tacking changes to objects and that is its "built-in" source control mechanism. It also can extend this prowess to use any commercial Source Code Control (SCC) with such products as PVCS, VSS, and QVCS. For large business applications with complex processes and/or large teams of developers, this is a must-have functionality for the development team in order to track various changes. The key information usually tracked is by whom, when, where, and why the change(s) occurred. This information can be critical for recovering a component to a previous state if any recent changes have made the current version unstable.

Neither the SQL Anywhere DBMS nor its related Sybase Central utility possesses any SCC functionality. The ideal solution would be to marry PowerBuilder's SCM functionality (especially the free built-in one) with your SQL Anywhere database design, changes, and maintenance. This doesn't seem immediately obvious as PowerBuilder does not have a database "Target" to attach your SQL Script files to. You can create the DML, DCL, and DLL files from PB, Sybase Central, or your favorite Database Design tool but the approach I have often seen is to store these in a .SQL file within a folder structure located on a file server.

It is possible though to have PowerBuilder track these items for you. For this approach to work though, you need to think "outside of the box" and use PB's "web" capabilities. This probably sounds extremely strange, but in actuality it is logical as web components are typically composed of text files such as XML, HTML, CSS, and JavaScript. If we use a "Web Target" in PowerBuilder, it will track any file type including .SQL.

SQL Anywhere Web Target
When you open the application WorkSpace and target the business application you are working on within your PB IDE, you can create a new Web Target and attach this to your current WorkSpace. For SQL Anywhere, I would normally create an "SA10," for example, a web target, by using the "New=>Web Target" option in PowerBuilder. Any type of web target will do as these are again just a "collection" of flat files to PB. Once you have completed this step, you should see a new target type under your current WorkSpace. The next step will be to activate Source Code Control (SCC) and to do this you will need to perform this activity on the "WorkSpace" component's properties. In the WS property dialog select the "Source Control" tab page and then appropriate the SCC system (Native, PVCS, QVCS, etc.). PowerBuilder will present the optional SCC interfaces to you automatically if it sees that the SCC client is installed. Once you have selected an SCC mechanism, the rest of the dialog will activate and you can fill in the balance of the information required. In many cases I just use the free PB Native option and thus map the SCC location to a file server or a local back-up folder that I copy off the contents to an external device on a daily basis for recovery purposes (when required).

If you would like to compare versions of your work, you might like to activate the use of a "Differential" utility. I like to use the WinDiff utility from Microsoft as it comes with Visual Studio (Express is free) or you can download the WinDiff.exe for free from the MSDN website. Once you have this utility installed, you'll need to inform the PowerBuilder WorkSpace component where it is located. To do this you will need to use the "Advanced" button on the WorkSpace's property Source Control tab page (see Figure 7). This will take you to a dialog that allows you to map the location and parameters for the comparison utility. In my case the location is:

C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\Bin\WinDiff.exe %s %s

By adding the %s %s parameters to the EXE path and name, it instructs the DIFF utility to compare the current version of the file/component in your PowerBuilder IDE to the one last saved in your SCC project's work location. Once this mapping has been completed, you may close the WorkSpace Properties dialog.

In the previous section we spoke about creating SQL files from any of your Database Painter work by using the "Activity Log" of PowerBuilder. You can also use Microsoft's NotePad, the PB NotePad feature, etc., to create an SQL file with your database schema statements included. Once you have completed the contents of the SQL file, just save this to your current PowerBuilder application's work folder. To have the SQL Anywhere Web Target pick up this file initially, use the RHMB on the SQL Anywhere 10 Web Target and choose the "Import Files" option from the resulting pop-up menu, then select the .SQL file from the next dialog. You should now see your SQL file logged under the PB Web Target. From here we need to register an initial copy (state) that you're the SCM. In PowerBuilder, just RHMB the SQL file in the System Tree and select the "Add to Source Control" menu item from the resulting pop-up menu. The SQL file should now have a green dot beside it indicating that the SCC software is now in control of this file.

To make changes to the SQL file, you can now treat this like any normal PowerBuilder component change, so we will need to first RHMB the file and perform a "Check-Out" request. Once checked out, the SQL file will show a green check mark beside it. By double-clicking the SQL file, the PB NotePad editor will appear and you can make the necessary changes (or solicit the DB/SQL Painters for help in doing so). Once your changes have been completed, save your work back to the SQL file. To view the differences between the original and your current work-in-progress, RHMB the SQL file in the PB System Tree and select the "Show Differences" option in the resulting pop-up menu. This should now show you the current changes to the SQL script and how it differs from the last version (see Figure 8).

If your latest changes are correct, process them in Sybase Central or within the Database Painter of PowerBuilder. Then, use the RHMB on the SQL file in the PB System Tree and select the "Check-In" option to save the current SQL statements in the SCC. When using commercial SCC packages, you may be optionally prompted during the Check-In process to provide required comments as to the nature of the changes.

For other developers or DBA staff, they can see the historical information by selecting the "Show History" menu option when the RHMB is used on the SQL file. This will inform the person of all the changes, individuals who made them, date/time of the change, etc. You can even request that any version be compared to any other version. So, for example, you can request what the SQL file looked like last year (three revisions ago) versus today's current copy. It's a really nice feature to have and it has saved me a few times too.