databasePreparedStatement
Create SQL prepared statement
Description
creates an pstmt
= databasePreparedStatement(conn
,query
)SQLPreparedStatement
object using the database connection and SQL query.
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 queryParameterCount
— Parameter countParameterTypes
— Parameter typesParameterValues
— 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)
Insert Data Using SQL Prepared Statement
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 queryParameterCount
— Parameter countParameterTypes
— Parameter typesParameterValues
— 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)
Update Data Using SQL Prepared Statement
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 queryParameterCount
— Parameter countParameterTypes
— Parameter typesParameterValues
— 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)
Delete Data Using SQL Prepared Statement
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 queryParameterCount
— Parameter countParameterTypes
— Parameter typesParameterValues
— 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)
Call Stored Procedure Using SQL Prepared Statement
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 queryParameterCount
— Parameter countParameterTypes
— Parameter typesParameterValues
— 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
conn
— Database connection
connection
object
Database connection, specified as a connection
object created with the database
function.
Note
The databasePreparedStatement
function supports a JDBC
database connection only.
query
— SQL prepared statement
character vector | string scalar
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
pstmt
— SQL prepared statement
SQLPreparedStatement
object
SQL prepared statement, returned as an SQLPreparedStatement
object.
Version History
Introduced in R2019b
Commande MATLAB
Vous avez cliqué sur un lien qui correspond à cette commande MATLAB :
Pour exécuter la commande, saisissez-la dans la fenêtre de commande de MATLAB. Les navigateurs web ne supportent pas les commandes MATLAB.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)