Is it possible to open a database connection (to an external d.b., i.e not the Rhythmyx d.b.) from the velocity template during a publish? I figure that, in order to do so I’d need to write java (which actually opens the connection, runs sql etc), and call this java from velocity passing in some parameters.
Presumably this is possible, but where would I need to put the java, would it be a case of extending a class as described in Rhymyx docs, or would I need only build a class/jar and put it in a classpath in Rhythmyx somewhere?
For the benefit of anyone who might be in the same position, I’ll briefly go through what I did.
A “datasource” needs to be set up in Rhythmyx with the connection details of the external database. I tried this as described in the Implementation Guide doc under “Creating a Datasource for Your Target Database”, but I had some trouble with this method. I tried again directly through the server administrator as described in the Technical Guide doc, and this worked once I’d made a couple of manual changes to Rhythmyx\AppServer\server\rx\deploy\rx-ds.xml (adding in username/password and removing the for security domain). Note: it’s worth taking a back up of this file before making changes through the server admin, which can then be restored if you find the system won’t come back up again when bounced after any of the above modifications.
Having set up the datasource, it should now be possible to call $rx.db.get() passing the name of the new datasource as one of the parameters. The method get() expects a return value from the sql, I however needed to make some inserts, so it would only work by appending a select as follows:
$rx.db.get(“newDatasource”,“insert into xxx VALUES('xxx);select 1”)
I’m using this sort of dynamic sql for dev, but am hoping to use procs, although I haven’t tried this yet so can’t say for sure that they can be called from the $rx.db.get().
Anyway, as before, I appreciate the suggestion below, there may be better ways of approaching this other than I’ve outlined above. If so feel free to comment, although I am quite happy that this seems to be working.
When I followed those steps, it seemed fine when running the “test” web page. However, the problem began when I tried a preview of the item. I’m not sure what the problem was, probably something wrong in the way I’d set the datasource up, as I tried a different approach and got it working.