bulk insert in procedure fails 1

[SimonSellick]

Hi,

My stored procedure attempts to insert from a query, along these lines:

insert into a (a, b, c)
  select 'a', b, c
  from b
  order by b;

The insert runs correctly in a MySQL window but within the procedure it fails and I cannot get at the errorno and message (actually, that is probably the real question - are they accessible within a stored procedure?).

Any pointers gratefully received.

Simon.

 

[DonQuichote]

I am afraid you over-simplified a bit. In your example, you re-use "b" so often that this cannot be right. Can you show us some real code?

Furthermore, I have never seen that an error was absent. Can you call the procedure from the MySQL command-line client? Does it give errors then?

If it does not give any error, what does "failing" mean in this occasion?


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[SimonSellick]

Hi,

Thanks for your response. The real procedure code is:

delimiter /go

drop procedure project_create
/go

create procedure project_create (
  p_programme integer
, p_PROJTYPE varchar(10)
, p_code varchar(10)
, p_name varchar(50)
, p_site integer
, p_modified_by varchar(10)
, out p_id integer
, out p_msg varchar(255)
)
begin
        /* set abort code */
        
        declare progress varchar(50) default 'set handler';
        
        declare exit handler for sqlwarning, sqlexception begin
                rollback;
                set p_msg = concat('project_create: aborted following sql error around ', progress);
        end;
        
	/* check params */
	
        set progress = 'check parameters';
        
	if not exists (select id from programme where id = p_programme) then
		set p_id = 0, 
		p_msg = concat('project_create: unknown programme id ', ifnull(convert(p_programme, char), '(null)'));

	elseif not exists (select code from refdata where domain = 'PROJTYPE' and code = p_PROJTYPE) then
		set p_id = 0, 
		p_msg = concat('project_create: unknown project type ', ifnull(p_PROJTYPE, '(null)'));

	elseif ifnull(p_code, '') = '' then
		set p_id = 0, 
		p_msg = 'project_create: null or blank project code';

	elseif exists (select code from programme where code = p_code) then
		set p_id = 0, 
		p_msg = concat('project_create: duplicate project code ', p_code);

	elseif ifnull(p_name, '') = '' then
		set p_id = 0, 
		p_msg = 'project_create: null or blank project name';

	elseif not exists (select id from site where id = p_site) then
		set p_id = 0, 
		p_msg = concat('project_create: unknown site id ', ifnull(convert(p_site, char), '(null)'));

	elseif not exists (select code from login where code = p_modified_by) then
		set p_id = 0, 
		p_msg = concat('project_create: unknown login ', ifnull(p_modified_by, '(null)'));

	else
		/* params ok - create project */

		start transaction;
        
                set progress = 'insert project';
		
		insert into project (programme,   PROJTYPE,   code,   name,   site,   modified_by) 
			values      (p_programme, p_PROJTYPE, p_code, p_name, p_site, p_modified_by);
		
		set p_id = last_insert_id();
        
                set progress = 'insert project_stages';
		
		insert into project_stage (project, stage)
			select p_id, s.code
                        from refdata_set pts
                        join stage s on s.code = pts.child_code
                        where pts.parent_domain = 'PROJTYPE' and pts.parent_code = p_PROJTYPE
			order by s.seq;
		
		set p_msg = 'Project created';
		
                set progress = 'insert history';
                
		call history_create('PRJ', 'INS', p_id, concat('created as type ', p_PROJTYPE), p_modified_by);

		commit;
	end if;
end
/go

Sorry that it's a bit long for a post. If I run it in the MySQL command line client, I get back p_msg reporting failure '...around insert project_stages' which points me towards the bulk insert being the problem - but if I paste the insert into MySQL client and replace the variables with the actual values, it runs correctly.

What I meant about the error number and message is that when you run a client you can get at them, but they do not seem to be available for inspection within the running procedure. That would help me to see what is going on, although in this case I suspect that I have just missed something silly - it's been a long day.

Thanks for your help.

 

[DonQuichote]

I cannot see anything wrong with this. My gut feeling says that the bulk insert could give a warning that is not displayed by your front-end application but is trapped by the procedure. Does SHOW WARNINGS; Say anything directly after issuing the bulk insert query?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[SimonSellick]

Hi,

Excellent suggesion - thank you! I removed the exit handler and added 'show warnings' just after the bulk insert, and it turned out that I had suffered a Homer (Simpson) moment - I forgot to give a value to a not-null column.

Thanks again for your help.

Is it possible to retrieve warnings/errors via a SELECT, or only through 'show'? The former would suggest a way to handle them programatically within the proc.

Simon.

 

[DonQuichote]

I spent an hour searching through the manual, and you can only retrieve the warning count, but not the warnings themselves, alas. Also, SHOW statements cannot be used as subqueries :-(.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[SimonSellick]

Cheers - have a star for spending an hour on my behalf!