Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
26
Erster Beitrag:
vor 4 Jahren, 8 Monaten
Letzter Beitrag:
vor 2 Jahren, 9 Monaten
Beteiligte Autoren:
Charles U. Schneiter, Peter Holemans, Fabrice Harari, DerekM, GuenterP, Tor-Bjarne, Luiz Menezes, DW, DarrenF, Ola, ... und 3 weitere

[WD18 - HFSQL C/S] How to 'reset' AutomaticID's

Startbeitrag von Charles U. Schneiter am 09.10.2013 14:15

Hi Folks,

Upon finally changing from HF Classic to HF C/S the following question popped up:

After a lot of testing, Automatic ID's on some files have added up quite some.
In order to roll out the app, I would like to 'reset' the Auto-ID's to 0.

For the life of me, I couldn't find a solution to this simple(?) problem neither here, nor in the help files.

What I did, is to define the analysis without any link. I do this in the declaration code of the project like

HOpenConnection(CharliesConnection)

after setting the various properties of it.

For the primary keys I am using WD's own data type of 'Automatic ID'.

With HF Classic I did roll my own Auto ID feature by maintaining a file called 'PrimaryKeys' and a function 'PullKey' where I lock said file while pulling a PK and incrementing the original value. Each File needing a PK has it's own column in this file. It is trivial to 'reset' ID's with a setup like this. The whole shebang is to avoid locking issues while assigning PK's using flat file DB's in a multi-user setup which is not anymore necessary in a c/s setup..

Everything works just fine, but I would like to start my Primary Keys at 1 all over again after having deleted my test-data.

What obvious thing did I miss :confused: ?

Antworten:

Hi Charles, though I strongly oppose using any AutoIDs .. many reasons .. did you try to HCreation(FileName) the files?

von GuenterP - am 09.10.2013 14:36
Hi Charles

I strongly advise to ALWAYS use autoIDs... for many reasons also, including 20 years of using them without problems...

Jus use hCreation to recreate the file empty/reset IDs

But, juts FYI, there is absolutely no technical reason why you should reset them

Best regards

von Fabrice Harari - am 09.10.2013 14:44
Hi Fabrice,

imho AutoIDs are nothing else than just troublemakers, no good for anything. Sorry for that.

- they do not represent any sensible / useful sorting sequence, they're just telling the sequence of inserts into a file. Any time-stamp does better.

- they effectively prevent any merging of files from different sources.

- they're prone to errors. If a power-out happens, the header of the file may be written with a useless high 'last AutoID' which will result in a (mostly) huge gap in the sequence of AutoIDs.

There are good alternatives to using AutoIDs.

- if it shall be a unique but otherwise useless ID except for linking another file, then just use GUIDs. Merging of files from different sources becomes a treat.

- Most times I'm using a 'useful' unique ID, which can be composed in the 'Trigger before HAdd/HModify'. My favorite one is to combine a formatted unique ID, which the user has/had to input (CustomerID for example) anyway plus a short text (which identifies the customer). This number + text composite unique key is directly available on ComboBoxes. Better, you can always change the CustomerID, the customer will show up in a different sorting sequence then.One that makes sense to the users of the system.

von GuenterP - am 09.10.2013 15:47
Hi Guenter

as very often, we'll have to agree to disagree on that one too... Point by point:

- they do not represent any sensible / useful sorting sequence, they're just telling the sequence of inserts into a file. Any time-stamp does better.

Just add, nothing to do, and you DO have an insertion sequence... Time stamp are easily fooled by incorrecrt date/time on computer... It doesn't mean I don't use them, but not for anything SYSTEM

- they effectively prevent any merging of files from different sources.

They do no such thing... Any merging implies some processing (or you get potential duplicates and lots of problem)... So merging should mean creating a new local ID, even if you want to keep the old ID as reference somewhere. If you use a time stamp, you will have also a chance of duplicates, which means that you will have to do the same kind of control.

- they're prone to errors. If a power-out happens, the header of the file may be written with a useless high 'last AutoID' which will result in a (mostly) huge gap in the sequence of AutoIDs.

They are not... 20 years using them with all my programs and my customers (thousands of them) and I had the problem maybe THREE times

- There are good alternatives to using AutoIDs.

Of course there are, but JUSTIFED alternative, I think not.

