QSqlQuery / SQL2005, stored procedure does not return string

Hello,

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

Code:

---

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:

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?

Anyone with the same experience here or any pointer on what could be wrong?

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?

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 =/

Hey I think I just found your problem while I was reading more about ODBC support!

Quote:

---

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:

Code:

---

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

---

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

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 ;)

Quote:

---

Originally Posted by _Stefan

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.

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 =/

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/2960...953#post138953

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

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.

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