PDA

View Full Version : QSqlQuery / SQL2005, stored procedure does not return string



_Stefan
2nd April 2010, 14:43
Hello,

I have a stored procedure declared in a SQL Server 2005 database, declared as followed:



CREATE PROCEDURE insertCustomer
@CustomerMap varchar(32),
@Result int OUT,
@Message nvarchar(255) OUT AS

SELECT * FROM Customers WHERE MapName = @CustomerMap
IF @@ROWCOUNT <> 0 BEGIN
SET @Message='Er bestaat al een klantmap met deze naam...'
SET @Result=1
RETURN
END

INSERT INTO Customers (MapName, Status) VALUES (@CustomerMap, 'N')
SET @Message='OK'
SET @Result=0
RETURN


In Qt, I want to execute this procedure, and get my result and message back.

I amusing the following code:



OdbcDbHandler(QString OdbcDriver, QString Host, QString Database, QString User, QString Password)
{
m_dbDriver = OdbcDriver;
m_dbHost = Host;
m_dbDatabase = Database;
m_dbUser = User;
m_dbPass = Password;

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("Driver={" + OdbcDriver + "};Server=" + Host + ";Database=" + Database + ";");
db.setUserName(m_dbUser);
db.setPassword(m_dbPass);
}

bool open()
{
return QSqlDatabase::database().open();
}

bool execProcedure (DbProcedure Procedure)
{
QSqlDatabase db = QSqlDatabase::database();

if (!db.isOpen())
{
if (!db.open())
return false;
}

QSqlQuery query;

query.prepare("{ CALL insertCustomer (?, ?, ?) }");

QVariant Map = "customermap";
QVariant Result = -1;
QVariant Message("");

query.bindValue(0, Map);
query.bindValue(1, Result, QSql::Out);
query.bindValue(2, Message, QSql::Out);

if(!query.exec())
{
QString error = query.lastError().text();
return false;
}

query.nextResult();

Result = query.boundValue(1);
Message = query.boundValue(2);

return true;
}


The stored procedures executes as expected and my result code is set, to 0 or 1, depending on what i put int.
However, Message always seems to be empty, while this should be set too.

Anyone any clue what I'm doing wrong?

_Stefan
7th April 2010, 09:40
Anyone with the same experience here or any pointer on what could be wrong?

ajg85
7th April 2010, 20:08
I had a similar problem with receiving empty strings from QSqlRecord values using a simply QSqlQuery even though numeric types populate and I can insert and update the table values just fine. Unfortunately I have not figured out a way to get it working yet. I believe it is a collation problem specific to QODBC which may be dependent on what driver you are using. I'm using freeTDS version 8.0 on Ubuntu ... what are you using?

_Stefan
8th April 2010, 15:35
I am using two Win XP Pro stations. I use QODBC and set the driver to the SQL Native Client. Could be it conflicts right there. Rather odd it would give numbers but interprets strings wrong still though =/

ajg85
8th April 2010, 16:04
Hey I think I just found your problem while I was reading more about ODBC support!



ODBC Stored Procedure Support

With Microsoft SQL Server the result set returned by a stored procedure that uses the return statement, or returns multiple result sets, will be accessible only if you set the query's forward only mode to forward using QSqlQuery::setForwardOnly().


So in your code add the following call before your prepare statement:


QSqlQuery query(db);
query.setForwardOnly(true);
query.prepare("{ CALL insertCustomer (?, ?, ?) }");


Now if only I could figure out my problem :cry:

_Stefan
8th April 2010, 16:52
Yes, I had found that solution, but it turns out, that option is set by default anyway and hence, does not solve the problem, unfortunately ;)

ajg85
8th April 2010, 17:43
Yes, I had found that solution, but it turns out, that option is set by default anyway and hence, does not solve the problem, unfortunately ;)

It's set to false by default though ... have you tried explicitly setting it true? If it still doesn't work I'd check if query.lastError().isValid() returns true ... if so check that error text() for better clues.

_Stefan
9th April 2010, 11:23
Yup tried setting it explicitly, same result.
It says it has no errors at all, which would make sense, since it did actually execute the query.
Just strings are not filled in =/

ajg85
15th April 2010, 00:50
I solved my problem with empty strings it was because the qodbc driver was compiled with unicode support and I forgot unix/linux only does ASCII ... can check my thread here in case it's related to your problem:

http://www.qtcentre.org/threads/29608-QSqlTableModel-select%28%29-fails-with-QODBC-connection-to-SQL-Express?p=138953#post138953

_Stefan
16th April 2010, 14:37
I tried including that define, still gave me nothing.
But I also do not run this on Linux, but XP Pro.

Still a weird problem. Could still be in conversion somewhere, I just do not have a clue on how you would solve that ;p

ajg85
16th April 2010, 19:39
Bummer ... yeah that would be a unix only problem with the unicode. Does your stored procedure behave as expected when you run it from query analyzer or another app (like osql from the commandline)? If you remove the QT layer entirely and it works you'd at least be certain it's something in your QT code or with the QODBC sqldriver. Try throwing a break point into your app and following it into QT to see what's happening in the QSqlDriver when it's executed as that's what narrowed it down for me.

randydellinger
11th April 2014, 15:11
I had a similar problem on Linux, what worked for me was to initialize the variable that's expecting the return string with enough spaces to hold the longest message.
So instead of
QVariant Message("");
use
QVariant Message(" ");

I hope this helps someone, I wasted a lot of time trying to figure this out!

Randy