datainsert

(To be removed) Export MATLAB data into database table

The datainsert function will be removed in a future release. Use the sqlwrite function instead. For details, see Version History.

Syntax

datainsert(conn,tablename,colnames,data)

Description

datainsert(conn,tablename,colnames,data) exports data from the MATLAB^® workspace and inserts it into existing columns of a database table using the database connection conn.

Examples

Export MATLAB Cell Array Data

Use an ODBC connection and a cell array to export inventory data from MATLAB into a MySQL^® database table.

Create a database connection conn to the MySQL database using the native ODBC interface. Here, this code assumes that you are connecting to an ODBC data source named MySQL with a user name and password. This database contains the table inventoryTable with these columns:

• productNumber

• Quantity

• Price

• inventoryDate

conn = database('MySQL','username','pwd');

Display the last rows in inventoryTable before inserting data.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...    
    [14]    [2000]    [19.1000]    '2014-10-22 10:52...'
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'

Create a cell array of column names for the database table inventoryTable.

colnames = {'productNumber','Quantity','Price','inventoryDate'};

Define a cell array of input data to insert.

data = {50 100 15.50 datestr(now,'yyyy-mm-dd HH:MM:SS')};

Insert the input data into the table inventoryTable using the database connection.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display the inserted data in inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'
    [50]    [ 100]    [15.5000]    '2014-10-22 11:29...'

The last row contains the inserted data.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Export MATLAB Table Data

Use a JDBC connection and a MATLAB table to export inventory data from MATLAB into a MySQL database table.

Create a database connection conn to the MySQL database using the JDBC driver. Use the Vendor name-value pair argument of the database function to specify a connection to a MySQL database. Here, this code assumes that you are connecting to a database named dbname on a database server named sname with a user name and password. This database contains the table inventoryTable with these columns:

• productNumber

• Quantity

• Price

• inventoryDate

conn = database('dbname','username','pwd', ...
    'Vendor','MySQL', ...
    'Server','sname');

Display the last rows in inventoryTable before inserting data.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...    
    [14]    [2000]    [19.1000]    '2014-10-22 10:52...'
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'

Create a cell array of column names for the database table inventoryTable.

colnames = {'productNumber','Quantity','Price','inventoryDate'};

Define the input data as a table.

data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ...
    'VariableNames',colnames);

Insert the input data into the table inventoryTable using the database connection.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display the inserted data in inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'
    [50]    [ 100]    [15.5000]    '2014-10-22 11:29...'

The last row contains the inserted data.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Export MATLAB Structure Data

Use an ODBC connection and a MATLAB structure to export inventory data from MATLAB into a MySQL database table.

Create a database connection conn to the MySQL database using the native ODBC interface. Here, this code assumes that you are connecting to an ODBC data source named MySQL with a user name and password. This database contains the table inventoryTable with these columns:

• productNumber

• Quantity

• Price

• inventoryDate

conn = database('MySQL','username','pwd');

Display the last rows in inventoryTable before inserting data.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...    
    [14]    [2000]    [19.1000]    '2014-10-22 10:52...'
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'

Create a cell array of column names for the database table inventoryTable.

colnames = {'productNumber','Quantity','Price','inventoryDate'};

Define the input data as a structure.

data = struct('productNumber',50,'Quantity',100,'Price',15.50, ...
    'inventoryDate',datestr(now,'yyyy-mm-dd HH:MM:SS'));

Insert the input data into the table inventoryTable using the database connection.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display the inserted data in inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data

ans = 

    ...
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'
    [50]    [ 100]    [15.5000]    '2014-10-22 11:29...'

The last row contains the inserted data.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Export MATLAB Numeric Matrix Data

Use a JDBC connection and a numeric matrix to export sales data from MATLAB into a MySQL database table.

Create a database connection conn to the MySQL database using the JDBC driver. Use the Vendor name-value pair argument of database to specify a connection to a MySQL database. Here, this code assumes that you are connecting to a database named dbname on a database server named sname with a user name and password. This database contains the table salesVolume with the column stockNumber and columns for each month of the year.

conn = database('dbname','username','pwd', ...
    'Vendor','MySQL', ...
    'Server','sname');

Display the last rows in salesVolume before inserting data.

curs = exec(conn,'SELECT * FROM salesVolume');
curs = fetch(curs);
curs.Data

ans = 

  Columns 1 through 8

    ...
    [470816]    [3100]    [9400]    [1540]    [1500]    [1350]    [1190]    [ 900]
    [510099]    [ 235]    [1800]    [1040]    [ 900]    [ 750]    [ 700]    [ 400]
    [899752]    [ 123]    [1700]    [ 823]    [ 701]    [ 689]    [ 621]    [ 545]

  Columns 9 through 13

    ...
    [867]    [ 923]    [1400]    [ 3000]    [35000]
    [350]    [ 500]    [ 100]    [ 3000]    [18000]
    [421]    [ 495]    [ 650]    [ 4200]    [11000]

