Difference between revisions of "ExecSQL"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 30: Line 30:
       // Error occurred
       // Error occurred
       String msg = "Sample: Error during SQL search";
       String msg = "Sample: Error during SQL search";
       Functions.debug("Sample:\n" + result.getMessage());
       Logger.info("Sample:\n" + result.getMessage(), "SQL");
   }
   }
   else if (resultCode > 0)
   else if (resultCode > 0)
Line 38: Line 38:
       Parameters params = it.next();  // Use a loop if Limit > 1       
       Parameters params = it.next();  // Use a loop if Limit > 1       
       latest_value = params.get("some_field");
       latest_value = params.get("some_field");
       Functions.debug("Sample: latest value = " + latest_value);         
       Logger.info("Sample: latest value = " + latest_value, "SQL");         
   }
   }
} catch (Exception e) {
} catch (Exception e) {
   String msg = "Sample: Exception during SQL search";
   String msg = "Sample: Exception during SQL search";
   Functions.debug("Sample:\n" + e.getMessage());         
   Logger.info("Sample:\n" + e.getMessage(), "SQL");         
}
}
</syntaxhighlight>
</syntaxhighlight>

Revision as of 01:08, 10 September 2013

Execute a SQL query.

Syntax

Result result = Functions.execSQL(String query);

Parameters

query
The SQL query to execute.
Learn more: SQL Syntax

Returns

Result object. If the return code is greater than zero, use the Result_Class#getIterator method to cycle through the list of Parameters objects it contains, one per record.

Sample Code

This sample retrieves a value from the most recent record that matches the specified criteria:

try {
   String latest_value;
   String sql = 
      "SELECT some_field FROM MyObject " +
      "WHERE another_field = '" + someValue + "' " +
      "ORDER BY date_created DESC " +            
      "LIMIT 1";
   Result result = Functions.execSQL(sql);
   int resultCode = result.getCode();
   if (resultCode < 0)
   {
      // Error occurred
      String msg = "Sample: Error during SQL search";
      Logger.info("Sample:\n" + result.getMessage(), "SQL");
   }
   else if (resultCode > 0)
   {
      // A record was found. (Otherwise, resultCode == 0)                     
      ParametersIterator it = result.getIterator();
      Parameters params = it.next();  // Use a loop if Limit > 1       
      latest_value = params.get("some_field");
      Logger.info("Sample: latest value = " + latest_value, "SQL");        
   }
} catch (Exception e) {
   String msg = "Sample: Exception during SQL search";
   Logger.info("Sample:\n" + e.getMessage(), "SQL");        
}

Sample App

This sample uses the execSQL operation to populate a JSP page with a list of object records.

Learn More