Layer7 API Management

Cannot call Free SQL Resource with custom parameter

I would like to call CountRow with parameter arg_table

Cannot call Free SQL Resource with custom parameter

I would like to call CountRow with parameter arg_table

I wrote JavaScript as below

var Response = SysUtility.getResource("CountRow", {arg_table: "ADR2_CEP"});

return Response;

the result is error like didn't send any parameter

{
"statusCode": 500,
"errorCode": 50045,
"errorMessage": "Error while executing Resource JavaScript API-level function FuctionByPostParameters - line 0: Free SQL arg_table or arg.table value not found in url parameters"
}

So I try to create FIX free SQL Resource

I wrote JavaScript as below

var Response = SysUtility.getResource("CountRowFix");

return Response;

and the result is success

[{"ADR2_CEP":211252}]

When calling SysUtility.getResource, the second parameter is an object that can have multiple values, like pagesize, offset, etc...

In your case, instead of:

var Response = SysUtility.getResource("CountRow", {arg_table: "ADR2_CEP"});

you can try:

var Response = SysUtility.getResource("CountRow", {params:{arg_table: "ADR2_CEP"}});

Please keep in mind that this is potentially opening up a huge security hole in your system -- now every table can be accessed, with no limitations, by anyone who has access to this resource. That may be fine in your case, I just wanted to make sure you're aware of it.

Another thing, which you may already know, is that, depending on your underlying database, you may need to quote table and column names in your SQL resource, e.g.:

select * from "@{arg_table}" where "@{arg_colname}" = @{arg_value}

In the case of a string value for arg_value, you'll have to decide whether you want the caller to pass in the quotes, or whether you prefer to bake them in:

select * from "@{arg_table}" where "@{arg_colname}" = '@{arg_value}'

But do keep in mind that this makes you totally vulnerable to SQL injections.

still error 

Sorry to hear that. I am a bit surprised, though. This works for me. Can you copy the code for your SQL resource and the code that invokes it?

sql resource

select count(*) as @{arg_table} from @{arg_table}

java script (both return error)

var Response = SysUtility.getResource("CountRow", {arg_table: "ADR2_CEP"});

return Response;

var Response = SysUtility.getResource("CountRow", {params:{arg_table: "ADR2_CEP"}});

return Response;

This is peculiar. I can create the following SQL resource:

select count(*) as @{arg_tbl} from @{arg_tbl} where FirstName = '@{arg_fn}'

and invoke it from the REST Lab as:

http://localhost:8080/rest/default/ygqsh/v1/CountRows?arg_fn=Kara&arg_tbl=Customer 

and I get the expected result: 

[
  {
    "Customer": 1
  }
]

I can then invoke this free SQL resource from the following function:

return SysUtility.getResource("CountRows", {params:{arg_fn:"Kara", arg_tbl: "Customer"}});

and I get the expected result (different format because of the way we return it):

[{"Customer":1}]

The way to debug this is to first verify that you can call your free SQL resource successfully from the REST Lab using a syntax similar to the one just above. If you cannot call it from the REST Lab, there is no point in trying to call it from a function. Is that successful for you? If you get an error response, what is it? Is there an error message in the console?

If we assume that you can in fact invoke your free SQL resource successfully from the REST Lab, then it's a matter of debugging why the function cannot invoke it. What result are you getting? Any errors in the console?

As I mentioned earlier, this could be a SQL-level error, for instance if your database requires quoted table names. What is your database type? Oracle? SQL Server? Most importantly, we need to know what, if any, error messages or error responses you are getting.

Thank you for your patience. We will figure this out eventually.