...
You can also use SQL expressions to return information in addition to just selecting database fields from the file. So in the example, using the expression TRIM(CNAMEDBLNAM) || ', ' || CATIDDBFNAM allows us to return a concatenated search value with formatting for our search, but the returned value to the application is unchanged.
This is what the SQL expression returns:
Max Choices
The max choices property allows you to set the number of choices you will see from your database-driven selection. There is no limit to the number of choices you can show, however if there is no limit set the default is 10.
...
The choices URL property allows you to use an external program to return your choice options and values by passing them using JSON formatting. A PHP script would be one example of a custom program used to pass the values to your application. When using the choices url URL property, all database-driven auto-complete properties are ignored.
Good JSON Syntax Successful Response Example
These are values returned from an auto-complete textbox in a good example of JSON formattingsuccessful response from the external program:
| Code Block |
|---|
{
"success": true,
"response": {
"colWidths": [
41,
7
],
"results": [
{
"00001": "Alpineaire Food, 58",
"CATID": "58"
},
{
"00001": "Altimiters, 13",
"CATID": "13"
},
{
"00001": "Avalanche Gear, 82",
"CATID": "82"
}
]
}
}
|
...
| Code Block |
|---|
// Example of error reporting.
$con = db2_connect(DB, USER, PASS, array("i5_naming" => DB2_I5_NAMING_ON));
if (!$con) {
$response = array(
"success" => false,
"errorId" => db2_conn_error(),
"errorText" => db2_conn_errormsg()
);
print json_encode($response);
return;
}
|
Returned JSON Error Response Example
This is the information returned using JSON formatting to report an error with an auto-complete textbox. This was passed from the example above.
...
Example of the showErrors() API displaying the JSON error response example:
Full PHP Script Example:
| Code Block |
|---|
<?php
define("MAX", 10);
define("DB", "S100450A");
define("USER", "QPGMR");
define("PASS", "makeitcoun");
$records = array();
$query = "";
$limit = 0;
if (isset($_REQUEST["query"])) $query = $_REQUEST["query"];
if (isset($_REQUEST["limit"])) $limit = intval($_REQUEST["limit"]);
if ($query != "") {
$query = strtoupper($query) . "%";
if ($limit == 0 || $limit > MAX) $limit = MAX;
// Example of error reporting.
$con = db2_connect(DB, USER, PASS, array("i5_naming" => DB2_I5_NAMING_ON));
if (!$con) {
$response = array(
"success" => false,
"errorId" => db2_conn_error(),
"errorText" => db2_conn_errormsg()
);
print json_encode($response);
return;
}
// The following will not be error checked, for brevity.
$stm = "select distinct pname, prid from rpgspcart/prodp where pname like ? order by pname";
$stm = db2_prepare($con, $stm);
db2_bind_param($stm, 1, "query", DB2_PARAM_IN, DB2_CHAR);
db2_execute($stm);
$count = 0;
while (db2_fetch_row($stm) && $count < $limit) {
$record = array();
$record["PNAME"] = trim(db2_result($stm, "PNAME"));
$record["PRID"] = trim(db2_result($stm, "PRID"));
$records[$count++] = $record;
}
}
// "colWidths" is optional, this helps the widget to
// size the columns when multiple fields are displayed.
$return = array(
"success" => true,
"response" => array (
"results" => $records,
"colWidths" => array(
db2_field_precision($stm, "PNAME"),
db2_field_precision($stm, "PRID")
)
)
);
print json_encode($return);
?>
|