Import Data Using SQL Prepared Statement with Multiple Parameter Values

This example shows how to import data from a Microsoft® SQL Server® database using an SQL prepared statement with a JDBC database connection. Use the SELECT SQL statement in a loop to execute the same SQL query for multiple values. Import the data from the database and display the results.

The SQL prepared statement is a database feature that enables you to execute the same SQL statement repeatedly with high efficiency. As you define the SQL prepared statement and bind values to parameters, the database completes these actions:

• Create an SQL statement template with parameters.

• Parse, compile, and perform query optimization on the SQL statement template, and store the results without execution.

• Bind values to the parameters and execute the SQL statement. (An application can execute the statement as many times as specified with different values.)

The advantages of using SQL prepared statements include improved performance and security.

You can execute SQL prepared statements by using a JDBC database connection only.

Connect to Database

Create a JDBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = "MSSQLServerJDBCAuth";
conn = database(datasource,'','');

Create SQL Prepared Statement

Create an SQL prepared statement for importing data from the SQL Server database using the JDBC database connection. The question marks in the SELECT SQL statement indicate it is an SQL prepared statement. This statement selects all data from the database table productTable for specified product descriptions.

query = strcat("SELECT * FROM productTable ", ...
    "WHERE productDescription = ?");
pstmt = databasePreparedStatement(conn,query);

Bind Multiple Values and Execute SQL Prepared Statement

Select the single parameter in the SQL prepared statement using its numeric index. Specify the values to bind as a string array containing three product descriptions: train set, engine kit, and slinky.

selection = [1];
values = ["Train Set" "Engine Kit" "Slinky"];

Bind parameter values in the SQL prepared statement. Using a for loop, bind the values for each product description and import data from the database using the bound parameter values. The results contain a table with three rows of data for the products with the specified product descriptions.

for i = 1:3
    pstmt = bindParamValues(pstmt,selection,values(i));
    results(i,:) = fetch(conn,pstmt);
end
results

results=3×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          8          2.1257e+05          1001             5         {'Train Set' }  
          7          3.8912e+05          1007            16         {'Engine Kit'}  
          3            4.01e+05          1009            17         {'Slinky'    }  

Close SQL Prepared Statement and Database Connection

close(pstmt)
close(conn)