Thank you Charlie. While security is always an issue, I'm not sure that
allowing users to connect to the database as datasource for MS Word mail-merges
is much more secure. Anyhoo, here's my proof of concept.
First, I created a table to store the list of fieldnames available for users to
incorporate into their letters. I will be the one populating this table to
reduce some security concerns.
create table mso.letter_fields
(
id number primary key,
otc varchar2(128), -- this is an extra column for now OwnerTableColumn
pk varchar2(128), -- also extra, indicates primary key column for OTC
cname varchar2(128), -- common user-friendly name
cfcode varchar2(128) -- the #code# that will be evaluated
);
Next, a sample row.
insert into mso.letter_fields values (mso.seq.nextval, 'MSO.STAFF.NAMEFIRST||''
''||MSO.STAFF.NAMELAST||'', ''||MSO.DEGREE_DESIGNATION as fullName',
'MSO.STAFF.ID', 'Full Name', '#qStaff.fullName#');
Then, I created the table to store the form letters.
create table mso.letters
(
id number primary key,
created date, -- date document created
owner varchar2(64), -- owner's name
lmod date, -- last modified date
modby varchar2(64), -- last modifier's name
lname varchar2(128), -- name of the form letter
ltxt varchar2(4000) -- text of the form letter
);
Next, insert a sample row.
insert into mso.letters values (mso.seq.nextval, sysdate, 'SYSTEM', sysdate,
'SYSTEM', 'Welcome Aboard','Dear #qStaff.fullName#,<br>Welcome to Athens
Regional Health Services.');
Then I created a cfm file to create sample letters.
<cfquery name="qLetter" datasource="armc_linux">
select * from mso.letters where id=35753
</cfquery>
<cfquery name="qStaff" datasource="armc_linux">
select MSO.STAFF.NAMEFIRST||' '||MSO.STAFF.NAMELAST||',
'||MSO.STAFF.DEGREE_DESIGNATION as fullName from mso.staff where rownum <= 10
</cfquery>
<cfoutput query="qStaff"><p>#evaluate("#de(qLetter.ltxt)#")#</p></cfoutput>
Finally, I ran the page and rendered me some output. Woo-hoo! It's not perfect,
but with some tidying up, it should work nicely.
Dear IMA SAMPLE, MD,
Welcome to Athens Regional Health Services.
Dear SHEEZA SAMPLE, RN,
Welcome to Athens Regional Health Services.
Dear HEEZA TEST, MD,
Welcome to Athens Regional Health Services.
Dear PETER PARKER, ,
Welcome to Athens Regional Health Services.
Dear BRUCE WAYNE, PHD,
Welcome to Athens Regional Health Services.
Dear WALLY WEST, ,
Welcome to Athens Regional Health Services.
Dear JOHN STEWART, GL,
Welcome to Athens Regional Health Services.
Again, thank you all for your input.
hth.
mf
-----Original Message-----
From: ad...@acfug.org [mailto:ad...@acfug.org]on Behalf Of Charlie
Arehart
Sent: Wednesday, July 01, 2009 11:43 AM
To: discussion@acfug.org
Subject: RE: [ACFUG Discuss] NEVERMIND RE: CF in the database?
FWIW, I'll note that while I appreciate the cons of the idea, I also know
that others have seen the pros of it, and the BlueDragon engineers did as
well, and they added a Render function to let you execute CFML code as found
in any variable, whether from a DB query, a shared scope variable, etc.
It would suit your needs, Mark, though obviously if you're on CF it won't
help. Not trying to push anyone to BD or OpenBD.
Just saying that the request has been frequent enough that it's been
addressed by at least one of the engines, and people have asked for it of
CF, too. Naturally, it has all the security implications mentioned by others
here, and that may be why CF still doesn't have it.
For more on the Render function, I just found a blog entry in the OpenBD
blog (though it's existed for years in BD):
http://blog.openbluedragon.org/render.htm
Just sharing info, as your friendly neighborhood CFML librarian. :-)
/charlie
> -----Original Message-----
> From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Mischa
> Uppelschoten
> Sent: Wednesday, July 01, 2009 9:05 AM
> To: Web Site
> Subject: re: [ACFUG Discuss] NEVERMIND RE: CF in the database?
>
> As a last resort I have in the past written out and cfincluded a file
> based on the contents of a database field. Obviously there are some
> security and performance implications, but the ability to have CF
> interpret code (and not just an expression) from a database can
> sometimes come in handy.
>
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
