Do you have an example of how I can use a variable in the Database Toolbox SQL query?

Do you have an example of how I can use a variable in the Database Toolbox SQL query?

For example, I have the variable "lognum" in MATLAB and want to perform the following query:

 'select * from tablename where field1 = lognum'

How can I do this?

Answer 1

[MathWorks Support Team]

Unlike some other languages, MATLAB doesn't perform variable "interpolation" in strings. What MATLAB sees inside of a string is always a string. So in the following call the value in "lognum" does not ever get changed.

'select * from tablename where field1 = lognum'

When you consider this along with the fact that MATLAB passes the string in the EXEC statement directly to the ODBC driver (and consequently your remote database) without any processing at all, it should make sense that the query doesn't do quite what you think it ought to do.

What you must do instead is create a string which contains the exact query you want to pass to the ODBC driver. In the case here it will be:

'select * from tablename where field1 = 20055'

if the value of field1 is expected as numeric, or:

'select * from tablename where field1 = ''20055'''

if the data is expected as a string.

You must create this string using square brackets to perform string concatenation. The contents of the square brackets are made into one longer string, which is exactly what EXEC expects. Your code here would be:

lognum = '20055';  %set the variable in the workspace

cursorA = exec(connA, ['select * from tablename where field1 = ', lognum])

or

lognum = '20055';  %set the variable in the workspace

cursorA = exec(connA, ['select * from tablename where field1 = ''', lognum, ''''])

or

lognum = 20055;  %set the variable in the workspace

cursorA = exec(connA, ['select * from tablename where field1 = ', num2str(lognum)])

The first example example above corresponds to the first statement where Logsheet is numeric. The second governs the case where Logsheet is expected as a string. The third example above, the NUM2STR function is used to convert the value that the variable stores to a string and then the string that's returned is concatenated with the rest of SQL query string.

Notice that the difference involves the use of extra quotes. We are "quoting" the quotes to make sure they show up in the string. Consider the difference between the following output in MATLAB:

    ['select * from tablename where field1 = ', lognum]

    ['select * from tablename where field1 = ''', lognum, '''']