Persistent Class - get_persistent_by_query

[ralf_wenzel_heuristika]

Hi everybody out there,

I created a persistent class and want to get a few items by query. Because I have a selection screen and this was the way I did it by using SELECT-commands, I created a query like this: 'CARRID IN S_CARRID AND CONNID IN S_CONNID' - s_carrid and s_connid are ranges of the fields carrid and connid. This does not work, probably because "in" is not a native SQL command.

But how I have to rewrite the code? In fact, there is a selection screen and I have my query-parts in this ranges.

I hope you can help me....

Kind Regards

Ralf

[custodio_deoliveira]

Hi Ralf,

The query does not accept ranges/select-options, which makes it quite useless, to be honest... From help (documentation of if_os_query, then "query services"):

Relational Operators 
attr =|<>|<|>|<=|>= { operand }

LIKE 
attr [NOT] LIKE { pattern } [ESCAPE escape]

IS NULL 
attr IS [NOT] NULL

EQUALSREF 
attr EQUALSREF par
Comparison of two object references. When the query is executed, parameter par has to be bound to a reference variable that refers to a persistent instance of a persistent class.

AND, OR, NOT 
NOT expr
expr AND expr
expr OR expr
The semantics of the logical operands AND, OR, and NOT are also defined by their semantics in Open SQL. Explicit parentheses are possible. If no parentheses are specified, the Open SQL priority rules apply.
Examples 
price < '100' AND currency = 'EUR'
name LIKE PAR1 AND age > '45' AND age < '65'
( department EQUALSREF DEP1 OR department EQUALSREF DEP2 ) AND NOT 
( salary > '50000' AND currency = 'EUR' ) )

A piece of advice: it's always good to learn new stuff, but persistent classes is not something I would suggest anyone to invest time into. Instead, I would recommend you get to know the BOPF. There are lots of materials in the space.

Cheers,

Custodio

[former_member186905]

Hi Ralf,

As Custodio stated: You cannot use select-options with the query service directly.

If you need to work with select-options, I suggest you read the entities' key values from the database (not by using the object services, but more or less direct database access) and then use the GET_PERSISTENT_BY_KEY_TAB method of the interface IF_OS_CA_PERSISTENCY in the generated (base) agent classes.

Alternatively, you could restrict the select-option (by using the NO-EXTENSION or NO INTERVALS addition to the SELECT-OPTION keyword) and convert the select option to one or more queries in some helper method.

Note that the object services are not designed to be used for processing mass data (see e.g. SAP note 1882133 "Object Services: Performance issues with mass data"). However, nothing prevents you from using persistent classes when working with some individual objects (in an application for the creation of objects, for example, which provides e.g. the typical CRUD operations) and using different classes for working with larger groups/set of objects when working with mass data.

I hope this helps.

Kind regards,

Valentin

[former_member222573]

Hi,

I have started to use the object services around 2002.

Sorry, it is not possible, to learn, how to use it, by the given documentation and articles.

I tried an practiced a lot an I will never write a select statement by myself, keep changed records in special tables and write them to database on commit (Perform on Commit or Call on Commit).

All these duties are processed by the objetct-services properly and completely.

(In combination with the Transaction-Service, you can do Precomits, local redos and so on ... its so fine!!)

I don't know, what are "Mass-Data" in the meaning of the above mentioned "Sap-Article".

