Old/New Dynamic SQL Cheat Sheet
Tags: sql, code, snippet, cache
Published: 2021-03-18 16:37:28

The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.

First, here is a somewhat more verbose adaptation of my cheat sheet:

<table>
  <tr>
    <th>
      1
    </th>
    
    <th>
      %ResultSet::%New()
    </th>
    
    <th>
      %SQL.Statement::%New()
    </th>
  </tr>
  
  <tr>
    <td>
      2
    </td>
    
    <td>
         Call the Prepare() instance method
    </td>
    
    <td>
         Call the %Prepare() instance method
    </td>
  </tr>
  
  <tr>
    <td>
      3
    </td>
    
    <td>
         Previous step returns status; check that
    </td>
    
    <td>
         Previous step returns status; check that
    </td>
  </tr>
  
  <tr>
    <td>
      4
    </td>
    
    <td>
         Call the Execute() instance method
    </td>
    
    <td>
         Call the %Execute() instance method
    </td>
  </tr>
  
  <tr>
    <td>
      5
    </td>
    
    <td>
         Previous step returns status; check that
    </td>
    
    <td>
         Previous step returns instance of %SQL.StatementResult; use that in next steps
    </td>
  </tr>
  
  <tr>
    <td>
      6
    </td>
    
    <td>
         Call the Next() instance method (e.g., iteratively, in while loop)
    </td>
    
    <td>
         Call the %Next() instance method (e.g., iteratively, in while loop)
    </td>
  </tr>
  
  <tr>
    <td>
      7
    </td>
    
    <td>
         Call the GetData() instance method to get a column by column number
    </td>
    
    <td>
         Call the %GetData() instance method to get a column by column number
    </td>
  </tr>
  
  <tr>
    <td>
         Call the Get() or Data() instance method to get a column by column name
    </td>
    
    <td>
         Call the %Get() instance method to get a column by column name
    </td>
  </tr>
</table>

 

Then here is the terser cheat sheet that I actually use:

<table>
  <tr>
    <td>
      1
    </td>
    
    <td>
      %ResultSet::%New()
    </td>
    
    <td>
      %SQL.Statement::%New()
    </td>
  </tr>
  
  <tr>
    <td>
      2
    </td>
    
    <td>
         Prepare()
    </td>
    
    <td>
         %Prepare()
    </td>
  </tr>
  
  <tr>
    <td>
      3
    </td>
    
    <td>
         Check status
    </td>
    
    <td>
         Check status
    </td>
  </tr>
  
  <tr>
    <td>
      4
    </td>
    
    <td>
         Execute()
    </td>
    
    <td>
         %Execute()
    </td>
  </tr>
  
  <tr>
    <td>
      5
    </td>
    
    <td>
         Check status
    </td>
    
    <td>
         Use value returned by %Execute in next steps
    </td>
  </tr>
  
  <tr>
    <td>
      6
    </td>
    
    <td>
         Next()
    </td>
    
    <td>
         %Next()
    </td>
  </tr>
  
  <tr>
    <td>
      7
    </td>
    
    <td>
         GetData()
    </td>
    
    <td>
         %GetData()
    </td>
  </tr>
  
  <tr>
    <td>
         Get() or Data()
    </td>
    
    <td>
         %Get()
    </td>
  </tr>
</table>