Forum OpenACS Q&A: Re: ANN: acs-kernel port to MS-SQL Server

Posted by leigh silvester on
Just come back to this after having been diverted to other things.
Been playing with your SQL so that it parses neatly with no errors or warnings.

One bit I am having trouble with during the initial "installation" phase is in procedure acs_rel_type__create_type.

It has a line...
insert into acs_rel_types ( rel_type, object_type_one, role_one, min_n_rels_one, max_n_rels_one, object_type_two, role_two, min_n_rels_two, max_n_rels_two ) values ( @create_type__rel_type , @create_type__object_type_one , @create_type__role_one , @create_type__min_n_rels_one , @create_type__max_n_rels_one , @create_type__object_type_two , @create_type__role_two , @create_type__min_n_rels_two , @create_type__max_n_rels_two )

When this is called via acs_rel_type__create_type in the procedure inline_0 in acs-relationships-create.sql section it causes via a temporary procedure I fabricated to replace something that appears to have been missed in your translation...

@ms_return_value integer = 0 OUTPUT
DECLARE @attr_id int
EXEC dbo.obs_rel_type__create_type
'relationship', --@create_type__rel_type
'Relationship', --@create_type__pretty_name
'Relationships', --@create_type__pretty_plural
'obs_object', --@create_type__supertype
'obs_rels', --@create_type__table_name
'rel_id', --@create_type__id_column
'obs_rel', --@create_type__package_name
'obs_rel_types', --@create_type__object_type_one
'obs_object', --@create_type__role_one
0, --@create_type__min_n_rels
null, --@create_type__max_n_rels_one
'obs_object', --@create_type__object_type_two
'', --@create_type__role_two
0, --@create_type__min_n_rels_two
null, --@create_type__max_n_rels_two
@ms_return_value = @attr_id OUTPUT
END -- stored proc

... I get the error:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'acs_rel_types_obj_type_1_fk'.
The conflict occurred in database 'orgbase', table 'acs_object_types', column 'object_type'.

The problem seems to be with the foreign keys for table acs_rel_types which are conflicated when the relationship type is added?

Any thought anyone?

I am nearly at a stage where I can post the extended code from John's original somewhere.
Is any one interested in this?