Power to Build

Home » Posts tagged 'Sybase'

Tag Archives: Sybase

Sybase to Oracle conversion


Few years ago, I worked on a project where I had to convert close to 100 stored procedures from Sybase to Oracle. There are several differences between the 2 databases.

I was hired to be the Oracle expert in a team full of Sybase developers. I constantly worked with them to convert the databases and the code from Sybase to Oracle. I was asked to set up the environment, set the standards for Oracle coding and convert a lot of stored procedures. I participated in their migration project and eventually even supported their batch environment that ran in both Sybase and in the new Oracle environment. I even got a chance to work as a Oracle development DBA within the team. This gave me a lot of opportunity to learn about both the databases. I got to know their similarities (they both are SQL based relational databases, aren’t they?) and their differences and idiosyncrasies (Did you know, Sybase “Database” is the same as Oracle “Schema” and Oracle “Database” is the same as Sybase “Server”??).

There are a lot of sites out there discussing the differences and even migration from one to another. I benefited from a lot of those and I made a lot of notes at the time. When I was working on the project, I was so busy I couldn’t post all those notes online. Now, that I have some time, I am dusting off my old notes and posting here. I hope it benefits someone doing similar kind of migration.

Sybase to Oracle Database conversion

This is a much bigger topic than I intend to cover in this post. My take on the conversion is mainly from a developer point of view. The DBAs will have a lot more to add about the database setup and architecture. Though, you will see references to database level differences, as I did perform the duties of a development DBA within the team. Below presentation lists out some of the differences while coding a stored procedure in the 2 databases. I took one of the procedures we migrated and annotated it with comments. I made this for the team at the time. I’ve changed the names of the tables and procedures to be more generic.

Note about the convention in the attached presentation: I wrote a complete Oracle Coding standards manual for the Sybase developers. I am following this convention in the attachment below. In general keywords are upper cased, other identifiers lower cased. The names of identifiers follow certain naming convention. I will post about this separately.

What is Powerbuilder?

Powerbuilder is an IDE, an integrated Development Environment, a tool where a programmer do everything within it, including design and develop screens/reports, write command line programs, compile, deploy applications all from within Powerbuilder.

This is in contrast to programming in languages like C or Pascal, where you use a text editor to edit, a compiler to compile and copy files to deploy.

IDE’s and case tools were popular in early 90’s. There were several such tools that promised “less or no coding” and yet develop and deploy enterprise applications. Powerbuilder was one of many tools available. I remember reviewing PB, Visual Basic (VB), Gupta SQL windows and Oracle CDE (successor to SQL forms in windows). Of all these, I took to PB easily, I liked SQL windows better (I remember it was more colorful and windowy!). Oracle CDE (forms 4.0) didn’t measure up. And Visual Basic needed more programming.  I remember the days when VB and PB were constantly compared and contrasted. Each had it’s own strength but PB won in some of the corporate contracts. The datawindow, and the quick reporting capability were probably some of the secret.

Contrary to popular belief, it’s not a tool for non-programmers – managers, Business Analysts and the like. Yes, several tried, but any serious screens in PB always needed programming. To this day, I hear people brushing it as a child’s play in the world of complex Java and .Net applications. But PB is used, surprisingly, in building big corporate applications, even today. And such serious development activities always involve programming. Programming in a language called Powerscript. It’s basic with a twist. Long before Java became so popular, it took some tenets of C++ and became Object oriented. (OO pundits may not agree and call it more Object based than object oriented, but that’s a topic for another post!). Oh, about PB for managers, Sybase made another tool called Info-maker, a trimmed down version of PB, which didn’t require programming (or lacking programming, depending on who looks at it).

PB is definitely easier to use to build screens and rudimentary database applications. I feel, the user interface is definitely easier to use than that of VB, but then I’ve been using it for many years. The strength of Powerbuilder, as against other tools was and has been the datawindows, a compiled widget readily available to include in any windows. Strictly speaking, a datawindow is a window that may have many controls on it, but the difference is that a datawindow is managed by PB routines as against windows API. For this reason, Sybase always suggests to combine many different controls in a window into a single datawindow, wherever possible, for performance.

