execute
Execute SQL statement using relational database connection
Description
Examples
Execute Non-SELECT
SQL Statement
Using a relational database connection, create and execute a non-SELECT
SQL statement that deletes a database table.
This example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Load patient information into the MATLAB® workspace.
patients = readtable('patients.xls');
Create the patients
database table using the patient information.
tablename = 'patients';
sqlwrite(conn,tablename,patients)
Import the data from the patients
database table.
data = sqlread(conn,tablename);
Delete the patients
database table using the execute
function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)
Close the database connection.
close(conn)
Call Stored Procedure Without Input and Output Arguments
Working with a Microsoft®
SQL Server® database, run a stored procedure by using the native ODBC database
connection conn
.
Define a stored procedure named create_table
that creates a table
named test_table
by executing the following code. This procedure has
no input or output arguments. The code assumes that you are using a Microsoft
SQL Server database.
CREATE PROCEDURE create_table AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE test_table ( CATEGORY_ID INTEGER IDENTITY PRIMARY KEY, CATEGORY_DESC CHAR(50) NOT NULL ); END GO
Connect to the Microsoft
SQL Server database. This code assumes that you are connecting to a data source named
MS SQL Server
with a user name and password.
conn = database('MS SQL Server','username','pwd');
Call the stored procedure create_table
.
execute(conn,'create_table')
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)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
function.
sqlquery
— SQL statement
character vector | string scalar
SQL statement, specified as a character vector or string scalar. The SQL statement
can be any valid non-SELECT
SQL statement.
The SQL statement can be a stored procedure that does not return any result sets.
For stored procedures that return one or more result sets, use the fetch
function. For procedures that return output arguments, use the
runstoredprocedure
function.
For information about the SQL query language, see the SQL Tutorial.
Example: 'DROP TABLE patients'
Data Types: char
| string
pstmt
— SQL prepared statement
SQLPreparedStatement
object
SQL prepared statement, specified as an SQLPreparedStatement
object.
Version History
Introduced in R2018b
See Also
close
| database
| fetch
| sqlread
| runstoredprocedure
| databasePreparedStatement
| bindParamValues
| close
Topics
External Websites
MATLAB 명령
다음 MATLAB 명령에 해당하는 링크를 클릭했습니다.
명령을 실행하려면 MATLAB 명령 창에 입력하십시오. 웹 브라우저는 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)