SQL in an SQLRPGLE program

25 pts.
Tags:
C+
Embedded SQL
LIKE statement
SQL
SQL/400
SQLRPGLE
I want to use embedded SQL in an SQLRPGLE program to search a file for a field entered in a screen to select data using the LIKE operand. This is my code C/EXEC SQL C+ DECLARE C1 CURSOR FOR SELECT * FROM F0101JN WHERE ALADDZ = :SZIP C+ AND ABALPH LIKE :SINNME ORDER BY ABAN8 C/END-EXEC * I entered %VALLEY% into screen field SINNME but the statement did not select any records.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Alpfalpf

I’m supposing that you had a good value for :SZIP
and SINNME = %VALLEY%

SELECT * FROM F0101JN WHERE ALADDZ = :SZIP
C+ AND ABALPH LIKE :SINNME ORDER BY ABAN8
C/END-EXEC

If I were doing it interactively I would have to type
‘%VALLEY%’ with the quotes so I expect the the value you need in SINNME is
‘%VALLEY%’

SINNME = ‘%VALLEY%’
SINNME = ”’ + SINNME + ”’

and is ALADDZ char or numeric?

Remember this will only find upper case values.
Phil
———————————————————————–

This code works for me

<pre>
* #csr_prep subroutine – begin
c #csr_prep begsr
c eval select_str = ‘SELECT ITMNUM, ITDESC ‘
c + ‘FROM FPITMMAS’
c if filter <> *blanks
c eval select_str = %trim(select_str) + ‘ WHERE ‘
c + ‘ITDESC LIKE ”%’ + %trim(filter)
c + ‘%”’
c endif
c eval select_str = %trim(select_str)
c + ‘ ORDER BY ITMNUM’

c/EXEC SQL
c+ PREPARE SELECT_STMT FROM :select_str
c/END-EXEC

c/EXEC SQL
c+ DECLARE SUBFILE_CSR CURSOR FOR SELECT_STMT
c/END-EXEC

c/EXEC SQL
c+ OPEN SUBFILE_CSR
c/END-EXEC
c endsr
* #csr_prep subroutine – end
</pre>
Kevin C. Ketzler – <a href=”http://www.aresgrp.com”>Affiliated</a>

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • WoodEngineer
    Be sure *INLR is not on. If *INLR = *On, the select statement will not return any records.
    6,680 pointsBadges:
    report
  • BigKat
    Not true, WoodEngineer *inlr only affects the "cycle" operations. (most modern programs run in one cycle, just looping around in the calcs until finished.) I routinely set *inlr = *on as the first calc in my program, and then READ, CHAIN, exec sql, etc. That way, whenever I encounter a situation that says my program is finished, I can just code a RETURN and everything is closed up and ends.
    8,210 pointsBadges:
    report
  • WoodEngineer
    Hello BigKat, I'm speaking from experience here, as in this week. As soon as I moved the seton LR to occur after the select statement, data was returned. That was the only change I made to the program. Perhaps you are setting on LR after your select statement. I was very suprised because I also thought *INLR only effected files accessed in the RPG cycle. By the say, the program seton LR in the *INZSR subroutine. Maybe that is the cause. In any case, I'd like to fully understand this.
    6,680 pointsBadges:
    report
  • BigKat
    If you set *INLRon in *INZSR, the system will immediately end the program (due to the cycle) after initialization, and BEFORE any of the calculations (including the SQL) are run. Setting on *INLR in *INZSR is like aborting the program because something failed to initialize.
    8,210 pointsBadges:
    report
  • BigKat
    this is part of a program I am working on now and it works just fine
     * Main Program                                 
    c                   eval      *inlr = *on       
    c                   dou       #exit             
    c                   exsr      openCsr           
    c                   exsr      clearSfl          
    c                   exsr      loadSfl           
    c                   dou       #reload or #exit  
    c                   exsr      displaySfl        
    c                   enddo                       
    c                   exsr      closeCsr          
    c                   enddo                       
    
    8,210 pointsBadges:
    report
  • Gilly400
    Hi, I find it really strange to see the *INLR at the start of the program, but if it works for you, why not? I guess eventually the *INLR will disappear and get replaced with a less cycle-oriented opcode. Maybe there'll even be a CYCLE or NOCYCLE keyword for the H-SPEC... Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • BigKat
    It really is just a matter of being able to issue a return anywhere in the program that it is necessary, without having to have eval *INLR= *ON all over the place. But yeah, it is just a matter of coding style. The important thing to this thread, is that it is NOT in *INZSR, as that makes the cycle think you are aborting the program.
    8,210 pointsBadges:
    report
  • WoodEngineer
    Thanks for the clarification, Big Kat. I should have known that. It makes a lot of sense. It also clears up the cause of my problem. Well . . . this one at least. ;-)
    6,680 pointsBadges:
    report
  • Cwc
    With 6.1 of IBM i (a.k.a., V6R1 of OS/400), a callable program can be created without the cycle, by using the MAIN keyword and specifying which procedure will be the entry point of the program. http://www.mcpressonline.com/programming/rpg/v6r1-rpg-enhancements.html I'd start using, but we're not at 6.1 yet - hopefully, one day soon.
    4,290 pointsBadges:
    report
  • Koohiisan
    I also use *INLR at the start of many of my programs. I always comment it to explain why, in case someone not familiar with the effects of that on the cycle reviews the code for some reason. I also do it so I can use return by itself in the program I suppose I feel warm/fuzzy not having to repetitively type *INLR = *off before every return in my program. ^_^
    5,020 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following