How to write database query using sprintf command?

6 vues (au cours des 30 derniers jours)
Struggling in MATLAB
Struggling in MATLAB le 22 Nov 2022
I have a POSTGRESQL database from which has a column named 'referencetime' which is a timestamp. (e.g. '04/06/2022 12:41:46.5'). I am trying to fetch all data for date 04/06/2022. I am using the following command:
referencetime = char(subjectData.referencetime);
date = referencetime(1:10);
% Select data of same date
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime = '%s';",date);
But it is not working for me. It works if I hard code it. for example
dataOnDateQuery = "SELECT subjectid FROM live_table WHERE referencetime ILIKE '04/06/2022%';";
But I need the date query to be generalized by '%s' to use in a n algorithm. How can implement both %s and LIKE in the same query? I have attached live_table and the .m file for your reference.
  4 commentaires
Jan
Jan le 23 Nov 2022
This line from your question:
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime = '%s';",date);
does not occur in the attache M-file. Similar lines from the file:
dateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE id = %d;", id);
dataOnDateQuery = sprintf("SELECT subjectid, trialname, referencetime " + ...
"FROM live_table WHERE referencetime ILIKE '%s';",date);
It is confusing, if you post different versions of the code, because it is not clear, which one causes which problem.
dataOnDateQuery = "SELECT subjectid FROM live_table WHERE referencetime ILIKE '04/06/2022%';";
Are you sure, that there is a % in the date?
Struggling in MATLAB
Struggling in MATLAB le 23 Nov 2022
Modifié(e) : Struggling in MATLAB le 23 Nov 2022
Hi Jan, sorry for not explaining my question well. I have not attached the 'live_table' in this question. The 'dateQuery' query merely fetches the referencetime corresponding to the id. The date part from the referencetime in turn is used in 'dataOnDateQuery' to fetch all data on that date. Let me attach the partial 'live_table' here.

Connectez-vous pour commenter.

Réponse acceptée

Struggling in MATLAB
Struggling in MATLAB le 23 Nov 2022
I was finally able to solve this by separating '%s' and 'LIKE' in the query string. Here is the code attached.
referencetime = char(subjectData.referencetime);
thisDate = referencetime(1:10);
% Select data of same date
dataOnDateQuery = strcat("SELECT xcoordinates2, ycoordinates2, referencetime " + ...
"FROM live_table WHERE referencetime LIKE '",sprintf('%s',thisDate), "%';");
dataOnDate = fetch(conn,dataOnDateQuery);
  2 commentaires
Jan
Jan le 24 Nov 2022
This is a wild mixture of string and CHAR vector commands. With one method only, repsectively:
% Strings:
dataOnDateQuery = "SELECT xcoordinates2, ycoordinates2, referencetime " + ...
"FROM live_table WHERE referencetime LIKE '" + thisDate + "%';";
% CHAR vectors:
dataOnDateQuery = sprintf(['SELECT xcoordinates2, ycoordinates2, referencetime ', ...
'FROM live_table WHERE referencetime LIKE ''%s%'';'], thisDate);
Struggling in MATLAB
Struggling in MATLAB le 2 Déc 2022
Thanks for your response. I tried both of them. The 1st method works, but the 2nd did not. I getting an error.
JDBC Driver Error: Unterminated string literal started at position 92 in SQL SELECT
xcoordinates2, ycoordinates2, referencetime FROM live_table WHERE referencetime LIKE
'04/06/2022. Expected char
But, it's okay. I just needed one method.

Connectez-vous pour commenter.

Plus de réponses (0)

Produits


Version

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by