MATLAB Answers

0

Putting two seperate columns in to one variable and how to insert a Array into mysql

Asked by Martijn Roks on 11 Apr 2019
Latest activity Commented on by Martijn Roks on 14 Apr 2019
Hello,
I have to question which you have seen in the title. My questions are:
  1. How can I put two columns into one variable? Example: You have column A and column B, but you want them both in C. I have no Idea how I can do that. Every time I tried they were put beneath eachother.
  2. How can I send the variable Data to my sql tabel. I know you need something like this: query= ['INSERT INTO ' table ' VALUES (' pk ',''' voornaam ''',''' achternaam ''')']; But with that line of code you can't send a array.
I hope someone can help me with this, because I need to know this for next week !!
b=0;
for i= 1:1:10
analog= readVoltage (board, 'A0');
writePWMVoltage (board, 'D3', analog);
disp(['analog= ', num2str(analog)]);
pause(1);
A(i)= analog;
A=A';
b= b+1;
B(i)= b;
B=B';
end
Data= [A:B];

  2 Comments

Martijn - if you want to create a matrix from your two columns (so that they are "side by side") then change your line of code
Data= [A:B];
to
Data= [A B];
The colon operator (that you are using) is not appropriate in this context. If you want to create just one column of data then you would do
Data= [A;B];
using a semi-colon to indicate that B should be vertically concatenated with A. As for writing/inserting the data into the database table, perhaps try using sqlwrite or an equvalent method.
Hello,
Thanks for helping me with the first problem!
The code that I want to use is this:
But with that code you can only send one thing that someone has set in the gui.
I need to send the value data in to mysql, but I don't know how to do that. Do you know how to do that?
query= ['INSERT INTO ' table ' VALUES (' pk ',''' voornaam ''',''' achternaam ''')'];

Sign in to comment.

1 Answer

Answer by Guillaume
on 12 Apr 2019

Assuming that by column, you mean column vector use the horizontal concatenation operation , instead of the vertical concatenation operator ;, or [horzcat], or cat(2, ...):
%all of these do horizontal concatenation:
C = [A, B];
C = [A B]; %space works the same as comma. Comma is clearer.
C = horzcat(A, B);
C = cat(2, A, B);
%all of these do vertical concatenation
C = [A; B];
C = vertcat(A, B);
C = cat(1, A, B);
To insert multiple rows in mySQL, write one VALUES statement per row:
INSERT INTO tbl(COL1, COL2)
VALUES(col1row1, col2row1),
VALUES(col1row2, col2row2),
...
VALUES(col1rown, col2rown);
To construct that SQL statement in matlab, you could use:
%demo data:
A = [1 2; 3 4; 5 6];
columnnames = {'Day', 'Month'};
tablename = 'MyTable'
values = compose('VALUES(%d, %d)', A); %note that the format depends on the type of data you insert %d works for INTEGER only
select = sprintf('ISERT INTO %s(%s) %s;', tablename, strjoin(columnnames, ', '), strjoin(values, ', '))

  6 Comments

I just found out that matlab sends out this message:
Attributes: []
Data: 0
DatabaseObject: [1×1 database.jdbc.connection]
RowLimit: 0
SQLQuery: 'INSERT INTO table(PK, Voltage) VALUES(1, 4.418377e+00), VALUES(2, 3.338221e+00), VALUES(3, 2.399804e+00), VALUES(4, 1.295210e+00), VALUES(5, 1.466276e+00), VALUES(6, 2.834800e+00), VALUES(7, 4.012708e+00), VALUES(8, 5), VALUES(9, 2.609971e+00), VALUES(10, 2.297165e-01);'
Message: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table(PK, Voltage) VALUES(1, 4.418377e+00), VALUES(2, 3.338221e+00), VALUES(3, 2' at line 1'
Type: 'Database Cursor Object'
ResultSet: 0
Cursor: 0
Statement: 0
So how I read it I need to de replace sprintf with something else, but I don't know what.
Hope you can help me out!
"I read it I need to de replace sprintf with something else"
Huh! Why? sprintf does the required job, the problem is with the query syntax.
So, look at your query and the mysql manual and figure out the syntax error(s). Problems I see,
  • The name of the table in the query is table. The code you show has Data as the table name. There is no way that the exact code you show would have put table there, so you have been using a different code. In any case, make sure you've got the correct table name. table as a name for a table is utterly useless, it doesn't describe anything about the table purpose.
  • As I wrote as a comment in the code %d as a format only works properly for integers. Your Voltage is clearly non-integer so you should use a different format. Possibly %g
  • I made a mistake with the syntax of VALUES. The VALUES keyword is only needed once. Again, look at the mysql documentation to work out the syntax. I haven't used mysql for years.
So, the query should probably be:
values = compose('(%d, %g)', Data1); %format string to be TAILORED to the content of Data1
query = sprintf('INSERT INTO %s (%s) VALUES %s;', tablename, strjoin(columnnames, ', '), strjoin(values, ', '));

Sign in to comment.