- if it shall be a unique but otherwise useless ID except for linking another file, then just use GUIDs. Merging of files from different sources becomes a treat.

Mergin, yes, but you loose the sequence information, which means that when you want to easily SYNCHRONIZE different DBs, you don't know what's new (and again, time stamp, easily fooled)

- Most times I'm using a 'useful' unique ID, which can be composed in the 'Trigger before HAdd/HModify'. My favorite one is to combine a formatted unique ID, which the user has/had to input (CustomerID for example) anyway plus a short text (which identifies the customer). This number + text composite unique key is directly available on ComboBoxes. Better, you can always change the CustomerID, the customer will show up in a different sorting sequence then.One that makes sense to the users of the system.

That has nothing to do with AutoID... This is the concept of record "NAMES" that I have been using for several cases/programs IN ADDITION to the autoIDs... This gives a whole other sets of advantages and inconvenient...

Now contrary to what you seem to say, I do not say that ONLY autoID are good or that any of your proposed solutions are bad. I live in the real workd where there is no absolute.

As I have been using TimeStamp, GUID, and record NAME, I know that these solutions are all well and good, but they are NOT replacing an AUTOID. They are certainly adding some functionalities, but they also lack some of the one given by an autoID.

Best regards

von Fabrice Harari - am 09.10.2013 18:41
Hi guys,

Just to add my spoon to the soup, I think the so called AutoID is just fine -- I just would not even think of leaving the control/handling of it to PCSoft, exept in the case of very simple, parameter type files with a low number of records, such as methods of delivery etc. For all major data, such as products and contacts, I want to handle it with my own ID fields and own code, which takes the next number from a control file, where there are (among other things) several number fields for the different files.

I also use alphameric "human keys" (or "record names" like Fabrice calls them). These are automatically suggested, were feasible, and fully user-defineable/changeable, but require that the whole database must be checked and modified in case of changes.

best regards
Ola

von Ola - am 10.10.2013 07:51
All,

I have to agree with Fabrice on this one...

Good database design (including correct normalisation) actually implies the use of some sort of autoID's (be it GUID's or Auto-Increment-ID's).

Once more I can only refer to the bible in the area of relational database design: 'An introduction to database systems' by C.J. Date...

Also note that computers are much more efficient in comparing numbers rather than texts. Although database indexes are stored as binary/bitmap representations of keys, experience shows me that, in general, complex joins on large database sets are much faster when using autoID's and joins are based on them (primary keyforeign key)...

Just my 2 cents,

Peter H.

von Peter Holemans - am 10.10.2013 07:57
Hi,
as Peter H. already posted, there is a HUGE difference in SPEED when using unique integers/autoID PKs versus text type PKs in a RDBM system.
Not only on HFSQL but on ANY modern RDBMs ....

Ofcourse things always could "get wrong" but this can happen even if somebody bypasses AutoIDs and implements there own integer type primary keys.