Create a cell array of column names for the database table salesVolume.

colnames = {'stockNumber','January','February' ...
    'March','April','May', ...
    'June','July','August', ...
    'September','October','November', ...
    'December'};

Define the numeric matrix data that contains the sales volume data.

data = [777666,0,350,400,450,250,450,500,515, ...
    235,100,300,600];

Insert the contents of data into the table salesVolume using the database connection.

tablename = 'salesVolume';

datainsert(conn,tablename,colnames,data) 

Display the inserted data in salesVolume.

curs = exec(conn,'SELECT * FROM salesVolume');
curs = fetch(curs);
curs.Data

ans = 

  Columns 1 through 8

    ...
    [510099]    [ 235]    [1800]    [1040]    [ 900]    [ 750]    [ 700]    [ 400]
    [899752]    [ 123]    [1700]    [ 823]    [ 701]    [ 689]    [ 621]    [ 545]
    [777666]    [   0]    [ 350]    [ 400]    [ 450]    [ 250]    [ 450]    [ 500]

Columns 9 through 13

    ...
    [350]    [ 500]    [ 100]    [ 3000]    [18000]
    [421]    [ 495]    [ 650]    [ 4200]    [11000]
    [515]    [ 235]    [ 100]    [  300]    [  600]

The last row contains the inserted data.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

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.

tablename — Database table name

string scalar | character vector

Database table name, specified as a string scalar or character vector denoting the name of a table in the database.

Example: "employees"

Data Types: string | char

colnames — Database table column names

cell array of character vectors | string array

Database table column names, specified as a cell array of one or more character vectors or a string array to denote the columns in the existing database table tablename.

Example: {'col1','col2','col3'}

Data Types: cell | string

data — Insert data

cell array | numeric matrix | table | structure | dataset

Insert data, specified as a cell array, numeric matrix, table, structure, or dataset array.

If you are connecting to a database using a JDBC driver, then convert the insert data to a supported format before running datainsert. If data contains MATLAB dates, times, or timestamps, use this formatting:

• Dates must be character vectors of the form yyyy-mm-dd.

• Times must be character vectors of the form HH:MM:SS.

• Timestamps must be character vectors of the form yyyy-mm-dd HH:MM:SS.FFF.

The database preference settings NullNumberWrite and NullStringWrite do not apply to this function. If data contains null entries and NaNs, convert these entries to an empty value ''.

The datainsert function supports inserting MATLAB date numbers and NaNs when data is a numeric matrix. Date numbers inserted into database date and time columns convert to java.sql.Date. Upon insertion into the target database, any converted date and time data accurately reverts to the native database format.

If data is a structure, then field names in the structure must match colnames.

If data is a table or a dataset array, then the variable names in the table or dataset array must match colnames.

Tips

• When you establish a database connection using a JDBC driver, datainsert performs faster than fastinsert.

• datainsert uses the SQL TRANSACTION statement to insert records with faster performance for these databases:

  • Microsoft^® SQL Server^®

  • MySQL

  • Oracle^®

  • PostgreSQL

  For other databases, refer to your database documentation to start a transaction manually. Before running datainsert, use exec to start the transaction.

• The value of the AutoCommit property in the connection object determines whether datainsert automatically commits the data to the database.

  • To view the AutoCommit value, access it using the connection object; for example, conn.AutoCommit.

  • To set the AutoCommit value, use the corresponding name-value pair argument in the database function.

  • To commit the data to the database, use the commit function or issue an SQL COMMIT statement using the exec function.

  • To roll back the data, use rollback or issue an SQL ROLLBACK statement using the exec function.

Alternative Functionality

To export MATLAB data into a database, you can use the fastinsert and insert functions. For maximum performance, use datainsert.

Version History

Introduced in R2011a

R2018a: datainsert function will be removed

The datainsert function will be removed in a future release. Use the sqlwrite function instead. Some differences between the workflows require updates to your code.

Update Code

In prior releases, you exported data from the MATLAB workspace into a database by using the datainsert function and four input arguments. For example:

colnames = {'productNumber','Quantity','Price','inventoryDate'};
data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ...
    'VariableNames',colnames);
tablename = 'inventoryTable';
datainsert(conn,tablename,colnames,data)

Now the sqlwrite function requires only three input arguments.

colnames = {'productNumber','Quantity','Price','inventoryDate'};
data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ...
    'VariableNames',colnames);
tablename = 'inventoryTable';
sqlwrite(conn,tablename,data)