SQL query in javascript transformer

[tGitm]

Hy 👋

I created JS transformer with SQL query, but I am getting errors when try to run it. I don't use because variable in WHERE statement didn't work.

Error:
INFO 2021-12-14 08:05:25,479 [Source Filter/Transformer JavaScript Task on From DB to completed HL7 response (ba7dad39-ca73-42d2-8405-1537ab7a3835) < pool-1-thread-4] transformer: DB Exception: java.sql.SQLException: [SQLITE_SCHEMA] The database schema changed (no such column: Tasks.Id)

And this is my code:
``

var patient = $('patient_id');

var query = "SELECT Id, VisitNumber, ActivityId, IntegrationServiceId, TabletGroupId, TaskType, Status, FirstName, LastName, BirthDate, Gender, Race, EmailAddress, PhoneNumber, AddressStreet, AddressPostalCode, AddressCountry, DoctorFirstName, TabletAcceptedBy, RequestDate, AddressCity WHERE PatientId = patient";

	
var result = dbConn.executeCachedQuery(query);

var id;
var visitNumber;
var activityId;
var integrationId;
var tabletGroupId;
var taskType;
var status;
var firstName;
var lastName;
var birthDate;
var gender;
var race;
var emailAddress;
var phoneNumber;
var addressStreet;
var addressPostalCode;
var addressCountry;
var doctorFirstName;
var doctorLastName;
var tabletAcceptedBy;
var requestDate;
var addressCity;

//checking gender codes from db
function genderCheck(sex) {
	if (sex == 1) { return gen = 'M'; }
	return gen = 'F';
}

//checking race codes from db
function raceIdentifier(raceCode) {
	if (raceCode == 'C') { return 'White'; } 
	else if (raceCode == 'M') { return 'African American'; } 
	else { return 'Asian'; }
}


while (result.next()) {
	id = result.getString('Id');
	visitNumber = result.getString('VisitNumber');
	activityId = result.getString('ActivityId');
	integrationId = result.getString('IntegrationServiceId');
	tabletGroupId = result.getString('TabletGroupId');
	taskType = result.getString('TaskType');
	status = result.getString('Status');
	firstName = result.getString('FirstName');
	lastName = result.getString('LastName');
	birthDate = result.getString('BirthDate');
	gender = result.getString('Gender');
	race = result.getString('Race');
	emailAddress = result.getString('EmailAddress');
	phoneNumber = result.getString('PhoneNumber');
	addressStreet = result.getString('AddressStreet');
	addressPostalCode = result.getString('AddressPostalCode');
	addressCountry = result.getString('AddressCountry');
	doctorFirstName = result.getString('DoctorFirstName');
	doctorLastName = result.getString('DoctorLastName');
	tabletAcceptedBy = result.getString('TabletAcceptedBy');
	requestDate = result.getString('RequestDate');
	addressCity = result.getString('AddressCity');
}``

Thanks for help!

[pacmano1]

patientid is still a literal string there.

var query = "select stuff from table where patientid = '" + patientid + "'";   -- use your local var

or

var query = "select stuff from table where patientid = '" + $('patientid') + "'";  -- use the channel var you already have

if you do either of these it is obvious:

logger.info(query);  // show it it on the console
$c('SQLQueryFindPatient',query) // store it in the channelMap so you can see it in the message context

[tGitm]

Thanks for your answer! I got problem, that now server is returning this error:
DB Exception: java.sql.SQLException: [SQLITE_SCHEMA] The database schema changed (no such column: Id)

My database have this attributes:

[pacmano1]

I don't know SQLLite and how it handles case sensitivity for column names - it is one reason I never use combined case columns (aka "Camel") , rather I used doctor_first_name (aka "Underscore")

Try a simple query not using mixed case. Also, I don't think I would ever use SQLLite for a healthcare application that serves more than one person (e.g. It is fine for phone app, i.e. an "embedded" app).

[tGitm]

Yes, I wouldn't use SQLlite also, but my company does :)

[pacmano1]

Your SQL query is missing a FROM.

select Id FROM <some table> WHERE

[pacmano1]

I don't know how case sensitivity works with your db engine. If you want to build your query like your last post, it would look like this:

var patientid = 123;
var query = <sql> 
SELECT Id,
       VisitNumber,
       ActivityId,
       IntegrationServiceId,
       TabletGroupId,
       TaskType,
       Status,
       FirstName,
       LastName,
       BirthDate,
       Gender,
       Race,
       EmailAddress,
       PhoneNumber,
       AddressStreet,
       AddressPostalCode,
       AddressCountry,
       DoctorFirstName,
       DoctorLastName,
       TabletAcceptedBy,
       RequestDate,
       AddressCity
FROM Tasks
WHERE PatientId = {patientid}
<sql>.toString();
$c('SQLQuery',query) // This will store the full sql in the channel map so you can cut and paste it
$gc('SQLQuery',query) // use this instead of the previous line if the SQL is used in the source tab.
var result = dbConn.executeCachedQuery(query);

[tGitm]

Thanks! :)

[pacmano1]

When you build SQL in an XML blob like you did the code inside the curly brackets is substituted. Also, be sensitive to when you need to wrap that string in quotes. e.g. WHERE PatientId = '{patientid}'

[tGitm]

Taken into consideration. Thanks again.

[tonygermano]

Using parameterized queries means you don't need to worry about escaping or quoting.

WHERE PatientId = ?

Then var result = dbConn.executeCachedQuery(query, [patientid]);