von Steven Sitas - am 10.10.2013 08:47
Hi Folks,
[sorry for my late'ish reply, have been away...]

I wouldn't have thought about HCreation() for the life of me - but now, that you mention it :cool: The help on this function is falling a wee bit short of being descriptive.. they only talk about this in the context of 'Replication'...
Thanks Günter and Fabrice for pointing that out!

Thats a very interesting discussion re AutoID or GUID - thanks for that! :spos:

One difference has me got wondering though:
I have the notion that using a client/server database engine does away with AutoID inconsistencies in case of multi-user apps?!? Especially by combining them with a transaction-bracket for critical data ops?

Isn't this handled by the database server in this case? Or is HF C/S just a 'wrap around' (for lack of a better word) to the good ole flat file HF Classic database :confused: If 'yes' I will return to my own AutoID system (akin to what Ola and me are describing above).

Thanks all for your valuable input, most appreciated! :cheers:

von Charles U. Schneiter - am 10.10.2013 16:45
Hi Charles,

HFCS uses the same Classic HF files for pure storage but has a whole server and management component wrapped around it!

Since about 6 years I never used HF Classic anymore except for rare local storage within a Client-Server app. Remember that you can mix both from a single analysis easily within a single app.

It is pretty bullet proof and although V19 promises to be ACID compliant I'm still missing major features which would make it a real enterprise level usable transactional database system:
- Nested transaction support (use of subtransactions). When I started reading the V19 spec, I hoped it to be there. Unfortunately I misread...
- Server side (materialized or non materialized) database views

I find the above a necessity for any real server side db system. But then of course it is free, so I don't complain at all...

So my suggestion is to use HFCS as a first option. Only when the application architecture or database usage is so locally hooked up to a single PC/machine, I would recommend HF Classic...

Just my 2 cents,

Peter H.

von Peter Holemans - am 10.10.2013 17:38
Charles,

It's seems this thread turned into a purist discussion about the use or not of WX AutoID's, which is great in a way as it gives background to the pros and cons of AutoID use.

For what it's worth, I'm with Fabrice on this one as well... I always use Auto IDs on tables and haven't experienced any issues so far. That's not to say Guenter's approach doesn't work (it obviously does), but I didn't want the extra work of coding it myself - just my penny's worth :xcool:

In answer to your initial question, take a look at some of the options of the HAdd function for resetting AutoIDs without having to wipe the table contents - more specifically, the hSetIDAuto and/or hForceIDAuto

http://doc.pcsoft.fr/en-US/?3044147&name=hadd_function&q=hadd

HTH...

von DarrenF - am 10.10.2013 18:00
Darren, Peter,

Thanks for your input!

@ Peter: Yes, I too changed from Classic to C/S in the midst of the development for a POS solution (Chances are, that it will be used in a LAN environment sooner or later, or even to/from remote locations via the web).

The only question now is, how well this server and management component is wrapped around these fic's...
If it is - in this respect - akin to the Classic variant, I can definitely see where Günters reservations come from. On the other hand, there seem to be quite a few statements about good stability.

@ Darren: Thanks for the link - I wasn't aware of these optional parameters! Since I need to empty the tables, using HCreation() will do fine.

von Charles U. Schneiter - am 11.10.2013 13:58
go to EXE directory and look and for the name of the table who content the data. let say in example: ABCD.FIC
the
Delete the ndx file ( ABCD.ndx) then restart your app. that's it.

von perceval - am 04.12.2013 20:31
Thanks Perceval.

Although I am not at all sure, if that's a good idea...

Or do you know of a built in housekeeping feature which cleans out the *.FIC if it doesn't find a corresponding *.NDX file?

I don't know but what do I know ... What would then happen if e.g. one reorganizes a data file?

Any takers with some more technical background info than me?

von Charles U. Schneiter - am 05.12.2013 08:03
Perceval,
When using HRead and so on the indexes are recreated automatically.
But when using certain queries they are not and your software wil throw an error. Or give you no data at all, which can be misleading.

von Arie - am 05.12.2013 09:53
Hello Charles,

Late to the dance but just found this in the help.

Look at HDeleteAll()

From the Help:
The following operations are performed:
•the records are deleted: the records are not referenced in the index anymore. The data is not kept.
•the indexes (corresponding to the keys of the records) are deleted from the index file.
•the memos associated with the records are deleted from the memo file.
•the automatic identifiers are re-initialized.

Not tested but found this for something else

DW

von DW - am 05.12.2013 14:07
Thanks Arie and DW!

@ Arie: Yes, that's what I thought too. Never a good thing to just delete Index-Files only. Comforting to know, that WD automagically recreates them upon using some of the Hxxx functions. But 'static' queries (as I call them) would be shot and give back wrong data!...

@ DW: Oh, thanks for that! That's what I was looking for - even though the other solution using HCreation() also works fine.
Thanks for saving that last dance for me :cheers:

von Charles U. Schneiter - am 06.12.2013 10:45
Hi,

With the HdeleteAll() I belive you need to do a Hindex also:

Example:

HDeleteAll(hfFile)
HIndex(HFfile,hNdxNormal)


Cheers
Tor-Bjarne

von Tor-Bjarne - am 06.12.2013 17:45
Hi Tor-Bjarne,

Thanks for responding!

In the help of this function they say:

Quote
WD Help
- the records are deleted: the records are not referenced in the index anymore. The data is not kept.
- the indexes (corresponding to the keys of the records) are deleted from the index file.


From this I gathered, that this function does indeed handle the index?..
On the other hand, if read again, one could get the notion that just the references to the deleted records are removed from the index file?? Would this amount to a reorganization 'on the fly' or would that be still another 'animal'?

May be this is another language related ambiguity (wth does indexes are removed from the index file exactly mean? :drink: )

von Charles U. Schneiter - am 07.12.2013 09:49
Hi,

Well I have a product LAB-it that collects scaledata and barcodedata via ethernet, comports etc. and the user are presented with a choice to delete all samples.

When I tested I noticed that LAB-it continued to use the Next record-id eaven if all data was deleted (with HdeleteAll) - but after I started using Hindex it started on no 1 again - perhaps a bug in dokumentation or WinDev (17 at the time) but I have to use HIndex to get the automated id to start over :)

