32
Views
4
Comments
[Advance Query] Escaping entity name transformation in data transfer by using "ExecuteCommand"
Question
Forge component by Paulo Fagundes Jr.
12
Published on 20 Dec 2019

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






Rank: #111

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

Rank: #6245

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