-IF (@ll_value = 1)
SELECT @ll_col=col FROM table
-IF (@ll_value = 2)
BEGIN
SELECT @ll_col=col FROM table
SET @ll_col=@ll_col + 1
END

-SET @ll_col = ISNULL(@ll_col, 0)

The dba/dbo thing was easy to fix in our PowerBuilder code, but it turned out that "dba" turned up in some strange places. For example, it was in some embedded SQL:

SELECT cola, colb, colc FROM dba.table

as well as some DataWindows. The most annoying place it showed up was in the PBSELECT statement where it appeared as ~"~~~"dba~~~". If we weren't careful, we would remove the preceding ~", which caused the statement to fail. Of course, we also found "dba" in our triggers, stored procedures, and function where we simply removed it.

ASA was nice enough to remove the semicolons from our statements and prepend the @ symbol to our variables. It also commented out the items that were not used in Transact-SQL, such as "FOR EACH ROW", SQLSTATE, and some of the looping syntax. It didn't, unfortunately, provide alternate syntax for the conversion of ROW level triggers to STATEMENT level triggers. For that we had to create a cursor to select from the two temporary tables that were created: INSERTED and DELETED:

DECLARE cur_insert CURSOR
FOR SELECT inserted.cola,
inserted.colb,
inserted.colc
FROM inserted

INSERTED represents the new rows created by the last SQL statement and DELETED represents the old rows created by the SQL statement. For an INSERT trigger, only new rows exist, so the cursor will only select from the INSERTED table. For a DELETE trigger, the reverse is true. That is, the cursor will only select from the DELETED table. For an update trigger, both the INSERTED and DELETED tables exist and thus the trigger must reference both:

DECLARE cur_update CURSOR
FOR SELECT inserted.cola,
inserted.colb, inserted.colc, deleted.cola,
deleted.colb, deleted.colc
FROM inserted, deleted
WHERE inserted.keycol = deleted.keycol

We found that two triggers with the same name cannot be opened by the same user at the same time. For that reason, we've taken to appending the name of trigger to the cursor name:

cur_tia_po_dtl_update

Once a cursor is declared it can be opened and used in a manner similar to Watcom SQL:

DECLARE cur_test CURSOR
FOR SELECT id,
name
FROM deleted
OPEN cur_test
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
...
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
...
END
close cur_test
deallocate cur_test

Notice the second fetch in the example above? This is required because the first fetch appears outside the loop. The construct works well in small, confined loops but fails if there's a need to exit the loop prematurely using a continue:

WHILE (@@FETCH_STATUS = 0)
BEGIN
...
IF (@ll_id = 2)
BEGIN
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
CONTINUE
END
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
END

The extra fetch is required because the continue restarts the loop, bypassing any remaining statements. Without the fetch before the continue, the loop will reevaluate the same row in the cursor indefinitely. Since our triggers tend to be very large, we've taken to using a different construct:

OPEN cur_test
WHILE 1=1
BEGIN
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
IF (@@FETCH_STATUS <> 0)
BREAK
END

This construct means that we only need one FETCH and we can break out of or restart the loop at any time. We also started using this construct because of one of the other differences between ASA and MSSQL: cursors in MSSQL are not implicitly closed. That is, leaving a SQL script before closing the cursor neither closes nor deallocates the cursor. That's left up to the developer:

OPEN cur_test
WHILE 1=1
BEGIN
FETCH NEXT FROM cur_test
INTO @ll_id,
@ls_name
IF (@@FETCH_STATUS <> 0)
BREAK
END
CLOSE cur_test
DEALLOCATE cur_test

Some of the more subtle differences actually became apparent when we tried to convert our DataWindow SQL to use the Transact-SQL concatenate symbol (+). The first DataWindow we tried to convert contained inner and outer joins. Since Transact-SQL does not support key joins, we converted the SQL to use the standard join syntax:

tablea left outer join tableb on tablea.column = tableb.column

It was in this conversion that we encountered our first subtle difference. The SQL included a retrieval argument and, after modifying the syntax and even running it in MSSQL and ASA, we received an error that looked something like this:

The column prefix 'table_name' does not match with a table name or alias name used in the query.

