ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

A steer in the right direction (SQL to RPG)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • A steer in the right direction (SQL to RPG)

    Hello code400 forum!

    Firstly I think I should give a little background about myself.
    I am in no way a professional in any aspect of IT, I am just an end user who learns as much as I can about the systems I use to improve things / make my life easier.

    I began using an AS/400 exactly a year ago today, which has been configured and maintained for years by a company who no longer provide support or development. This leaves me in a quandary as the AS/400 is extremely stable and reliable.... If it isn't broken...

    So, in the past I've used SQL to query and update records in a database, I am by no means an expert in SQL either, but I am fairly comfortable with the syntax & have been using it via STRSQL in the as400. Obviously this is fairly cumbersome for batch queries/updates. So I began using FDF/DTT/DTF files, which has been fantastic.
    However (afaik) it doesnt support updating one "column" in a row of a given table (file?)

    For example, in SQL;
    Code:
    update warefil_ml/warpall
    set clsspm = '74'
    where pal#pm = '1005520062'
    -- If this can be done using FDF/DTT then I'd love to know how! Anyway......

    Obviously the best solution is to write RPG/CLP to do what I require, this will then be portable for anything new in the future, given I know the structure of SQL statements - I looked into this and as I understand it, AS/400 does support SQL to RPG. Great!

    The wall I have hit is;

    How do I take an SQL statement, turn it into an RPG and compile it.
    From what I've read this week trying to get it to work I need to invoke an SQL compiler and the RPG compiler.

    Ive seen the latter in STRPDM - and I have written a basic RPG using a guide I found online, which worked, so there's something to go on....

    I just dont know where I put the SQL into (and changes I may need to make)
    How to then compile it into an RPG via an SQL compiler.

    I have looked and looked, but I really don't know what I'm supposed to do next and could really do with some guidance, I have absolutely no concerns reading up on the menus that I need to use, if I knew them!

    Thanks
    Steven

  • #2
    Just google "embedded sql rpgle" lots of articles to read, as for compiling once you change your source from rpgle to sqlrpgle compiling should take care of itself.

    Comment


    • #3
      Just to clarify what JJ said, if you change the source type column for your program to:
      Type
      SQLRPGLE

      ...then when you use option 14 to compile the program, it will perform a CRTSQLRPGI instead of CRTBNDRPG (as it would for a source type of RPGLE).

      Cheers,

      Emmanuel

      Comment


      • #4
        First, if there is no longer support from a 3rd-party, is there any in-house support at all?

        We can guess that you understand that running SQL against a business database can be a very risky undertaking. If first you're not clear on how to undo what you've done, it's probably not a good idea to do it -- even if it's a required task. A minor typo or logic error can introduce a real mess. If, for example, you accidentally typed "where pal#pm = '1005220062'" instead of "where pal#pm = '1005520062'", it might be difficult to find, much less to know what the original value of "clsspm" was for that row so that it could be set back again. Trivial example, perhaps, but it shouldn't be hard to find much better ones at your site.

        Next, it's not clear that RPG has much applicability for what you're asking. If you're not already a developer in some significant language, it seems like an unlikely jump to think about RPG (or COBOL or C). A language such as REXX would seem a far better choice. For your example SQL, it wouldn't be much more than:
        Code:
        address '*EXECSQL' execsql ,
           'update warefil_ml/warpall ',
           'set clsspm = ''74'' ',
           'where pal#pm = ''1005520062'' '
        exit
        That's not exactly the coding that I'd recommend, but it'd give pretty much the same result as your statement in STRSQL.

        But then, it's not clear if REXX is right either, even though it's available on your system and can be easier than RPG.

        Your example might not make sense for what you're asking. Why would you want such an example embedded in some programming language? It looks like a statement that would only be executed once, so there'd be no reason to keep it around in program form. Of course, there may be statements that need to be run over and over, say, with different dates.

        And if that's what you're going for, then Query Manager seems a better choice than REXX (or RPG). If you have the STRSQL command, then you should also have Query Manager.

        Regardless, in order to give really useful advice, are you prepared to answer a few questions about your system and what you really need to do?
        Tom

        There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

        Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

        Comment


        • #5
          Hi Tom,

          Perfectly valid post and I fully appreciate the points you are making.

          To clarify a couple of things:

          First, if there is no longer support from a 3rd-party, is there any in-house support at all?
          Me? The IT Manager, but he isn't from a background in AS/400 either, so no, it's two people with an interest in systems, one in a professional context and myself, more a hobbyist.

          We can guess that you understand that running SQL against a business database can be a very risky undertaking. If first you're not clear on how to undo what you've done, it's probably not a good idea to do it -- even if it's a required task. A minor typo or logic error can introduce a real mess. If, for example, you accidentally typed "where pal#pm = '1005220062'" instead of "where pal#pm = '1005520062'", it might be difficult to find, much less to know what the original value of "clsspm" was for that row so that it could be set back again. Trivial example, perhaps, but it shouldn't be hard to find much better ones at your site.
          Nope, you're entirely right, the lack of a where clause could have catastrophic consequences, I'm well aware of the risks, currently I'm working in my own library away from the live database - I always select before update/insert to ensure my queries aren't potentially dangerous.

          Next, it's not clear that RPG has much applicability for what you're asking. If you're not already a developer in some significant language, it seems like an unlikely jump to think about RPG (or COBOL or C). A language such as REXX would seem a far better choice. For your example SQL, it wouldn't be much more than:
          Code:
          address '*EXECSQL' execsql ,
          'update warefil_ml/warpall ',
          'set clsspm = ''74'' ',
          'where pal#pm = ''1005520062'' '
          exit
          That's not exactly the coding that I'd recommend, but it'd give pretty much the same result as your statement in STRSQL.
          I have no idea what REXX is, basically, the end goal is to have some PGM/RPG to perform monotonous tasks where the end user doesn't see something like STRQSL, for want of a better description, some element of front end. There are a variety of tasks we're looking to do, but to capture two key ones at the minute (not necessarily RPG related):

          a) We receive a file from our customers (.csv), we want to parse this into WARSTHD to create a Stock Transfer Header file with certain specifics (batch control etc) I have currently done this using VBA to parse the data into a BIFF5 .csv, the VBA then launches Windows Shell to launch a .DTT file and the FDF takes care of the rest.
          - There is also an item level file that goes into a file, WARSTIT, which is "linked" via the STN#ST/STN#IT 'columns' in WARSTHD/WARSTI respectively.

          b) This one I've been unable to do using Excel VBA (where I'm more comfortable) - They change "stock status" from '51' to '31' - unfortunately I haven't managed to ascertain if it's possible to only update one field in the row using DTT/FDF.
          I know I can update if I have the whole row of data and update the respective field, so could query the database, then update and DTT back to the host.


          But then, it's not clear if REXX is right either, even though it's available on your system and can be easier than RPG.

          Your example might not make sense for what you're asking. Why would you want such an example embedded in some programming language? It looks like a statement that would only be executed once, so there'd be no reason to keep it around in program form. Of course, there may be statements that need to be run over and over, say, with different dates.
          Yes, I "kept it simple" once I understand the syntax better and the structuring, there will be more complex queries written - Ideally I'd like to have a menu on the screen where the end user puts in one of the 3 fields below, and fills in the 4 field. This will then be referenced by the SQL, the top 3 forming the "where" clause and the 4th carrying the updated value.

          Code:
          [B]Pallet ID
          Batch[/B]
          [B]Internal Pallet ID
          ------------------------
          New Status[/B]
          And if that's what you're going for, then Query Manager seems a better choice than REXX (or RPG). If you have the STRSQL command, then you should also have Query Manager.

          Regardless, in order to give really useful advice, are you prepared to answer a few questions about your system and what you really need to do?

          I'll happily answer as much as I can RE the system, while I clearly am new to this platform & also not an IT professional, I have self taught myself SQL & VBA - so I'm not adverse to getting my head down and researching. However with SQL/VBA, the language seems a little easier. I do honestly believe once I can get queries running, I can begin to progress and understand further, it's disheartening with a compile error that won't go away - even after I added D Specs, albeit probably incorrectly.

          Many thanks for taking the time to reply.
          Steven

          Comment


          • #6
            Easier options may be available, depending on what you need to do.

            1. Upload the file to imported, then parse AFTER. - or build an SQL view over it.
            2. Look into RUNSQLSTM as an option to 'pre-code' some fixed SQL that need to be run.
            3. Upload using the FDF/DTT method, then use SQL to modify as needed.

            Comment

            Working...
            X