Inserting Data into SQL database table without database toolbox

I am trying to insert data into an SQL database, but do not have the database toolbox. I approached the issue similar to the example given in https://www.mathworks.com/matlabcentral/answers/323587-error-when-inserting-data-into-database-without-toolbox. I am receiving the following error message:

*No method 'Execute' with matching signature found for class 'COM.ADODB_Connection'.

Error in SQL_Practice (line 38)
SynergyInsert = conn.Execute(sqlinsert);*

I am running R2015a and my code is shown below:

sqlquery1 = ['SELECT * FROM cct_pcm_test_results '];

conn = actxserver('ADODB.Connection');

conn.Open('driver={ABC Server}; server=123.123.123; port=123; dns=123_ABCDE');

conn.Execute('use [123_ABCDE]'); 

SynergyTable1 = conn.Execute(sqlquery1).GetRows();

Inserted = {acell, TC, frz, melt, frzminpass, frzmaxpass, meltminpass, meltmaxpass,...
    npfrz, npmelt, frzslope, meltslope};
sqlinsert = ['INSERT INTO cct_pcm_test_results (test_no, tc_no, frz_temp, melt_temp, frz_min_pass, frz_max_pass, melt_min_pass, melt_max_pass, frz_data_point_cnt, melt_data_point_cnt, frz_slope, melt_slope)' ...
  'VALUES ' (Inserted)];
SynergyInsert = conn.Execute(sqlinsert);

Answer 1

[Guillaume]

You have

Inserted = {acell, TC, frz, melt, frzminpass, frzmaxpass, meltminpass, meltmaxpass,...
  npfrz, npmelt, frzslope, meltslope};

So Inserted is a cell array. Then:

sqlinsert = ['INSERT INTO cct_pcm_test_results (acell; TC; frz; melt; frzminpass; frzmaxpass; meltminpass; meltmaxpass; npfrz; npmelt; frzslope; meltslope)' ...
  'VALUES ' (Inserted)];

which concatenates a char vector with a cell array (note that the () brackets around Inserted don't do anything) and creates a cell array with one more element. It does not insert the content of the cell array into the char vector. Indeed, as the error tells your there's no execute method that takes cell arrays as inputs.

You will have to use sprintf or the newer compose to build your SQL. Possibly, the simplest is to convert your cell array to string to let matlab do the type conversion automatically, then use compose and strjoin:

sqlvals = string(Inserted);  %use default conversion from whatever is Inserted to string. May not always be right
sqlinsert = compose(['INSERT INTO cct_pcm_test_results (acell; TC; frz; melt; frzminpass; frzmaxpass; meltminpass; meltmaxpass; npfrz; npmelt; frzslope; meltslope), ...
                     ' VALUES (%s)'], ...
                    strjoin(sqlval, ', '));

The above should work if the values are numeric but will fail if the values are text since it doesn't insert the '' around the values.

Also, I'm not sure you can separate the fields by ;. It's normally a ,.

[Nicholas Bergstrom]

Guillaume,

Thank you for your prompt and detailed answer. I am new to MATLAB and extremely new to communicating with sql tables through it and your answers have been a great resource.

I think it will work I just have a primary key issue in my table that, based on my researched, needs to be fixed by removing the primary key constraint. I ended up turning my "Inserted" values into strings and making "Inserted" a 1xN cell array of strings as was required by strjoin. Also R2015 did not have compose so I had to use sprintf. My code is below:

Inserted = {acell, TC, frz, melt, frzminpass, frzmaxpass, meltminpass, meltmaxpass,...
    npfrz, npmelt, frzslope, meltslope};

sqlinsert = sprintf(['INSERT INTO cct_pcm_test_results (test_no, tc_no, frz_temp, melt_temp, frz_min_pass, frz_max_pass, melt_min_pass, melt_max_pass, frz_data_point_cnt, melt_data_point_cnt, frz_slope, melt_slope)' ...
                      'VALUES (%s)'], ...
                    strjoin(Inserted, ', '));
SynergyInsert = conn.Execute(sqlinsert);

[Nicholas Bergstrom]

Fixed the primary key issue and it works! Thank you!