/**
* @param string $name
* @param array $params
* @param string $returns
* @param array $schema
* @param string $wrapper
*
* @throws \Exception
* @return array
*/
public function callProcedure($name, $params = null, $returns = null, $schema = null, $wrapper = null)
{
if (empty($name)) {
throw new BadRequestException('Stored procedure name can not be empty.');
}
if (false === ($params = DbUtilities::validateAsArray($params, ',', true))) {
$params = [];
}
foreach ($params as $key => $param) {
// overcome shortcomings of passed in data
if (is_array($param)) {
if (null === ($pName = ArrayUtils::get($param, 'name', null, false))) {
$params[$key]['name'] = "p{$key}";
}
if (null === ($pType = ArrayUtils::get($param, 'param_type', null, false))) {
$params[$key]['param_type'] = 'IN';
}
if (null === ($pValue = ArrayUtils::get($param, 'value', null))) {
// ensure some value is set as this will be referenced for return of INOUT and OUT params
$params[$key]['value'] = null;
}
if (false !== stripos(strval($pType), 'OUT')) {
if (null === ($rType = ArrayUtils::get($param, 'type', null, false))) {
$rType = isset($pValue) ? gettype($pValue) : 'string';
$params[$key]['type'] = $rType;
}
if (null === ($rLength = ArrayUtils::get($param, 'length', null, false))) {
$rLength = 256;
switch ($rType) {
case 'int':
case 'integer':
$rLength = 12;
break;
}
$params[$key]['length'] = $rLength;
}
}
} else {
$params[$key] = ['name' => "p{$key}", 'param_type' => 'IN', 'value' => $param];
}
}
try {
$result = $this->dbConn->getSchema()->callProcedure($name, $params);
if (!empty($returns) && 0 !== strcasecmp('TABLE', $returns)) {
// result could be an array of array of one value - i.e. multi-dataset format with just a single value
if (is_array($result)) {
$result = current($result);
if (is_array($result)) {
$result = current($result);
}
}
$result = DbUtilities::formatValue($result, $returns);
}
// convert result field values to types according to schema received
if (is_array($schema) && is_array($result)) {
foreach ($result as &$row) {
if (is_array($row)) {
if (isset($row[0])) {
// Multi-row set, dig a little deeper
foreach ($row as &$sub) {
if (is_array($sub)) {
foreach ($sub as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$sub[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
} else {
foreach ($row as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$row[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
}
}
// wrap the result set if desired
if (!empty($wrapper)) {
$result = [$wrapper => $result];
}
// add back output parameters to results
foreach ($params as $key => $param) {
if (false !== stripos(strval(ArrayUtils::get($param, 'param_type')), 'OUT')) {
$name = ArrayUtils::get($param, 'name', "p{$key}");
if (null !== ($value = ArrayUtils::get($param, 'value', null))) {
$type = ArrayUtils::get($param, 'type');
$value = DbUtilities::formatValue($value, $type);
}
$result[$name] = $value;
}
}
return $result;
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to call database stored procedure.\n{$ex->getMessage()}");
}
}
/**
* @param string $name
* @param array $params
* @param string $returns
* @param array $schema
* @param string $wrapper
*
* @throws \Exception
* @return array
*/
public function callFunction($name, $params = null, $returns = null, $schema = null, $wrapper = null)
{
if (empty($name)) {
throw new BadRequestException('Stored function name can not be empty.');
}
if (false === ($params = DbUtilities::validateAsArray($params, ',', true))) {
$params = [];
}
foreach ($params as $key => $param) {
// overcome shortcomings of passed in data
if (is_array($param)) {
if (null === ($pName = ArrayUtils::get($param, 'name', null, false))) {
$params[$key]['name'] = "p{$key}";
}
} else {
$params[$key] = ['name' => "p{$key}", 'value' => $param];
}
}
try {
$result = $this->dbConn->getSchema()->callFunction($name, $params);
if (!empty($returns) && 0 !== strcasecmp('TABLE', $returns)) {
// result could be an array of array of one value - i.e. multi-dataset format with just a single value
if (is_array($result)) {
$result = current($result);
if (is_array($result)) {
$result = current($result);
}
}
$result = DbUtilities::formatValue($result, $returns);
}
// convert result field values to types according to schema received
if (is_array($schema) && is_array($result)) {
foreach ($result as &$row) {
if (is_array($row)) {
if (isset($row[0])) {
// Multi-row set, dig a little deeper
foreach ($row as &$sub) {
if (is_array($sub)) {
foreach ($sub as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$sub[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
} else {
foreach ($row as $key => $value) {
if (null !== ($type = ArrayUtils::get($schema, $key, null, false))) {
$row[$key] = DbUtilities::formatValue($value, $type);
}
}
}
}
}
}
// wrap the result set if desired
if (!empty($wrapper)) {
$result = [$wrapper => $result];
}
return $result;
} catch (\Exception $ex) {
throw new InternalServerErrorException("Failed to call database stored procedure.\n{$ex->getMessage()}");
}
}
