Update row in database

Hi ..

In database I have Id and its type is 'int', also I have Name and its type is varchar

When I tried to update the Id it was work:

Id = str2num(get(handles.ID,'String'));
y = exec(conn,'select Id from admin_info')
y = fetch(y);
y1=(y.Data)
colnames3={'Id'};
whereclause3 = ['WHERE ID= ' num2str(y1{1})]
update(conn,'admin_info', colnames3, Id, whereclause3);

But the update for the name it doesn't work !

name = get(handles.name, 'String')
x = exec(conn,'select name from admin_info');
x = fetch(x);
x1 =  x.Data
n = x1{1}
colnames2={'name'};
whereclause2 = ['WHERE name = ' n]
update(conn,'admin_info', colnames2, name, whereclause2);

[Han]

I'm stuck here I don't know why isn't worked !

Any help please?

[Bob Thompson]

Is it giving you a specific error? If so would you please post it along with which line is causing the error.

Also, what type of variable is 'n'?

[Han]

There is no errors.

n it should be string

 x1 =

cell

    'Admin'

n =

Admin

[Bob Thompson]

Ok, if there is not error how do you know that it is wrong? What results is it giving you, and what would you expect?

[Han]

It should update the name on database but it doesn't ! just updated the ID

[Bob Thompson]

I realize this is things you probably already checked, but it's what I usually go through to investigate my errors.

Have you confirmed that name, n, and whereclause2 are all proper values and classes?

If those are all correct, then it appears that all of the inputs to the update() command are correct and I don't know that I'm really going to be able to help you much, since I don't really know anything about Simulink commands.

[Greg]

I may be way off here (not a lot of experience with databases), but:

It looks like you are retrieving the entire column from the database, using MATLAB to find the row you want to change, then telling the database to change it. Why not build a single exec command and let the database do it all?

Otherwise, I notice you have a space before the = in the second where clause, but not the first. I don't think that matters, but maybe?

Answer 1

[Greg]

Reading the documentation for update, it indicates that the new data (input number 4) cannot be a character array. The ID worked because it can handle numeric matrix. To update a char/string entry, use a cellstr input.

update(conn,'admin_info', colnames2, cellstr(name), whereclause2);

[Han]

Thank you everyone..

..

Mr. Greg, I tried what you said and it's working when the whereclause like the following (Admin is the name saved in the database):

whereclause2 = ['WHERE name = ''Admin''']

Because the name in the database will change every time the user want to update the name, this will not work.

I tried this but it doesn't work:

whereclause2 = ['WHERE name = ' n]

..

n =
Admin

I should convert n to string but I don't know how can I do it?

[Bob Thompson]

I had no problems producing a character string for whereclause using two different methods:

whereclause2 = ['WHERE name = ' n]; % Your method
whereclause2 = strcat('WHERE name = n ',n); % Additional method

You may be running into the same issue as what Greg mentioned with the string vs character issue, so maybe use cellstr() to convert the character array into a string.

[Han]

Thank you for your answer..

x = exec(conn,'select name from admin_info');
x = fetch(x);
x1 = (x.Data) 
n = x1{1}
colnames2={'name'};
whereclause2 = ['WHERE name = ' cellstr(n)];
update(conn,'admin_info', colnames2, cellstr(name), whereclause2);

until now is not working for me :\ :\

[Han]

whereclause2 =

    1×2 cell array

      'WHERE name = '    'Admin'

Why is it 1×2 cell array?

[Bob Thompson]

You don't want to do cellstr(n), you want cellstr(whereclause2).

Of course I could also be totally just blowing smoke and have no idea what I'm doing.

[Han]

Ok, thank you anyway.

Answer 2

[Han]

Thank you to everyone..

Now it's working for me.

In the future, if someone faced the same problem, here is the code:

x = exec(conn,'select name from admin_info');
x = fetch(x);
xx = char(x.Data) 
n = sprintf('''%s''',xx)
colnames2={'name'};
whereclause2 = (['WHERE name =' n])
update(conn,'admin_info', colnames2, cellstr(name), whereclause2);

[Greg]

That is extremely complex code.

x = exec(conn,'select name from admin_info');
x = fetch(x);
colnames2={'name'};
% Probably want an actual index here, not a hardcoded 1
whereclause2 = sprint('WHERE name = ''%s''',x.Data{1});

Your use of

xx = char(x.Data);

will not do well when x.Data has multiple rows.