Question
· Jul 13, 2023

How can I run an iris sql command using AWS SSM?

I'm trying to execute SQL on a EC2 via SSM:

import boto3

instanceid = "i-123456789"
sql = """SELECT path FROM Security.Applications WHERE ID = '/csp/sys'"""
template = """su - irisusr -c 'cat << EOF | iris sql iris -U %SYS
                """ + sql + """
                        q
                        EOF'
                       """
template = [line.strip() for line in template.splitlines()] 
template = """\n""".join(template) 
ssm_client = boto3.client('ssm') 
response = ssm_client.send_command(
            InstanceIds=[instanceid],
            DocumentName="AWS-RunShellScript",
            Comment=AWS,
            Parameters={'commands': template})

Tried various escape options but ' and \ and / symbols seem to cause issues:

ERROR #5540: SQLCODE: -12 Message:  A term expected, beginning with either of:  identifier, constant, aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %MVR %SQLSTRING, %SQLUPPER, %STRING, %TRUNCATE, or %UPPER^ SELECT path FROM Security . Applications WHERE ID = /\n[SQL]IRIS:%SYS>>\n"

I have a workaround with iris session and %SQL.Statement:%ExecDirec, but I'm interested if anyone has an example with iris sql?

Discussion (0)1
Log in or sign up to continue