A datawindow widget is not much unlike ADO or DAO objects in VB. The strength of the datawindow is that it’s built around SQL (what’s called a datasource – there are other data sources, more later) and by keeping the SQL in PB itself and database neutral, we could make the same datawindow work with many different databases, like Oracle, Sybase, Informix etc.

PB has changed a lot over the years. People who have seen it in the 90’s will definitely realize the transformation. The IDE itself started packing more in version 7.0. Now, in version 10.x and beyond, it’s turned into a true development tool. (Rumor has it that PB 12.0 could actually be used as an IDE for .Net development!). The question still remains, in the world of Eclipse and Netbeans, Java and .net, why PB? That too enterprise edition costs a bundle when Java is all free. This blogger aims to ask some of those questions and try to find answers live on this blog.

Currently I am working at the City of LA where we use Powerbuilder heavily in a clustered EA Server environment. We even have extensions on the web using JSP and Coldfusion. The software is a tax software used by thousands of businesses in and around LA. This is no child’s play. EA Server is to Powerbuilder like weblogic to Java. It’s an application container that can run PB objects and Java objects alike and it uses CORBA (how much it reminds me of Forte UDS!).

From the simple client/server tool, PB has reinvented itself several times to become a n-tier platform here. Earlier PB turned out a non-visual object (NVO) that proved vital to this n-tier platform. An utterly client widget, datawindow, gave birth to (non-visual) datastores that eventually helped them to be n-tier objects as well.

But then again, I feel, Sybase lost several opportunities to build it up and market it as the greatest tool on earth (I don’t mean exactly that, but at least the ads should say it, right?).  They are constantly toying with the ideas and drop them like a fly. Most recently, the EA framework, built by Cynergy Systems for Sybase, that can be used to build Enterprise applications in PB/EA Server environment has been discontinued. EA Server itself is dangling. You hear about PBNI, PB ORCA, I’ve used them too, but their future is always questionable. In the world, where every version of a software gets better with time, some of these tools inside PB appear and disappear. With PB becoming an IDE to .net, their neutrality to languages may be disappearing. And finally, with SAP buying Sybase recently, the whole existence of PB as a tool may be in jeopardy. (You think this is not possible, think what happened to Forte UDS. Sun bought this company only to kill the software, as it was a competition to Java J2EE at the time. How did they do it? They created a lousy tool called Forte for Java to erase the name Forte and eventually pulled the plug on many websites that talked about the old Forte).

We will go on a journey here in  this blog, where I will not only share my experiences with the tool, but also technical articles, how-tos about PB.

My Journey with and without Powerbuilder

As I mentioned in my previous post, I worked on Powerbuilder (PB) for a while, went away and finally came back to using it. I came  in contact with Powerbuilder in the early 1990. I was working for a Consulting company and we evaluated several products to port an existing applications written in B-Trieve/C and some others in d-Base.

The version I came to see was ver 3.0 and it really took me by surprise. In those days, writing windows programs was not easy. Anyone written programs in C/C++ programs would understand. I also dabbled in a few tools, including C and Turbo Pascal. Remember all those resource files you had to do, to make a screen? PB (and few other tools of the day) changed all that. It was called the 4GL (4th generation language, as against C which was a 3GL language). These were the times when CASE tools were also becoming popular. See this wiki article for more on those.

So, PB naturally had a lot of competition. Microsoft Visual Basic, Gupta SQL, Oracle forms for windows (CDE) were there to name a few. We did try each of these, but PB fared better than the rest. (Of course Oracle forms was there already, which was popular and did the job, but it wasn’t a windows application builder. It’s cousin Oracle CDE didn’t do a good job at the time). Powerbuilder had something unique that made it win that round of the competition, the datawindows.

