SQL error or missing database (near "01": syntax error)

[tGitm]

Hi.
I have to use multiple WHERE statements in my query. When I use just 1 it works, for example:

var tasks_query = <sql> SELECT Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, PatientId, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, DoctorFirstName, DoctorLastName, TabletAcceptedBy, RequestDate, AddressCity FROM Tasks WHERE PatientId = {patientid} </sql>

But when I try to use all 3, I get the error ->
Wrapped java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "01": syntax error)

This is my statement, the variables are not null and they works fine.
var tasks_query = <sql> SELECT Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, PatientId, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, DoctorFirstName, DoctorLastName, TabletAcceptedBy, RequestDate, AddressCity FROM Tasks WHERE PatientId = {patientid} AND RequestDate = {requestDate} AND Status = 2 </sql>;

Thanks for helping me out 🤘

[pacmano1]

logger.info out your query or put it in $c to see the substituted data. I expect your date needs to be wrapped in quotes. If patientID is of type char, text, varchar, etc it would need to be wrapped in single quotes also.

AND RequestDate = '{requestDate}'

[jonbartels]

Casting may also help AND RequestDate = '{requestDate}'::DATE or AND RequestDate = IDK_SQL_LITE_DATE_PARSEFUNC('{requestDate}', 'yyyyMMdd')

Review https://sqlite.org/lang_datefunc.html for the exact syntax.

Pushing the date parsing into SQL is often simpler than doing it with MCs date parsing functions.

[tGitm]

Ou yes... the requestDate is Text type, patientId also. I will add quotes.

[siddharthsharma9258]

requestDate is text? why not a timestamp?

[tGitm]

@siddharthsharma9258 I didn't implement the database, but yes it should be timestamp 👍