Search code examples
pysparkazure-databricksazure-synapse

calling Synapse stored procedures with input and output param's and capture output result

The below code returns true or false but not the output from stored procedure

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url, mssql_user, mssql_pass)
connection.prepareCall("EXEC sys.sp_tables").execute()
connection.close()
Solution

  • You can try below code which give you each rows of table in array.

    driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url, mssql_user, mssql_pass)
tst = connection.prepareCall("EXEC sys.sp_tables")
tst.execute()
results = tst.getResultSet()
column_count = results.getMetaData().getColumnCount()
output_arrays = []

while results.next():
    row_array = []
    for i in  range(1, column_count + 1):
        column_data = results.getString(i)
        row_array.append(column_data)
    output_arrays.append(row_array)

output_arrays

    You will get the result as below.

    [['master', 'dbo', 'MSreplication_options', 'TABLE', None],
 ['master', 'dbo', 'spt_fallback_db', 'TABLE', None], 
 ['master', 'dbo', 'spt_fallback_dev', 'TABLE', None], 
 ['master', 'dbo', 'spt_fallback_usg', 'TABLE', None], 
 ['master', 'dbo', 'spt_monitor', 'TABLE', None], 
 ['master', 'sys', 'trace_xe_action_map', 'TABLE', None],
 ['master', 'sys', 'trace_xe_event_map', 'TABLE', None], 
 ['master', 'dbo', 'spt_values', 'VIEW', None],
 ['master', 'INFORMATION_SCHEMA', 'CHECK_CONSTRAINTS', 'VIEW', None]

    Output:

    enter image description here