Querying through a vector

Hi,

I have configured matlab to read a database located in postgresql. At the moment is working fine if the query implies only one element. However, in the application where this is going to be used it will need to read a vector of data (i.e Lat = [90 80 70 60 50 ....]) and when I have been trying to do that with my current code is not querying the values.

Here is my code to see if you could detect something wrong in it.

I would appreciate any hint or help to try to solve this issue.

Regards

tic
  conn = database('mydb', 'postgres', 'postgres', 'Vendor', 'PostgreSQL', 'Server', 'localhost');
  sqlquery = ['select Att from coordinate6 where Lat = ' num2str(lat) 'AND Lon = ' num2str(lon) 'AND Height = '   num2str(height) 'AND Elev = ' num2str(elev) 'AND Freq = ' num2str(freq) 'AND Ant_Diam = ' num2str(ant_diam) 'AND Avail = ' num2str(avail)];
  curs = exec(conn,sqlquery);
  curs = fetch(curs);
  curs.Data
  toc

[the cyclist]

What do you get for a result, and what did you expect to get? If you don't get a result, what is the complete error message you are getting?

[Gustavo Gonzalez]

Hi,

Today I was trying this in PostreSQL command line

SELECT att FROM test2 WHERE lat IN (-91,-81,-70,-60)

The result I got matched the att values associated to the values I specified for lat.

When I tried to replicate this same command on Matlab

tic
  lat = [-91,-81,-70,-60];
  conn = database('mydb2', 'postgres', 'postgres', 'Vendor', 'PostgreSQL', 'Server', 'localhost');
  sqlquery = ['SELECT att FROM test2 WHERE lat IN ' lat];
  curs = exec(conn,sqlquery);
  curs = fetch(curs);
  curs.Data
  toc

I get "Invalid Cursor: ERROR: syntax error at or near " ".

Am using the wrong syntax or is there a different way to do the query in a vector of data? I know that a possible option could be through a for loop but for my application it wouldn't help as the set of data is quite big.

Thanks for any support you can provide.

Regards.

Answer 1

[Guillaume]

With your latest comment, have you actually looked the content of sqlquery you generate? You'll see it didn't add the latitude (well, it did, each as character ascii 0)

sqlquery = ['SELECT att FROM test2 WHERE lat IN (' num2str(lat) ')'];

Note that I don't know anything about the database toolbox, but if it returns a cursor as it claims, then you probably need to move the cursor to access each row returned by the query.

[Gustavo Gonzalez]

Hi Guillaume,

Thanks for your reply.

I tried the way you told me to do but still without working.

I would have thought that the sql commands and syntax could be entirely replicated in Matlab without any problem but it doesn't seem to be like that.

I will continue looking on a possible way to solve this issue.

Thanks for your help.

Regards.

[Guillaume]

"it's not working" is not helpful. We need to know the details of the error if one occur, or in what way it's not working otherwise.

I should have reminded myself of the syntax of the IN clause. You need a comma between each value, so:

sqlquery = sprintf('SELECT att FROM test2 WHERE lat IN (%s)', ...
              strjoin(arrayfun(@num2str, lat, 'UniformOutput', false), ','))

or, using the new string class in R2016b:

sqlquery = compose('SELECT att FROM test2 WHERE lat IN (%s)', ...
             join(string(lat), ','))