databasePreparedStatement

Create SQL prepared statement

collapse all in page

Syntax

pstmt = databasePreparedStatement(conn,query)

Description

pstmt = databasePreparedStatement(conn,query) creates an SQLPreparedStatement object using the database connection and SQL query.

example

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
    1    1700    14.5000    '2014-09-23 09:38:34'
    2    1200     9.0000    '2014-07-08 22:50:45'
    3     356    17.0000    '2014-05-14 07:14:28'
    7    6034    16.0000    '2014-08-06 08:38:00'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)
Open Live Script

Create an SQL prepared statement to insert data from MATLAB® into a Microsoft® SQL Server® database using a JDBC database connection. Use the INSERT SQL statement for the SQL query. Execute the SQL prepared statement 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,'','');

Import data from the database using the sqlread function. Display the last few rows of data in the database table inventoryTable. 

tablename = "inventoryTable";
data = sqlread(conn,tablename);
tail(data,3)
ans=3×4 table
    11     567          0    '2012-09-11 00:30:24'
    12    1278          0    '2010-10-29 18:17:47'
    13    1700    14.5000    '2009-05-24 10:58:59'

Create an SQL prepared statement for inserting data using the JDBC database connection. The question marks in the INSERT SQL statement indicate it is an SQL prepared statement. This statement inserts data from MATLAB into the database table inventoryTable.

query = "INSERT INTO inventoryTable VALUES(?,?,?,?)";
pstmt = databasePreparedStatement(conn,query)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "INSERT INTO inventoryTable values(?,?,?,?)"
     ParameterCount: 4
     ParameterTypes: ["numeric"    "numeric"    "numeric"    "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 all parameters in the SQL prepared statement using their numeric indices. Specify the values to bind for the product number, quantity, price, and inventory date. 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 3 4];
values = {20,1000,55,"2019-04-25 00:00:00.000"};
pstmt = bindParamValues(pstmt,selection,values)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "INSERT INTO inventoryTable values(?,?,?,?)"
     ParameterCount: 4
     ParameterTypes: ["numeric"    "numeric"    "numeric"    "string"]
    ParameterValues: {[20]  [1000]  [55]  ["2019-04-25 00:00:00.000"]}

Insert data from MATLAB into the database using the bound parameter values. Execute the SQL INSERT statement using the execute function.

execute(conn,pstmt)

Display the inserted data in the database table inventoryTable. The last row in the table contains the inserted data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
tail(data,4)
ans=4×4 table
    11     567          0        '2012-09-11 00:30:24'
    12    1278          0        '2010-10-29 18:17:47'
    13    1700    14.5000        '2009-05-24 10:58:59'
    20    1000    55.0000    '2019-04-25 00:00:00.000'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)
Open Live Script

Create an SQL prepared statement to update data in a Microsoft® SQL Server® database using a JDBC database connection. Use the UPDATE SQL statement for the SQL query. Execute the SQL prepared statement 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,'','');

Import data from the database using the sqlread function. Display the first few rows of data in the database table inventoryTable. 

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×4 table
    1    1700    14.5000    '2014-09-23 09:38:34'
    2    1200     9.0000    '2014-07-08 22:50:45'
    3     356    17.0000    '2014-05-14 07:14:28'

Create an SQL prepared statement for updating data using the JDBC database connection. The question marks in the UPDATE SQL statement indicate it is an SQL prepared statement. This statement updates data in the database table inventoryTable.

query = strcat("UPDATE inventoryTable SET Quantity = ? ", ...
    "WHERE productNumber = ?");
pstmt = databasePreparedStatement(conn,query)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "UPDATE inventoryTable SET Quantity = ? WHERE productNumber = ?"
     ParameterCount: 2
     ParameterTypes: ["numeric"    "numeric"]
    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 all parameters in the SQL prepared statement using their numeric indices. Specify the values to bind for the quantity and product number. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1 2];
values = {2000,1};
pstmt = bindParamValues(pstmt,selection,values)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "UPDATE inventoryTable SET Quantity = ? WHERE productNumber = ?"
     ParameterCount: 2
     ParameterTypes: ["numeric"    "numeric"]
    ParameterValues: {[2000]  [1]}

Update data in the database using the bound parameter values. Execute the SQL UPDATE statement using the execute function.

execute(conn,pstmt)

Display the updated data in the database table inventoryTable. The first row in the table contains the updated quantity.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×4 table
    1    2000    14.5000    '2014-09-23 09:38:34'
    2    1200     9.0000    '2014-07-08 22:50:45'
    3     356    17.0000    '2014-05-14 07:14:28'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)
Open Live Script

Create an SQL prepared statement to delete data in a Microsoft® SQL Server® database using a JDBC database connection. Use the DELETE SQL statement for the SQL query. Execute the SQL prepared statement 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,'','');

Import data from the database using the sqlread function. Display rows of data in the database table productTable. The data contains rows for product numbers 16 through 20, which you will delete later in this example.

