SQLPreparedStatement

SQL prepared statement

expand all in page

Description

The SQLPreparedStatement object enables you to create an SQL prepared statement. An SQL prepared statement can import, update, insert, or delete data in a database. Also, an SQL prepared statement can call stored procedures in a database.

The SQL statement can be one of these statements:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • CALL

An SQL prepared statement contains parameters that are bound to values. By binding the parameters, you can execute the same SQL statement for different values repeatedly. The benefits of using SQL prepared statements include improved performance and security.

Note

If you use SQL prepared statements with a JDBC driver and a database other than Microsoft® SQL Server® or PostgreSQL, the behavior of the SQL prepared statement varies based on the JDBC driver implementation of the statement. The behavior can cause unexpected results.

Creation

Create an SQLPreparedStatement object with the databasePreparedStatement function.

Properties

expand all

This property is read-only.

SQL prepared statement query, specified as a string scalar.

Example: "SELECT * FROM inventoryTable WHERE inventoryDate > ? AND inventoryDate < ?"

Data Types: string

This property is read-only.

Parameter count, specified as a numeric scalar for the total number of parameters in the SQL prepared statement.

Data Types: double

This property is read-only.

Parameter types, specified as a string array. The bind values must be one of the parameter types.

The parameter type is one of these data type values:

  • "double"

  • "string"

  • "datetime"

  • "logical"

Example: ["string" "string"]

Data Types: string

Parameter values, specified as a cell array of the values to bind with the defined parameters in the SQL prepared statement.

Example: {2 5}

Data Types: cell

Object Functions

bindParamValuesBind values to parameters
closeClose SQL prepared statement

Examples

collapse all

Open Live Script

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)

Limitations

  • The SQLPreparedStatement object supports a JDBC database connection only.

See Also

| |

Topics

Introduced in R2019b

Database Toolbox Documentation

Support