前提・実現したいこと
LaravelのDBをOracleからMariaDBに変更しようとしていますが、'dbms_lob.INSTR'エラーが出ます。
発生している問題・エラーメッセージ
[2022-03-10 10:06:22] staging.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1370 execute command denied to user '' for routine 'dbms_lob.INSTR' (SQL: select count(*) as aggregate from `bbs` where `bbs_id` = LB041 and `fu_id` = and `article_title` = Tu and `reg_date` >= 2022-03-10 10:04:22 and `wrt_ip` = 172.18.0.1 and exists (select `article_no`, `article_text` from `bbs_text` where `bbs`.`article_no` = `bbs_text`.`article_no` and DBMS_LOB.INSTR(bbs_text.article_text, Tu
該当のソースコード
QnaController.php
<?php namespace App\Api\V1\Controllers\Forum; use App\Api\V1\Controllers\BaseController; use App\Api\V1\CustomException; use App\Api\V1\Resources\Forum\Qna\CategoryCollection; use App\Api\V1\Resources\Forum\Qna\ListCollection; use App\Api\V1\Resources\Forum\Qna\ShowResource; use App\Models\CategoryInfo; use App\Models\Forum; use Illuminate\Http\Request; use Illuminate\Validation\Rule; /** * * * Class QnaController * @package App\Api\V1\Controllers\Forum */ class QnaController extends BaseController { /** * * * @param Request $request * @return \Illuminate\Http\JsonResponse * @throws \Throwable */ public function store(Request $request) { $options = $this->categories()->resource; $request_data = $request->validate([ 'category_id' => [ 'required', Rule::in($options->pluck('catg_id')->toArray()) ], 'title' => 'required|string|max:100', 'content' => 'required|string|max:500', ], [ 'category_id.required' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_category_id_required_msg'), 'category_id.in' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_category_id_in_msg'), 'title.required' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_title_required_msg'), 'title.string' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_title_string_msg'), 'title.max' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_title_max_msg'), 'content.required' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_content_required_msg'), 'content.string' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_content_string_msg'), 'content.max' => \LittlefoxLocalization::lang('api_qna_store_validate_fail_content_max_msg'), ]); $exists = Forum::where('bbs_id', config('api.forum.qna')) ->where('fu_id', auth()->id()) ->where('article_title', $request_data['title']) ->where('reg_date', '>=', now()->subMinutes(2)->toDateTimeString()) ->where('wrt_ip', $request->ip()) ->whereHas('text', function ($query) use ($request_data) { $query->whereRaw('DBMS_LOB.INSTR('.$query->qualifyColumn('article_text').', ?) > 0', [Forum\Text::escapeText($request_data['content'])]); }) ->count() > 0; if ($exists) { throw new CustomException(200, \LittlefoxLocalization::lang('api_forum_qna_store_already_registered_same_contents_msg'), 422); } \DB::transaction(function () use ($request_data) { $article = Forum::make([ 'bbs_id' => config('api.forum.qna'), 'article_title' => $request_data['title'], 'article_opt1' => $request_data['category_id'], 'fu_id' => auth()->id(), 'open_yn' => 'Y', ]); $article->saveOrFail(); $forum_text = $article->text()->make(['article_text' => $request_data['content']]); $forum_text->saveOrFail(); }); return $this->responseOnlyMessage(\LittlefoxLocalization::lang('api_forum_qna_store_success_msg')); } }
試したこと
$query->whereRaw('DBMS_LOB.INSTR('.$query->qualifyColumn('article_text').', ?) > 0', [Forum\Text::escapeText($request_data['content'])]);
デバッグして、この行でエラーがあることを確認。Oracleのこの書き方をMariaDBに変えたいが、どう編集すればいいのか困っている。
補足情報(FW/ツールのバージョンなど)
Laravel5.7, MariaDB10.3
まだ回答がついていません
会員登録して回答してみよう