SQL Function for SX Enterprise??

[Auriferous]

I was wondering if anyone could tell me what the possibility of creating a function that would fire every time a new product is created in PUB.icsp that would create a note attached to the product. The idea is to insert a note with a URL such as "http://www.mydomain/eternaldb/product.cfm?prod=XXXX" with the X being the product sku. Any Ideas of how to accomplish this in progress / openedge 10?

Thanks,

Bryan

 

[doom1701]

I should note that I'm not a Progress developer, but I know enough to be dangerous. Seems like this might be better handled either with a database trigger of some sort, or just a quick and dirty to run on a regular basis looking for product records that don't have a note attached yet. I believe you could just look for any record with a icsp.notesfl of no.

ODBC could do this, but I'd recommend just doing it directly in Progress. It's quite simple, and considering the level of activity that the SXE portion of this forum sees (ie, none), it might give us SX users something to talk about.

 

[Auriferous]

Ya a db trigger probably would work the best. I'm not sure about how to go about this though and was reading that if things aren't done right it could overide existing database logic.

Also rather than use the note field I think I am going to use the "webpage" field in icsp and point it to a php script. It would still be nice to have the url automatically put in when the product is saved... and even better would be with a query string with the prod..
http://192.168.136.55/update/product.php?prod=mysku-101

I can add all the fields and logic I want this way using html and connecting via the built-in web browser in SX.

I thought perhaps at least I could put in the basic url as a default value for the webpage field... this would trigger a script to update all new products and update the webpage urls. then the user could refresh the page to access the form. I tried to change the default value in the sys tables but was having problems with privaleges.. not allowing me to do so :/

If you have any ideas of how to proceed or could assist me with writing a db trigger i'm all ears!

-Bryan

 

[doom1701]

I actually know almost nothing about triggers, so most of what we do is just done by nightly report jobs. To do what you're talking about, this should work. First, create a file in your /rd/cust folder on your database server and name it appropriately--but keep the name at 8 characters or less and end it with ".p".

Then, put the following code in the file (and Progress professionals, please forgive me):

for each icsp where icsp.cono = 1 and icsp.webpage = "":
assign icsp.webpage = "[URL]http://192.168.136.55/update/product.php?prod=[/URL]" + icsp.prod.
end.

Finally, go into SARR in SX. SARR lets you run a progress script on a schedule. Do a New One Time, setup your schedule and output (automated scripts should usually be output to a file, but you can print or view if you'd like--you won't see much). On the next screen, enter the filename (without the .p). On the last screen name the task (I usually use the same name as the progress script file, but this can be anything).

icsp.webpage isn't an indexed field, so I'm not sure how long this very basic "For" loop will take; if you find that it runs quickly, you can schedule it multiple times per day. Otherwise you can just run it off hours.

 

[TomBascom]

Run time will depend on the amount of data in the table. Without an index to rely on you'll be looking at every record every time you run it. That could be painful.

There's no reason you couldn't add an index on cono and webpage though. That would speed it up nicely.

A more natural way to do it would be to add a trigger. You just need to make sure that there aren't any pre-existing triggers. If there are then you would need to add your logic to those triggers (I've no idea if SXE allows this). If there is no existing trigger then just create one. From the description you probably want a CREATE trigger. Gory details, like what to name it and where to put it, are up to you (or your DBA). SXE may have some standards or suggestions to follow, I don't know offhand. There are example triggers in the $DLC/sports directory. The following is a CREATE trigger for the Customer table:

TRIGGER PROCEDURE FOR Create OF Customer.

/* Automatically Increment Customer Number using Next-Cust-Num Sequence */

ASSIGN Customer.Cust-Num = NEXT-VALUE(Next-Cust-Num).

You make it active by going to the data dictionary, choose Schema -> Modify Table (pick your table) -> Triggers and entering the path & filename.