Does the RDSDataService support array parameters?

[arcturial]

Confirm by changing [ ] to [x] below:

• [ x ] I've gone though Developer Guide and API reference
• [ x ] I've checked AWS Forums and StackOverflow for answers

I am encountering an error when trying to use the "arrayValue" parameter for the RDSDataService, it results in the "Array parameters are not supported." error.

My request looks like this

{
  query: xxx
  continueAfterTimeout:false,
  includeResultMetadata:true,
  parameters: [
    { name: company_id, value: { arrayValue: { longValues: [11] } } }
  ]
}

This is how the documentation seems to format the packet, am I doing something wrong?

[ajredniwja]

Hey @arcturial thank-you for reaching out to us with your issue, I am a bit confused and was not able to reproduce it, can you please share your complete code and the API you are trying to use from RDSDataService.

[arcturial]

I am using version 2.585.0 of the SDK with:

const aws = require('aws-sdk')

const db = new aws.RDSDataService({
  params: {
    resourceArn: 'xxx',
    secretArn: 'xxxx',
    database: 'xxx'
  }
})

const query = `SELECT * FROM some_table WHERE id IN (:ids)`

const params = {
  sql: query,
  continueAfterTimeout: false,
  includeResultMetadata: true,
  parameters: [
    { name: 'ids', value: { arrayValue: { longValues: [11] } } }
  ]
}

db.executeStatement(params).promise()

This results in (node:31131) UnhandledPromiseRejectionWarning: BadRequestException: Array parameters are not supported.

I can also replicate this via the AWS CLI using.

aws rds-data execute-statement --resource-arn "xxxx" --secret-arn "xxxx" --database "mydb" --sql "SELECT * FROM some_table WHERE id IN (:ids)" --parameters "[{\"name\": \"ids\", \"value\": { \"arrayValue\": { \"longValues\": [11] } } }]" --region "eu-west-1"

An error occurred (BadRequestException) when calling the ExecuteStatement operation: Array parameters are not supported.

[theBenForce]

I'm also experiencing the same thing, but with a string array from a lambda in us-east-2 with the Node 12.x runtime.

Here's the exception that I'm getting:

ERROR	Unable to execute query: BadRequestException: Array parameters are not supported.
    at Object.extractError (/var/runtime/node_modules/aws-sdk/lib/protocol/json.js:51:27)
    at Request.extractError (/var/runtime/node_modules/aws-sdk/lib/protocol/rest_json.js:55:8)
    at Request.callListeners (/var/runtime/node_modules/aws-sdk/lib/sequential_executor.js:106:20)
    at Request.emit (/var/runtime/node_modules/aws-sdk/lib/sequential_executor.js:78:10)
    at Request.emit (/var/runtime/node_modules/aws-sdk/lib/request.js:683:14)
    at Request.transition (/var/runtime/node_modules/aws-sdk/lib/request.js:22:10)
    at AcceptorStateMachine.runTo (/var/runtime/node_modules/aws-sdk/lib/state_machine.js:14:12)
    at /var/runtime/node_modules/aws-sdk/lib/state_machine.js:26:10
    at Request.<anonymous> (/var/runtime/node_modules/aws-sdk/lib/request.js:38:9)
    at Request.<anonymous> (/var/runtime/node_modules/aws-sdk/lib/request.js:685:12)

[ajredniwja]

Apologies for late reply @arcturial @theBenForce.

According to the RDSDataService array parameters are not supported by the service at this point. You can find more information about it here.

Please reach out if you have any additional questions.

[theBenForce]

Thanks for the reply @ajredniwja. Do you know when this feature will be implemented?

[jackstevenson]

I'd also like to know when this feature will be made available.

[ajredniwja]

Hey @theBenForce @jackstevenson,

The SDK team has already things set up, its up-to the service team the time they take to implement it. Will update if they implement the feature.

[djacques7188]

I would also like to throw my weight behind this request

[djacques7188]

Has anyone found a work around for this issue?

[bmacher]

Hi, i have the same problem with an array of strings and solved it with the following code. But be carful on getting SQL injections. In my case I get the data from another DB request, therefore I am fine with this solution.

/**
 * Includes a string in single quotes. Should be used to
 * convert (map) array elements so they can be used in:
 * SELECT ... WHERE foo IN ( array.toString() )
 */
function addSingleQuotesToArrayElementMapper(element) {
  return `'${element}'`;
}

function createSqlStatement(stringValues) {
  return `
    ...
    WHERE stringValues IN (${stringArray.map(addSingleQuotesToArrayElementMapper).toString()})
 `;
}


const databaseResponse = await RDS
  .executeStatement({
    // ...
    sql: createSqlStatement(stringValues),
  })

[yghaile]

Adding to bmacher's solution - In Postgres, we can run ANY instead of IN. The advantage is that we can cast a parameter to an array (hence avoiding SQL injection):

const params = {
    secretArn: "yourSecretArn",
    resourceArn: "yourResourceArn",
    sql: `SELECT * FROM member WHERE member_id = ANY(:id::varchar[])`,
    parameters: [
      {
        name: 'id',
        value: {
          stringValue: '{Tl0nqR6LkrasSvvOQEzBRUqeG0G3,pKVEH0c1Z7h4M8kRBK5eM2ELXhx2}' 
        }
      }
    ],
    database: 'database'
}

This works, but I should add that the "::" does make me nervous since the ":" is used to find variables. In addition, I want to use "data-api-client" for formatting and that uses the "::" for dynamic identifiers. In my single test, it does seem to work there as well. Any feedback would be appreciated...

[jeremydaly]

Still getting "Array parameters are not supported."

[sambs]

Another work around is to pass each item of the array as a separate parameter:

