uniqueness test problem on active link mysql

[kitship]

Please help,

Can alpha five perform a uniqueness test on an active link mysql table? If yes how do you make it work? been trying to do this but alpha five would still accept duplicate value in a field even though uniqueness test is active.

[GGandhi]

Re: uniqueness test problem on active link mysql

I do not work with active link tables or desktop so I may not be able to direct you to right method.
but as I said earlier you can ask MySQL to check for uniqueness by defining an index and make the index unique and MySQL will do the checking
for you.

[GGandhi]

Re: uniqueness test problem on active link mysql

follow up1
looks like you don't need field rules and mysql index, after all.
you can write a xbasic routine and display a message warning of the presence of a duplicate value.

[cmshore]

Re: uniqueness test problem on active link mysql

Got the same problem. Can you send the code for the Xbasic?

[Ted Giles]

Re: uniqueness test problem on active link mysql

Have you thought of making the unique field a Primary Key Field in your SQL Table?
Then you get the error message if you try and duplicate.
Or did you want something different? Like MUST NOT EXIST in the Index?

Just tested this some more. The Surrogate Pk created by Alpha doesn't prevent a duplicate even if the Field Rules say that the value should be unique.
It looks as though you will have to rely on the Server side to trap the error.
Using cross file validation doesn't work either.

[GGandhi]

Re: uniqueness test problem on active link mysql

hello

here is one way of doing
http://screencast.com/t/wTpRrmjW9

dim conn as sql::Connection
dim args as sql::Arguments
dim sqlSelect as c
dim rs as sql::ResultSet
dim result as l
dim flag as l
sqlSelect = "Select * FROM alpha_table1 WHERE string1 = :newString1"
dim name1 as c
name1 = topparent.string1.value
result = conn.open("::Name::local_MYSQL")
if result then
	args.add("newString1",name1)
	flag = conn.Execute(sqlSelect, args)
	if flag then
		rs = conn.ResultSet
		flag = rs.nextRow()
		if flag = .t. then
			ui_msg_box("Duplicate","The name is already on file",UI_ATTENTION_SYMBOL)
			conn.Close()
			'here you can write more code if you want to do something with that situation
		end if
	end if
	conn.Close()
	
end if

no field rules declared in the active link table
no mysql index created on that field
since i do not work with desktop i do not know how to write some more code to do few things like resending the focus to the same string1 field.

[Ted Giles]

Re: uniqueness test problem on active link mysql

Nice catch Govindan. Now all we need is the person who started this post to join in.

[cmshore]

Re: uniqueness test problem on active link mysql

Exactly what I needed Govindan, thanks.