I have productive Application (individual programmed) handling around 10.000 Objects and Records in one Transaction. (The "background-booking-algorithm" has been skipped, because reading and writing is that fast, so user can do it via "execute" or "save"-Button.

Let's not discuss the mental and psychological state of the developers, which designed this scary Query-Stuff ... (Query-Manager, Query, ... huhhaaaa) ... as I said, not discussing, but USING!

From the performande view I avoid SELECT with IN and SELECT FOR ALL ENTRIES, because these statements are executed vial thousands of SELECT SINGLE statements, passed from SAP-Kernel to the database ... or they are concatenated to one really "LONG" Select-Statement.

(If you know, that most databases store the Select-Statements and buffer the results ... you may guess and understand, that such really big statemends are not for "recycling" and "buffering" and not for quick performance)

--

As said above, one can restrict the selections and pass the entries from Select-Options to the "Key_tab" and use the "get_by_keytab"-service.

Or you can do it like SAP-Kernel, write a little LOOP AT and concatenate the values to the "wherestring" ... not a real problem.

--

All these minor "difficulties" can not prevent me from intensive Use of these Object-Services.

If you know how to do, it is so easy (but forget about every documentation you have ever read ..)

many Greetings

Bert

[matt]

Do you mean you avoid SELECT IN entirely, or only in conjunction with FOR ALL ENTRIES?

[former_member222573]

Yes, I avoid to use SELECT ... IN range, and I avoide FOR ALL ENTRIES.

The statements generated by SAP are real large "wherestrings" ... not useful for caching, prefetching, storing or sth. else which database-developers have in mind, when looking for good performance.

I cannot remember a single case, which an IN or ALL ENTRIES -statemen was the one and only solution.

Normally, you have "wildcards" or an interval with given upper and lower ... thats it for most of the cases ... (and all these statements are recyclable by the databases ...)

[matt]

I can give you one extremely common scenario where IN is essential - ERP end user reports! If you've select options to give users flexibility, you've got IN in your WHERE. Can't see a way around that, except for telling users they can't have select options - and I can't see the business accepting that!

However, I never use FOR ALL ENTRIES, and campaign hard to destroy the widespread myth that FAE is more efficient than a JOIN.And outside of report type programs, I don't use ranges.

[former_member222573]

Business needs ... flexibility ... ok with that.

So, even here, no discussion necessary ... SELECT where IN ... and get_by_keytab ...

(or some small coding transferring to wherestring ...)

... it is dialogue ... not mass-data ...

🙂

(JOIN can be very horrible and deliver unexpected performance issues on database-side ... I often dealt with issues like that ... mostly a new secondary index will solve the problem ... until the programmer has a bad dream and changes his select-statement ... 😉 )

[former_member222573]

Thought some seconds on the user-forced IN ...

I myself find it very uncomfortable to select the needed keys from the sap "F4" dropbox.

So I provide selection via interval (keys or date) or wildcards, well knowing, to fetch show more records than may be needed afterwards...

Later presenting the selection on ALV-GRID with additional information (which standard drop box cannot calculate or provide), the user may mark the wanted records and "process" them with the "desired funcionality" ...

[matt]

Good luck getting that past most of today's functional analysts!

[former_member222573]

What's the problem with "todays functional analysts" ??

[matt]

Lack of "push back" to the business, especially with an off-shore model that just gives what the customer asks for, rather than what they need.

Lack of imagination - "this is the way its done why should we change".  For example, I suggested a while back that select options with dates rarely made any sense. The users really just want to be able to specify a single date or a range of date. The code even assumes that they do. But try to convince a functional analyst that this is the better way (and the way many standard reports are written) and they don't want to know.

Lack of technical knowledge, coupled with telling developers in great detail how to write the code. They're functional specialists, not technical. Yet so often - again especially with the offshoring model - they're designing the programs instead of leaving it to the developer, or, better, designing it with the deveoper.

Of course, this is in the general case, not applicable to all. Just something I've observed in the last fifteen years.

[former_member222573]

You are so right, I am observing the same over the last years.

It takes me more time to write a concept for "offshoring", "nearshoring", "sh***shoring" ... than having the whole thing complete coded, tested and given to the "customer" ... some smaller changes done within hours and everybody is satisfied ... according to my experience and opinion, it is much ceaper ...

I have experienced all these functional "conceptionists", with offshoring to I****, with nearshoring to Ser*** ... it won't work. (No one has eggs enough to tell the management, they ordered me, to be quiet ... and they managed, that I was not asked ...)

Good concept designs INPUT ... and desired OUTPUT ... or some Dialoge steps and "behavior" ... everything else may work "behind the curtain" (it should only be taken care of or modified, when not working properly or when it slows down the system ... some of my jobs!)

Let's deliver the best, we can!

[matt]

Rather than the query service, I use a opensql directly, while still retaining persistence for reading and writing small volumes of data. So, finding the few application objects the user wants to work with - I use opensql. For then working with those objects, I use the persistence framework. Largely because the query service isn't flexible enough.

So long as those opensql statements are hidden away in a method, and so properly encapsulated, unless you're an object purist (fixed eyes staring, repeating the mantra -all-must-be-objects) I really can't see there's a problem. We're hired to write programs that work - not ones that fulfil the ideal OO-paradigm.

For software I develop that is sold to customers on different releases, I can't use query services. This is because there have been changes to the supporting classes, and if you're on a later release and try to import to an earlier release, you get syntax errors. When you import a persistent class, it doesn't get regenerated from the meta information - it just gets imported as a class.

[former_member222573]

Yes you may avoid the query-service, do the "keyfetching" by yourself and get the objects by "get_persistent_with_keytab" .. (or sth like that).

I am not focused to "mantras" ... I am pragmatic!

You also know all the linear "intestable" coding which is delivered within each SAP-System (Dynpros, Module-pools, global variables, import from, export to memory ... )

I am often hired when "elderly" programs are producing more and more errors ... So, I am asked to look after .... and take care, what follows next is printout hundreds of lines of code, working through with colored pens, marking loop in loop in loop in loop (horrible performance, hard to supervise), globals, and so on.

Yes, sometimes I press the delete-button and write it all new! (it is quicker than cleaning up!)

----

One main issue is TESTING.

FORMS cannot be tested separately.

With FUNCTION-MODULES it is possible, to test a single FUNCTION with some "datasets" ... provide local "correctness" as far as possible.

With OBJECTS, classes and methods, you are able, to deliver small pieces of coding, which can be tested, and defined as working (mostly) correct. Each Object is responsible for its data, for giving information to the requestor, receiving information and process it as defined.

Same to the object-service. They can be used in newer releases with no restriction. (transporting to a lower release ... ok, its your decision to do that, or provide that feature to your customer ... I did it once, and won't do it again!)

SHORT CODINGS

Modularization within Classes has one great advantage: short codings!

Each coding, which is longer than one page ... I declare "tooo long!".

I myself write short codings, understandable, testable, correct (...), and working ... after that, I forget the details, remember the "signature" and use it, as given and working POINT!

Ok, one issue is, make clear, how all the methods work together ... and transfer this knowledge to subsequent programmers or even the customer. (Documentation within the Class-Builder is fine!)

MOVING TARGETS

HIRED to deliver working CODE ... hmmm (correct in a certain way, but not what the customer really wants ...)

Not discussing about "one hour programs" ... quick coding, to solve a "one minute" question ... (parameters, select into, loop, write ... thats it)

But thinking on more complex scenarios makes it more useful to spend more thougts about ...

Preventing new features and coding "old school" may seem to be quicker, than going "a new way".

But, according to my experience, it is the other way round. When the first error occurs , the first modification is made to the requirements (input, output ...), the first "oops, we thougt it would work ..."  your investment in object oriented coding and some new features will pay you! (Everybody thinks: Ok, we have "lost", give him the "normal" time, which is needed to overcome the situation ... I relax, do my work, and I am ready befor even someone expects it :-D)

Ok, too much of my opinion.

Last word to object-services: They work! They do it with "lots of data", and I leave it to SAP, whether they work correct with all the transaction and query-stuff ... 🙂

(If anyone is interested, please ask for one day "know-how-transfer" ... to learn "my way" to deal with these object-services ... not all, but knowing all you need!)

Best Regards

Bert