I am trying to insert rows into a db table through a stored procedure. My table contains more than 500000 rows so when I execute the procedure, it takes more than 4h executing. Knowing that I am using Xampp server,…
$query = "CALL GetAllCategories()"; $stmt = $pdo->prepare($query); $stmt->execute(); $categories = $stmt->fetchAll(); I use this code to get categories from DB using stored procedures, no problems with that, everything works fine. This is GetAllCategories() procedures: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllCategories`()…
this is my proc, and below is my error, apart form the numerous ; missing, what am I Missing on line 2?? CREATE PROCEDURE sp_insertOnReject @id int AS BEGIN START TRANSACTION; UPDATE content_manager.submission SET state = 'Rejected' WHERE id =…
I'm trying to create stored procedures for my teammates who are non-technical and can't write MySQL queries on their own. They are currently accessing the database through phpMyAdmin. However, when I create stored procedures, my teammates are unable to execute…
DROP PROCEDURE CREATE_DUPLICATE_PRODUCT; DELIMITER $$ CREATE PROCEDURE `CREATE_DUPLICATE_PRODUCT`(IN `old_course_id` INT(25), IN `new_course_id` INT(25)) BEGIN DECLARE db_cursor CURSOR FOR SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity FROM cdp_organization_licenses as lic JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id JOIN cdp_organization_purchases as…
Here's shortened script of my stored procedure: DROP PROCEDURE IF EXISTS `GetVehicleDetails`; DELIMITER // CREATE PROCEDURE `GetVehicleDetails`( IN `inRefNo` VARCHAR(30) COLLATE utf8mb4_general_ci, IN `inSurveyType` VARCHAR(20) COLLATE utf8mb4_general_ci ) BEGIN DECLARE vehicleTypeID VARCHAR(2); SET FOREIGN_KEY_CHECKS = OFF; SELECT * FROM vehicle_details…
I am trying to create a login in SQL and using stored procedures to capture the login information. Even when executing the stored procedure on phpmyadmin, my out param is always returning Null, and I can't figure out why. userid…
How to display the information of the customer who has the most 'use' in 'audit' table. problem i dont know how to get information customer. select * from audit where used= (select MAX(used) from audit) audit table customer table
I have following query that works in phpMyAdmin SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS fence_ids FROM asset_fence af INNER JOIN assets a ON a.vehicle_id = af.vehicle_id WHERE a.client_id=1 But the same query gives me error in stored procedure ERROR…
I am having an issue with ONLY_FULL_GROUP_BY on MySQL 5.7.14 where I can't seem to disable it even though I've set the SQL mode. I have set both @@sql_mode to remove ONLY_FULL_GROUP_BY by running this command: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));…