During the upgrade of an application from Sybase PowerBuilder 6.5 to SAP PowerBuilder 12.6, I ran into a bug in the Update event that had me suspicious that the TransactionObject was somehow changing, causing the update to be directed toward a different database.
As it turns out, the bug was due to a trigger in the main database that was selecting data from another database.
Even so, I learned about something quite valuable: the SQLPreview event.
If you set a watch on sqlsyntax, you can see the SQL code associated with the data update.
I’ve been upgrading an old Sybase PowerBuilder 6.5 app to a much newer version, SAP PowerBuilder 12.6.
During this little adventure, I’ve had to update much of the hard-coded SQL code that was originally written for SQL Server 4.2, and was later upgraded to SQL Server 2000.
This application’s database had been running on SQL Server 2008 since it was still compatible with SQL Server 2000 via the code below:
ALTER DATABASE appdatabase
SET COMPATIBILITY_LEVEL = 80
Since we’re about to hit Microsoft’s End of Life for any version of SQL Server that will allow compatibility with SQL Server 2000, any code which is not obsolete must be rewritten.
I knew that using “*=” and “=*” instead of LEFT JOIN and RIGHT JOIN would have to change. What I had never seen before is that using “= NULL” rather than “IS NULL” in a WHERE clause would no longer work. Why that is makes sense; it’s just that older versions of SQL were more forgiving for straying from the ANSI standard.
If you have an older program and you upgrade the SQL Server database and all of a sudden you get “no rows returned” on queries that should be returning data, this could be your problem.