Main Content

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
    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)
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
    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

         11             567         0     {'2012-09-11 00:30:24'}
         12            1278         0     {'2010-10-29 18:17:47'}
         13            1700      14.5     {'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
    productNumber    Quantity    Price           inventoryDate       
    _____________    ________    _____    ___________________________

         11             567         0     {'2012-09-11 00:30:24'    }
         12            1278         0     {'2010-10-29 18:17:47'    }
         13            1700      14.5     {'2009-05-24 10:58:59'    }
         20            1000        55     {'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
    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'}

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
    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

          1            2000      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'}

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
    productNumber    stockNumber    supplierNumber    unitCost    productDescription 
    _____________    ___________    ______________    ________    ___________________

          9          1.2597e+05          1003            13       {'Victorian Doll' }
          8          2.1257e+05          1001             5       {'Train Set'      }
          7          3.8912e+05          1007            16       {'Engine Kit'     }
          2          4.0031e+05          1002             9       {'Painting Set'   }
          4          4.0034e+05          1008            21       {'Space Cruiser'  }
          1          4.0035e+05          1001            14       {'Building Blocks'}
          5          4.0046e+05          1005             3       {'Tin Soldier'    }
          6          4.0088e+05          1004             8       {'Sail Boat'      }
          3            4.01e+05          1009            17       {'Slinky'         }
         10          8.8865e+05          1006            24       {'Teddy Bear'     }
         16          5.6789e+05          1001            10       {'Magnetic Links' }
         17           5.688e+05          1002            15       {'Hot Rod'        }
         18           5.679e+05          1003            20       {'Doll House'     }
         19          5.7761e+05          1004            25       {'Plush Monkey'   }
         20          5.0034e+05          1005            30       {'Kitchen Set'    }

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
    productNumber    stockNumber    supplierNumber    unitCost    productDescription 
    _____________    ___________    ______________    ________    ___________________

          9          1.2597e+05          1003            13       {'Victorian Doll' }
          8          2.1257e+05          1001             5       {'Train Set'      }
          7          3.8912e+05          1007            16       {'Engine Kit'     }
          2          4.0031e+05          1002             9       {'Painting Set'   }
          4          4.0034e+05          1008            21       {'Space Cruiser'  }
          1          4.0035e+05          1001            14       {'Building Blocks'}
          5          4.0046e+05          1005             3       {'Tin Soldier'    }
          6          4.0088e+05          1004             8       {'Sail Boat'      }
          3            4.01e+05          1009            17       {'Slinky'         }
         10          8.8865e+05          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
    SupplierNumber        SupplierName            City             Country            FaxNumber    
    ______________    ____________________    ____________    _________________    ________________

         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