If no deviations are selected, the program fails

[RossBoylan]

Test Condition

Running v0.0.6, select G:\ispy_2022\4493_UCSF\02941\20231005_v25\E10232\brtool_DB_Text\UCSF_02941_v25_forDB.txt as the input fiile. Set visit to A6w, site to 4493|UCSF, Volume Calculation to Possible and Report Received Date day to some value, leaving month and year blank. Then hit save.

Alternate, while Ross tests, was to take the same input file and just hit save.

Either resulted in the same failure (i.e., same traceback, point 4 below).

Symptoms

1. No user feedback that something has gone wrong.
2. No data are committed to db. (So transaction is working to prevent partial commits).
3. The database is locked for all other uses until you quit the program.
4. Tracing it under the debugger yields:

   Traceback (most recent call last):
   File "C:\Users\rdboylan\Documents\ispy2_mri\src\ispy2_mri\ispy2_gui.py", line 581, in save
   self.write()
   File "C:\Users\rdboylan\Documents\ispy2_mri\src\ispy2_mri\ispy2_gui.py", line 573, in write
   self.curs.execute(
   pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW)")

   Line 573 is

       self.curs.execute(
           "{call insert_ispy2_deviation(?,?)}", (ispy2_tbl_id, discrepvals)
       )

   and discrepvals == '', which is what it should be.

Diagnosis

Selecting Scan quality insufficient---I expect any deviation reason would do--results in successful write to db.

If just the day is set for the received date, NULL gets written to db for date.

Preliminary Dx

Selecting no deviations results in failure writing to the deviation table. The reason it does so is unclear, since the procedure being called can cope with empty strings (I recall). Maybe it gets converted to None or NULL.

Ancillary Issues

These all have bugs of their own, listed in parenthesis, even though they came up while investigating this problem.

1. The GUI eats errors silently. (#4)
2. The GUI provides no feedback on success. (#5)
3. The program can write values to the db that break the tomcat application. When I selected a deviation reason and wrote the result it wrote such records. (#7)
4. Transaction locks are being held open on failure. (#4)