execute

Execute SQL statement on database

Syntax

execute(conn,sqlquery)

execute(conn,pstmt)

Description

execute(conn,sqlquery) executes an SQL statement on a database using the specified connection object, conn. sqlquery must contain a non-SELECT SQL statement such as INSERT, UPDATE, or DELETE.

conn can be any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• SQLite

• ODBC

• JDBC

Use execute to run SQL statements that modify data or database structure. You can also run stored procedures that do not return result sets.

execute(conn,pstmt) runs a non-SELECT SQL prepared statement specified by pstmt. Use prepared statements for parameterized SQL queries to improve performance when executing the same statement multiple times. conn must be a JDBC connection object.

Examples

Execute Non-SELECT SQL Statement

Using a relational database connection, create and execute a non-SELECT SQL statement that deletes a database table. The MySQLNative data source configures a database connection to a MySQL® database.

This example uses a MySQL database version 5.7.22 database and the MySQL Connector/C++ driver version 8.0.15.

Connect to the database using the data source name, user name, and password.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password);

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 = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

Ensure that the table no longer exists.

data = sqlfind(conn,tablename)

data =

  0×5 empty table

Close the database connection.

close(conn)

Execute Non-SELECT SQL Statement on PostgreSQL Database

Using the PostgreSQL native interface, create and execute a non-SELECT SQL statement that deletes a database table. The PostgreSQLDataSource data source configures a database connection to a PostgreSQL database.

This example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Connect to the database using the data source name, username, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";

conn = postgresql(datasource,username,password);

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 = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

Ensure that the table no longer exists.

data = sqlfind(conn,tablename)

data =

  0×5 empty table

Close the database connection.

close(conn)

Execute Non-SELECT SQL Statement on DuckDB Database

Create a transient, in-memory DuckDB™ database connection by using the duckdb function.

conn = duckdb();

Load the patients information into the MATLAB® workspace.

patients = readtable("patients.xls");

Create a database table to store the patients information by using the sqlwrite function.

tablename = "patients";
sqlwrite(conn,tablename,patients)

Import the data from the patients database table by using the sqlread function.

sqlread(conn,tablename);

Delete the database table by using the execute function with the following query.

sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

Use the sqlfind function to verify that the table no longer exists.

data = sqlfind(conn,tablename)

data =

  0×5 empty table

    Catalog    Schema    Table    Columns    Type
    _______    ______    _____    _______    ____

Close the connection.

close(conn);

Execute Non-SELECT SQL Statement

Using an SQLite database connection and the MATLAB® interface to SQLite, create and execute a non-SELECT SQL statement that creates a temporary view in the database and imports its contents.

Create an SQLite database connection to the SQLite database file tutorial.db.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Create an SQL statement that creates a temporary view named PRODUCTNAMES. The view selects all product names using the productDescription column of the productTable database table. Execute the CREATE SQL statement.

sqlquery = strcat("CREATE TEMP VIEW PRODUCTNAMES AS", ...
    " SELECT productDescription FROM productTable");
execute(conn,sqlquery)

Import the product names using the new temporary view and display the first three names.

sqlquery = "SELECT * FROM PRODUCTNAMES";
results = fetch(conn,sqlquery);
head(results,3)

    productDescription
    __________________

     "Victorian Doll" 
     "Train Set"      
     "Engine Kit"     

Close the database connection.

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
    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)

Input Arguments

conn — Database connection

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | SQLite connection object | ODBC connection object | JDBC connection object

Database connection, specified as any of the following:

• MySQL connection object created by using the mysql function.

• PostgreSQL connection object created by using the postgresql function.

• DuckDB connection object created by using the duckdb function.

• SQLite connection object created by using the sqlite function.

• ODBC connection object created by using the database function.

• JDBC connection object created by using the database function.

sqlquery — SQL statement

string scalar | character vector

SQL statement, specified as a string scalar character or vector. sqlquery must be a valid non-SELECT SQL statement such as INSERT, UPDATE, DELETE, CREATE TABLE, or DROP TABLE.

You can also specify a stored procedure that does not return result sets. For procedures that return results, use fetch.

For information about the SQL query language, see the PostgreSQL Documentation.

Example: "DROP TABLE patients"

Data Types: string | char

pstmt — SQL prepared statement

SQLPreparedStatement object

SQL prepared statement, specified as an SQLPreparedStatement object. A prepared statement is a precompiled SQL command that can include parameter placeholders. Use this object when you need to execute parameterized queries securely or run the same SQL statement multiple times with different values.

Version History

Introduced in R2020b

R2026a: execute supports DuckDB connection objects

You can now execute queries on a DuckDB database by specifying a DuckDB connection object when using the execute function.