How do I retrieve an OUTPUT parameter using an SQL Stored Procedure Call?

[jim006]

I've searched through the Developer Zone and also searched www.ni.com for "stored procedure" and still having problems with retreiving an OUTPUT parameter from an SQL Stored Procedure Call.

I'd post the VI I'm working on but without the SQL database and specific procedure, it won't do much good. Suffice it say that the database is an corporate wide SQL database with validated procedures... so the problem is on my side with Labview.

Here's the sequence of operations I'm using in my VI:

1. Open a DB connection using the string name of the ODBC object.

2. Create Parameterized Query using an constant array of parameter clusters where each cluster has parameter name, parameter type enumer
ation, direction type enumeration, and variant. I type in a parameter names to match the parameters in the stored procedure (for example: @sint_status). I also select INPUT and OUTPUT direction types as appropriate and the proper data type (for example: LONG). The variant is left blank. The Query string is "call Method1(?, ?, ?, ?)" without the quotes.

3. Set all the INPUT parameters using the Set Parameter VI

4. Execute the Query using the Execute Query VI

5. Read the OUTPUT parameters using the Get Parameter VI

6. Free the command-record reference

7. Free the command reference

8. Close the DB connection

I know the Query runs OK because the Stored Procedure creates a new row in a database table with the correct values that I passed with the INPUT parameters.

However, the OUTPUT Parameter is ALWAYS zero! If I initialize the OUTPUT parameter using a Set Parameter VI before the Execute Query VI, then the parameter value after the query is always what I initia
lized before the query.

I'm really perplexed on this one.

Am I doing something stupid here?

Looking for feedback.

Thanks!

Jim

[David Duffey]

Hi Jim,

Check out this post:

http://exchange.ni.com/servlet/ProcessRequest?RHIVEID=101&RNAME=ViewQuestion&HOID=506500000008000000BC750000&ECategory=LabVIEW.LabVIEW+General

And let me know if that helps you.

[jim006]

Thanks,

Unfortunately I found that earlier. The corporate database and stored procedures already comply with thy syntax specified in that post.

I think something larger scale is wrong. I wrote a C++ DLL and test application using the CDatabase class. That program can also execute the stored procedure and pass values through INPUT parameters. The DLL is experiencing the same problems I saw in Labview... no values in the OUTPUT parameters.

Is it possible that ODBC is messing this up, or possibly some setting in ODBC or in the SQL Server?

[jim006]

After much hair pulling and some help from a few friends, the solution to this problem lies with Microsoft Knowledge Base article 181837.

The SQL Stored Procedures I'm calling do not returns records. As a result, any queries made with the CRecordSet Object don't return OUTPUT parameter values.

I suspect that the Database Toolset Execute SQL VI is also using a derrivative of the CRecordSet class because it doesn't return OUTPUT parameters either.

The solution in Visual C++ was to use a class derrived from CDatabase with a overridden bind parameter function per the knowledge base article above.

I believe the Database Toolset is also effected by this problem and should probably be fixed.

[mikeporter]

If I were to make a bet, I would say the issue is related to ODBC. Can you get access to you database using ADO directly? This removes two pieces: the ODBC drivers and the SQL Toolbox.

Mike...