I use Clarion 11 to create a front end to a sqldatabase in ms sql express. I usually create the database first with MS Sql Server Management Studio. The tables I then import into the application’s dictionary.
This works fine and there is no big problem with that. But the problem for me is that I always need to make changes to the database afterwards. I need to add fields, change field types, create relationships etc.
I try to update the dictionary manually afterwards to reflect the changes in the tables and this works most of the time.
But the best approach is import the updates tables and give them a new name in the dictionary. Afterwards I need to update all the browses, forms, trees etc with the new tablechanges. This is a lot of work.
I would like to get some advise from the community on what is ‘best practices’ that works best for other developers when they need to update their database back end.
The easiest way is you only make changes in the dictionary and then the MS-SQL tables are automatic updated with the changes you made.
For that i use Capesoft’s FM3 it does all that what you want!
Hi Werner, we do this manually. Most of the time when we need a new field we need to fill it not with a single default value but with logic that depends on the other fields, files, etc.
Usually we write a sqlscript to make the change of the table layout, and the assigning of values in the new layout.
This is completely normal.
[Aside: Relationships are a bit different because they are used differently on the server and client side, and do not need to match]
Good. You can change the server side, and dictionary to match. For small changes, especially as you gain experience, this is a good approach.
Nooo - don’t do this… In no world is this the “best approach”… because …
exactly. Items in the dictionary are linked into the app using a (hidden) id called a GUID. By importing the table again you “disconnect” the app from the guids - which is a “bad thing”.
As in most things, best practice depends on your context. you don’t say if this is a one-database system (ie only one production database out there) or if this is part of a package you intend to sell, and so have lots of databases out there. Which approach you take will depend on your context.
If you are a single-database system then manually changing the database, and manually updating the Dictionary to match is do’able. If you have a small number of databases to physically update a small script which you run manually is also ok.
If you are creating a commercial product then things are a bit different, because the manual approach does not scale well, and is prone to errors. In this context using an automatic upgrading tool (like File Manager 3 mentioned earlier) is definitively a good thing to do.
[Disclosure: I profit from the sale of FM3, so consider by opinion here to be biased]
FM3 from CapeSoft is a must have in every project. The time savings are huge and it takes care of all dictionary changes without having to worry about it at all.
You might also take a look at SQL Script.It! from ohnosoft. It’s written by John Hickey (one of the ClarionLive organizers) and Mary Wade. They’ve done a couple of ClarionLive webinars walking through how it works.
Might be overkill for your needs, but they have a free demo you can try:
IMO, FM3 is great as long as you are only using tables and indexes in the SQL database. FM3 does a good job of maintaining your file structures. However, once you move on from simple tables and start using other aspects of SQL (Stored procedures, views, computed columns, etc), then you have to maintain those on your separately. Rather than have two different upgrade methodologies, I’d recommend using a single solution. SQL Script.It is great, SQL Source Control, or you can role your own. Just make sure you are very meticulous about tracking your changes to the database regardless of the method you use.
Thanks for your replies and comments. I do not use Clarion professionally at the moment, so money available for purchase of extras is limited. I use Clarion 11.
My applications are quite simple with one database and approx. 30 tables and views. I also use Microsoft Access as a front end to test sql queries for views etc, before I create them in SSMS.
With regard to adding fields to tables does people have a strategy on this? Like for instance do you add fields to the end of the current sql table or do you drop the current table and place new fields inside the table in a place of your liking?.
I am not new to sql, but I am rather new to Clarion and SQL. The ‘problem’ with the update of the dictionary after database changes is relatively minor compared to how professional the application looks with Clarion. I like to use the traditional approach with mdi windows, browses/trees and update forms. My application is currently on the local pc, but since everything is moving to the sky nowadays, the plan is to modify the application to work as a front end for the database in the sky.
Script.It doesn’t appear to be sold any more. I am using FM3 but it only transferred the primary key from my TPS files to PostgreSQL. Do I really have to create all the indexes and relationships by hand?
10 years ago I had to migrate TPS-> MSSQL and TPS-> Postgres
I exported the original DCT (tps)
Then I wrote a program which analyzes this dct and operates on the changes
Ex
TPS: MyDate Date
by generating MydateS string (8)
MydadeG group, OVER (MyDateS)
Mydate Date
Mydate_Time Time
End
Change ULONG -> LONG (postgress case)
and others …
To answer your other question, you do not need all of the indexes in your dictionary on the backend. If you do a fetch or get on an index in Clarion it just generates the SQL where clause with the equality values. The SQL engine will return that data regardless of whether or not there is an index. Same with sorting.
Having an index on the backend can improve performance for returning data when the filter or order of the statement leads the engine to use that index, but too many indexes can impact insert/update operations.
I read this a while ago and forgot about it again. Thanks for the reminder.