Fuego.Sql.DynamicSQL - executeQuery - Dynamic SQL

I ran across a stituation where I had serveral different queries that I needed to write depending on if my data was populated. It could be a number of combinations, so I didn't want to use the typcial INSERT SQL statement. I needed it to be more dynamic.

Here is what I can up with:

Before starting here are some key points to remember:
ALBPM Strips off any special characters, so you need to know if you table or columns have the special characters in them before starting. For example in BPM when I look at my tables I see ACF2PROF - however my actual table name is ACF2_PROF
Also note - everything on the left hand side is an attribute I created in BPM - everything on right hand side is what I am getting from the database.


myDynamic as Fuego.Sql.DynamicSQL
myDynamic = DynamicSQL()
params as String[]
sqlCommand as String
index as Integer
index = 0
params
params[0] = String(aCF2Management.aCF2Profile.dbKey)
sqlCommand = "select * from ACF2_PROF where acf2_Prof_Id LIKE ?"

for each row in executeQuery(myDynamic, sentence : sqlCommand, implname : "External Resource Name for Database", inParameters : params) do

aCF2Management.aCF2ProfileResults[index].profiles.profDescipt = String(row["PROF_DESC"])
aCF2Management.aCF2ProfileResults[index].profiles.idModel = String(row["ID_MODEL"])
aCF2Management.aCF2ProfileResults[index].profiles.numberIds = Decimal(row["ID_QTY"])

index = index + 1
end

More help on this can be found on BEA / Oracle's website at http://download.oracle.com/docs/cd/E13154_01/bpm/docs65/standard_components/index.html?t=Fuego/Sql/DynamicSQL/DynamicSQL_component.html

Array of Links in a Repeatable Section

I needed to repeat text and links within an array. So I created a group with the following:

requestGrp
Label
Link

However when I created a repeatable section and selected requestGrp.Label as my Label and requestGrp.Link as my Link.....

I assigned it as following:

requestGrp[0].Label = "cnn"
requestGrp[0].Link = "www.cnn.com"
requestGrp[1].Label = "fox news"
requestGrp[1].Link "foxnews.com"



...my text box worked as it should. However the link took the last one in the array and repeated it. Darn!

Database Insert / Update Statement Made Easy

I was having issues with the fact that I couldn't do a dynamic SQL insert on the table. So after discussing with other people I work with we were able to figure this out. Thanks Harsha for following through on this one!

auditTable as Database.AUTOPROMO.SRVR_ACCS_REQ_AUDIT
auditTable = Database.AUTOPROMO.SRVR_ACCS_REQ_AUDIT()

auditTable.srvrAccsReqAuditId = 1
auditTable.accsReqorCidId = "req-cid"
auditTable.accsReqorFullNme = "req-name"
auditTable.clntCidId = "client-cid"
auditTable.clntEqId = "client-eq-id"
auditTable.recCreatTmst = 'now'
auditTable.recCreatUserid = "hxs5303"
//add rest of the columns later.....
store auditTable

clearing out an array

This seems easy and it is, but I always seem to forget it. Instead of having to recall it out of memory I thought I would add it to the blog. It's super simple code, so here it goes.

Quick Explanation:I populate an array (or group) and now I want to repopulate that array. Here's the problem. If the first time I fill up 92 elements and then I try to repopulate it with 61 I still have 31 of my old arrays populated. The trick is to go through and delete out the array prior to repopulating it.

Simple as this:

while 0 < this.arrayName.count do
delete this.arrayName[this.arrayName.count-1]
end

Administrator Guide

http://e-docs.bea.com/albsi/docs60/pdf/ALBPM60_Process_Administrator.pdf

Array Arrow Images are Missing....

The oddest thing when you are in studio or enterprise is that I started noticing that when I used the pagination on an array the arrows to know when to go forward a page or backwards a page was gone! I was hoping it was just a studio problem, but unfortunately it was also an Enterprise problem as well.

Here is the info I was given to fix it:

It is replacing two lines in a properties file.

\enterprise\webapps\workspace\WEB-INF\classes\XObject.properties

Replace this lineweb.img.next_paging_dis.gif=next_paging_dis.gif
for this lineweb.img.next_paging_dis.jpg=next_paging_dis.jpg
and replace this lineweb.img.prev_paging_dis.gif=prev_paging_dis.gif
for this lineweb.img.prev_paging_dis.jpg=prev_paging_dis.jpg

After updating the XObject.properties restart the web application to see the changes.

Also, just so you know exactly what I am talking about. This issue is when you have a presentation with an array in it. If you take the row from -1 to a greater number let's say 5 then this causes it to use pagination that will only show 5 records on each page. You have to hit the arrow button to flip through them. User beware.... Once you move out of -1 you are unable to move it back to -1 again. Basically you have to start by creating the array all over.

Make Popups Wider

