Sybase APT to PowerBuilder Conversion

Sybase APT to PowerBuilder Conversion

I just completed a migration project. TXL from http://www.txl.ca the wonderful Source Code Transformation tool played a major role in my effort.

Source Platform:

OS - SUN Solaris

RDBMS - Sybase 11

Forms - Sybase APT - CUI vt220 toolset

Reports 4GL - DWB (also known as RWB)

Reports 3GL - DBLIBRARY/C

Target Platform:

OS - Windows

RDBMS - MS SQLServer

Forms - PowerBuilder

Reports 4GL - PowerBuilder

Reports 3GL - DBLIBRARY/C

Background: This was a poorly maintained application and it took me a lot of time to identify the active from the inert objects. I chose Microsoft SQL server as it was closest to Sybase. The best reference on this is

"Making the Move from Sybase to SQL Server"

http://www.sql-server-performance.com/sg_from_sybase_to_sql_server.asp by Sayed Geneidy

http://www.anylex.com/ from AnyLex/Ellipsys is the only tool I could locate that Sybase Migration but it does not do RDBMS, RWB, DBLibrary - only APT and that too Groups are not made into DataWindows. However I could guess that they must have used lex and yacc and this led me to study a lot of parsers. After 2 months of evaluation I decided on TXL. I visited and studied all the web sites like ANTLR, Javacc, JFlex but had no neat solution for tree transformation and unparsing. Until I finally hit TXL.

TXL is the best possible tool for source code transformation. Prof. Jim Cordy - the driving force behind this tool was very kind and took me through his lessons.

SybAPT.Grm & SybaseSQL.grm are 2 grammar files used in APT2PB.txl to change Sybase APT fpls to Powerscript. fpl is a APT code snippet. I wrote some code to merge all fpls into 1 file. Then using napt2pb.bat I converted fpls to Powerscript. I did some manual visual inspection of all else and moved comments "near" else out. The reason is TXL version used did not preserve comments. But comments are the very heart of software! So I used a simple gawk trick to convert existing comments including nested comments etc to APT print statements. This was later edited back to PowerBuilder comments. http://unxutils.sourceforge.net/ has gawk.exe.

ppljobreg_1.fpls.txt is a sample input and ppljobreg_1.pb7s.txt is sample output. Without TXL I would be dead!

I used sql.txl as a Sybase SQL pretty printer! This too was very useful to understand and debug Sybase SQLs. Some very huge ugly SQLs esp. in DB/Library/C looked extraordinarily beautiful by simply passing through TXL.

The grammar is NOT my sole effort. there are a few public flex, antlr, javacc samples that I read and used. I got a good vanilla SQL grammar from http://examples.oreilly.com/lex/ By John Levine, Tony Mason, Doug Brown ( http://www.oreilly.com/catalog/lex/ )

I translated to TXL and then modified to suit Sybase 11. I have added APT grammar & PowerBuilder grammar - bare bones. Good enough for my project.

frm2srw.bat uses frm2srw.awk.txt to convert a Sybase frm to PB7 srw. But I changed all Groups to Datawindow Grids manually. Thereafter I plugged in the TXL output into PB7 events. The resultant software is maintainable and looks neat and almost original! No sense in doing transformation that is not maintainable. 99% of the APT forms used non-nested Groups. The rare nested groups I did by 2 coordinated PB7 datawindows.

SybRepSQL.sql & SybRep2PB.sql were used to create Powerscripts from Sybase DWB/RWB reports. This plugged into a template RWTMPLT.txt to migrate Sybase Reports.

dsyb2ms.bat & dblib.awk.txt were used to do 99% of the edits to make Sybase DB library C Microsoft compliant.

All files mentioned are available in http://geocities.datacellar.net/ojnc in SybAPT2MSSPB7.zip

The above is a brief summary of months of labour in this project. I did a similar job of migrating VAX/RDB/RALLY to Unix/Oracle/D2K but then I did my own transformations using VB6 & regexp. Subsequently I did a Oracle ProC to PL/SQL converter in Java & regexp. But my earlier efforts were at best heuristic. I did a lot of tricks to transform deeply nested while/if/else/for and it worked. But having tasted TXL I know this to be nearly flawless. My homegrown regexp methods always preserved comments. In TXL I used pre and post gawk scripts to preserve comments.

I have decided to "move my cheese". Now I am 100% into ERP esp. SAP but I do Oracle ERP infrastructure as well because of my strong Oracle D2K background. SAP is of course the best ever! SAP as me totally captivated. From ABAP, ABAP Objects, CCMS, CTMS, WorkFlow etc. SAP ERP is an amazing marvel of software engineering.

I have made up my mid - SAP or nothing! Migrations are boring and too demanding.

My habit of taking varied assignments is costing me dearly!

But adversity can always force changes -

"The best-laid plans of mice and men often go awry"!

I would strongly recommend TXL from http://www.txl.ca to anyone contemplating migrations. No other tool does tree transformation and unparsing as an integral part.

But you need to use gawk or Perl or any such tool to supplement and complement. 70-80% effort will be tool based. 20% to 30% should be hard work if you are serious of creating a robust application that is maintainable and is truly infused with a new lease of life.

Sybase to Microsoft SQLServer traps besides http://www.sql-server-performance.com/sg_from_sybase_to_sql_server.asp

select * from t1
where i = NULL

Above SELECT OK in SYBASE BUT NOT in MSSQL:

in MSSQL you HAVE to say where i Is NULL

SYBASE select substring('abc',4,1) gives NULL

MSSQL select substring('abc',4,1) gives '' ZERO length string which is NOT NULL

Sybase is case sensitive but MSSQL is case-insensitive

PowerBuilder is case sensitive

In SQL there are many issues but the worst one is

- SYBASE allows (non standard)

select distinct a, b, sum(c), sum(d), e, f
from t1
group by a, b

- MSSQL Code to do exactly above is (using in-line view)

select distinct ft1.a, ft1.b,
       st1.c,
       st1.d,
       ft1.e, ft1.f
from t1 ft1, ( Select a, b, sum(c) c, sum(d) d
                   from t1
                  group by a, b ) st1
where ft1.a = st1.a
and ft1.b = st1.b