31
Views
18
Comments
sql advance query
Question

Hi All,

In the where clause, I want to check that the UserId in the table Report is in the parameter UserIds or IsAdmin, but I have error like this

This is the line where the query is error


Regards,

Kane

Hi Kane,

Good morning!

I think it's ({report}.[id] in (@UserIds) or @IsAdmin  = 1)

Thank you and kind regards,

Chris

Rank: #30439

Hi Christopher,

Thank you for your attention, but where IsAdmin Parameter called


Regards

Kane

Hi Kane,

Apologies, I edited it. And as Tom mentioned, please use the Sanitization API to build the value for the @userid parameter.

Building Dynamic SQL Statements the Right Way - OutSystems

Kind regards,

Chris

Rank: #484

Hi,

The error is caused by the @IsAdmin. Although it is boolean type, but you need to compare to 1 to make it a complete statement.

Try some like this: ... or @IsAdmin = 1)

Hope this help

Rank: #30439

Hi Shingo,

Thank you for your answer, I've been added = 1 after @IsAdmin but i still get an same error

Regards,

Kane

Rank: #287

Hi Kane,

Can you tell us what value that you passed to the UserIds parameter?

The expand attribute is true, you should pass value like this. e.g "'1','2'"

Please consider using Sanitization API, Such as BuildSafe_InClauseTextList  to prevent SQL Injection.


Kind Regards,




Rank: #484

Yeah, if u already added = 1 in the query, so the root cause is from the input value of the ids, and Sanitization is good way to build right text list to 1 text

Rank: #30439

Hi Tom,

I'm sorry for late reply

Yap in UserIds parameter i pass variable with data type User Identifier 


Regards,

Kane

Rank: #287


Hi Kane,

Because the UserIds parameter's expand attribute is true, you should pass a value like this. e.g "1,2,3"

Also, please consider using Sanitization API, Such as BuildSafe_InClauseIntegerList to prevent SQL Injection.

Kind Regards,

Rank: #299

Hi Kane,

If you are still having the issues, please share the OML to look into the issue.

Thanks & Kind Regards,

Sachin

Rank: #55

Hi Kane,


Your WHERE clause should look like this:


WHERE {Report}.[UserId] IN (@UserIds) OR @IsAdmin = 1


Given that your @UserIds has the Expand Inline set to Yes, you need to provide a value so the Test Query can validate the syntax and you don't get this error.

 

Your test value should be comma-separated integers like 1 or 1,2 or 1,2,3.

 For the same reason, you should ensure that the parameter value your passing to the @UserIds parameter is filled with comma-separated integers to avoid run-time issues.


Kind Regards,
João

Rank: #30439

Hi João,

i have tried your solution and yap in test input I've added value "1" to UserIds parameter and query works well, but inside application that query returned the error

FYI: UserIds parameter is User Identifier Variable

Regards,

Kane

Rank: #55

Hi Kane,


In your screenshot, the UserIds was of type Text and had the Expand Inline set to True, therefore my answer.

If the UserId parameter is a User Identifier, then your WHERE clause should be like this:


WHERE {Report}.[UserId] = @UserId OR @IsAdmin = 1 

Rank: #30439

Hi João, 


WHERE {Report}.[UserId] = @UserId OR @IsAdmin = 1  

I still get an error "error executing query" inside application

In UserIds parameter i using EncodeSql(SelectId)

SelectId is a variable with data type is User Identifier


Regards,

Kane

Rank: #55

Can you share the OML, or some screenshot that captures completely your query?

What is the value that you are using in SelectId? Wasn't it a User Identifier and Expand Inline = False?


Hi Kane,

Aside from the OML the guys requested, can you please share as well the error log/stack from Service Center.

Kind regards,

Chris


Rank: #287

Hi Kane,

Is this not the same Query with your original post?

Please provide more detailed information.

Anyway, I don't understand your description.

>WHERE {Report}.[UserId] = @UserId OR @IsAdmin = 1  

>SelectId is a variable with data type is User Identifier

What is the SelectId?  I suppose it's a typing error to @UserId,

If so, Can you check UserId's type and expand property?  

The type should be  User Identifier and expand should be False.

Kind regards,

Rank: #1031

Hello kane ,

Also you need to set Isadmin Expend Inline property to Yes. After that you will check for the Same