def run_test_054(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals") val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print val op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print val if (serverinfo.DBMS_NAME[0:3] != 'IDS'): op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} else: op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print val op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print val
def run_test_054(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info( conn ) stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals") val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(val) op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(val) if (serverinfo.DBMS_NAME[0:3] != 'IDS'): op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} else: op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(val) op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC} stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op) val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(val)
def run_test_setgetOption(self): if sys.platform == 'zos': options = {} else: options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'} conn = ibm_db.connect(config.database, config.user, config.password, options) # Get the server type serverinfo = ibm_db.server_info(conn) if conn: if sys.platform != 'zos': value = ibm_db.get_option(conn, ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1) print("Connection options:\nSQL_ATTR_INFO_PROGRAMNAME = ", end="") print(value) else: print("Connection options:\n", end="") returncode = ibm_db.set_option(conn, {ibm_db.SQL_ATTR_AUTOCOMMIT: 0}, 1) value = ibm_db.get_option(conn, ibm_db.SQL_ATTR_AUTOCOMMIT, 1) print("SQL_ATTR_AUTOCOMMIT = ", end="") print(str(value) + "\n") drop = "DROP TABLE TEMP_TEST" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the table temp_test create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO temp_test values (1, 'cat')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1") if sys.platform != 'zos': returnCode = ibm_db.set_option( stmt, {ibm_db.SQL_ATTR_QUERY_TIMEOUT: 20}, 0) value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_QUERY_TIMEOUT, 0) print("Statement options:\nSQL_ATTR_QUERY_TIMEOUT = ", end="") print(str(value) + "\n") ibm_db.execute(stmt) if result: ibm_db.free_result(stmt) else: print(ibm_db.stmt_errormsg()) ibm_db.rollback(conn) ibm_db.close(conn) else: print("Connection failed.")
def run_test_trusted_context_pconnect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print "Connection failed." options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % ( config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Making trusted connection and performing normal operations. tc_conn = ibm_db.pconnect(dsn, "", "", options) if tc_conn: print "Trusted connection succeeded." val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print "User has been switched." # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print ibm_db.stmt_errormsg() ibm_db.close(tc_conn) else: print "Trusted connection failed." # Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections) for i in xrange(10): tc_conn = ibm_db.pconnect(dsn, "", "") if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userAfter = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print "Explicit Trusted Connection succeeded." # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print "Connection succeeded." try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(conn) else: print "Connection failed."
def run_test_053(self): print("Client attributes passed through conection string:") options1 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'} conn1 = ibm_db.connect(config.database, config.user, config.password, options1) val = ibm_db.get_option(conn1, ibm_db.SQL_ATTR_INFO_USERID, 1) print(val) options2 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'} conn2 = ibm_db.connect(config.database, config.user, config.password, options2) val = ibm_db.get_option(conn2, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1) print(val) options3 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'} conn3 = ibm_db.connect(config.database, config.user, config.password, options3) val = ibm_db.get_option(conn3, ibm_db.SQL_ATTR_INFO_APPLNAME, 1) print(val) options4 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'} conn4 = ibm_db.connect(config.database, config.user, config.password, options4) val = ibm_db.get_option(conn4, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1) print(val) options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'kfb', ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'kfbwork', ibm_db.SQL_ATTR_INFO_ACCTSTR: 'kfbacc', ibm_db.SQL_ATTR_INFO_APPLNAME: 'kfbapp'} conn5 = ibm_db.connect(config.database, config.user, config.password, options5) val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1) print(val) val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1) print(val) val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_APPLNAME, 1) print(val) val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1) print(val) print("Client attributes passed post-conection:") options5 = {ibm_db.SQL_ATTR_INFO_USERID: 'db2inst1'} conn5 = ibm_db.connect(config.database, config.user, config.password) rc = ibm_db.set_option(conn5, options5, 1) val = ibm_db.get_option(conn5, ibm_db.SQL_ATTR_INFO_USERID, 1) print(val) options6 = {ibm_db.SQL_ATTR_INFO_ACCTSTR: 'account'} conn6 = ibm_db.connect(config.database, config.user, config.password) rc = ibm_db.set_option(conn6, options6, 1) val = ibm_db.get_option(conn6, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1) print(val) options7 = {ibm_db.SQL_ATTR_INFO_APPLNAME: 'myapp'} conn7 = ibm_db.connect(config.database, config.user, config.password) rc = ibm_db.set_option(conn7, options7, 1) val = ibm_db.get_option(conn7, ibm_db.SQL_ATTR_INFO_APPLNAME, 1) print(val) options8 = {ibm_db.SQL_ATTR_INFO_WRKSTNNAME: 'workstation'} conn8 = ibm_db.connect(config.database, config.user, config.password) rc = ibm_db.set_option(conn8, options8, 1) val = ibm_db.get_option(conn8, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1) print(val)
print("\nERROR: Unable to set the connection options specified.\n") if not dbConnection is None: ibm_db.close(dbConnection) exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Connection option settings:\n") print("OPTION SETTING") print("________________________ ____________________") # Retrieve And Display The Accounting String That Was Defined For The Client value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_INFO_ACCTSTR, 1) print("SQL_ATTR_INFO_ACCTSTR " + value) # Retrieve And Display The Application Name That Was Defined For The Client value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_INFO_APPLNAME, 1) print("SQL_ATTR_INFO_APPLNAME " + value) # Retrieve And Display The User ID That Was Defined For The Client value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_INFO_USERID, 1) print("SQL_ATTR_INFO_USERID " + value) # Retrieve And Display The Workstation Name That Was Defined For The Client value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_INFO_WRKSTNNAME, 1) print("SQL_ATTR_INFO_WRKSTNNAME " + value) # Add A Blank Line To The End Of The Report
def run_test_trusted_context_connect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print("Connection failed.") options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } tc_all_options = { ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Makeing normal connection and playing with it. tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_all_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "", tc_all_options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") get_tc_user = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if config.tc_user != get_tc_user: print("But trusted user is not switched.") ibm_db.close(tc_conn) # Making trusted connection and performing normal operations. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Trusted connection failed.") # Making trusted connection and switching to fake user. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") ibm_db.set_option(tc_conn, {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******"}, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_options_reversed = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user} if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options_reversed, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_user_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user} tc_pass_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass} if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) try: ibm_db.set_option(tc_conn, tc_pass_options, 1) except: print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing only user while switching when both user and password are required. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: ibm_db.set_option(tc_conn, tc_user_options, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Dropping the trusted context, in case it exists try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass # Dropping Role. try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass # Creating Role. try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass # Granting permissions to role. try: sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01" result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass # Creating trusted context try: sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION" result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Closing connection ibm_db.close(conn) else: print("Connection failed.") # Making trusted connection tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_user_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)" try: stmt = ibm_db.exec_immediate(tc_conn, sql_insert) except: print(ibm_db.stmt_errormsg()) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20" stmt = ibm_db.exec_immediate(tc_conn, sql_update) ibm_db.close(tc_conn) else: print("Connection failed.") # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print("Connection succeeded.") try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(conn) else: print("Connection failed.")
def run_test_trusted_context_pconnect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print("Connection failed.") options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass} dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Making trusted connection and performing normal operations. tc_conn = ibm_db.pconnect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Trusted connection failed.") # Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections) for i in range(10): tc_conn = ibm_db.pconnect(dsn, "", "") if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("Explicit Trusted Connection succeeded.") # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print("Connection succeeded.") try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(conn) else: print("Connection failed.")
def run_test_cursortype(self): options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'} conn = ibm_db.connect(config.database, config.user, config.password, options) if conn: drop = "DROP TABLE TEMP_TEST" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the table temp_test create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO temp_test values (1, 'cat')" ibm_db.exec_immediate(conn, insert) print("Setting cursor type to SQL_CURSOR_FORWARD_ONLY") op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY} stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1", op) val = ibm_db.cursor_type(stmt) print("statement cursor type = ", end="") print(val, end="\n") value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(value) print() print("Setting cursor type to SQL_CURSOR_KEYSET_DRIVEN") op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN} stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op) val = ibm_db.cursor_type(stmt) print("statement cursor type = ", end="") print(val, end="\n") value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(value) print() print("Setting cursor type to SQL_CURSOR_STATIC") op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC} stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op) val = ibm_db.cursor_type(stmt) print("statement cursor type = ", end="") print(val) value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(value) print() print("Setting cursor type to SQL_CURSOR_DYNAMIC - zOS only") op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_DYNAMIC} stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op) val = ibm_db.cursor_type(stmt) print("statement cursor type = ", end="") print(val) value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) print(value) print() ibm_db.close(conn) else: print("Connection failed.")
def run_test_trusted_context_connect(self): sql_drop_role = "DROP ROLE role_01" sql_create_role = "CREATE ROLE role_01" sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx" sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID " sql_create_trusted_context += config.auth_user sql_create_trusted_context += " ATTRIBUTES (ADDRESS '" sql_create_trusted_context += config.hostname sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR " sql_create_trusted_context += config.tc_user sql_drop_table = "DROP TABLE trusted_table" sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)" sql_select = "SELECT * FROM trusted_table" # Setting up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01" sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION" try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass try: result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Populate the trusted_table values = (\ (10, 20),\ (20, 40),\ ) sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)' stmt = ibm_db.prepare(conn, sql_insert) if stmt: for value in values: result = ibm_db.execute(stmt, value) ibm_db.close(conn) else: print("Connection failed.") options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE} tc_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } tc_all_options = { ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % ( config.database, config.hostname, config.port, config.auth_user, config.auth_pass) # Makeing normal connection and playing with it. tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "") if tc_conn: print("Normal connection established.") result = ibm_db.set_option(tc_conn, tc_all_options, 1) print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) tc_conn = ibm_db.connect(dsn, "", "", tc_all_options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") get_tc_user = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if config.tc_user != get_tc_user: print("But trusted user is not switched.") ibm_db.close(tc_conn) # Making trusted connection and performing normal operations. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options, 1) userAfter = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)" stmt = ibm_db.prepare(tc_conn, sql_insert) result = ibm_db.execute(stmt, (300, 500)) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Trusted connection failed.") # Making trusted connection and switching to fake user. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") ibm_db.set_option( tc_conn, { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******" }, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_options_reversed = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user } if tc_conn: val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: print("Trusted connection succeeded.") userBefore = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_options_reversed, 1) userAfter = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing password first then user while switching. tc_conn = ibm_db.connect(dsn, "", "", options) tc_user_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user } tc_pass_options = { ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass } if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) try: ibm_db.set_option(tc_conn, tc_pass_options, 1) except: print(ibm_db.conn_errormsg(tc_conn)) ibm_db.close(tc_conn) else: print("Connection failed.") # Making trusted connection and passing only user while switching when both user and password are required. tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: ibm_db.set_option(tc_conn, tc_user_options, 1) sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500" try: stmt = ibm_db.exec_immediate(tc_conn, sql_update) except: print(ibm_db.stmt_errormsg()) ibm_db.close(tc_conn) else: print("Connection failed.") # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Dropping the trusted context, in case it exists try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass # Dropping Role. try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass # Creating Role. try: result = ibm_db.exec_immediate(conn, sql_create_role) except: pass # Granting permissions to role. try: sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01" result = ibm_db.exec_immediate(conn, sql_grant_permission) except: pass # Creating trusted context try: sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION" result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01) except: pass # Closing connection ibm_db.close(conn) else: print("Connection failed.") # Making trusted connection tc_conn = ibm_db.connect(dsn, "", "", options) if tc_conn: print("Trusted connection succeeded.") val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1) if val: userBefore = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) ibm_db.set_option(tc_conn, tc_user_options, 1) userAfter = ibm_db.get_option( tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1) if userBefore != userAfter: print("User has been switched.") # Inserting into table using trusted_user. sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)" try: stmt = ibm_db.exec_immediate(tc_conn, sql_insert) except: print(ibm_db.stmt_errormsg()) # Updating table using trusted_user. sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20" stmt = ibm_db.exec_immediate(tc_conn, sql_update) ibm_db.close(tc_conn) else: print("Connection failed.") # Cleaning up database. conn = ibm_db.connect(config.database, config.user, config.password) if conn: print("Connection succeeded.") try: result = ibm_db.exec_immediate(conn, sql_drop_trusted_context) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_table) except: pass try: result = ibm_db.exec_immediate(conn, sql_drop_role) except: pass ibm_db.close(conn) else: print("Connection failed.")
if dbConnection is None: print("\nERROR: Unable to connect to the \'" + dbName + "\' database.") print("Connection string used: " + connString + "\n") exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Connection option settings:\n") print("OPTION SETTING") print("_________________________ ____________________") # Retrieve And Display The Ordinal Value For The Current AUTOCOMMIT Setting value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_AUTOCOMMIT, 1) print("SQL_ATTR_AUTOCOMMIT ", end="") print(ord(value)) # Retrieve And Display The Schema Name That Was Set During The Connection value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_CURRENT_SCHEMA, 1) print("SQL_ATTR_CURRENT_SCHEMA " + value) # Retrieve And Display The Program Name That Was Set During The Connection value = ibm_db.get_option(dbConnection, ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1) print("SQL_ATTR_INFO_PROGRAMNAME " + value) # Add A Blank Line To The End Of The Report print() # Attempt To Close The Db2 Database Connection That Was Opened Earlier
pass # If The SQL Statement Could Not Be Executed, Display An Error Message And Exit if resultSet is False: print("\nERROR: Unable to execute the SQL statement specified.\n") conn.closeConnection() exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Verify That The Type Of Cursor Being Used Is Type Of Cursor That Was Specified print("Obtaining information about the type of cursor being used ... ", end="") try: cursorType = ibm_db.get_option(resultSet, ibm_db.SQL_ATTR_CURSOR_TYPE, 0) except Exception: pass # If Information About The Cursor Could Not Be Obtained, Display An Error Message And Exit if cursorType is False: print("\nERROR: Unable to obtain the information desired.\n") conn.closeConnection() exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Verify That The Type Of Cursor Being Used Is Type Of Cursor That Was Specified print("Type of cursor specified : Static (SQL_CURSOR_STATIC)")