datainsert

(To be removed) Export MATLAB data into database table

collapse all in page

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

Syntax

datainsert(conn,tablename,colnames,data)

Description

example

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

collapse all

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)

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)

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)

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

collapse all

Database connection, specified as a connection object created with the database function.

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

Example: 'employees'

Data Types: char | string

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

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.

Compatibility Considerations

expand all

Not recommended starting in R2018a

See Also

| | | | |

Topics

External Websites

Introduced in R2011a

Database Toolbox Documentation

Support