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

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];

[Geoff Hayes]

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.

[Martijn Roks]

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 ''')'];

Answer 1

[Guillaume]

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, ', '))

[Martijn Roks]

This is the code I have now. But the code doesn't uploade anything to my mysql table. Or am I doing something horribly wrong. Please help (this is just a bit of my code, I don't get errors or something it just doesn't upload to my mysql) .

board= arduino();
b=0;

for i= 1:1:10
    analog= readVoltage (board, 'A0');
    writePWMVoltage (board, 'D3', analog);
    disp(['analog= ', num2str(analog)]);
    pause(1);
    
    A(i)= analog; 
   
    b= b+1;
    B(i)= b;
 
end

A=A';
B=B';

handles.Data= [B A];

disp(handles.Data);

colnames= {'PK','Voltage'};
set(handles.uitable2,'data',handles.Data ,'ColumnName',colnames);

guidata(hObject, handles);

  function verstuur_Callback(hObject, eventdata, handles)
% hObject    handle to verstuur (see GCBO)
% eventdata  reserved - to be defined in a future version of MATLAB
% handles    structure with handles and user data (see GUIDATA)
gebruikersnaam= get(handles.edit_gebruikersnaam, 'String');
wachtwoord= get(handles.edit_wachtwoord, 'String');
datasource= get(handles.edit_tabel, 'String');

conn= database(datasource, gebruikersnaam, wachtwoord, handles.driver, handles.url);

Data1 =[handles.Data];
columnnames = {'PK', 'Voltage'};
tablename = 'Data';
values = compose('VALUES(%d, %d)', Data1);  %note that the format depends on the type of data you insert %d works for INTEGER only
query = sprintf ('InSERT INTO %s(%s) %s;', tablename, strjoin(columnnames, ', '), strjoin(values, ', '))

%query= ['INSERT INTO ' table ' VALUES (''''handles.Data'''')'];

%execute(conn,query);

close (conn);

[Martijn Roks]

I have changed the code in to the code below, but the data I want to send doesn't come into my table in mysql. The odd thing is I don't get an error or something like that. The rest of the code works just fine. So there is a mistake in how I send the data over, but I don't know what.

% --- Executes on button press in inputdata.
function inputdata_Callback(hObject, eventdata, handles)
% hObject    handle to inputdata (see GCBO)
% eventdata  reserved - to be defined in a future version of MATLAB
% handles    structure with handles and user data (see GUIDATA)
board= arduino();
b=0;

for i= 1:1:10
    analog= readVoltage (board, 'A0');
    writePWMVoltage (board, 'D3', analog);
    disp(['analog= ', num2str(analog)]);
    pause(1);
    
    A(i)= analog; 
   
    b= b+1;
    B(i)= b;
 
end

A=A';
B=B';

handles.voltage= A;
handles.pk= B;

handles.Data= [B A];

disp(handles.Data);

colnames= {'PK','Voltage'};
set(handles.uitable2,'data',handles.Data ,'ColumnName',colnames);

guidata(hObject, handles);

  function verstuur_Callback(hObject, eventdata, handles)
% hObject    handle to verstuur (see GCBO)
% eventdata  reserved - to be defined in a future version of MATLAB
% handles    structure with handles and user data (see GUIDATA)
gebruikersnaam= get(handles.edit_gebruikersnaam, 'String');
wachtwoord= get(handles.edit_wachtwoord, 'String');
datasource= get(handles.edit_tabel, 'String');

conn= database(datasource, gebruikersnaam, wachtwoord, handles.driver, handles.url);

Data1 =[handles.pk, handles.voltage];
columnnames = {'PK', 'Voltage'};
tablename = 'Data';
values = compose('VALUES(%d, %d)', Data1);  %note that the format depends on the type of data you insert %d works for INTEGER only
query = sprintf('InSERT INTO %s(%s) %s;', tablename, strjoin(columnnames, ', '), strjoin(values, ', '));

%query= ['INSERT INTO ' table ' VALUES (''''handles.Data'''')'];

send = exec(conn,query);

close (conn);

[Guillaume]

It would be unusual for nothing to happen and yet not getting an error.

• Are you sure that your callback is actually getting called?
• Have you tried the same query from a mysql command prompt? Copy the fully composed query straight from matlab.

[Martijn Roks]

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!

[Guillaume]

"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, ', '));

[Martijn Roks]

Thanks!

That was it. Thanks for helping!