MySql Stored Procedures Woes

Hi,

I've been having some trouble with some database access code on MySql5.
A few weeks ago this code worked fine without a hitch, now I'm getting dopey results.

I have a couple of stored procedures which work together as part of a transaction.
CreateNewBatch and InsertNewImage.

Here's the scenario:
I call the CreateNewBatch stored proc.
For example CreateNewBatch('New Job', @outval1)
I get an int back as an out parameter.
So, we'll call the new BatchID = 52 for this example.

Then I feed 52 (foreign key to batch table) into the InsertNewImage stored proc.
For example InsertNewImage(52, '000000ca', @outval1)
I would expect to get back the ID of the new record in the Image table in which 000000ca is inserted.
Instead I keep getting back 52.

I know my stored procedure works fine because I have run it and tested the out param within a MySql front end called SqlYog.
Here's what I run:

Code:

---

call InsertNewImage(40, 'Filename', @imageId);
select @imageId;

---

The result is always an incrementing number which does represent the new Image table records being inserted.

I create the above "call <proc>" string which I exec on a QSqlQuery.
Here's the code for both the CreateNewBatch and InsertNewImage functions:

Code:

---

int ImageCacheDataConnector::CreateBatch(const QString& jobName)
{
	bool closeOnExit = false;
	if(!_dbase.isOpen())
	{
		_dbase.open();
		closeOnExit = true;
	}
 
	QString command = QString("call CreateNewBatch('%1', @outval1)").arg(jobName);
 
	QSqlQuery query;
	query.exec(command);
    query.exec("select @outval1");
    query.next();
 
	if(closeOnExit)
		_dbase.close();
 
	return query.value(0).toInt();  // New Batch ID
}
 
bool ImageCacheDataConnector::InsertImage(int batchID, const QString& filename)
{
	bool ret = false;
 
	bool closeOnExit = false;
	if(!_dbase.isOpen())
	{
		_dbase.open();
		closeOnExit = true;
	}
 
	QString command = QString("call InsertNewImage(%1, '%2', @outval1)").arg(batchID).arg(filename);
 
	QSqlQuery query;
	query.exec(command);
	query.exec("select @outval1");
	query.next();
	int i = query.value(0).toInt();  // New Image ID
 
	if(i > 0)
	{
		ret = true;
	}
 
	if(closeOnExit)
		_dbase.close();
 
	return ret;
}

---

As stated in the Qt docco MySql can't make use of the standard bind parameters for stored procedures, so you have to execute a call to the proc with a session parameter variable called @outval1 (or anything starting with @) then call another select statement to "get" the values.

In the above code, if I use @outval1 in each member function then I get the BatchID back rather than the ImageID when running InsertNewImage.
If I change the out parameter name in the InsertNewImage call to anything different to that used in InsertNewBatch (or vice versa) then the return value from InsertNewImage is always 0.

As I said, I'm calling CreateBatch , then InsertImage within a transaction.
I was under the impression that using @outval1 type parameters as above meant the variable was around for the life of the QSqlQuery instance but it seems it's being shared for the life of the transaction in my case (maybe).
If so then why wouldn't it be overwritten anyway with the second use of it in InsertImage?
Can anyone explain why using any other parameter name yields a return result of 0?

Remember that if I run InsertNewImage stored procedure manually within SqlYog I get the correct output parameter result.

Here's a listing of the stored procedures I'm calling:

Code:

---

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateNewBatch`(
	p_jobName varchar(32),
	out p_identity int
)
BEGIN
	declare m_jobID int;
	set p_identity = -1 ;
	select ID into m_jobID
	from job
	where job.JobName = p_jobName;
	# Stick code in here to order by batch number
	# and get the next batch number (maybe a function)
	if m_jobID > -1
	then
		insert into batch
		(
			JobID,
			Status
		)
		values
		(
			m_jobID,
			1
		);
 
 
		set p_identity = LAST_INSERT_ID();
	end if;
END$$

---

Code:

---

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertNewImage`(
	p_batchID int,
	p_cacheFilename varchar(128),
 
	out p_identity int
)
BEGIN
	DECLARE m_nextPosition int;
	SET m_nextPosition = NextImageNumber(p_batchID);
	insert into image
	(
		BatchID,
		Filename,
		Position,
		Format,
		HasAlternate
	)
	values
	(
		p_batchID,
		p_cacheFilename,
		m_nextPosition,
		'tif',
		0
	);
	set p_identity = LAST_INSERT_ID();
 
