PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 23 → Automatic ID Data Type
Automatic ID Data Type
Débuté par André Labuschagné, 17 fév. 2018 22:17 - 16 réponses
Posté le 17 février 2018 - 22:17
Hi All

I am trying to get my head around this data type, with specific reference to working with SQL flavours other than the HyperSQL stuff where the primary field will auto-increment using and internal sequence generator. Think PG. What does the Automatic ID Data Type setting do when a non-HyperSQL database is addressed? Does it ignore the field and leave it up to the DB to do the incrementing or does it interfere in any way? I am asking as I have a weird situation that has arisen that I am trying to get my head around. It seems as though the sequence value in one table has gone adrift and needs resetting when using other languages [duplicate primary key value returned] but WX quite happily continues to inserts record into the same table without returning any errors and the record is actually inserted. As the other languages and tools are returning a duplicate on the PK because the sequence value has gone wonky with WX inserts still work. I am baffled - and concerned that WX may be messing with the counter.

Any ideas?

Cheers
André
Posté le 17 février 2018 - 22:52
Hi André,

for the automatic ID type, and use GUIDs...

Best regards
Posté le 18 février 2018 - 05:10
Hi Fabrice

I do not understand. How does it affect PK fields of non HyperSQL files where the backend does the auto-incrementing. That is what I am asking. Does it ignore the field? Or does it try and prime it? I do NOT use HyperSQL files at all.

Does anyone know?

Cheers
André
Posté le 18 février 2018 - 10:34
André
I have a number of applications running against a MySQL back-end and have not had any issue with the auto-increment function.
Even in cases where a change has been made from using HFCS to MySql auto-increment has continued the sequence without any problem.
Never actually looked into it but I have always assumed this to be an internal function, an insert trigger if you like, of whichever database you are primarily connected to.
Indeed for a non HF database it is not actually necessary to define the PK as auto-increment in the analysis.

These days, as Fabrice eluded to, I use GUID's as the PK which would prevent any problem such as yours ever occurring.
Does increase the size of the data tables and indexes somewhat especially when you need multiple FK's (GUID = 32 chars) but performance is not affected.
On older apps and db's you do not have control over it is not always possible but well worth consideration if you are the master of all you see.
Posté le 18 février 2018 - 12:25
Hi Derek

I have just established that WX is screwing with the PK and badly so. This is terribly bad news. It is in fact a show stopper of gigantic proportions. It renders WX completely unusable when a database is shared. What I am trying to establish is how to stop WX passing anything to the backend for this field while using HAdd. Is there anyway to do this? All other languages I have used have this functionality. I am going to have a look at the HAdd help. I may have to hand code this. I was trying to avoid that option.

Cheers
André
Posté le 18 février 2018 - 12:35
Hi Derek

Just checked the Help. There is no parameter for HAdd that instructs the black box to not pass the value of the PK and to leave it up to the backend. This is bad - very, very, very bad indeed. We will have to hand code all inserts in all our WX apps. Unless someone can chip in and point us in the right direction that is the way we will have to go. A great disappointment indeed.

Cheers
André
Posté le 18 février 2018 - 13:19
The only options available to you are hForceIdAuto or hSetIdAuto but in both cases you would need to know the last assigned id.
No easy in a multi user OLTP system - even worse if you are transaction processing.
Or of course do you have either of these lurking somewhere and wreaking havoc.

May be that I am not understanding properly but are you saying that you have 2 different back-end db's responsible for assigning PK values or that 2 applications are sharing the same db.
I will assume the latter so do you apply the hLockWrite function when calling Hadd() or possibly problematically HLockNo ?

Were it to be the former and I can see no possible reason why anybody would do this I cannot see how WX can interfere with the PG back-end as to do so would need to have some huge error in the gateway used for access.
In addition to that I fail to see how duplicates can be entered if the target db has that column set as a unique index.

A bit more detail on your setup and specifics as to when this typically occurs would be helpful.

Would you have the option to convert to using GUID's?
Maybe a bit of a slog depending on your methods - easier if you are using a single class method to handle your CRUD operations.
Posté le 18 février 2018 - 13:45
Hi DerekT

Thanks for your continued interest. What I require is insanely simple. On an insert WX must not touch the PK as it is handled by the backend. It must not pass anything to the backend and treat the field as read only. It is that simple. There does not seem to be an option to achieve this with WX unless I am missing something. This is a huge limitation as insisting on passing a value completely undermines the sequence counter on the backend and actually results in it not being incremented. HExecuteSQLQuery of course gets around this as the programmer decides on what fields gets included in the insert. But it really is pretty tedious.

