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