ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Prepared SQL for Single Row into Data Structure; without cursor. Possible?

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

  • Prepared SQL for Single Row into Data Structure; without cursor. Possible?

    I am attempting to use a prepared statement to fetch information (a single row) about a location; based on a given ID. The location ID is either passed via parameter, or directly entered and read from the display/user interface.

    I have tried stepping through the RDi debugger. I see the program walking through the code, executing the subroutine. The problem revolves around the data structure and the sql "prepare and execute". I was hoping to select the data into a data structure (without listing the individual "INTO" fields); while also avoiding the use of a SQL cursor (since it is a single row). Is this possible?

    Problem:
    1. Data not stored into the data structure.
    2. The prepared query is failing to execute.

    Investigation (scattered thoughts; ideas to try):
    1. The 'actual' SQL query with a valid location ID, works outside of the program.
    2. The dsLocationInfo is qualified, appears to be valid, but Job Log says it is not defined or usable.
      1. Is a cursor required for a single row result?
        1. Tried using dim(1) in data structure, but still failed.
        2. Should I use the individual fields instead of the data structure?
    3. SQLSTATEMENT not found. Tried shortening the object to sqlSTMT, but same results. Could this be a false-positive due to DS/variable fields? Database Resource not created?

    Note: Code has been condensed (below); striping out some logic and comments.

    JOB LOG
    PHP Code:
    Variable DSLOCATIONINFO not defined or not usable.
    Prepared statement SQLSTATEMENT not found.
    DSPLY Street

    CODE
    PHP Code:
    **FREE
    ctl
    -opt dftactgrp(*noactgrp(*callermain(procMainline);

    dcl-pr procMainline extpgm('a001');
      *
    n int(20options(*nopass);
    end-pr;

    dcl-proc procMainline;
      
    dcl-pi *n;
        
    inLocID int(20options(*nopass);
      
    end-pi;

      
    dcl-f a001ui workstn alias infds(dsINFDS);

      
    dcl-c F03 x'33';  // F3 - Exit

      
    dcl-s sqlString  varchar(1024);

      
    dcl-ds dsUIHead likerec(UIHEAD:*output);
      
    dcl-ds dsUIFoot likerec(UIFOOT:*output);
      
    dcl-ds dsUIBody likerec(UIBODY:*all);  

      
    dcl-ds dsINFDS;
        
    actionKey            char(1)  pos(369); 
      
    end-ds;

      
    dcl-ds dsLocationInfo qualified;
        
    adrStreetNumber      int(5);
        
    adrStreetPrefix      char(2);
        
    adrStreetName        char(15);
        
    adrStreetSuffix      char(4);
        
    adrAdditionalInfo    char(10);
      
    end-ds;

      if %
    parms() = 1;
        
    dsUIBody.uiLocationID inLocID;
      endif;    

      
    write UIHead dsUIHead;
      
    write UIFoot dsUIFoot;
      
    write UIBody dsUIBody;
      
    read  UIBody dsUIBody;

      
    dou actionKey F03;
        
    exsr queryForLocationInfo;

        
    select;
          
    when actionKey F05;
            
    dsUIFoot.msgLine 'Data refreshed.';
          
    other;
            
    dsUIFoot.msgLine '';
        
    end-sl;
      
    enddo;

      
    // End program
      
    return;

      
    begsr queryForLocationInfo;
        
    sqlString  'SELECT STRNUM, STRPRE, STRNAM, STRSUF, STRADI ';
        
    sqlString += 'INTO :dsLocationInfo ';  // May need to remove this and use the modified execute, below
        
    sqlString += 'FROM PROD.LOCMSTRP ';  
        
    sqlString += 'WHERE LOCID = ? ';
        
    sqlString += 'OPTIMIZE FOR 1 ROW;';

        
    exec sql prepare sqlStatement from :sqlString;
        
    exec sql execute sqlStatement using dsUIBody.uiLocationID;
        
    // exec sql execute sqlStatement into :dsLocationInfo using dsUIBody.uiLocationID;

        
    dsply 'Street: ' dsLocationInfo.adrStreetName;
      
    endsr;

    end-proc

  • #2
    SELECT ... INTO is not supported with dynamic SQL, but VALUES(...) INTO in composition with parameter markers is.

    Code:
    CmdSQL = 'VALUES( SELECT .... ) into ?, ?, ?';
    Exec SQL Prepare DynSQL from :CmdSQL;
    Exec SQL Execute DynSQL Using :HostVar1, :HostVar2, .... :RtnVar1, :RtnVar2, ... RtnVarM;
    And you missed a leading colon in front of DSUIBody.UILocationId in the EXECUTE Statement.
    You should also check the SQLCODE or the SQLSTATE immediately after having executed an (embedded) SQL Statement.

    But why you want to use dynamic SQL? It is not necessary in this situation!
    ... and please do not use variables beginning with SQL, these Variables are reserved for the SQL Precomiler.
    Even if you do not get any problems today, the precompiler may generate in future an additional variable with the same name as you variable ... and then you'll get a problem!

    Birgitta

    Comment


    • #3
      I don't use SQL in RPG all that often, but couldn't you just code this in your program instead of the PREPARE and EXECUTE:
      Code:
      Exec SQL
         Select STRNUM, STRPRE, STRNAM, STRSUF, STRADI
         Into :dsLocationInfo
         From PROD.LOCMSTRP
         Where LOCID = :dsUIBody.uiLocationID
         Fetch First Row Only;

      Comment


      • #4
        I appreciate the advice. I ended up using static SQL as suggested. As Birgitta suggested, I changed my SQL variable prefixes to something else besides sqlXXX (actually used dbvXXX). The query looks very similar to Brian's. That is what I initially had, but then wanted to try using the parameter/dynamic route. I reverted back to static.

        I was hoping the Dynamic SQL route (albeit slight performance hit) would work because with static, you have to trust the user input. Coming from a non-RPG background/previous web-related job, where all input was to be parameterized to avoid SQL injections, I still continue to struggle with learning RPG at times; but getting better thanks to help from people like those above!

        Comment


        • #5
          I could be wrong, but I suspect that SQL injection is a far more likely prospect with dynamic SQL compared to static.

          Comment


          • jtaylor___
            jtaylor___ commented
            Editing a comment
            Agreed. Static SQL accomplishes the same thing as parameterized SQL.

          • Scott Klement
            Scott Klement commented
            Editing a comment
            Right... Static SQL determines the statement at compile-time, so it's basicaly impossible to change it at run-time. (That's what SQL injection attacks do -- they change the SQL statement.) Using host variables in static SQL is the same thing as using parameters in dynamic SQL. So, static SQL provides all of the safety of parameterized dynamic SQL, plus even more safety.
        Working...
        X