tablename = "productTable";
data = sqlread(conn,tablename)
data=15×5 table
     9    125970    1003    13     'Victorian Doll'
     8    212569    1001     5          'Train Set'
     7    389123    1007    16         'Engine Kit'
     2    400314    1002     9       'Painting Set'
     4    400339    1008    21      'Space Cruiser'
     1    400345    1001    14    'Building Blocks'
     5    400455    1005     3        'Tin Soldier'
     6    400876    1004     8          'Sail Boat'
     3    400999    1009    17             'Slinky'
    10    888652    1006    24         'Teddy Bear'
    16    567890    1001    10     'Magnetic Links'
    17    568800    1002    15            'Hot Rod'
    18    567900    1003    20         'Doll House'
    19    577610    1004    25       'Plush Monkey'

Create an SQL prepared statement for deleting data using the JDBC database connection. The question marks in the DELETE SQL statement indicate it is an SQL prepared statement. This statement deletes data in the database table productTable for a specified range of product numbers.

query = strcat("DELETE FROM productTable ", ...
    "WHERE productNumber > ? AND productNumber < ?");
pstmt = databasePreparedStatement(conn,query)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "DELETE FROM productTable WHERE productNumber > ? AND productNumber < ?"
     ParameterCount: 2
     ParameterTypes: ["numeric"    "numeric"]
    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 all parameters in the SQL prepared statement using their numeric indices. Specify the values to bind for the range of product numbers between 15 and 21 (exclusive). The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1 2];
values = {15,21};
pstmt = bindParamValues(pstmt,selection,values)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "DELETE FROM productTable WHERE productNumber > ? AND productNumber < ?"
     ParameterCount: 2
     ParameterTypes: ["numeric"    "numeric"]
    ParameterValues: {[15]  [21]}

Delete data in the database using the bound parameter values. Execute the SQL DELETE statement using the execute function.

execute(conn,pstmt)

Display data in the database table productTable. The rows with product numbers 16 through 20 are no longer in the table.

tablename = "productTable";
data = sqlread(conn,tablename)
data=10×5 table
     9    125970    1003    13     'Victorian Doll'
     8    212569    1001     5          'Train Set'
     7    389123    1007    16         'Engine Kit'
     2    400314    1002     9       'Painting Set'
     4    400339    1008    21      'Space Cruiser'
     1    400345    1001    14    'Building Blocks'
     5    400455    1005     3        'Tin Soldier'
     6    400876    1004     8          'Sail Boat'
     3    400999    1009    17             'Slinky'
    10    888652    1006    24         'Teddy Bear'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)
Open Live Script

Create an SQL prepared statement to call a stored procedure in a Microsoft® SQL Server® database using a JDBC database connection. Use the CALL SQL statement for the SQL query. Execute the SQL prepared statement and display the results.

For this example, the SQL Server database contains the stored procedure getSupplierInfo, which returns the information for suppliers in a specified city. This code defines the procedure. 

CREATE PROCEDURE dbo.getSupplierInfo
    (@cityName varchar(20))
AS
BEGIN
  	-- SET NOCOUNT ON added to prevent extra result 
	-- sets from interfering with SELECT statements.
	SET NOCOUNT ON
    SELECT * FROM dbo.suppliers WHERE City = @cityName
END

For SQL Server, the statement SET NOCOUNT ON suppresses the results of INSERT, UPDATE, and non-SELECT statements preceding the final SELECT query, so that you can import the results of the SELECT query.

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 calling the stored procedure using the JDBC database connection. The question marks in the CALL SQL statement indicate it is an SQL prepared statement. This statement calls the getSupplierInfo stored procedure in the database.

query = "{CALL dbo.getSupplierInfo(?)}";
pstmt = databasePreparedStatement(conn,query)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "{CALL dbo.getSupplierInfo(?)}"
     ParameterCount: 1
     ParameterTypes: "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 the parameter in the SQL prepared statement using its numeric index. Specify the value to bind as the city New York. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1];
values = "New York";
pstmt = bindParamValues(pstmt,selection,values)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "{CALL dbo.getSupplierInfo(?)}"
     ParameterCount: 1
     ParameterTypes: "string"
    ParameterValues: {["New York"]}

Display the results of the stored procedure. Execute the SQL CALL statement using the fetch function. The SQL prepared statement returns all information for suppliers located in New York City. 

results = fetch(conn,pstmt)
results=2×5 table
    1001     'Wonder Products'    'New York'    'United States'    '212 435 1617'
    1006    'ACME Toy Company'    'New York'    'United States'    '212 435 1618'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object created with the database function.

Note

The databasePreparedStatement function supports a JDBC database connection only.

SQL prepared statement query, specified as a character vector or string scalar that contains one of these SQL statements:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

  • CALL

Example: "SELECT * FROM inventoryTable WHERE inventoryDate > ? AND inventoryDate < ?" selects all data from the database table inventoryTable with an inventory date between two parameters.

Example: "INSERT INTO inventoryTable VALUES(?,?,?,?)" inserts data into the database table inventoryTable based on parameters for four database columns.

Data Types: char | string

Output Arguments

collapse all

SQL prepared statement, returned as an SQLPreparedStatement object.

Version History

Introduced in R2019b

See Also

| | | | |

Topics