[Advance Query] Escaping entity name transformation in data transfer by using "ExecuteCommand"

Forge Component
(12)
Published on 2019-12-20 by Paulo Fagundes Jr.
12 votes
Published on 2019-12-20 by Paulo Fagundes Jr.

OutSystems version: 11.7.13
Advance Query version: 11


Hi Paulo,


Thanks for this beautiful extension, it helps us a lots.

But I have encountered one issue when tried to use "ExecuteCommand" from "Advance Query" extension to migrate/transfer data among OutSystems entities.

The issues is that if there are any {entityname} in the data, they will always be transformed into physical_table_name.

For example:

Inserting a row of data that contains SQL statement into another table:

INSERT INTO test_entity VALUES ('Select * from {tablename} ..')

The data will end up in the target table "test_entity" as:

"Select * from OSUSR_ql3_tablename ...."  //being converted into physical table name


It will best if there is a way to escape entity names when doing purely data transferring.

but it isn't available at the moment,  so I made some changes to get what I wanted.

My solution is to add double brackets to entity names in order to escape them.

For example:
Input:    INSERT INTO test_entity VALUES ('Select * from {{tablename}} ...')
Expected Output:   'Select * from {tablename} ...'   // In the target table


With modified the "ReplaceSQL" functon as below:
---------------------------------------------------------------------------------------------------------------------------------

        private string ReplaceSQL(string sql)

        {

            string nameFormated = string.Empty;

            Match[] matches = Regex.Matches(sql, @"{{1,}[\w.]*}{1,}")

                    .Cast<Match>()

                    .ToArray();

            foreach (Match match in matches)

            {

                string nameEntityNonFormated = Regex.Replace(match.Value, @"[},{]", "");

                if (match.Value.IndexOf("{{") != -1)

                {

                    sql = sql.Replace(match.Value, "{" + nameEntityNonFormated + "}");

                }

                else

                {

                    nameFormated = this.getPhysicalName(nameEntityNonFormated, this.EspaceName);

                    if (nameFormated != string.Empty)

                    {

                        sql = sql.Replace(match.Value, nameFormated);

                    }

                }

            }

            return sql;

        }

----------------------------------------------------------------------------------------------------------------------------

This change above help me to achieve my specific goal, but may not be suitable for other purposes.


Hope that you will come out a better idea for escaping the entity name in order to avoid the unwanted data transformation.


Best Regards






Hi,

If you already know the physical name then you should not add it inside flower brackets. Limit the use of flower brackets to entity name defined in outsystems service studio.

Regards.

Hi David Chen, thanks for your feedback. As Prasad said if the physical name is already known you just need to remove the brackets, so it won't be converted.



Kind Regards

Paulo

Hi Prasad and Paulo,

Thanks for the quick response.

If physical table name is known, then yes.

But our intention is to copy data from one existing Tenant to multiple new Tenants and even to the Tenants that haven't been created yet.

So the physical table name may not be known at the time, it makes sense to copy the SQL Statement with {entityname}...etc.., so it can be converted dynamically to any corresponding physical table names in any particular tenants when it get called.

Best Regards

David


Paulo Fagundes Jr. wrote:

Hi David Chen, thanks for your feedback. As Prasad said if the physical name is already known you just need to remove the brackets, so it won't be converted.



Kind Regards

Paulo

 Hi Paulo - let me clarify the issue - we have the following insert statement -

insert into {ReportingTable} (id,report_sql) values (1,'select code,description from {LookupTable}')

The replacement logic is correctly replacing {ReportingTable} with the physical table name, but we do not want it making replacements to the attribute values which are inserted into {ReportingTable}.

{LookupTable} should remain unchanged. Furthermore, any data column that we insert into an attribute containing braces is corrupted e.g.

update {ReportTable} set comment = '{hello world}'

The comment table contains the text 'hello world' after update. We expect it to contain '{hello world}'