How can I update a database field that contains curly brackets {} in the content?

+1 vote
asked Dec 12, 2017 by mike-r-7535 (13,630 points)
JSON and PHP strings both use curly bracket characters {}.  The characters get stripped out when I try to use qie.doQuery() to update a database field with a JSON string or PHP string.  How can I set a database value with curly brackets in the value?

1 Answer

+1 vote

qie.evaluateTemplate Removes Unused {}

Before your qie.doQuery() is executed, qie.evaluateTemplate() is called on the query statement.  The curly brackets {} are used to signify a nodeTag, and get replaced with their evaluated values.  PHP strings and JSON strings contain the curly brackets, but get evaluated as nodeTags.  As they don't contain a valid nodeTag, they are replaced with blank values.

Safe Way to Insert and Update Any Database Value

Anytime you are updating or inserting into a database using QIE, you should use parameterized queries.  This will allow you to use {}.  You can also use binary data.  And most importantly, it will prevent SQL injection, which can mess up your data.

Below is a simple example of how to use parameterized queries to insert into a table:

First, store your query statement in a variable.  Use placeholders for your named paramters by prepending either an @ or : character.

Then create your parameterizedQuery from your statement.

Now you can use your parameterizedQuery to set each of the parameters.  Note that each parameter must be set with its type specific method. These type specific methods can be found in the Code Wizard > Database > Parameterized Query > Set Parameter.

Once your parameters are set, you execute the query on a specified database connection.

For your convenience, you can copy the text below as a starting point for your parameterized queries.

var insertStatement = "INSERT INTO my_db_table\n" +
   "(id, meta_key, meta_value)\n" +
   "VALUES(@id, @metaKey, @metaValue)";

var pInsertStatement = qie.getParameterizedQuery(insertStatement);

var myInt = 456798;
var myString = 'myJsonTest';
var myJsonString = '{"Name":"QIE", "Purpose":"HL7 Interface Engine", "Ranking":"#1"}';

pInsertStatement.setInt("@id", myInt);
pInsertStatement.setString("@metaKey", myString);
pInsertStatement.setString("@metaValue", myJsonString);

var dbName = 'myDatabaseName';
pInsertStatement.doQuery(dbName, false);

Another example explaining other parameter types that can be used can be found here.

answered Dec 12, 2017 by mike-r-7535 (13,630 points)
commented May 29, 2019 by lewis-s-9714 (850 points)
Does using the parameterized query method to do a SQL query allow you to use single quotes without escaping? For instance, would this work:
var myString = "Jason'sTest";
pInsertStatement.setString("@metaKey", myString);
Or would you have to do this:
var myString = "Jason''sTest";
pInsertStatement.setString("@metaKey", myString);
commented May 29, 2019 by mike-r-7535 (13,630 points)
In a parameterized query, you do not manually escape the parameters.  The parameterized query will take care of that.