Check Index Query


buster reinke
 

Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster


Govert Schipper
 

Hi Buster,

Are you using Pervasive or SQL?
In the help for this database property, you will find the following line:

This property is only relevant for ISAM databases.


So just for Btrieve/Pervasive, not for SQL databases. I don't think that has changed since uniPaaS.

Govert


Van: main@magicu-l.groups.io <main@magicu-l.groups.io> namens buster reinke via Groups.Io <buster_png@...>
Verzonden: vrijdag 30 augustus 2019 16:34
Aan: main@magicu-l.groups.io <main@magicu-l.groups.io>
Onderwerp: [magicu-l] Check Index Query
 
Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster

Virusvrij. www.avast.com


buster reinke
 

Hi Govert, I am using the default Database SQLite. I have seen the line "This property is only relevant for ISAM databases", but I don't see how that helps me. I was using Btrieve with UniPaaS, but I don't have this option with the single user version of XPA.

Buster

On Saturday, 31 August 2019, 12:51:33 am AEST, Govert Schipper <govertschipper@...> wrote:


Hi Buster,

Are you using Pervasive or SQL?
In the help for this database property, you will find the following line:

This property is only relevant for ISAM databases.


So just for Btrieve/Pervasive, not for SQL databases. I don't think that has changed since uniPaaS.

Govert


Van: main@magicu-l.groups.io <main@magicu-l.groups.io> namens buster reinke via Groups.Io <buster_png@...>
Verzonden: vrijdag 30 augustus 2019 16:34
Aan: main@magicu-l.groups.io <main@magicu-l.groups.io>
Onderwerp: [magicu-l] Check Index Query
 
Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster

Virusvrij. www.avast.com


Steven Blank
 

Buster,

You're stuck between a rock and a hard place, my friend – the behavior you desire is simply not available for SQLite.

If you want the same behavior in this regard that you used to have with Btrieve, then you either need to switch back to Btrieve, or develop your own Duplicate Index error handler to override the default behavior.

Steve Blank

On 8/30/2019 1:32 PM, buster reinke via Groups.Io wrote:
Hi Govert, I am using the default Database SQLite. I have seen the line "This property is only relevant for ISAM databases", but I don't see how that helps me. I was using Btrieve with UniPaaS, but I don't have this option with the single user version of XPA.

Buster

On Saturday, 31 August 2019, 12:51:33 am AEST, Govert Schipper <govertschipper@...> wrote:


Hi Buster,

Are you using Pervasive or SQL?
In the help for this database property, you will find the following line:

This property is only relevant for ISAM databases.


So just for Btrieve/Pervasive, not for SQL databases. I don't think that has changed since uniPaaS.

Govert


Van: main@magicu-l.groups.io <main@magicu-l.groups.io> namens buster reinke via Groups.Io <buster_png@...>
Verzonden: vrijdag 30 augustus 2019 16:34
Aan: main@magicu-l.groups.io <main@magicu-l.groups.io>
Onderwerp: [magicu-l] Check Index Query
 
Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster

Virusvrij. www.avast.com


Steven Blank
 

Here I go replying to my own message...

An upside to rolling your own is that you would be able to apply your duplicate index checking on a column-by-column or form-control-by-form-control basis – checking some fields for duplicates, only when and for whom it's necessary, while ignoring other instances/times.

Steve Blank

On 8/30/2019 1:40 PM, Steven Blank wrote:

Buster,

You're stuck between a rock and a hard place, my friend – the behavior you desire is simply not available for SQLite.

If you want the same behavior in this regard that you used to have with Btrieve, then you either need to switch back to Btrieve, or develop your own Duplicate Index error handler to override the default behavior.

Steve Blank

On 8/30/2019 1:32 PM, buster reinke via Groups.Io wrote:
Hi Govert, I am using the default Database SQLite. I have seen the line "This property is only relevant for ISAM databases", but I don't see how that helps me. I was using Btrieve with UniPaaS, but I don't have this option with the single user version of XPA.

Buster

On Saturday, 31 August 2019, 12:51:33 am AEST, Govert Schipper <govertschipper@...> wrote:


Hi Buster,

Are you using Pervasive or SQL?
In the help for this database property, you will find the following line:

