Calling stored procedures with parameters with the Database Connectivity Toolkit

[jdonad90]

Hi all,

I am new to the forum and am having difficulty finding a solution to a particular problem I am having regarding using the LabVIEW Database Connectivity Toolkit on a project I am currently working on at my job.  I have a database in which I have tables and stored procedures with parameters.  Some of these stored procedures have input, output, and return parameters.

I have been trying to follow this example but to no avail:  http://digital.ni.com/public.nsf/allkb/07FD130746083E0686257300006326C4?OpenDocument

One such stored procedure I am working on implementing is named "dbo.getAllowablePNs", which executes "SELECT * from DeviceType" (DeviceType is the table).  In this case, it does not require an input parameter, it has an output parameter that generates the table [cluster], and has a return parameter which returns an integer value (execution status code) to show if an error occurred.  The DeviceType table has 3 columns; ID (PK, int, not null), PN (nvarchar(15), null), and NumMACAddresses (int, null).  I have gone over many examples and have talking to NI support to try to implement this and similar stored procedures in LabVIEW but have not been successful.  I am able to connect to the database with the Open Connection VI without error, but am running into some confusion following this step.  I am then trying to use the Create Parameterized Query VI to call the stored procedure and set the parameters.  I assume I would then use the Set Parameter Value VI for each parameter that is wired into the parameters input on the previous Parameterized Query VI?  I am also having some confusion during and following these steps as well.  I would greatly appreciate any advice or suggestions anyone might have in regards to this situation as I am not a SQL expert.  Also, I would be happy to provide any more information that would be helpful.

Regards,

Jon

[aputman · Accepted solution]

Here is what i use to do this.  I believe i had to modify the Create Parameterized Query VI for it to work.  There is a forum post about it somewhere.

[aputman]

Here is the forum post i mentioned:

http://forums.ni.com/t5/LabVIEW/2013-Database-Toolkit-Create-Parameterized-Query-Postgres-Bug/m-p/24...

[jdonad90]

Thanks a lot for the help, it is much appreciated.  However, I can't find what function you are using following the output of the bundle section of your program.  Also, I'm not sure exacty how you went about wiring the parameters into the bundle and I am not certain what your "PARAM CONTROL" subVI is doing.  Could you please help explain this?

Regards,

Jon

[aputman]

First of all, the attached picture is a snippet which is LabVIEW code embedded into a .png file.  Drag and drop the image into a block diagram and the code will drop in automatically.  The version of Labview used to create the snippet is located at the top of the image.  Oh the beauty of technology. 

The function after the bundle is a "Build Array".  The VI after the Build Array is in the database toolkit, called Create Parameterized Query.  If you right-click the Parameters input of this VI, Create Constant....you will get an array of Param Control constants.  I removed the constant from the array so that i could use it to bundle the values into a cluster....and then build the cluster back into an array. 

[jdonad90]

Great, I will try these methods now.  I am on labVIEW 13.0 currently, is there a way to still be able to drag the snippet into the block diagram even though your example is using 12.0?  I had no idea about that feature, very cool indeed

[aputman]

Yes, you can open all code created in earlier versions.  And to be more specific about the snippet, my understanding is that the code is not actually embedded into the file but only a reference to the VIs.  If you don't have the Database Toolkit, it won't work.  Someone can correct me if i am wrong. 

[jdonad90]

The stored procedure I am working on now has an input parameter which is a string.  My confusion is how to I add a control or where would this go so the user can have the ability to enter the input parameter (which returns an output).  Attached is what I have so far. 

[jdonad90]

Also, I don't know if this would be helpful but here is the actual stored procedure in SQL:

CREATEPROCEDURE [dbo].[getLastSequenceNumber]

@p1 nvarchar(10)='WO-00000'

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

selectmax(SequenceNumber)from Devices where WorkOrderNumber= @p1

END

GO

[aputman]

Wire a string control to the variant value of the cluster.  Also remove the @ symbol from the parameter name (in the LabVIEW code....not the stored procedure).