User`s guide

6 Using Database Toolbox Functions
6-30
close(conn)
Bulk Insert into MySQL
1
Connect to the MySQL server. For JDBC driver use, add the JAR file to the
MATLAB Java class path.
javaaddpath 'path\mysql-connector-java-5.1.13-bin.jar';
conn = database('databasename', 'user', 'password',...
'com.mysql.jdbc.Driver',...
'jdbc:mysql://machine:port/databasename');
2
Create a table named BULKTEST.
e = exec(conn,['create table BULKTEST (salary decimal, '...
'player varchar(25), signed_date varchar(25), '...
'team varchar(25))']);
close(e)
3
Create a data record, such as the one that follows.
A = {100000.00,'KGreen','06/22/2011','Challengers'};
4
Expand A to be a 10,000-record data set.
A = A(ones(10000,1),:);
5
Write data to a file for bulk insert.
Note: MySQL reads files saved locally, even if you are connecting to a remote
machine.
fid = fopen('c:\temp\tmp.txt','wt');
for i = 1:size(A,1)
fprintf(fid,'%10.2f \t %s \t %s \t %s \n',...
A{i,1},A{i,2},A{i,3},A{i,4});
end
fclose(fid);
6
Run the bulk insert. Note the use of local infile.
e = exec(conn,['load data local infile '...
' ''C:\\temp\\tmp.txt'' into table BULKTEST '...
'fields terminated by ''\t'' lines terminated '...
'by ''\n''']);
close(e)