
SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE db.get_posts_by_userid does not exist
SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE db.get_posts_by_userid does not exist
In this tutorial, we will try to find the solution to "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE db.get_posts_by_userid does not exist" through programming.
I am getting the error `SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE db.get_posts_by_userid does not exist` while calling the procedure. Please tell me how to create a stored procedure and call it.-
Create stored procedure using migration in Laravel 9
You are getting error because you are trying to call the procedure which you did not have created. You have to create the stored procedure before calling it. Here is the code snippet you can use to create and call the procedure.
Create stored procedure
1. Create migration file using below command.
php artisan make:migration create_get_post_by_userid_procedure
2. Open created migration file.
File name will be different.
database\migrations\2022_08_18_072135_create_get_post_by_userid_procedure.php
3. Add the content to created migration file.
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; return new class extends Migration { /** * Run the migrations. * * @return void */ public function up() { $procedure = "DROP PROCEDURE IF EXISTS `get_posts_by_userid`; CREATE PROCEDURE `get_posts_by_userid` (IN idx int) BEGIN SELECT * FROM posts WHERE user_id = idx; END;"; DB::unprepared($procedure); } /** * Reverse the migrations. * * @return void */ public function down() { } };
4. Run migration command to migrate.
To run all migration you can call php artisan migrate command.
php artisan migrate
Or
To Run specific migration file you can specify the path of migration file using
–path=
php artisan migrate --path=/database/migrations/2022_08_18_072135_create_get_post_by_userid_procedure.php
Call stored procedure
routes\web.php
Route::get('call-procedure', function () { $userId = 1; $getPost = DB::select( 'CALL get_posts_by_userid('.$userId.')' ); dd($getPost); });
Output :
^ array:5 [▼ 0 => {#313 ▼ +"id": 11 +"title": "Labore do commodi et" +"slug": null +"body": "" +"is_shared": 0 +"parent_id": null +"user_id": 1 +"created_at": "2021-10-13 05:10:23" +"updated_at": "2021-10-13 05:10:23" } 1 => {#315 ▼ +"id": 16 +"title": "Sit quibusdam qui au" +"slug": null +"body": "Magni aut et velit e" +"is_shared": 0 +"parent_id": null +"user_id": 1 +"created_at": "2021-10-13 11:22:51" +"updated_at": "2021-10-13 11:22:51" } 2 => {#316 ▶} 3 => {#317 ▶} 4 => {#318 ▶} ]
If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.
Don't forget to share this article! Help us spread the word by clicking the share button below.
We appreciate your support and are committed to providing you valuable and informative content.
We are thankful for your never ending support.
Random Code Snippet Queries: Laravel
- How to customize or Change validation error messages
- How to pass variable from controller to model in Laravel
- Sample .htaccess file and index.php file under public directory in laravel
- Automatically remove records using Prunable trait in Laravel
- How to upload local Laravel project to server ?
- Run artisan command to generate key in laravel
- Laravel 11 project setup on localhost using breeze with blade step by step
- Credit card validation in laravel
- Ajax POST request in laravel
- Delete all related comments on deleting a post in Laravel
- Use withCount() to Calculate Child Relationship Records
- How to get column names from table in Laravel
- Count all and get 10 records after where condition in laravel
- External link not working in laravel blade
- How to get tomorrow and yesterday date in laravel
- Get duplicate records in laravel
- Add [name] to fillable property to allow mass assignment on [App\Models\Project]
- Syntax error or access violation: 1072 Key column 'role_id' doesn't exist in table (SQL: alter table `users` add constraint `users_role_id_foreign` foreign key (`role_id`) references `roles` (`id`))
- How to insert dynamic value to additional column in pivot table in laravel
- On delete set foreign id column value null using migration in laravel 8
- Call to undefined relationship [user] on model [App\Models\Post]
- Trying to access array offset on value of type null error in laravel
- Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails
- How to insert multiple rows in mysql using loop in laravel?
- JQuery each loop on json response after ajax in laravel