bindParamValues

Bind values to parameters

Syntax

pstmt = bindParamValues(pstmt,selection,values)

Description

pstmt = bindParamValues(pstmt,selection,values) binds parameters specified in the selection argument to the values specified in the values argument for an SQLPreparedStatement object.

Examples

Import Data Using SQL Prepared Statement

Create an SQL prepared statement to import data from a Microsoft® SQL Server® database using a JDBC database connection. Use the SELECT SQL statement for the SQL query. Import the data from the database and display the results.

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 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 inventoryTable for the inventory that has an inventory date within a specified date range.

query = strcat("SELECT * FROM inventoryTable ", ...
    "WHERE inventoryDate > ? AND inventoryDate < ?");
pstmt = databasePreparedStatement(conn,query)

pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "SELECT * FROM inventoryTable where inventoryDate > ? AND inventoryDate < ?"
     ParameterCount: 2
     ParameterTypes: ["string"    "string"]
    ParameterValues: {[]  []}

pstmt is an SQLPreparedStatement object with these properties:

• SQLQuery — SQL prepared statement query

• ParameterCount — Parameter count

• ParameterTypes — Parameter types

• ParameterValues — Parameter values

Bind parameter values in the SQL prepared statement. Select both parameters in the SQL prepared statement using their numeric indices. Specify the values to bind as the inventory date range between January 1, 2014, and December 31, 2014. Match the format of dates in the database. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1 2];
values = {"2014-01-01 00:00:00.000", ...
    "2014-12-31 00:00:00.000"};
pstmt = bindParamValues(pstmt,selection,values)

pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "SELECT * FROM inventoryTable where inventoryDate > ? AND inventoryDate < ?"
     ParameterCount: 2
     ParameterTypes: ["string"    "string"]
    ParameterValues: {["2014-01-01 00:00:00.000"]  ["2014-12-31 00:00:00.000"]}

Import data from the database using the fetch function and bound parameter values. The results contain four rows of data that represent all inventory with an inventory date between January 1, 2014 and December 31, 2014.

results = fetch(conn,pstmt)

results=4×4 table
    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

          1            1700      14.5     {'2014-09-23 09:38:34'}
          2            1200         9     {'2014-07-08 22:50:45'}
          3             356        17     {'2014-05-14 07:14:28'}
          7            6034        16     {'2014-08-06 08:38:00'}

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

Input Arguments

pstmt — SQL prepared statement

SQLPreparedStatement object

SQL prepared statement, specified as an SQLPreparedStatement object.

selection — Selected parameters

numeric scalar | numeric array

Selected parameters, specified as a numeric scalar for one index or a numeric array for multiple indices.

Example: 1

Example: [1 2 3]

Data Types: double

values — Parameter values

numeric scalar | string scalar | character vector | ...

Parameter values to bind for the selected parameters, specified as a numeric scalar, string scalar, character vector, datetime array, logical, or cell array. The function treats missing values as NaN or missing. Use a cell array to specify multiple values.

The values for each parameter must have one of the types specified by the ParameterTypes property of the SQLPreparedStatement object.

Example: true

Example: "USA"

Example: {true,"USA",2,datetime('now')}

Data Types: double | logical | char | string | cell | datetime

Output Arguments

pstmt — SQL prepared statement

SQLPreparedStatement object

SQL prepared statement, returned as a SQLPreparedStatement object.

Version History

Introduced in R2019b