I hated how when you log in and select an application - the popup screen was teeny tiny. So I emailed someone at BEA and here's how they were able to help me solve it. Note: make sure your web applications isn't running when you make this change or it won't allow you too.

Using a text editor (like Notepad and not Word) open the workspace.properties file in the C:\bea\albpm6.0\studio\webapps\workspace\WEB-INF directory.
Change these settings to:
fuego.workspace.executionDialog.width=700
fuego.workspace.executionDialog.height=600

studio help

here is the link for studio help:

http://e-docs.bea.com/albsi/docs60/studio/index_raw.html

so you want all the emails in a particular role in your process?

no big deal. here's how you do it:

i set mine up in a method/function so i can reuse it. i return out a string (all of my email addresses). you could do this if you just want a list of names in a role to populate a list box or something. here i use it to email all the people in this role:


manager as String
myRole as Role
myRole = Role.find(name : "Manager")
for each element in myRole.participants do
if element.email = "" or element.email = null then
else
manager = element.email + ", " + manager
end
end
// take off last semicolon or comma
emailLength as Integer
emailLength = length(manager)
emailLength = emailLength - 2
manager= strip(manager, len : emailLength)
return manager

change info on inbox - description...

if you want to change the description in your inbox, just do this:

description = "whatever you want"

usually, i try to put the ticket number in it doing this:

description = "ticket number: " + id.number

also, remember to do this in an automatic box on the process.

BEA BPM Enterprise 6.0 - creating an excel and attaching it to the instance/ticket

this one was tricky! it took me several tries to finally figure out what was going on. first attempt i tried loading the excel com service. and i wrote some code to create the file (just search for excel examples on bea.com's website - they have several) and attach it (using the simple Attachment.create...). however here is the problem. just b/c it works on studio, doesn't mean it will work on enterprise. when using studio your com bridge is usually automatically started. on enterprise you have to go to the server that is running bpm and make sure the com bridge is a service that is automatically running and make sure that the port for the com bridge is open to use. (make sure no firewalls are blocking it). i was able to get that far and have the excel document be created, but for some reason it wouldn't close the file! i had close worksheets, close worksheet, and quit application in my code and it wouldn't work. it left the process running. so after several attempts, i decided to take a different direction. here's what i did to make it work:

i went about it a completly different way. i simple wrote a text file using Fuego.Io.textFile. here's an example:

stText as String
textFile as Fuego.Io.TextFile
excelFileName = "C:\\niki.csv"
excelFile = "niki.csv"
myFile as Fuego.Io.File
textFile = Fuego.Io.TextFile()
result2 as Bool

logMessage "entering create requestor text"
openForWriting(textFile, name : excelFileName, append : false, lineSeparator : "\n")
stText = "Niki's file"
result2 = writeLineTo(textFile, line : stText)
close textFile

simple, right? then i just needed to attach it. here's where i ran into problems. i couldn't figure out why it would show up as a csv file sometimes and then other times it would come up as document.htm. luckily i currently work with some smart people who pointed me in the right direction. document.htm means that the file size has grown so large it didn't read the whole file. so it would work if the file was less than 512 and if it was larger i would get the document.htm result. so i started researching mime types. after using google, if found a website that gave the different types of mime files to use with csv. after several attempts i figured out that application/csv was the one i needed to get my attachments to work correctly. here's the attachement info that worked:

if not excelFileName is null and excelFileName != "" then
binaryFileContents = BinaryFile.readToBinaryFrom(name : excelFileName)
Attachment.create(contents : binaryFileContents,
contentType : "application/csv", name : excelFile, description : "Closed Ticket Summary",
remarks : "Ticket is Closed")
end

note that the excelFileName is the path and filename. now when the user is in their inbox they can view an attachment and it will open in excel. it's a comma delimited file, so where ever you are needing the information in the file to show up in the next column - just put a comma - and magically it will be in the next column

button names.....

so typically we use 3 buttons - previous, next (submit), cancel. did you know that as long as your conditional name and the name of your button matches you don't have to do anything else???? cool, huh? just a simple tip and trick.

BEA BPM studio - can't see anything in log viewer

i like to call this one an easter egg. it's the little things that can make you frustrated and it took me a second to figure out the trend on this one. all you have to do is hit the refresh button in the log viewer and then click on an active window (like for example where your code is at) and click back into the log viewer. now you should see it! if it doesn't work on yours ... sorry - no guarantees, it works on mine!

introduction

why am i writing this blog? for a few reasons..... i figured that i have been through so many struggles and i wanted to share my experiences. plus i have a horrible memory so anyway to have something written down and organized is awesome. hopefully i will remember to update my blog as i figure out not only the complex things for BEA's BPM, but the simple tricks to get the work done. so many times i have gone to google and tried to find out how to do something with little success or even opened up tickets with BEA and got nowhere.