Talis Developer Network - Inserting email details via script - Comments

Cheers Brian

richardl — Tue, 28 Nov 2006 10:27:50 +0000

Thank you ever-so much Brian.
Looks like just the thing we need to get us started.

Wonderful!
Rich

example SQL for CONTACT table

Brian Crampton — Tue, 21 Nov 2006 16:03:47 +0000

As an example the following SQL would insert a row in the CONTACT table. However this is in no way a script that would work as written for what you want but it does give a starting point.

It is just for a specified borrower id and reports the CONTACT_ID used for checking in the db. As I said in my earlier posting there are a lot more questions that would need to be considered for a robust script. Another check would be has a row already been inserted. This script would just keep inserting a row for a borrower everytime it was run which would not be a godd idea.

As always run this on your MIS server and not your live system. The script returns the CONTACT_ID so you can check the inserted row in the CONTACT table.
isql -Usa -P<<-END


use prod_talis

go
	declare @contact_id ID,

		@borrower_id ID,

		@address OBJNAME
	select @borrower_id = 11111
	select @address = "mailto:" + REGISTRATION_NUMBER + "@bradfordcollege.ac.uk"

	from BORROWER

	where BORROWER_ID = @borrower_id
        begin tran
        update  CONTACT_RID

        set CONTACT_ID = CONTACT_ID + 1
        select @contact_id = CONTACT_RID.CONTACT_ID

        from CONTACT_RID
        insert  into CONTACT

                (CONTACT_ID        ,

                 CONTACT_TYPE      ,

                 TARGET_ID         ,

                 TARGET_TYPE       ,

                 PREFERRED         ,

                 DISPLAY_VALUE     ,

                 CONTACT_NAME      ,

                 START_DATE        ,

                 END_DATE          ,

                 NOTE

                )

        values

                (@contact_id,

                 0,

                 @borrower_id,

                 0,

                 'T',

                 @address,

                 'Email',

                 'Jan 1 1970',

                 'Jan 1 1970',

                 ''

                )
        if @@rowcount != 1

        begin

                select 0

                raiserror 20001 'insert failed'

        end
        else

        begin

                select @contact_id

        end
        commit tran

go END

Brian Crampton
Developer, Talis

adding CONTACT table rows

Brian Crampton — Tue, 21 Nov 2006 15:21:15 +0000

The CONTACT_RID update is probably the easier part of a script.

The logic would be

update CONTACT_RID
set CONTACT_ID = CONTACT_ID + 1

select CONTACT_RID.CONTACT_ID
from CONTACT_RID

Although the challenging side is creating the email address. I assume that the college id is taken from the data in the ID field which is held in the BORROWER.REGISTRATION_NUMBER attribute.

Concatening this between the "mailto:" and "@bradfordcollege.ac.uk" is straightforward but the problem will be if you want any validation or checking of borrowers. At its most basic this is checking if there is a value at all for a borrower in the REGISTRATION_NUMBER attribute but it could be more complicated if you want to do more checking e.g. where there is a number of characters or that the id data starts with a particular string. To a large extent it depends on the data in your database.

If you don't do any checks then you run the risk of "dodgy" emails being entered. As with most scripts the complex bit is the checks that make sure something unexpected doesn't happen.

Brian Crampton
Developer, Talis

Cheers Brian... so not as

richardl — Mon, 20 Nov 2006 11:00:04 +0000

Cheers Brian... so not as easy as we might have hoped then (curses).
I don't suppose anyone can offer any ideas can they? Essentially we need to update two tables then - the CONTACT_RID table (which just seems to be a container for CONTACT_ID) and then the rest of the details in the CONTACT table.
sounds messy!

Any thoughts gratfully received!
Rich

CONTACT table

Brian Crampton — Fri, 10 Nov 2006 15:42:51 +0000

The CONTACT table contains the attribute CONTACT_ID which is the unique identifer for each row and so cannot be NULL which it would be in your SQL as it does not specify the value.

The CONTACT_ID is taken from the CONTACT_RID table. The value is incremented and then selected for use.

Any process for inserting records in the CONTACT table needs to take account of the CONTACT_RID table. If you don't you will get into issues with the on-line as well.

Thanks

Brian Crampton
Developer, Talis

Inserting email details via script

richardl — Fri, 10 Nov 2006 14:49:27 +0000

Hi - apologies for the long post.....

This year we were hoping to import borrower email addresses with the normal import script. I understand that there is a problem with this script so we are trying to roll our own solution. Student email addresses this year take the form of COLLEGE_ID_NUMBER@bradfordcollege.ac.uk so we assumed that it would be a fairly painless process to cobble together a script which would fill in the email details based on the student ID.
The nub of the script performs the following sql
INSERT into CONTACT
(CONTACT_TYPE, TARGET_ID, TARGET_TYPE, PREFERRED, DISPLAY_VALUE, CONTACT_NAME)