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})
PythonPython
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?