The problem is that this error has absolutely nothing to do with the actual problem, which is that retrieval arguments and outer joins don't mix unless you add the DisableBind=1 parameter to the DBParm property. We also found that unless the Call Escape Syntax parameter was off and the Strip Parameter Names parameter was on, we could not retrieve into a DataWindow through a stored procedure. In the end, our connect string looked like this:

"ConnectString='DSN=MSSQL', CallEscape='No',StripParmNames='Yes', DisableBind=1"

As a side note, we also found that it's a good idea to copy the SQL prior to returning to the DataWindow painter design mode. This is because the error actually occurs in design mode and the original SQL is restored, which removes any changes you've made.

Speaking of joins, we found that MSSQL does not approve of straight joins that use the inner/outer join syntax with a retrieval argument. This error appears only in PowerBuilder DataWindow SQL:

SELECT tablea.col1,
tableb.col1
FROM tablea join tableb on
tablea.keycol = tableb.keycol
WHERE tablea.col =: arg

Again, the error message is not related to the problem (or at least the solution):

The column prefix 'table_name' does not match with a table name or alias name used in the query.

In this case, the solution is simple: translate the join into more conventional syntax:

SELECT tablea.col1,
tableb.col1
FROM tablea,
tableb
WHERE tablea.keycol = tableb.keycol
AND tablea.col =: arg

Along related lines, we also found that MSSQL does not support referencing derived or computed columns by name within the SQL. For example, consider this syntax:

SELECT cost * qty as tot_cost,
tot_cost * tax_rate as tax_amt,
tot_cost + tax_amount as total_amt
FROM sales

In ASA, the above statement is perfectly legal, but MSSQL will tell you that total_cost and tax_amount do not exist in the SQL statement. In MSSQL, the statement would be written as:

SELECT cost * qty as total_cost,
cost * qty * tax_rate as tax_amount,
cost * qty + cost * qty * tax_rate as total_amount
FROM sales

In ASA, computed columns could also be referenced by name in the group by and order by clauses. We found that although the MSSQL documentation claims you can reference them, statements with derived columns in the group by or order by clause fail. Fortunately, we can remove them from ASA and our SQL performs in exactly the same way as it did before.

When converting SQL syntax embedded in PowerBuilder, one of the biggest challenges was to make sure that the SQL would run on both ASA and MSSQL. For the triggers and stored procedures, we didn't make the distinction because the SQL was on the database. As a general rule, if the SQL would run in MSSQL, it would also run in ASA. One area where this is not always true is functions.

As noted earlier, there are several functions in ASA where there is no MSSQL equivalent. In some cases, the workaround is simple: LTRIM( RTRIM( ) ) for TRIM(). In other cases, LEN() and LENGTH(), for example, the solution was not so obvious (remember, our application has to run against both ASA and MSSQL). We could have created a stored function, but then we'd have the owner name (dbo versus dba) to deal with.

Another more obscure issue is that of modulo arithmetic. In MSSQL, the modulus operator is a percent sign: %. In ASA, the percent sign is a comment marker. Fortunately, you can turn this functionality off in ASA using the SET OPTION syntax:

SET OPTION PERCENT_AS_COMMENT=OFF

which turns off the option for the current user in the current session. You can also change the option permanently by following the above SQL with this:

SET PERMANENT

Or make the change through the database options interface in Sybase Central. Keep in mind that this change may have other ramifications.

Of course, both the modulo arithmetic and the function name issue could be handled in PowerBuilder by creating two different SQL statements, depending on the database. In this case, I would create a transaction object hierarchy and instantiate the appropriate object at runtime, then let the transaction object do the work:

// First Destroy the current instance of SQLCA (a descendent of n_tr)
DESTROY SQLCA

// Now instantiate the appropriate type of transaction object
// (assume ls_dbms has been populated earlier)
IF ls_dbms = "MSSQL" THEN
SQLCA = CREATE n_tr_mssql
ELSE
SQLCA = CREATE n_tr_asa
END IF

All three classes, n_tr, n_tr_mssql, and n_tr_asa, will have similar functions to handle converting the SQL, but each will have their own specific syntax. The instantiation needs to happen only once, at the beginning of the application. After that, we can reference the specific functions generically:

ls_sql = SQLCA.of_ProcessSQL( ls_sql )

which would make the appropriate conversions to convert ASA syntax to MSSQL.

Are there other differences? You bet, and as I encounter them I'll report on them. Until then, happy converting!