Inserting email details via script
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)
VALUES
(0, 194890, 'T', 'mailto:12312312@bradfordcollege.ac.uk', 'Local')
The script will not run and kicks off the folowing error message:
The column CONTACT_ID in table CONTACT does not allow NULL values.
We assumed that the table would auto-generate a new CONTACT_ID but that doesn't seem to be the case.
Any feedback would be gratefully received
All the best
Rich
Bradford College
CONTACT table
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
Cheers Brian... so not as
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
adding CONTACT table rows
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
example SQL for CONTACT table
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
Cheers Brian
Thank you ever-so much Brian.
Looks like just the thing we need to get us started.
Wonderful!
Rich