Querying through a vector

3 次查看（过去 30 天）
Gustavo Gonzalez
Gustavo Gonzalez 2017-1-5
评论： Guillaume 2017-1-11
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
  2 个评论
the cyclist
the cyclist 2017-1-6
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
Gustavo Gonzalez 2017-1-9
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Guillaume
Guillaume 2017-1-9
编辑：Guillaume 2017-1-9
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.
  2 个评论
Gustavo Gonzalez
Gustavo Gonzalez 2017-1-10
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
Guillaume 2017-1-11
"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), ','))

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by