Edit: sqlwrite( ) fails when integer variable's value range is set too small.
6 views (last 30 days)
Show older comments
Edit:
previous wrong undestanding: "sqlwrite( ) fails to save a tall table to MySQL (native connection mode), when the number of rows of the table is equal to or greater than 19324."
Thanks for the answer below, I realized I had a range value problem.
% finb is my original table
tic
qb = finb(1:19324,:);
sqlwrite(conn, 'qb', qb)
toc
%---- Error message----------
Error using database.relational.connection/execute
Out of range value for column 'value' at row 19324.
Error in database.mysql.connection/sqlwriteHook (line 56)
execute(conn,insertquery);
Error in database.relational.connection/sqlwrite (line 880)
But when the table is shortened to 19323 rows, the code works fine.
qb = finb(1:19323,:);
tic
sqlwrite(conn, 'qb', qb)
toc
Elapsed time is 84.472844 seconds.
I found an explanation in a post from StackOverflow. It said MySQL imposes a limit on the number of rows for insertion. Is that the reason sqlwrite( ) fails? What does this magic number '19324' mean?
I am thinking about saving to MySQL chunk-wise through a for-loop. Is there other cleaner way to do it? Any tips you have are welcomed. Thank you!
0 Comments
Accepted Answer
Keshav
on 12 Apr 2023
Hi Simon,
I understand that you are trying to use “sqlwrite” function to save the table to MySql and you are getting an error “Out of range value for column 'value' at row 19324”. It might be possible that at row 19324 the column ‘value’ has a value that is greater than the range for the column ‘value’ datatype.
you can refer the below mysql documentation to check the range of differents datatypes:
More Answers (0)
See Also
Categories
Find more on Database Toolbox in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!