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
demandé il y a 2 ans1241 vues
1 réponse
0
Réponse acceptée

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
répondu il y a 2 ans

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions