I’m doing a lot of work in a MSSQL database and have been using the GROUP OVER technique (standard) for dealing with the sql DATETIME column type.
One thing I’ve noticed for a while now is that I do not seem to be able to add the actual date/time fields (within the group) into a key through the dictionary.
In SQL they become a Datetime variable type, a single variable. The functionality you are seeing in the clarion dictionary editor is correct. If you are using FM3 it will be ignoring all of the non-unique indexes in the clarion dictionary when it does the push to sql, it only creates the unique indexes.
Happy to take this one off line if you want to take this question further. I have “a lot” of experience in this area. PM me.
Good to hear from you. I’d rather have convos here around this, as it will hopefully be helpful for anyone else having the issue.
Yup, they do become DATETIME (as indicated in my original post). My issue is not that they are a single column in SQL but that Clarion has them as separate columns in the dictionary table.
If Clarion only wants to allow for a single value, then my testing of the STRING(8) OVER’d column that represents the name of the column in SQL - That this cannot be used in ISAM filtering (set/next), when checking key values against other values.
This project doesn’t make use of FM3 to push to SQL (all structural changes are managed elsewhere from the system I’m building).
I can use PROP:SQL or PROP:SQLFilter, which achieve the end result, but I’m wanting to understand why I can’t add clarion columns to clarion keys for the purposes of set/next checking against clarion keys.
I suspect the answer is that you can’t add GROUP columns into keys, or GROUP with OVER.
But I can’t find any reference to that, so I’m asking here.
I think you know the answer. Set/Next operations are basically a waste of time when using Microsoft SQL Server. Yes, Prop:SQL etc etc is the way to go. What you haven’t mentioned (and I assume they still exist in v11) is the Turbo SQL tables switch. Alot of this Set/Next logic can be moved to these temp tables which is really cool alot faster and involves less data traffic across the network. Set/Next will see the entire record set pulled, using small select scripts with the turbo sql means only the fields you want are returned. So using “select *” in these turbo sql tables is seen as being lazy and is somewhat stupid as it just increases the data traffic.
An alternatively approach is to uplift important logic to an SQL function or Stored Procedure and then manage it out of Visual Studio, both can be called from the guts of clarion.
Sometimes that’s what you want, depends on your table structure.
Alternatively, you can use a VIEW and just PROJECT the fields you want. You can leverage existing dct and tables into views and go a long way with filters and other things before PROP:SQL is appropriate.
Many different approaches depending on the problem you want to solve!
You used to be able to. Is it a technical change or did someone, in all their wiseness decided to protect us for doing a bad thing™ by forcing this choice in the DCT editor
If you know why you are doing it and what you are using it for I don’t see a reason not to put just the date part of a DateTime in a clarion key.
Was on the train last night thinking I should have updated the question/post above about the option to use a VIEW.
Allows the set/next usage AND gets around the dictionary columng/keying issue (obviously considering that SQL needs to be indexed properly etc on the columns).
Hi,
I have these keys but in a browse e.g. I hand code tests on the component at [BRW].ValidateRecord after the template filtering, priority 6600, and set the templates return to Record:Filtered. That allows me to forget the time component but still use the templates, e.g.
IF WOR:WantedDateTime_DATE <> TODAY() THEN BRW1::RecordStatus = Record:Filtered.
Then lower down the template builds this:
ReturnValue=BRW1::RecordStatus
! [Priority 9000]
! End of “Browser Method Code Section”
RETURN ReturnValue
This way you are sticking closely to the ABC code and unlikely to get unexpected failures when Clarion updates are released.
