Performing a database call

[pvanzyl]

Upfront I apologize for my lack on knowledge about databases and the database connectivity toolkit.  I am struggling through it with my IT team and making forward slow progress.  

We are finally able to connect to an Oracle(11g) database with the database connectivity toolkit (LV2015) and I have successfully read some data using the select VI.  I am pretty impressed with how this works and like the integration this will provide for my project.  The problem is that my IT team is very nervous about a 3rd party application reading and writing to their database tables.  Their concern is for corrupting data, expecially when writing.  

They would rather me perform a call to the database using APIs.  To begin, they gave me a test script that would send an email once received by the database.  The script is the following:

    sy_common.send_mail('dgeitz@seatsinc.com'

                       ,'Pvanzyl@----------.COM'

                       ,'Database connection worked'

                       ,'You have successfully called a database procedure from LabView.'

                       ,'d------z@---------.com'

                       ,null

                       ,'text/html');

Is it possible to run such a script with any of the database connectivity toolkit VIs?  

If this works, the next step would be to have several calls that would read and write data using APIs to make the equipment communicate with the database as needed.

[aputman · Accepted solution]

Yes, look at the "Create Parameterized Query VI".  Supply the name of the procedure as the query text and then build the parameter array with your input values.  Set stored procedure to TRUE. 

At least this is how it works with MSSQL.  I am not familiar with Oracle but it should work the same way across all platforms. 

[pvanzyl]

Thanks, your solution worked.  I just wanted to output my solution so that it can be used by others.  Please keep in mind that the procedure called by my VI is defined in Oracle, so if you want to perform the same task it will have to be done similarly.

[aputman]

You can clean up the diagram doing something like this.  All of those parameter set VIs are not necessary.    

If you need tighter control over the parameter type (INT, STRING, DATETIME, etc), and specifying direction, you can add a case structure inside the for loop with the names of the parameters fed into it by an array.  The order of the array is important as it determines the order that they are sent to the stored procedure.