ABAP log

October 4, 2007

Using ABAP Native SQL with SAP Namespaces.

Filed under: ABAP, SAP — abaplog @ 7:58 pm

Starting, I think, with SAP R/3 4.6, we can use namespaces to name our objects. (“We” includes, in this case, SAP itself too.) That means that instead of using the Y- or Z-prefix to distinguish between SAP’s objects (like tables or programs) and customer or third-party objects, we can register a namespace with SAP that will be used as object name prefix exclusively by our company, thus preventing name conflicts. SAP itself is using, for example, /SAPAPO/ namespace for all objects specific to APO. The feature was logical to expect from SAP to add it, but sometimes it is bringing problems.

Till now all I had were some warning messages when activating tables or ABAP programs (already forgot what was that exactly). But today, when I was writing DELETEs in Native SQL, I had to spend some time to figure out what kind of syntax does it want. The problem was that when the native SQL statement was sent to our IBM DB6 database, it was causing a short dump. I did not expect it to be happy though, and tried to enclose my table names that had slashes in them into single quotes. Doesn’t work. I used the SQL Trace (ST05) then to see which syntax does SAP use internally. They used double quotes. (Note that unless it is the native SQL, double quotes will be considered a comment opening.) But double quotes didn’t help me either – it was still crashing.

I did a quick search in OSS and found one rather unrelated note that among other things mentioned that in addition to double quotes, all table and column names should be in upper case. This was my last trouble to fix. My ABAP Pretty Printer setting is “all lowercase”, and though the pretty printer doesn’t touch the native SQL, I typed everything in lower case myself. If I only copied the syntax that was shown in the “Detail” screen of the SQL Trace, my problem would be solved right away! But well, it was a nice little trick for me to learn.

Advertisements

4 Comments »

  1. hi

    by using native SQL in ABAP you are opening a can of worms (or driving on the road to hell, if you will). you don’t want to do that precisely because of hairy problems like the one you experienced (portability problems etc.).

    was there a specific reason for writing native SQL in the first place?

    regards
    nicola

    Comment by Nicola Fankhauser — November 1, 2007 @ 8:53 am

  2. There can be many reasons because the Open SQL is a subset. Examples:

    DELETE without WHERE condition, to trash the data quickly. Of course, you can trick it with supplying MANDT.

    Using substrings or other functions in conditions. Normally you don’t need that but once in a while you can gain a great improvement in performance.

    Comment by abaplog — November 4, 2007 @ 8:56 pm

  3. what is the exact syntax to delete the records from a database table in native sql.

    Comment by phani — February 21, 2008 @ 4:40 pm

  4. To see the exact syntax of native SQL commands, you have to do two things:

    1. Read SAP help on EXEC SQL statement.

    2. Check the DELETE syntax of your database, as this is vendor-specific. Of course normally it will be as simple as standard DELETE in SQL.

    Comment by abaplog — February 23, 2008 @ 8:44 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: