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:
call InsertNewImage(40, 'Filename', @imageId);
select @imageId;
call InsertNewImage(40, 'Filename', @imageId);
select @imageId;
To copy to clipboard, switch view to plain text mode
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:
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
);
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
);
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;
}
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;
}
To copy to clipboard, switch view to plain text mode
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:
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$$
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$$
To copy to clipboard, switch view to plain text mode
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$$
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$$
To copy to clipboard, switch view to plain text mode
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
MySql Stored Procedures Woes
Reply With Quote
Originally Posted by stevey
).
Bookmarks