The ABAP Detective Sequel

[JimSpath]

For several years, I've been sharing my ABAP Detective case files from the perspective of an agent on the force, with side helpings of sarcasm, allegories, and attempted puns.  As any fictional hard-boiled film noir or pulp detective will tell you, getting kicked out of the department shouldn't be unexpected, if you have an independent streak. So here I am, hanging out my shingle as the sole proprietor of the firm yet to-be-named.  Hence the post title "Sequel".  And the pun to follow.

Though I wasn't going to go back to the land of cubicle walls anytime soon, I needed to find an easy case to work, whether I got paid or not. Moping around, hitting the office bottle and swatting flies may sound like the life of an investigator, I had to hit the bricks. Perusing the want ads, I spotted an expert wanted poster, SQL style.  I thought this might be my lucky day; I can use my hard-earned enterprising software skills to help out the geriatric set, which as it turns out is the audience of this quasi-governmental non-profit.  I aced the interview, apparently.  Oh wait, that was Oracle, not Microsoft (MVP).

When I dove deeper into the job (we'll call it a job even if it's pro bono), I found the goal interesting: to move an application developed a decade ago on the freeware OpenOffice platform to use a MS SQL Server backend database.  At this point you may be wondering what this case has to do with the SAP software world (where you're reading this, hopefully, not in a purloined letter copy).  Aspects of project management, software application testing, master data management, and, of course, buried SQL coding have similarities across the spectrum from giant intercontinental presence to the small shop I'm helping now.

Let's start with documentation, such as it is, when an analyst walks into the middle of a project like this ("let's throw out the old tested code and bring in new shiny untested code" "Yay!").

You look at a running application and think, how quaint.  As someone described in one of the many rocks I turned over trying to get the lowdown on this caper, form buttons in mid-1980s style, as well as technical features others have outgrown.  The documentation I found was more than expected (frankly I expected there'd be none, and it wasn't bad, but left a lot out). One paper was called "User Guide" and consisted of pasted screen shots and a few useful hints.  The other was called "Developer Guide" and had its share of screen caps plus a mixture of good technical clues and useless gibberish such as "Enter Aliases if desired. This will change the column name displayed in your query." Right, if I wanted an alias, I'd go downtown and ask for a guy in a black hat and many pockets.

So, documentation needs to be updated to fill in missing pieces (yeah, a picture of the database field names and sizes is great; can I get that in TEXT format so I can CODE new structures?). What I am supposed to do has broken down into two unequal halves - the first are bare minimum steps to move the backed DB from OO to SQL, and the second being "out-of-scope" items that would be really nice to have but that won't allow for verification that the new app does the same as the old app.

For me, it was a relief that the documentation I found was mainly in OpenOffice text file formats, to go along with the database itself. I added new files in the OO Drawing format.  A new colleague asked why I was retyping the database field names instead of copying/pasting and I explained that's one way I can remember the parts better.

Taking a look at the application "stack" (it's more like an unfinished puzzle but no matter), there are tables, queries, forms, and reports.  Seems fairly elementary.  What's not visible on first glance, among other buried treasures, are the "macros" which pass for programming logic in this app.  Called BASIC, they bear a striking resemblance to code I used in the 1970s. <shudder>

My goal is to complete the move of the tables from inside the OO database file to use MS SQL tables in an external database.  For this change, I probably would have picked mySQL instead of the other one but that bridge had already been crossed.  Keeping the customer happy and comfortable with the product even after you leave does wonders for the resume, not to mention state of mind.  Too many other walls to bang my head on. Checking out the DB tables, I found a few oddities, like different column names in the same table between old and new.  Looks like someone had taken a stab, gotten partway through, and then couldn't continue.  Anyone who's picked up someone else's coding has had similar experiences. No doubt someone on the force (or more than one) is cursing my cryptic logic or lack of documentation on my legacy code.

I tweaked a couple tables, tried to unload and reload data from old to new DBs and realized a few things quickly.  First, I didn't know what I was doing.  I've used OpenOffice database, but not deeply, and definitely never coded macros or set up multi-table joins on screens.  It's one thing to roll out your own design based on what you know, quite another to reverse engineer what someone else wrote over a decade ago, where they apparently pulled in bits and pieces of "best practices" I've never heard of.

Loading data from the old system revealed small surprises, such as purported date fields being text (so one could say "Mon-Fri" instead of picking one start date).  Not a big deal except the MS SQL data imported would decide they field type was date, then fail to load when unrecognizable data showed up.  I had been told the command line loader "BCP" needed to be avoided.  Now I know why.  The GUI based loader mostly works, except where the input file is supposed to be CSV and is skewed with extra single or double quotes, extra line feeds and other glitches.  After I got enough data for meaningful tests I moved on to forms and queries.

 





What you won't likely find, even in well done documentation, are the institutional knowledge around data rules.  For example, I noticed in the "Check Number" field in one table the note "Early", after seeing that logic in a query.  Turns out early subscribers get a bonus "gift"; their identity is flagged by specific payment details rather than a standalone field. I could add this to the new documentation but who'd believe me?

Forms have turned into a morass of sorts.  Like transactions (T-CODES anyone?) in classic SAP GUI apps, just because the logic looks obvious (read: easy to port) doesn't mean that at all.  I found forms that directly accessed a table, while others accessed a query.  Tinkering with views, I learned that the OO app won't recognize those objects (unlike most other SQL statements I've used over the years that were fine with selecting from a table or a view - insert/update is another tale) so I was stuck with queries.  The downside of those is they stay on the PC in the app (the DB file) and can't be pushed down into the database.

Multi-table forms (showing header and line items, for instance) sort of worked with an OO frontend and a SQL Server backend,  But I struggled with days where I hit messages such as "invalid descriptor index" before understanding how to deal.

 

The contents of a combo box or list field could not be determined.

SQL Status: 07009

[Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

 

The error text doesn't leave anything to hang your hat on, as there is no source snippet one might see in an ABAP DUMP (code near here ">>>>"), and there is no indication what a descriptor index is or does.

Thought I found one answer, going back a decade or so, that indicated the sequence of fields in a query (fetch) needed to be in the exact order that the data dictionary had (field 1 is "ID", etc. - "Columns must be retrieved in increasing ordinal order.")  I even fooled myself into thinking that was the answer, until the issue came up again shortly after. Toss that theory in the bit bucket.

One more clue that almost worked was buried deep at the end of a forum thread.  I'll include the link here, as it's the classic offhand Columbo-type remark that you'd miss if you weren't paying attention ("Oh, one more thing..."). "Replace named parameters with '?'"

JDBC connector with subforms do not work, when there is no value in mainform

I'm using ODBC, as it turns out, not JDBC.  The error relates to forms and subforms but the setting on named parameters is defined to the database connection level, so that SQL cursor buffers can be used with different databases (again, what SAP ERP did as best of breed before the hana era).

Another surprise error, after thinking the above was the fix, set me back to the drawing board.  Existing joins worked, sometimes.  I rolled up my sleeves and set about building my own joins from scratch, to learn whether the joins would work at all.  Initial attempts showed me the depth of my ignorance - either the detail records were blank/missing or all records for all headers showed up instead of just the one.



I downloaded example databases (special thanks to the person behind sheepdogguides.com for basic elementary tutorials with useful content, instead of regurgitating the online manuals.  The Harry Potter world examples were funny and insightful) and proved to myself the joins between header and detail tables would work correctly with MS SQL behind OO forms.

Armed with this knowledge I decided throwing out the old forms and building them fresh was the only way to move forward.  Too many unknowns in the existing logic, and as long as the users can do their work, all to the good.  I added a multi-row form in the example screen shot above so that instead of "arrowing" from one sponsor to the next, you could "scroll down" quickly.  Such are the advantages of throwing out the old methods!

One SQL discrepancy I found with several queries is the way to concatenate 2 or more text fields to return a single-column value (e.g. combining first and last names).  OO uses the "||" syntax that also works in Oracle SQL though it does not on MS SQL.  I needed to convert those statements to use the concat() function.

I'm not quite done the conversion.  Next steps are to agree on what needs to be working right after a cutover, and what is either a "is nice-to-have" or isn't actually working now (more of that than you might expect).  A few cooks have been in this code over the years, as I've learned.  My favorite misnamed query is the "Query_New_Events_Combine" that turned out to be a event/event detail join.  It's not new anymore!

Putting my detective hat back on, what is the major puzzle here?  I think many small (particularly non-profits) organizations suffer from this dilemma.  Well meaning volunteers wrote an app in whatever tool they preferred at the time, and if that's gone out of print, they're stuck with a legacy app no one understands how to port.  It takes a detective who is also an engineer to take the thing apart and replace the oldest and clunkiest part with newer working ones.  Those parts don't need to be shiny, they just need to work.  Yeah, there's probably an app for that.  But how will it know what an "early" check means?

Last comment on legacy apps, as this is news happening in my town at the moment and relates to the above (except for the small, non-profit part, well OK government isn't supposed to be profitable).  The Baltimore City Police Department had an audit which revealed IT issues.  They're going to pay a contractor to patch up their code, which said contractor has been managing for a long long time.  Lotus Notes.  My force had that when I started in the late 90's and I avoided it like the plague for new logic.  Wikis anyone?

 

detailed widespread problems with the decades-old software Lotus Notes.

https://twitter.com/baltimoresun/status/1027305954474385408

https://twitter.com/baltimoresun/status/1027585256986161153