This property is only relevant for ISAM databases.


So just for Btrieve/Pervasive, not for SQL databases. I don't think that has changed since uniPaaS.

Govert


Van: main@magicu-l.groups.io <main@magicu-l.groups.io> namens buster reinke via Groups.Io <buster_png@...>
Verzonden: vrijdag 30 augustus 2019 16:34
Aan: main@magicu-l.groups.io <main@magicu-l.groups.io>
Onderwerp: [magicu-l] Check Index Query
 
Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster

Virusvrij. www.avast.com


buster reinke
 

Thanks Steve, now for the next trick. I realise that it is probably really simple to create my own duplicate checker, but I don't know how to go about it. The table I want to check has many fields, but it is only the first 2 fields together that cannot be duplicated, "Part Number" and "Serial Number". I have tried using Part Number<> Part Number and Serial Number <> Serial Number, but that just prevents me from creating anything new. Could you please tell me how to make a duplicate checker that will pull up the user if these first 2 fields already exist in the database as a pair? This is something I have always taken for granted. I don't understand why XPA would disable this for SQLite.

Thanks,

Buster

On Saturday, 31 August 2019, 6:50:28 am AEST, Steven Blank <sgblank@...> wrote:


Here I go replying to my own message...

An upside to rolling your own is that you would be able to apply your duplicate index checking on a column-by-column or form-control-by-form-control basis – checking some fields for duplicates, only when and for whom it's necessary, while ignoring other instances/times.

Steve Blank

On 8/30/2019 1:40 PM, Steven Blank wrote:

Buster,

You're stuck between a rock and a hard place, my friend – the behavior you desire is simply not available for SQLite.

If you want the same behavior in this regard that you used to have with Btrieve, then you either need to switch back to Btrieve, or develop your own Duplicate Index error handler to override the default behavior.

Steve Blank

On 8/30/2019 1:32 PM, buster reinke via Groups.Io wrote:
Hi Govert, I am using the default Database SQLite. I have seen the line "This property is only relevant for ISAM databases", but I don't see how that helps me. I was using Btrieve with UniPaaS, but I don't have this option with the single user version of XPA.

Buster

On Saturday, 31 August 2019, 12:51:33 am AEST, Govert Schipper <govertschipper@...> wrote:


Hi Buster,

Are you using Pervasive or SQL?
In the help for this database property, you will find the following line:

This property is only relevant for ISAM databases.


So just for Btrieve/Pervasive, not for SQL databases. I don't think that has changed since uniPaaS.

Govert


Van: main@magicu-l.groups.io <main@magicu-l.groups.io> namens buster reinke via Groups.Io <buster_png@...>
Verzonden: vrijdag 30 augustus 2019 16:34
Aan: main@magicu-l.groups.io <main@magicu-l.groups.io>
Onderwerp: [magicu-l] Check Index Query
 
Hi all, in UniPaaS, I was able to tick the Check Index box in the Database Properties, which was good for automatically detecting a duplicate Index violation and it instantly warned the user at the point of the violation. In XPA 3.2, this feature is disabled so the user gets no warning and is allowed to continue creating the record to the very end. Once finished, if it turns out to be a duplicate, only then do I find out. XPA just bombs out the transaction if in Create Mode, I don't just get a warning and have a chance to fix it, I lose the whole record and have to start again. Is there a way to get any warning that you are trying to create a duplicate before you finish, or at least get a chance to fix it instead of getting booted out and having to start again?

Thanks,

Buster

Virusvrij. www.avast.com


Wes Hein
 

Buster,

Put a Link Query to the main table using the "Part Number" and "Serial Number" (hopefully have an index on that pair) with a return code.  You can then trigger a Verification error if necessary and not let the cursor past the Serial Number.

Wes


buster reinke
 

Perfect Wes, works beautifully. Thanks a lot.

Buster

On Saturday, 31 August 2019, 2:52:34 pm AEST, Wes Hein <wes@...> wrote:


Buster,

Put a Link Query to the main table using the "Part Number" and "Serial Number" (hopefully have an index on that pair) with a return code.  You can then trigger a Verification error if necessary and not let the cursor past the Serial Number.

Wes