Passing a Matlab variable to an SQL Database

Question

I have to following problem:

I have a matlab script that consists of a loop. Each loop cycle produces an output. This output has the variable name 'outpt'.

After each cycle in the loop, I would like to take the value of the variable 'outpt' and put this value in a table stored in a datawarehouse.

I am using: Matlab R2012b and Microsoft SQL Server Management Studio 2008

The connection to the SQL table is NOT a problem. I am using these command lines:

%% LOAD SQL DATABASE
% Make connection to server.  Note that the password has been omitted.
% Using ODBC driver.
conn = database('servername','','password');
% Read data from database.
e = exec(conn, strcat('INSERT INTO [Tablename].[ref].[REG] ([REGTYP_ID]) VALUES (5555)'));
e = fetch(e);
close(e);
% Assign data to output variable.
SQL_DATA = e.Data;
% Close database connection.
close(conn);

The name of the column in the table is ([REGTYP_ID]. In the SQL string above, I put this value equals 5555. I would like to replace the number '5555' through a variable:

variable=555

    %% LOAD SQL DATABASE
% Make connection to server.  Note that the password has been omitted.
% Using ODBC driver.
conn = database('servername','','password');
% Read data from database.
e = exec(conn, strcat('INSERT INTO [Tablename].[ref].[REG] ([REGTYP_ID]) VALUES (variable)'));
e = fetch(e);
close(e);
% Assign data to output variable.
SQL_DATA = e.Data;
% Close database connection.
close(conn);

But this does not work. how can I pass a variable in Matlab to a database?

Regards.

sobek · Accepted Answer · 2014-07-15 16:48:48Z

0

Try the following:

variable=555

    %% LOAD SQL DATABASE
% Make connection to server.  Note that the password has been omitted.
% Using ODBC driver.
conn = database('servername','','password');
% Read data from database.
e = exec(conn, ['INSERT INTO [Tablename].[ref].[REG] ([REGTYP_ID]) VALUES ' num2str(variable)]);
e = fetch(e);
close(e);
% Assign data to output variable.
SQL_DATA = e.Data;
% Close database connection.
close(conn);

Edit:

I suppose everything in brackets is supposed to be a variable, so here we go:

[] is used to concatenate strings.

If REGTYP_ID is already a string, you don't need to convert it to a string with num2str().

So your query would then look like

['INSERT INTO ' tablename '.' ref '.' REG ' ' variable2 ' ' VALUES ' ' num2str(variable1)]

Edit2:

Ok, i understand what you want to do now. Please next time consider editing your original question, not posting questions inside answers. It's all getting a bit confusing.

Lets start over, your examples are overly complicated, i think you can do what you want much easier.

Suppose variable1 is a number and variable2 is a string. Your query now needs to look like this:

e = exec(conn, ['INSERT INTO [Tablename].[ref].[REG] ([Variable1],[Variable2]) VALUES (' num2str(variable1) ',' variable2 ')'] );

edited Jul 15, 2014 at 16:48

answered Jul 14, 2014 at 19:27

sobek

1,43612 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Turi Over a year ago

Dear sobe, thank you very much for your answer. The following approach worked:

sobek Over a year ago

I edited my answer again, i think this should cover it.

sobek Over a year ago

Did this cover your problem?

Turi Over a year ago

Hi sobek, sorry for the (very) late answer. Also with your help, I finally managed it. My solution worked and has the following form: SQLString=['DECLARE @GEOTEXT varchar(max) SET @GEOTEXT=''' variable5 ''' INSERT INTO [DATABASE].[ref].[REG] ([REGTYP_ID],[SHORT_NM],[LONG_NM],[ISO2A],[CREATE_DT],[CREATE_BY],[SQLGEO_STR],[SQLGEO]) VALUES (' num2str(variable1) ',' num2str(variable2) ',''' variable3 ''',''' variable4 ''',''' variable7 ''',''' variable6 ''',@GEOTEXT,geography::STGeomFromText(@GEOTEXT, 4326)) ']; . Thanks.

Collectives™ on Stack Overflow

Passing a Matlab variable to an SQL Database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related