Call a redshift stored procedure with dynamic parameter from boto3 lambda

0

I have a lambda that needs to call a stored proc in redshift with a parameter value not known at runtime. I have narrowed the code down to this in the call:

response = rs_client.execute_statement(
            ClusterIdentifier='my_cluster_name',
            Database='my_db_name',
            DbUser='my_user'',
            Sql='call my_schema.this_is_my_proc()',
            Parameters=[{'name': 'in_param', 'value': 'this is the parameter'}],
            StatementName='Test of SP call'
        )

The above works fine with the creds (I have anonymised them) and with no parameter and also a hardcoded parameter in the Sql argument i.e. Sql=call my_schema.this_is_my_proc('this is hardcoded)' so that's not the issue. I also tried passing in the parameter as a formatted string e.g.

param = 'this is the parameter'
stmnt = f'call my_schema.this_is_my_proc({param})',

The message I get back from the lambda Test execution is:

An error occurred (ValidationException) when calling the ExecuteStatement operation: Parameters list contains unused parameters. Unused parameters: [in_param]

Is it possible to call Redshift stored procs that have parameters from a lambda?

TIA

SevCo
asked 2 years ago1293 views
1 Answer
0
Accepted Answer

Hi, as mentioned in the documentation the parameter should be typed in the sql statement in the format :parameter.

following the doc your code should look like:


response = rs_client.execute_statement(
            ClusterIdentifier='my_cluster_name',
            Database='my_db_name',
            DbUser='my_user'',
            Sql='call my_schema.this_is_my_proc(:in_param)',
            Parameters=[{'name': 'in_param', 'value': 'this is the parameter'}],
            StatementName='Test of SP call'
        )

hope this helps,

AWS
EXPERT
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions