DB Query Builder is not showing Select Statement correctly for existing Server Actions

Wappler Version : 3.9.1
Operating System : MacOS
Server Model: ASP.NET
Database Type: MSSQL
Hosting Type: IIS

Expected behavior

What do you think should happen?

Select statement should show selected fields and conditions for existing server actions in DB Query Builder.

Actual behavior

What actually happens?

The Select statement shows no fields and conditions even when these are available in the associated JSON file.

How to reproduce

  1. Click on an existing Server Action
  2. Click on Query Builder
  3. Select statement does not show any fields and conditions

So you are editing an existing query and it doesn’t show all fields and condition in the query builder, is that correct? Can you also give us the json from the action step.

Hi @patrick, thank you for looking in to this.

Yes, that’s correct. This is happening with the existing queries only.

Some server actions are correctly showing all fields and conditions in Query Builder. I think there’s a link between this and DB Connections under Globals. I have 4 DB Connections and out of these, 1 connection has the Direct Connection set to OFF. The queries for this DB are showing correctly whereas the other 3 DB Connections with Direct Connection set to ON are not showing the queries with all fields and conditions. There could be other reasons for this, but, this is what I have noticed.

I have attached the json file from this server action.

products_details.json.zip (1.6 KB)

Did you ever find a resolution for this? I am seeing the same thing but with asp classic. My server scripts are way to big to rebuild…

If i create new pages then it works as it should. Its just pre-existing scripts that I’m having problems with.

I believe it was an issue with the dbo MSSQL Schema prefix in table names. I edited the JSON to remove this prefix. Make a copy of the current JSON before editing and see if the issue is resolved by removing dbo prefix in the original JSON file.

In v3.8.3, DB Query Builder for MSSQL was changed to handle tables with dbo prefix.

Database Query Builder
Improved working for MS SQL tables that are now prefixed with dbo.

The problem was indeed the dbo prefix, it is an issue specific with mssql database and happens only when you try to edit a query that was created with Wappler before the schema prefix support was added. There were several fixes for the dbo prefix released that solved several problems, but it still is not 100% fixed. Not sure if we will go fix this soon as we have other issues we work on at the moment and for new queries there should be no issues as far as I’m aware of.

So is there a way to manually edit the script so that it will work? Do I need to add the dbo or remove it or what? I have huge crazy server connect scripts that in no way do i want to recreate? Can I send you one of mine to look at, this site is classic asp as @guptast was using .net? @patrick

Always make sure you have an backup (for example by using Git). You can first try to edit it directly, several bugs for this issue were already fixed, so it is possible that it works without needing to change the files. It is a while back that I last looked at this issue, so I don’t know exactly if you had to add or remove the prefix within the json, perhaps @guptast knows that.

I created a new workflow(server connect action script, getting used to calling them work flows) and then compared the two. The old script doesn’t have the dbo. and the new script does. This will be a lot to edit. :frowning:

@patrick Will this be addressed soon? Or will I just have to edit all the scripts manually? So far my editing isn’t exactly working. Oh, yeah, welcome back from holiday!

If I recall correctly, the JSON files created before some version updates needed to be edited to work - to add or remove dbo. to match with the JSON schema required in that specific version. I have checked the JSON files in my current projects and these are set up without the dbo. prefix (in v3.9.9).

You can use find and replace command in the JSON editor to add dbo prefix. Find the table names and replace all instances with dbo.tablename one by one to ensure that the table names are being updated/replaced in correct places. You will need to repeat this step for each table name in the query.

I have also noticed that when editing JSON, don’t highlight the SA/Workflow being edited as it may not save the new information correctly.

ok thanks, I’m going to give this a spin tomorrow to see how it goes…

There really should be no need for replacing anything.

We fixed that a while ago and the rename should be done automatically on open and save of the query

Make sure you run the latest Wappler version.