const params = {
    secretArn: 'yourSecretArn',
    resourceArn: 'yourResourceArn',
    sql: 'SELECT * FROM member WHERE member_id IN (:id0, :id1)',
    parameters: [
      {  name: 'id0', value: { stringValue: '123'  }  },
      {  name: 'id1', value: { stringValue: 'abc'  }  },
    ],
  })

[vic-blt]

This is a big issue for me because I need to execute a SELECT for several IDs and, as I can't use the IN keyword, I have to move the SELECT into a loop.
Instead of one request with an array of IDs, I have to make a request for each ID which really impact performance, especially if you have a lot of IDs to SELECT.
BTW, I tried to join the IDs with ids.join("','") and pass it as a stringValue parameter but quotes are escaped to prevent SQL injection, so I can't use this tweak.

[silviuglv]

Ran into this issue as well, my workaround was to create the array as a string and cast it.

   {
        name: 'colum',
        value: `{${myArray.join(',')}}`,
        cast: 'text[]',
   }

[webjema]

Hey!
Do you have any ETA when the service team is going to release arrays support?

[jannikmi]

Ran into this issue as well, my workaround was to create the array as a string and cast it.

   {
        name: 'colum',
        value: `{${myArray.join(',')}}`,
        cast: 'text[]',
   }

I could not create any successful workaround with the code snippet you gave.
Could you please be more specific?

Additional search pointed me to this more explicit example: https://github.com/koxudaxi/local-data-api#how-to-insert-array-data-into-postgresql
They point out to put casting into the SQL command like

[...] cast(:parameter_name as text[]) [...]

In my case however that did not work.
I am trying to query for a list of certain ids like this:

SELECT ('id') FROM "table_name" WHERE "id" NOT IN cast(:allowed_ids as int[])

with paramters looking like:

[{'name': 'allowed_ids', 'value': {'stringValue': "{4,1}"}}]

That gives me:

[ERROR] BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: ERROR: syntax error at or near "cast"
  Position: 145

BTW: "SELECT cast('{4,1}' as int[])" works fine.
Is the error due to escaping as it has been pointed out here:

BTW, I tried to join the IDs with ids.join("','") and pass it as a stringValue parameter but quotes are escaped to prevent SQL injection, so I can't use this tweak.

[silviuglv]

I'm not the best person to answer this as I'm not that proficient in SQL but I believe the issue is that when you do SELECT cast('{4,1}' as int[]) you're basically querying a list of ids {} but SeLECT .... WHERE id IN is a function () that is why you're getting an error.

Have you tried with:
query: SELECT ('id') FROM "table_name" WHERE "id" NOT IN :allowed_ids
params: [{'name': 'allowed_ids', 'value': (${ids.join(',')})]

I think this needs a bit of trial and error but the main point is that after IN you should have a list of params like ('id1', 'id2') not a SQL type array {id1, id2} which the casting is giving you.

Hope this helps :)

[jannikmi]

Thanks for the idea. Unfortunately I get the same error with:

SELECT ('id') FROM "table_name" WHERE "id" NOT IN :allowed_ids

parameters = [{'name': 'allowed_ids', 'value': {'stringValue': "(4,1)"}}]

And it is also understandable, because SELECT ('id') FROM "table_name" WHERE "id" NOT IN "(4,1)" is not valid SQL.
But passing a list (or tuple) as value is not possible, which is the reason for this issue in the first place.

@silviuglv, could you please elaborate how you got the casting workaround to work? Perhaps I can apply that to my use case as well. Thx for the reply.

[ajredniwja]

Reached out to the RDS team again for them to work on it but I am not sure about the priority of it.
V397315224

[dhruvsense]

Bump, it seems like a basic functionality, it does make the use of DataAPI pointless if it cannot support the core datatypes of the database. Please take this up!

[nch3ng]

thumbsup, it's a relatively an important and basic function.

[rakesh-saiga]

is this feature to use Array parameters is possible anytime near soon? this would be super helpful if available

[frankisans]

Base on @sambs workaround:

const parameters = ids.map((id, index) => ({ name: `id${index}`, value: {stringValue: id} }))
const placeholder = [...Array(ids.length).keys()].map(x => `:id${x}`).join(',')
const params = {
    secretArn: 'yourSecretArn',
    resourceArn: 'yourResourceArn',
    sql: `SELECT * FROM member WHERE member_id IN (${placeholder})`,
    parameters 
  })

[rakesh-saiga]

Hello @frankisans I did try this option but i see same error An error occurred (BadRequestException) when calling the ExecuteStatement operation: Array parameters are not supported. it would be super helpful to have this feature if supported by default rather than attempting to find workarounds :)

[frankisans]

Hi @rakesh-saiga I use this solution with @aws-sdk/client-rds-data v3.72 with lambda function and is working fine.

[rakesh-saiga]

i see, in my case error was when using go library of rds-data package. hence may be the issue we are seeing are different

[lefnire]

@frankisans workaround works for me. Pretty bonkers this isn't supported, what does arrayValue from docs do then? Is it a placeholder for future functionality?

[jlag34]

@sambs @frankisans thank you so much for this. After hours of dead ends, I finally got it to work with your solutions

[lefnire]

Here's a bit of a shim per @frankisans fix https://gist.github.com/lefnire/26d72911ee7fb7dcf82fc5d919d192a5

[ashishdhingra]

Backlog item in queue in the service team backlog. As of now, we can't share specific timelines on when this might be implemented in GitHub. Reached out to service team for the update.

Closing this issue since it is tracked in the service team's backlog. The feature, once supported by service team, should be available to all SDK(s) automatically, with API documentation updated.

[github-actions]

This issue is now closed.

Comments on closed issues are hard for our team to see.
If you need more assistance, please either tag a team member or open a new issue that references this one.
If you wish to keep having a conversation with other community members under this issue feel free to do so.