END$$

---

NextImageNumber is a helper function I defined in MySql.

I'm using Qt 4.2.0 and the problem is also happening when I switch back to 4.1.4
Thanks in advance for any assistance,

Steve York

Quote:

---

Originally Posted by stevey

Then I feed 52 (foreign key to batch table) into the InsertNewImage stored proc.
For example InsertNewImage(52, '000000ca', @outval1)
I would expect to get back the ID of the new record in the Image table in which 000000ca is inserted.
Instead I keep getting back 52.

---

Does it work if you use a different variable (say @outval2) in second call?

If I use anything other than @outval1 then I get a return value of 0.
That said, if I change the first one in CreatBatch() top @outval2 and leave the one in InsertImage as @outval1 I also get 0 back.

Quote:

---

Originally Posted by stevey

if I change the first one in CreatBatch() top @outval2 and leave the one in InsertImage as @outval1 I also get 0 back.

---

I hope you did remember to update the variable names in select statements too.

Did you upgrade Qt or MySQL recently?

Yes, I did the replaement in both the call and select statement.
Maybe I need to recompile the MySql plugin against my MySql build.

I'll let you know how it goes.

Well, I'm afrid a recompile made no difference.
If I packaged up a test console application along with a DDL and stored procedures would someone be able to try it out and see if it's "just me"?


Thanks

Quote:

---

Originally Posted by stevey

If I packaged up a test console application along with a DDL and stored procedures would someone be able to try it out and see if it's "just me"?

---

Sure .

Hi,

I've attached some source code which will call 3 DAL functions that I use in my main application.

The process is:
1 - Create 'Job'
2 - Add a 'Batch'
3 - Insert a number in 'Image's (just filenames)

Each DAL call returns the ID column of each newly inserted row.
This time things are a little different to my first post in this thread.
Now I get no result back at all and no data is being inserted.
It's like the Stored Procedure call isn't even being made.
I've tried running it without a transaction and get the same results.
I thought that maybe it was a permissions thing as I hadn't added 'localhost' as an address from which the user could connect (within MySql Admin).
Still no joy.

I've tested the "call..." and related "select @id" commands within a query tab in SqlYog and it all works fine.
I could create a job, batch and add images.
So it definitely looks like this just can't reach my stored procedures.

It has worked before so I'm at a loss.
I've included a .sql script which can be loaded with MySql Admin to build the test schema on your MySql instance.

Thanks for any assistance,

Steve York

I've added "if( ! ... ) qDebug() << query.lastError();" around all query.exec() and got this:

Quote:

---

QSqlError(1318, "QMYSQL: Unable to execute query", "Incorrect number of arguments for PROCEDURE imagingsystem.CreateNewBatch; expected 4, got 2")
QSqlError(1048, "QMYSQL: Unable to execute query", "Column 'BatchID' cannot be null")
...
QSqlError(1048, "QMYSQL: Unable to execute query", "Column 'BatchID' cannot be null")
Batch 1 = -1
Image ID's:
0
...

---

Then I've changed CreateNewBatch to CreateNewJob in CreateJob() and got this:

Quote:

---

Batch 1 = 1
Image ID's:
1
...
1

Batch 2 = 2
Image ID's:
1
...
1

Batch 3 = 3
Image ID's:
1
...
1

---

so it looks like it worked... once (but it's due to auto increment fields).

Hi,

Yeah I realised after I read your message that I made a typo.
It should have been a call to SP CreateNewJob in the CreatJob function (slaps head).

The whole problem in the end was that my InertNewImage SP was flawed.
The issue was that I wasn't checking the lasterror().
In my full time job I do mostly C# programming, and ADO with SqlServer returns exceptions when things go haywire which causes the program to crash if you don't catch the exception.
QSqlQuery obviusly handles it all completely differently.
I figured that because there was no crash, then it was all okay.

Another thing I'm used to also is returning -1 int my p_identity out parameter.
If you set a value here then QSqlQuery will succeed on the .exec(command).
So the key here is to never assign a value to the out parameters, so if somethings goes wrong and you don't get to the SET p_identity = LAST_INSERT_ID(); at the end of the SP, then the out parameter will return NULL, in turn causing the .exec() to return false.

I now have my image records appearing.
Thanks Jacek for pointing out the test and qDebug message (why did I not think of it ? :o ).
It gave me the message I needed to work out how to proceed.
Above all I need to drop my .net habits.


Steve York