your problem is caused by NOT NULL statement at your table column, and your stored procedure make update to your table, so if you make some update or insert to a column with not null statement, and your value is null then Firebird force an error constraint. sorry for my english :))

----- Original Message -----
From: Anton Malinovskiy
To: firebird-support@yahoogroups.com
Sent: Monday, December 06, 2004 11:36 AM
Subject: [firebird-support] A problem with validation constraint



Hello,

I have very simple insert storing procedure. But it fails with very strange
error. The text of the error:



The insert failed because a column definition includes a validation
constraints. Validation error for column EMAIL, value "*** null ***"



The column definition doesn't include any constraints at all. I've searched
the answer, but I've found only that I am not the only one, who have such
problem.





The code of the stored procedure:



>>>>>BEGIN

>>>>> IF ((:email IS NOT NULL)

>>>>> AND (EXISTS (SELECT ID FROM MG_User WHERE Email = :email))) THEN

>>>>> BEGIN

>>>>> resultCode = 2;

>>>>> END

>>>>>ELSE

>>>>> BEGIN

>>>>> IF (EXISTS (SELECT ID FROM MG_User WHERE ID = :ID)) THEN

>>>>> BEGIN

>>>>>

>>>>> SELECT RootDirectoryID, Email, FirstName, LastName,

>>>>> Company, Street, City, Country, State, Zip, Phone, Fax

>>>>> FROM MG_User

>>>>> WHERE ID = :ID

>>>>> INTO :tempRootDirectoryID, :tempEmail, :tempFirstName,
:tempLastName,

>>>>> :tempCompany, :tempStreet, :tempCity, :tempCountry,
:tempState,

>>>>> :tempZip, :tempPhone, :tempFax;

>>>>>

>>>>> IF (:rootdirectoryID IS NULL OR :rootdirectoryID = 0 ) THEN

>>>>> rootdirectoryID = RootDirectoryID;

>>>>> IF (:email IS NULL) THEN

>>>>> email = email;

>>>>> IF (:firstname IS NULL) THEN

>>>>> firstname = FirstName;

>>>>> IF (:lastname IS NULL) THEN

>>>>> lastname = LastName;

>>>>> IF (:logontime IS NULL) THEN

>>>>> logontime = LogonTime;

>>>>> IF (:logoncount IS NULL) THEN

>>>>> logoncount = LogonCount;

>>>>> IF (:company IS NULL) THEN

>>>>> company = Company;

>>>>> IF (:street IS NULL) THEN

>>>>> street = Street;

>>>>> IF (:city IS NULL) THEN

>>>>> city = City;

>>>>> IF (:country IS NULL) THEN

>>>>> country = Country;

>>>>> IF (:state IS NULL) THEN

>>>>> state = State;

>>>>> IF (:zip IS NULL) THEN

>>>>> zip = Zip;

>>>>> IF (:phone IS NULL) THEN

>>>>> phone = Phone;

>>>>> IF (:fax IS NULL) THEN

>>>>> fax = Fax;



>>>>> UPDATE MG_User

>>>>> SET RootDirectoryID = :rootdirectoryID, FirstName =
:firstname,

>>>>> Email = :email, LastName = :lastname,

>>>>> LogonTime= :logontime, LogonCount=:logoncount,

>>>>> Company = :company, Street = :street,

>>>>> City = :city, Country = :country, State = :state,

>>>>> Zip = :zip, Phone = :phone, Fax = :fax

>>>>> WHERE ID = :ID;



>>>>> resultCode = 4;

>>>>> END

>>>>> ELSE

>>>>> BEGIN

>>>>> resultCode = 3;

>>>>> END

>>>>> END

>>>>> SUSPEND;

>>>>>END



Best regards, Anton Malinovskiy





[Non-text portions of this message have been removed]





Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]