Problem with sql parameters

[native gyro]

Hi,
In previous versions of Ballerina, I used to disable an Oracle constraints like this.

string sqlStr = "ALTER TABLE TABLE_NAME DISABLE CONSTRAINT " + constraintName;

And works well.

Now, the only way is using sql:ParameterizedQuery

sql:ParameterizedQuery sqlExec = ALTER TABLE IDN_OIDC_PROPERTY DISABLE CONSTRAINT ${constraintName};

But, Oracle respond an error because the constraint name can't be passed as parameter.

In each database, I don't know the name of the constraint, so so a select to the table.

SELECT constraint_name FROM user_constraints
WHERE table_name = 'TABLE_NAME'
and r_constraint_name = 'CONSUMER_KEY_CONSTRAINT'

Could you help me with this?
Thanks

[stark frost]

You can only pass parameters as insertions in sql:ParameterizedQuery. This is for security reasons as dynamically constructing queries are prone to SQL injection (If not properly validated).

There is a workaround for your specific use case. Please see the following guide for more information,
https://stackoverflow.com/collectives/wso2/articles/74602985/avoiding-sql-injection-in-ballerina

[native gyro]

Thanks for the response @stark frost.
I tried to use sql:QueryConcat before, but don't support a variable that must be a concat with the value as string, not concat as a parameter because is not supported by Oracle in this case.

Try this command with the constraint value as parameter and you will see.
The constraint have different names on each database at the moment that is created.
ALTER TABLE TABLE_NAME DISABLE CONSTRAINT XXXXXXXX;
Thanks,

[stark frost]

Currently there is no straight forward way to convert string to sql:ParameterizedQuery

There is a workaround considering the desugeraing aspect of the sql:ParameterizedQuery.

string constraintName = "";
sql:ParameterizedQuery constraintType = ``;
constraintType.strings = [constraintName];

The above constraintType query will consider the constraintName as part of the query and not an insertion. The final query can be constructed as,

string constraintName = "";
sql:ParameterizedQuery constraintType = ``;
constraintType.strings = [constraintName];

sql:ParameterizedQuery alterQueryPrefix = `ALTER TABLE IDN_OIDC_PROPERTY DISABLE CONSTRAINT `;

sql:ParameterizedQuery execQuery = sql:queryConcat(alterQueryPrefix, constraintType);

Though is doable, do note this does not guarantee the safety.