One of the most frustrating but critical areas to troubleshoot is the realm of database consistency, since it often involves two tiers and functionality that PowerBuilder typically lets you treat as “under the covers” that you’ll never have to look at.
Row Changed Between Retrieve and Update
This is a common and potentially frustrating message that is supposed to be used by the DataWindow’s logical record locking. The intended use is that when another user has updated a record you have already retrieved, you won’t be allowed to update the same record without re-retrieving it. While the WHERE clause in the Update Properties of the DataWindow defines the degree of logical locking implemented, and changing this property may make the symptom go away, changing it away from a more liberal level of logical record locking may be a quick fix that avoids understanding the problem and will likely cause other problems in the future. A change in the level of logical record locking should be made with careful planning and made universally.
The basic cause of this message is:
- the DataWindow has generated an UPDATE statement
- the WHERE clause generated for the UPDATE statement fails to match any records on the database
- the WHERE clause is based on the option selected in the Update Properties dialog and on the values that the DataWindow believes are on the database
The first thing to examine when you get this message is to ensure that it doesn’t actually reflect a concurrency issue. Things to check for concurrency conflicts include:
- other users
- other applications
- batch processes
- other windows or processes within a single instance of your application
Insert or update triggers that affect columns used in the WHERE clause will cause this problem. If a DataWindow generates an INSERT or an UPDATE, the DataWindow will continue on with the understanding that the values that it last sent to the database are on the record in question. The next time that DataWindow generates an UPDATE statement to modify that record, it will use those values in the WHERE clause. If a trigger has modified those values, the WHERE clause will not match the record.
This is the most common problematic cause of this message that I’ve seen. If a value in the DataWindow is changed and the status is changed to NotModified through PowerScript functions, then the new value is going to be used as the row’s original value in the WHERE clause. Other considerations should be given to programmatic manipulation of status flags, and the effect they have on the SQL that the DataWindow generates.
The most common objective I’ve seen when developers try to manipulate status flags is actually trying to imitate what the Initial Values attribute of a column does natively. When Initial Values on one or more columns is set, and a row is inserted into the DataWindow, those values are set in the DataWindow columns, but their status flags remain NotModified! until the first column is changed. At that point, all columns with Initial Values set are changed to Modified! so that they are included in the generated SQL statements. Using this, either setting Initial Value at design or run time, you can set up default values for columns which, by themselves, will not trigger the generation of a SQL statement on Update(), but will be included if the user modifies a field so that the rest of the row requires an INSERT. Besides simplicity, this approach has an advantage over flag manipulation of not influencing the original values buffer, which many developers will do inadvertently.
DBMS [X] is not supported in your current installation
This is a common message after deployment. This happens when your application is trying to connect. What PowerBuilder is doing at this moment is getting the first three letters of the DBMS attribute of the transaction object, and trying to find the DLL PBXXXY0.DLL, where XXX is the first three letters from the DBMS attribute, and Y is the PowerBuilder version number. This is the PowerBuilder database driver for that particular DBMS. When PowerBuilder can’t find this DLL, it throws this message. This usually means one of the following:
- The appropriate PowerBuilder database driver hasn’t been deployed
- The database driver can’t be “found” by the application (see the discussion of application paths)
- The client software for the DBMS that the PowerBuilder database driver is supposed to interact with cannot be found or loaded
- The DBMS attribute of the transaction object either hasn’t been populated or has been incorrectly populated (usually a problem with finding an INI file)
Monitoring the SQL that a DataWindow generates should be key in monitoring the SQL that is being sent to the database (most of your database access is being done with DataWindows, one would hope). Putting a null operation (integer i; i=1) and setting a breakpoint on it will will allow you to view the SQL being generated. This is yet another place where having a common DataWindow control ancestor, and a common datastore ancestor, will be an advantage, as you’ll only have to set one or two breakpoints to be sure you’re catching all the SQLPreview events. If you don’t understand how the DataWindow is including certain columns or getting certain values, put in debugging code to dump values from the primary (or more specifically, the buffer argument to the event) and the original buffer, as well as the status flags.
Database tracing, as described in the Troubleshooting chapter of the Connecting to your Database manual, takes things up a couple of notches. Not only does this trace embedded SQL, but it traces SQL that is automatically generated by PowerBuilder and is left as transparent to the PowerBuilder developer. The database trace should be a log of everything that is passed to the database client software.
Server Side Tools
While PowerBuilder gives you a complete picture of what gets generated in and passed out of a PowerBuilder application, it shouldn’t be viewed as a complete picture. What is most important is what the database receives. If what the database receives doesn’t correlate to what PowerBuilder is sending, an examination of DBMS client software and server settings may be in order. You should become familiar with the tools your DBMS offers.