The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Updating the database directly

SOLVED
Go to solution
Highlighted
Peter Dent
Frequent Visitor

Updating the database directly

I've been asked to look into the possiblity of updating the Person record directly, via an sql statement, not through the client.

I realise that this is not recomended and that you cannot create relationships this way. However, the field to be updated is the Person Leave Date.

This can be done and doesn't seem to cause any problems but, I have defined a database rule to send an email when the Leave date is entered.

Updating the database via SQL does not run the rule.

Is it possible to trigger the database rule associated with the field, when its updated via SQL?

(SD 4.5, SP17)
9 REPLIES
Ruth Porter
Honored Contributor

Re: Updating the database directly

Hi Peter,

I would recommed using sd_event to update the record. This would be "official" and would trigger rules.

to use sd_event see chapter 5 of the Data exchange manual; in essesnce you need to set up an import mapping and refer to that in the command.

If you need more help on using sd_event search for it or just ask.

Hope this helps

Ruth
http://www.teamultra.net
George M. Meneg
Honored Contributor

Re: Updating the database directly

Hello Peter,

There is no way to trigger the db rule if the update is done at DB level. Actually the only thing that can be triggered when update at db level is a db trigger but I doubt that that's you want.

Instead of updating at DB level why don't you use data exchange? Updating through data exchange ensures that the db rules are triggered.

The procedure ought to be quite simple, the most difficult part is to establish a common "field" between OVSD and probably your HR software (or wherever the persons and there leave dates are stored) to use this as a unique key.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Updating the database directly

OK, I guess I need to look at how sd_event works?

What do I need to install to be able to test sd_event?
Ruth Porter
Honored Contributor

Re: Updating the database directly

Hi Peter,

To use sd_event, you need to install the integrations option on the install menu.

Then to see the parameters, try sd_event /?

But basically, you need to set up an import mapping on your person record, choosing 1 field as the unique key and then refer to that mapping in the commend line of config file you use.

I do have some samples but they are on my laptop and at this site I cannot put that on the network so I will send them when I next get a chance unless someone else can help sooner.

Ruth
http://www.teamultra.net
George M. Meneg
Honored Contributor

Re: Updating the database directly

Hello again,

In your case I think that Data exchange should be easier than sd_event. Further more the data exchange guide has a through example of how to import data from an excel file.

My proposal is to create an excel with the persons and the leave dates (one per person) and use data exchange to import from excel.

Have a good read of data exchange guide, found on the doc\ folder on the installation CD. There you can find a through example of importing persons and their relations, your job is much easier since you haven't to import any relation. This guide is a pretty good starting point.
menes fhtagn
Peter Dent
Frequent Visitor

Re: Updating the database directly

Thanks to you both. Both solutions sound workable.

I'm going to investigate the use of Data Exchange, but only because I am much more familar with that process. I've used it several times in the past and am confident of getting it to work.

I would like to know more about the use of sd_event however?

Is the integrations option installed on the client machine or on the HPOV App. Server?
Ruth Porter
Honored Contributor
Solution

Re: Updating the database directly

Hi Peter,

The integrations option should be installed on the box on which it will be used.

As regards the use of sd_event or data exchange, I would point out that there is some overlap.

You will need to setup an import mappring for either method. If we say the import mapping is called MAP and the class is called CLASS with mappings say such as:

PERSON_NAME - your unique key
PLD - for Person Leaving date

then to use sd_event set up a config file with contents like:

[SD_EVENT]
LOGFILE=
ERROR_LOGIFLE=
ACCOUNT=useranme/password (of an integration account)
SERVER=your application server
PORT=port number (30980 by default I think)
MAPPING=MAP
CLASS=CLASS
MODUS=UPDATE

Then to use this file and mapping, the sd_event command would look like:

sd_event -f -v PERSON_NAME= PLD=

You will need to be careful with date format

Good luck & hope this helps

Ruth



http://www.teamultra.net
Peter Dent
Frequent Visitor

Re: Updating the database directly

Ruth,

Thanks for that, very nice explanation and example.

I'm still a little usure about where to install the integration options?

When you say "on the box that it is going to be used on", do you mean the box from which you will be sending the sd_event command?
Mark O'Loughlin
Honored Contributor

Re: Updating the database directly

Hi Peter,

yes, from the install menu choose the "integrations" option and is will install a folder on the machine that you want to run sd_event from with the basic but necessary files.
//Add this to "OnDomLoad" event