Environment:
When trying to pass the contents from a form in flask to a SQL Server stored procedure I will need to have the option of passing a null value.
@app.route('/', methods=['GET', 'POST'])
def index():
user_data = None
if request.method == 'POST':
test = request.form.get('test',None)
#test = request.form['test'] Original also failed
# Query the stored procedure
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute("EXEC [dev].[Test_Get] @test=?", test)
user_data = cursor.fetchall()
cursor.close()
conn.close()
return render_template('index_test.html', user_data=user_data)
The value of test is a blank string when it gets to SQL Server not a NULL value like I need.
At this point I can correct this error over on the SQL Server however since SQL is more expensive (and I have little desire to modify 154 stored procedures.) and these procs will be called a significant number of times I prefer to keep the work on the web servers.
Proc ran in SSMS proc called from flask
NOTE: This is a small demo script and flask page to display the problem and is not the full application.
When passing a null value using flask it is sending a string instead of a NULL Code being used: (Full Code above)
request.form.get('test',None)
The resulting empty string is displayed in the above attached flask image. When the empty string is passed to the stored procedure instead of the NULL value it is causing errors.
If a null value is passed to the proc as expected it returns a null value. (image above)
Since your SQL procedure has default parameter value as NULL, you can use it to call the procedure with NULL values.
I checked your demo image and I am assuming all your procedure has NULL value as default for parameter
In Python code, I added the if condition to check if the value passed from form is NUll, then call the procedure without any parameter ( this will invoke the procedure with default value which is NULL) else call the procedure with value provided.
@app.route('/', methods=['GET', 'POST'])
def index():
user_data = None
if request.method == 'POST':
test = request.form.get('test',None)
# Query the stored procedure
conn = get_db_connection()
cursor = conn.cursor()
if test == None:
cursor.execute("EXEC [dev].[Test_Get]")
else:
cursor.execute("EXEC [dev].[Test_Get] @test=?", test)
user_data = cursor.fetchall()
cursor.close()
conn.close()
return render_template('index_test.html', user_data=user_data)