Inserting data into MySQL Server Table

1 view (last 30 days)
Yaser Arafat
Yaser Arafat on 8 Nov 2016
Edited: Yaser Arafat on 8 Nov 2016
I executed the following Script step by step and getting error at last step (other are executed successfully)
conn = database('demo','','','Vendor','Microsoft SQL Server',...
'Server','YASER\SQLEXPRESS','AuthType','Windows',...
'PortNumber',63695);
sqlquery = ['CREATE TABLE Person(LastName varchar, '...
'FirstName varchar,Address varchar,Age int)'];
curs = exec(conn,sqlquery);
close(curs)
curs = exec(conn,'select * from Person');
curs = fetch(curs);
curs.Data
colnames = {'LastName','FirstName','Address','Age'};
data = {'kazi' 'yaser' 'osmanabad' 27};
tablename = 'Person';
datainsert(conn,tablename,colnames,data)
Error using database/datainsert (line 320)
Unable to complete insert operation. String or binary data would be truncated.

Answers (1)

Guillaume
Guillaume on 8 Nov 2016
Well, the error message is fairly clear, you're trying to insert data (in your case, strings) in a column that is bigger than the column size.
I was a bit puzzled why your VARCHAR did not have a size specifier in your table creation, most DB engines have it mandatory, but it turns out that SQL server does allow VARCHAR without a size specifier. However, in that case the size is assumed to be 1. Hence inserting any string longer than 1 character will cause the error you see.
The fix is simple, modify your table creation to add a size specification to your VARCHAR columns:
sqlquery = ['CREATE TABLE Person(LastName varchar(255), '... or however many characters is your maximum
'FirstName varchar(255), '...
'Address varchar(255), ' ...
'Age int)'];
Note that despite what you say in the title you're not using MYSQL since it would have rejected the table creation because the size specification is mandatory in MYSQL. From the connection string, it would appear you're using SQL Server.

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!