/** * @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()}"); } }