I am quite shocked that PCSoft have not thought of this problem. It is 2018 and databases are pretty much shared these days. I am gobsmacked.

Unless I am misunderstanding something that is where I am at the moment. Allowing the backend to handle the PK avoids all the problems you mention. That is why it is there in the first place.

Cheers
André
Posté le 18 février 2018 - 15:22
André

FYI
I have a current live app now some 5/6 years old.
Originally written in v17 now running in v21
Original DB was HFCS, converted to MySQL in Feb 2015
All PK's in analysis are set as AutoID
All PK's in MySQL are set as Auto Increment, Not Null, 11byte int

In total in the app there are 97 occurrences of using Hadd()
Accessed from 7 countries - never got involved in the tech side of this but can find out - and myself using VPN here in Wales to a server located in London.

Pleased to say that we have never had an issue with the allocation of PK's and this is a busy app needing to record time and expense entries pretty much 24/7.

I do not want to appear rude but are you obsessing regarding the HAdd() element of this?
I am sure that your setup or mine for that matter are in any way unique and yet the issue appears to be yours alone.

I would humbly suggest that you look further afield to try and identify if there is a certain set of circumstance causing your issue to arise - some form of logging would probably help.

And I am still struggling with you getting duplicate PK values.

Sorry I cannot be more positive.
Posté le 18 février 2018 - 17:00
Quote
André Labuschagné

Hi Derek



I have just established that WX is screwing with the PK and badly so. This is terribly bad news. It is in fact a show stopper of gigantic proportions. It renders WX completely unusable when a database is shared. What I am trying to establish is how to stop WX passing anything to the backend for this field while using HAdd. Is there anyway to do this? All other languages I have used have this functionality. I am going to have a look at the HAdd help. I may have to hand code this. I was trying to avoid that option.



Cheers

André

I could be wrong here, but in you Analysis, do you have the PK field set to Automatic ID? Doesn't that tell Wx to generate a value? If so I would change the data type for that field to something that would hold a GUID, declare it null and leave it blank to be filled in at the db level.
Posté le 18 février 2018 - 17:54
Hi Andre,

I am using MSSQL with PK. And whatever is in the WX-record for that field, MSSQL ignores it and will autoincrement, when using hadd.

Caution: In MSSQL you can submit a command, so the database is ignoring this behaviour. That is for you Need to restore a database for example and you Need to have the ID you want (to Keep the references to other tables). Maybe this could be possible with MySql, too? I am not really involved in MySQL, so don't know.

Greetings

Erik
Posté le 18 février 2018 - 18:56
Hi Art

>>I could be wrong here, but in you Analysis, do you have the PK field set to Automatic >>ID? Doesn't that tell Wx to generate a value? If so I would change the data type for that >>field to something that would hold a GUID, declare it null and leave it blank to be filled >>in at the db level.

You may have it on the button. That is precisely what I want to achieve. So is the message to WX to not send any value to the db declaring it a null? I may try that.

But you may have noted DerekT's reply that he has declared the PK as AutoID and the database ignores any value passed in an insert.

Cheers
André
Posté le 18 février 2018 - 21:01
Hi Erik

That is what I was hoping it would do but sadly it does not seem to do so. Need to do some more research on this.

Cheers
André
Posté le 19 février 2018 - 09:09
Do you use MSSQL also?
Then you can use the 'profiler' to track all sql-statements fired by the applications and see if it does something unexpected or unwanted.
I guess other big DB's also have this too
Posté le 19 février 2018 - 09:43
André
Do not really see this as a solution.
If your PK is set as a unique index then you may well send a Zero or Null value but only once.
The next Zero or Null, which is in fact a value in its own right, received would still trigger a 'duplicate' error.
Posté le 20 février 2018 - 08:03
Quote
André Labuschagné

Hi Fabrice



I do not understand. How does it affect PK fields of non HyperSQL files where the backend does the auto-incrementing. That is what I am asking. Does it ignore the field? Or does it try and prime it? I do NOT use HyperSQL files at all.



Does anyone know?



Cheers

André
Actually I am baffled.

pgSQL will never allow entry in any field that is configured as Serial Primary Key.

So now why is pgSQL allowing a WX application to enter this value? This also needs to be invistigated....

I think you need to use pgAdmin to check the actual structure of pgSQL Database before doing any other changes to your code.

My 2 cents!
Membre enregistré
67 messages
Posté le 19 novembre 2018 - 16:15
Andre,
Did you find a solution to your problem (I mean the Clarion way, using server sequence) ?

I am just starting a new project on MSSQL and the profiler shows that the implementation of auto increment is surprisingly simple:

SELECT max (ISNULL(field_value,0)) + 1

I am surprised that nobody has experienced duplicate attempts