Re: MySQL Store Procedure can't return multiple datasets
When you wrote "mysql client program" I'm assigning you mean the Python code I'm writing, the answer is no the stored procedure crashes.
As for create table statements...
/********************
* create tables *
********************/
create database db_settings;
use db_settings;
DROP TABLE IF EXISTS set_upload_type;
CREATE TABLE set_upload_type (
upl_id INT AUTO_INCREMENT PRIMARY KEY,
upl_name VARCHAR(255) NOT NULL,
upl_version VARCHAR(255) not null default '1.0',
upl_filetype VARCHAR(255) NOT NULL default 'csv',
upl_has_header boolean not null default true,
upl_prefix VARCHAR(255) NOT NULL
);
insert into set_upload_type (upl_name, upl_version, upl_filetype, upl_has_header, upl_prefix)
values("fred", '1.0', 'csv', true, 'tule');
SET @iUploadTypeID := last_insert_id();
DROP TABLE IF EXISTS set_upload_type_field;
CREATE TABLE set_upload_type_field (
fld_id INT AUTO_INCREMENT PRIMARY KEY,
fld_upload_type_id int,
fld_name VARCHAR(255) NOT NULL,
fld_header_text VARCHAR(255) NOT NULL,
fld_mysql_name VARCHAR(255) NOT NULL,
fld_field_type VARCHAR(255) NOT NULL
);
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'parent_category', 'parent_category', 'parent_category', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'category', 'category', 'category', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'partscode', 'partscode', 'partscode', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'brand_name', 'brand_name', 'brand_name', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'ean', 'ean', 'ean', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'sku', 'sku', 'sku', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'quantity', 'quantity', 'quantity', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'estimated_arrival_date', 'estimated_arrival_date', 'estimated_arrival_date', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'phase_out', 'phase_out', 'phase_out', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'modelname', 'modelname', 'modelname', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'option', 'option', 'option', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'customization', 'customization', 'customization', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'customization_char_limit', 'customization_char_limit', 'customization_char_limit', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'description', 'description', 'description', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'unit_measure', 'unit_measure', 'unit_measure', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'unit_weight', 'unit_weight', 'unit_weight', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_length', 'item_length', 'item_length', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_height', 'item_height', 'item_height', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_width', 'item_width', 'item_width', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'item_weight', 'item_weight', 'item_weight', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_length', 'shipping_length', 'shipping_length', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_height', 'shipping_height', 'shipping_height', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_width', 'shipping_width', 'shipping_width', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'shipping_weight', 'shipping_weight', 'shipping_weight', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'currency', 'currency', 'currency', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailprice', 'retailprice', 'retailprice', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailspecialprice', 'retailspecialprice', 'retailspecialprice', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'retailexpiredate', 'retailexpiredate', 'retailexpiredate', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerprice', 'resellerprice', 'resellerprice', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerspecialprice', 'resellerspecialprice', 'resellerspecialprice', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'resellerexpiredate', 'resellerexpiredate', 'resellerexpiredate', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'publish_date', 'publish_date', 'publish_date', '');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_1', 'image_1', 'image_1', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_2', 'image_2', 'image_2', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_3', 'image_3', 'image_3', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_4', 'image_4', 'image_4', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_5', 'image_5', 'image_5', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_6', 'image_6', 'image_6', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_7', 'image_7', 'image_7', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_8', 'image_8', 'image_8', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_9', 'image_9', 'image_9', 'image');
insert into set_upload_type_field (fld_upload_type_id, fld_name, fld_header_text, fld_mysql_name, fld_field_type) values (@iUploadTypeID, 'image_10', 'image_10', 'image_10', 'image');
This with this data using the parameter "fred" it should work.
call getSettingsUploadStock("fred")
Using any other parameter the stored procedure should still function however the result will tell you that there is no data for that value.
call getSettingsUploadStock("bob")
But if you look at the bottom of stored procedure you will see three select statements which are the three datasets that should be returned. Comment out any two and everything functions.
select err_ID, err_Category, err_Name, err_Long_Description, err_Values
from temp_errors;
select tmp_id, tmp_name, tmp_version, tmp_filetype, tmp_has_header, tmp_prefix
from temp_upload_type;
select tmp_id, tmp_supplier_id, tmp_name, tmp_header_text, tmp_mysql_name, tmp_field_type
from temp_upload_type_field;
But if you return more than one dataset it crashes.