This was on local Fic - files type of DB.


Cheers
Tor-Bjarne

von Tor-Bjarne - am 07.12.2013 13:41
Hi TorBjarne,

Thanks for this.
Out of curiosity, I will check that out and see how it works in WD 18 and HF/CS which I am using.
If it's unchanged and the docs are wrong on this part, I will probably stick with HCreation().

von Charles U. Schneiter - am 08.12.2013 12:45
In case this is useful to someone:-

With HFSQL Server data - (WD19) I cannot find a way to "reset" the Auto ID.
There are many, many situations where this is essential. If you never need to do this, that's fine.

To reset the Auto ID - you have to trash the data (eg HCreation).

Here is part of some sample code from a utility to distribute demo data. (I'm sure there are many ways to tackle this problem.)

The simplest method - in my case - was to create duplicate tables in the model - then:

PROCEDURE CopyDataToDemo()
HReadFirst(ADDRESS)
WHILE NOT HOut(ADDRESS)
HCopyRecord(ADDRESS_DemoData,ADDRESS)
HAdd(ADDRESS_DemoData)
HReadNext(ADDRESS)
END

PROCEDURE CopyDemoToData()
HCreation(ADDRESS)
HReadFirst(ADDRESS_DemoData)
WHILE NOT HOut(ADDRESS_DemoData)
HCopyRecord(ADDRESS,ADDRESS_DemoData, hCopyAutoId)
HAdd(ADDRESS)
HReadNext(ADDRESS_DemoData)
END

Useful for getting rid of 10 digit ID's - or even if you don't want to your customers' customer numbers starting with "1"

von DerekM - am 21.08.2015 04:16
Hi Derek

resetting the autoID is very easy, being in classic of C/S. Just do a hadd in the file with either the ForceAutoID or the FixedAutoId option, depending of what you need.

Then you can delete that record and be on your way.

Really nothing to it. Of course, YOU are now responsible of having used an autoID value in your hadd that makes sense and will not create duplicates afterwards.

And all that is done without loosing any data.

Also, as these options are also available on the hmodify function, you can in fact CORRECT any autoID problem (be careful what you do relating to links between files)

Best regards

von Fabrice Harari - am 22.08.2015 12:28
Hi Fabrice

Can you clarify this a bit please. You said 'being in classic of C/S'.

I ran a number of tests with hModify (CS only) using the ForceAutoID and the FixedAutoId options.

Indeed, I can set any ID I like or 'correct' an id - eg - reset a table with 20 records with IDs running 1 thru 20 - but when the application adds the next record - the Auto ID uses the highest number ever used - not the highest number in the table - e.g. Ids 18, 19, 20, 250000.) Deleting "index" files and regenerating does not make a difference.

My assumption then, was that the hyperfile engine uses the password protected files in the _system directory (element, integrity, procedure, trigger, etc) to manage the Auto Ids, and that this data can only be accessed thru a WinDev function.

I don't see that the documentation states anywhere that the AutoID will be reset, or that any of the available options will do this.

I am very happy to be proved wrong on this, but I have not been able to "reset" the Auto Id without deleting the data (eg HCreation).

Regards
Derek

von DerekM - am 22.08.2015 21:22
To Peter H. ,

What about the "Server side database views" of V.20 ?

Thanks in advance.

von Luiz Menezes - am 25.08.2015 14:42
Hi Luiz,

Views (materialized and non materialized) are finally covered as-of V20.
Haven't really tested the features of it yet in ral live applications since all my apps using them are running against SQL Server for now.
Still missing nested transactions though...

Cheers,

Peter H.

von Peter Holemans - am 25.08.2015 15:10
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.