MSSQL server integration bugs

[benaunt]

Hi,

We have encountered various bugs when utilising MSSQL as the backend database for an app.

All tables were created in the DB via the Budibase UI, BB version 2.6.9 and MSSQL 14.0.2047.8.

Scenario

• Parent table 'client_agency' has the inbuilt ID, a named column titled 'Agency'
• Child table 'portfolio' has the inbuild ID, a named column titled 'Portfolio'.

One client agency can have multiple portfolios.

Issue 1:Defining relationships via the Define Relationship button on the SQL data source.

Setup as per image, successfully saves the relationship:

On the tables

• client_agency - a new column titled 'portfolio' was created
• portfolio - a new column titled 'client_agency' was created
• both tables are drop downs that show the correct data.

When attempting to add data into the portfolio table i can specify the free text field, and the related client_agencies appear in a drop down, but the save button is not active:

Using the data grid form to add brings up an error when saving:

Issue 2: Defining a relationship via adding columns instead of at the define relationship process

After removing the relationship and related columns from above, on the portfolio table add a new relationship column:

Receives an error on save:

Re-fetching tables brings in the new number column on the portfolio (child table), but its not a drop down of the parent.

If you then add a definition and point the ID to the new FK that was generated it works:

Issue 3: Data randomly disappearing
Hard to provide information on this, we can fill in several lines of a table via the data grid, it appears to have saved, when you flick between a different screens and go back the data is gone.

Issue 4: table structure randomly disappearing
Similar to above, adding new columns to a table, everything appears there. Switch between pages then go back and the columns are no longer appearing, additionally whole tables disappear. Re-fetching the tables brings everything back though.

Thanks
Ben

[melohagan]

Hi @benaunt

Apologies for the late response, would you be able to share the schema for your tables (the SQL CREATE scripts).

[benaunt]

Hi,

Will look into that.

We have updated to version 2.7.21 now.

I am not sure if this is related but the schema detection when using MSSQL appears a bit off.

Scenario
On our database there are two schemas such as:

testrelationships
testschemaissue

Schema testrelatiohships has existing tables titled parent and child

Schema testschemaissue has no tables.

If i create a brand new app in budibase, connect to the DB with both schemas, but specify the default schema as testschemaissue. Budibase will correctly display no tables.

If i then create a new table via the budibase UI titled parent. The table is successfully created with basic columns.

Issue
When i then run the fetch tables process via the budibase UI, the parent table i just created displays all columns that exist in the table with the same name in the other schema despite the app not being connected to that schema.

I cannot save data to this table.

[melohagan]

Thanks @benaunt I was able to reproduce and I've raised an issue here: #11225