How can I handle output data errors related to Qt QOdbc Stored procedure?

thisisseven

This stored procedure attempts to import OUTPUT data into Qt QOdbc.

The source is shown below.

QSqlQuery query (db); 
query.prepare ("EXEC dbo.Test_tb_show: IDX,: Test_1,: Test_2");
query.bindValue (": IDX", "1");
QVariant text (QString (127, ''));
query.bindValue (1, text, QSql :: Out);
query.bindValue (2, text, QSql :: Out);
query.exec (); 
qDebug () <<query.boundValue(1).toString();
query.finish();

When the above code is executed, the OUTPUT data is loaded, but only 127 characters of OUTPUT data are loaded. There are about 3000 characters in the database.

QVariant text (QString (127, ''); << ---- where 127 is replaced by 3000
QODBCResult :: exec: Unable to execute statement: "[Microsoft] [ODBC SQL Server Driver] [SQL Server] Invalid parameter 2 (''): Data type 0x63 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.

I get the above error and the data is not imported.

If you know how to solve this, please let me know.

JonB

@thisisseven
Hmm, yeah. Well, if I were you I would still try the suggestions I made, you might also try making the SQL side use varchar(max) or text, but I don't hold out much hope. Otherwise it looks like a limitation/bug for OUTPUT parameter types in the Qt SQL/ODBC driver, you might have to ask those people.

BTW: are you in control of the database, are you allowed to write another stored procedure to wrap the call to existing dbo.Test_tb_show? If so we could make it so you can read the result back a different way....

JonB

@thisisseven
Don't know if anything will help, but a couple of questions/suggestions:

query.bindValue (1, text, QSql :: Out);
query.bindValue (2, text, QSql :: Out);

Why do you bind 2 output parameters to the same code variable (text)? Unfortunately it's probably not the cause of the error, but it doesn't help clarify does it?

Just try declaring as QVariant text without any QString initializer? But I think you'll either get a complaint or a crash :(

Do you know what the actual SQL/ODBC types of the output parameter(s) are?

Your problem seems to be convincing Qt to use an acceptable type for "long" SQL strings when used as OUTPUT parameters, some issue about TEXT or varchar(max) or similar I should think. And I don't know whether/how you can influence Qt or QODBC code for this parameter type.

thisisseven

@JonB
thanks for reply.

Actual SQL/ODBC types of the output parameter(s) are varchar(3000).

JonB

@thisisseven
I can see that would be the case from your code if you use QVariant text (QString (3000, ''));. My question was: what are the actual types in the declaration of dbo.Test_tb_show?

thisisseven

@JonB

[dbo].[Test_tb_show]
@Test_1 VARCHAR(3000) OUTPUT,
@Test_2 VARCHAR(3000) OUTPUT

I used.

JonB

@thisisseven
Hmm, yeah. Well, if I were you I would still try the suggestions I made, you might also try making the SQL side use varchar(max) or text, but I don't hold out much hope. Otherwise it looks like a limitation/bug for OUTPUT parameter types in the Qt SQL/ODBC driver, you might have to ask those people.

BTW: are you in control of the database, are you allowed to write another stored procedure to wrap the call to existing dbo.Test_tb_show? If so we could make it so you can read the result back a different way....