I joined one of the medium sized consulting companies in 1994. They got me into a shipping company, APL, in Oakland, CA. The company was porting their mainframe programs to client server world using Powerbuilder/Oracle. It was an ambitious plan, but it definitely looked glamorous (Kind of what mobile apps are to the current generation). With only some exposure to the product, I joined APL as a PB expert. All those mainframe programmers felt threatened and wanted to learn PB from me as quickly as possible to keep their jobs! Legacy has to go and the glitter of client/server was in! I saw several legacy products getting migrated from mainframe and other platforms to PB. There were products to bring mainframe data into PB screens including a “screen scraper” from Mitem View!

At that time Powerbuilder was owned by a company called Powersoft. It was a cute Client/Server product that allowed one to create windows screens that would connect to a database server and these were to replace those complex mainframe applications. It was like gold rush! For over 5 years, PB was ruling the corporate world. And like the gold rush came the bust as well.

The year was 1999. Sybase acquired PB by then. By then PB projects were dwindling down, and PB links on the web started disappearing on the web.  Java was in, at several of those places that went into PB earlier. Client/Server was not going to cut it any more and n-tier was the word. They did come up several contraptions to put themselves in the n-tier world, but none really succeeded back then.

I was in Toronto, trying to win a PB project at the time. I did some prototypes, but the local Sybase developers undercut us. They came with the just released PB 7.0, which we hadn’t seen. Their prototype and PB, both included a Treeview structure (sorta what you see in windows explorer). That was end of PB career, or so I thought at the time. I left the PB scene like 1000s of other programmers seeking greener pastures. Java came to the rescue, I also had an opportunity to get to know a great product called Forte. (Forte was a truly OO and n-tier product from the get go. More about it in another post).

Then, came recessions, job losses. In 2004, surprisingly, PB came to my rescue in an unsuspecting place in Fremont, CA. That kept me going a bit more and I branched into Oracle world. And another recession brought me back to the PB world again.

I recently took a job at the city of LA, in supporting and enhancing their Powerbuilder application running against EA Server.

In the world of short lived technologies and dying programming tools, I am amazed to find this product not only surviving, but evolving into what it is today, Powerbuilder + EA Server (Jaguar). In the upcoming posts, I will share some of my experiences and views about the tool!

Born Again Powerbuilder

I am a born again Powerbuilder (developer)! That’s right I program in Sybase’s Powerbuilder, again!

Like many I also left it sometime in 1999 when it looked like fading. But, Powerbuilder has proved it’s worth and reserved it’s place and pace in the ever growing world of Java, C++ and .Net. It has not only survived the onslaught of new technologies, but I feel it is coming out of hibernation.

To those who don’t have a clue as to what Powerbuilder is, it’s a client server development tool that a small company called Powersoft brought out in the late 80’s. It had it’s hay days in the 90’s during the hysteria in corporate world to migrate out of “legacy” mainframe applications to Client/Server. Few of us, that knew Powerbuilder were the “new kids on the block” and the new order priests. But it all dried down soon after Sybase took over. By then internet programming took over and the rest is history.

Now, Powerbuilder is becoming “legacy” application itself and I know what those Mainframe programmers must have felt when we “Client/Server” programmers arrived. One consolation: Powerbuilder did not die while competition like Visual Basic, Gupta SQL and Oracle CDE slowly disappeared in the corporate scene, either naturally or because of company policies. At least, it has been a recession proof job finder for me.

If anyone tries to search “Powerbuilder” on the net, they may be up against broken links and/or old contents. There hasn’t been any good books written on the subject and while it seems to be dying, you also hear about Sybase bringing out the Powerbuilder ver 12.x and Pocketbuilder etc. They also silently integrated Java into their product line.

I recently took up yet another Powerbuilder job maintaining a PB 10.x application using EA Server 5.5. I intend to share with you my experiences and my love-hate relationship with the product.

Hope you find this useful.