[ic] Detecting duplicate values in a database

[interchange-users@icdevgroup.org (Michael)]

Hi folks...

I have run into a bit of a problem using a form to update a database.

I have one column in userdb that really needs to be a unique value on 
a record by record basis.  However it will not really be used as a key - it
simply cannot have duplicates.

The gear is:  IC ver 4.8.2 (a bit old), MySQL ver 3.23.36, Apache 1.3.22 on
RedHat 7.1.  (not production gear)

Here is the mv code:  (bare bones)
----------------------------------------------------
[set mv_data_enable]1[/set]

[set valid_form]
&success=main1
&fail=@@MV_PAGE@@
ref_num=unique userdb "Value in use - Select another"
&fatal=1
[/set]

<FORM ACTION="[process secure=1]" METHOD=POST>
<INPUT TYPE=HIDDEN NAME=mv_session_id VALUE=[data session id]>
<INPUT TYPE=HIDDEN NAME=mv_todo VALUE=set>
<INPUT TYPE=HIDDEN NAME=mv_nextpage VALUE=main1>
<INPUT TYPE=HIDDEN NAME=mv_failpage VALUE=@@MV_PAGE@@>
<INPUT TYPE=HIDDEN NAME=mv_data_table VALUE=userdb>
<INPUT TYPE=HIDDEN NAME=mv_data_key VALUE=username>
<INPUT TYPE=HIDDEN NAME=mv_data_fields VALUE="ref_num">
<INPUT TYPE=HIDDEN NAME=mv_data_function VALUE="update">
<INPUT TYPE=HIDDEN NAME=username VALUE=[scratch Target_username]>
<INPUT TYPE=HIDDEN NAME=mv_form_profile VALUE=valid_form>

<INPUT TYPE="text" NAME=ref_num VALUE=[value ref_num]>

<INPUT TYPE=SUBMIT NAME=submit VALUE="SUBMIT">
</FORM>
-------------------------------------------------------
Note that an admin type user will be modifying this field
for other users.

If the database definition is the following:
     Database  userdb  COLUMN_DEF   "ref_num=VARCHAR(30)"
     Database  userdb  UNIQUE       ref_num

I get the following error string when submitting a duplicate value
in the ref_num column:

"Sorry, there was an error in processing this form action. 
Please report the error or try again later. 
(DBD::mysql::st execute failed: Duplicate entry 'A1' for key 1 at 
/path/to/interchange/lib/Vend/Table/DBI.pm line 936. )"

(not unreasonable... but I sure would like to catch the error.)

If the database definition is the following:
     Database  userdb  COLUMN_DEF   "ref_num=VARCHAR(30)"
then I get duplicate values in the ref_num column.

All other database access works just great...

I would like, if a duplicate entry is found, return to the same page
with the error flagged, much like the 'foo=required' pragma works in other
profiles.

Thanks in advance.

Michael Slack