Changing SQL in a Grid

Scriptcase 8 Discussion
#1

Hi All.

I’m a newbie here on the forum, but we have been playing with Scriptcase here for a while. Now we’re starting to use it for real in our client projects.

It’s going very well. But one thing which is bugging me a little is that every time I change the SQL on a Grid application, I seem to then have to redo all my other work in that application. For instance, I just added one field to the table behind my grid, adjusted the SQL to include that field and all my Refined Search, Grouping, Search, Columns, select fields etc disappeared.

Does anyone know any tricks to prevent this happening, or is it a quirk we have to live with?

Thanks

Jeff

#2

I would report this as a bug, I sure hope you don’t have to change your db often. You can create SC apps pretty fast, but you need to have a good designed datamodel. You can add / delete fields, but in general you will lose a lot of developmenttime tuning things. All forms needs to be synced, grids changes etc. Lot of the advantages gets lost if you need to change the structure.

#3

Thanks for taking the time to respond, Albert.

Yes, it’s true, we wouldn’t normally change the structure very often. But sometimes a client might ask for another column on a grid and we would normally then add it into the SQL. Just seems odd to lose so much for a simple change - doesn’t seem to happen with Forms, only with Grids.

#4

Same problem. I have to re-assign everything after a small change in sql.

#5

Hi ozgurkalan

We have since found that it’s usually when we adjust the tables or table links in the SQL. So we now always create a MySQL view for each grid. If the structure of the view changes, you just make the changes on your MySQL server, and then just edit the SQL to indicate the fields required in the grid. Just changing the field list seems to disrupt the grid much less.

​Hope this helps

Jeff

#6

I too have had the same experience… I have noticed that if I go slowly, adding just one field to the SQL, then going into the “Field Positioning” and adding that field to my Displayed Fields and saving it… I can add them one at a time without problem.

If I change the SQL a lot… it blows it up.

Thats been my experience.

#7

Loose all your work just be changing the SQL, wow, I didn’t know that.

Can ScriptCase fix this?

#8

Will this issue ever be fixed? Im updating to the new version soon which will hopefully have the fix, but just lost a very large filter grid to this bug in v8.1

#9

Hi storealutes.

I am the original poster, but we actually now manage to work around this pretty well.

My tips are…

  1. Use SQL views instead of tables when creating grids. This also helps if you have complex SQL, which SC may not be able to parse properly.

  2. If you change the SQL view structure, close your SC project and re-open it before you modify any app which uses the modified view. This forces SC to see the changes (there is no equivalent of ‘Synchronise Table’ in a grid).

  3. If you need to include in your grid any new fields which you have added to the SQL view, always add them to the bottom of your SQL statement in the grid, app, and not before any of the existing fields.

  4. Avoid deleting fields from the SQL if you can - instead, use Field Postioning to remove any unwanted fields from the grid.

  5. Always copy the grid app before making any structure changes - just in case!

Also, sometimes when a grid has crashed due to a SQL change, I have found that putting the old original SQL back can make it work again and all the refined search etc comes back.

Hope this helps. As you say, it will be great if v9 handles this better.

#10

I’m using SC9.2 and this is still an issue. I changed the SQL type of one field from “YEAR” to “INT” and then could not get the grid to see the change. Normally this would not be a problem but when exporting to Excel or CSV, those formats don’t read the “YEAR” SQL type and so print “0000” where there should be “2017” or “1998”. It doesn’t matter what field type I use - text, int, whatever - as long as the SC export to CSV/XLS sees “YEAR” when exporting, the column turns up “0000”.

bdl - your list of Tips are really, really helpful. I’ve been using SC since around 2012 and never thought to use views to create more complex queries to “import” into SC. Some of your other tips - especially making a copy of everything before making any changes - I learned the hard way right off the bat. And not deleting any fields but just removing them from the grid…that’s a good one I learned early on, too.

Many thanks for your insight.