How do I map values from a csv message to a parameterized query in a DBQuery Source?

Question

I am trying to get values from a csv message and map them to an INSERT query.

I plug in the below as the SQL Statement, though can't seem to map incoming data to the query using the source DB Query Configuration

        var insertStatement = "" +
           "INSERT INTO patients (" +
		   " pid, " +
		   " firstName, " +
           " lastName, " +
           " sex, " +
		   " dob, " +
		   " address1, " +
		   " city, " + 
		   " zip " + 
           ") VALUES (" +
		   " :Id, " +
           " :FIRST, " +
           " :LAST, " +
		   " :GENDER, " +
		   " :BIRTHDATE, " +
		   " :ADDRESS, " +
		   " :CITY, " +
		   " :ZIP " +
           ")";

Answer 1

Depending on your source CSV you would reference the fields from the source CSV message.

 

var pQuery = qie.getParameterizedQuery("INSERT INTO patients (\n" +
   " pid, \n" +
   " firstName, \n" +
   " lastName, \n" +
   " sex, \n" +
   " dob, \n" +
   " address1, \n" +
   " city, \n" +
   " zip \n" +
   ") VALUES (\n" +
   " :Id, \n" +
   " :FIRST, \n" +
   " :LAST, \n" +
   " :GENDER, \n" +
   " :BIRTHDATE, \n" +
   " :ADDRESS, \n" +
   " :CITY, \n" +
   " :ZIP \n" +
   ")");
pQuery.setString("Id", source.getNode("Id"));
pQuery.setString("FIRST", source.getNode("FIRST"));
pQuery.setString("LAST", source.getNode("LAST"));
pQuery.setString("GENDER", source.getNode("GENDER"));
pQuery.setString("BIRTHDATE", source.getNode("BIRTHDATE"));
pQuery.setString("ADDRESS", source.getNode("ADDRESS"));
pQuery.setString("CITY", source.getNode("CITY"));
pQuery.setString("ZIP", source.getNode("ZIP"));
var queryResult = pQuery.doQuery(
   "CPS10",
   false,
   false
);

Comments

Thank you, I am able to insert into the DB now.

For if I want to increment through the CSV file, how can I retrieve the 2nd, 3rd, 4th, etc row for the setString statements?

---

for (var i=0 ; i < source.getRowCount() ; i++) {
   var pQuery = qie.getParameterizedQuery("INSERT INTO patients (\n" +
      " pid, \n" +
      " firstName, \n" +
      " lastName, \n" +
      " sex, \n" +
      " dob, \n" +
      " address1, \n" +
      " city, \n" +
      " zip \n" +
      ") VALUES (\n" +
      " :Id, \n" +
      " :FIRST, \n" +
      " :LAST, \n" +
      " :GENDER, \n" +
      " :BIRTHDATE, \n" +
      " :ADDRESS, \n" +
      " :CITY, \n" +
      " :ZIP \n" +
      ")");
   pQuery.setString("Id", source.getNode("Id", i));
   pQuery.setString("FIRST", source.getNode("FIRST", i));
   pQuery.setString("LAST", source.getNode("LAST", i));
   pQuery.setString("GENDER", source.getNode("GENDER", i));
   pQuery.setString("BIRTHDATE", source.getNode("BIRTHDATE", i));
   pQuery.setString("ADDRESS", source.getNode("ADDRESS", i));
   pQuery.setString("CITY", source.getNode("CITY", i));
   pQuery.setString("ZIP", source.getNode("ZIP", i));
   var queryResult = pQuery.doQuery(
      "CPS10",
      false,
      false
   );
}