Sometimes I cannot quite get want I want from Raiser’s Edge using the API. Or if I can it is very slow to get the information. For example when you get a collection of top level records, say constituents, there are filter objects which work well, you can also use the custom where clause which works when there is not a filter parameter. However what do you do when you want a combination of different areas of The Raiser’s Edge all combined into one selection.
I have written custom where clauses that have sub selects that do this. You can write the where clause as:
id in (select constit_id from constituent_bank where account_no = ) & sAccountNo
That works and it will find you the constituent with a certain bank account number. However what if you want constituents who have a bank with a certain zip/post code? Sure you could do a join on the tables but this gets more and more complicated and can impact on performance.
One way around this is to fetch the data directly from the database using ADO. There are advantages and disadvantages to this.
On the downside:
- There may be security concerns in distributing a user name and password (unless you can use Windows authentication).
- If it is not distributed then it will have to be stored in the code which could be problematic when you want to update the password
- There is an added layer of code complexity that you have to managed dealing with ADO connections.
On the upside:
- You will have real flexibility to pull from any table in The Raiser’s Edge and optomise your queries accordingly
There is an alternative to opening your own connection to the database. The
CreateDisconnectedADORecordset method will return a recordset for your you based on a SQL string you enter. The code below show how you would get those constituent that have a bank at a certain zip/post code.
Dim oReservices As New REServices Dim oUtilCode As IBBUtilityCode Dim rs As Recordset Dim sSQL As String Dim oConstit As New CRecord sSQL = "SELECT RECORDS.ID " & _ "FROM CONSTITUENT_BANK INNER JOIN " & _ "BANK ON CONSTITUENT_BANK.BRANCH_ID = BANK.ID INNER JOIN " & _ "RECORDS ON CONSTITUENT_BANK.CONSTIT_ID = RECORDS.ID " & _ "WHERE (BANK.POST_CODE = '94122') " oReservices.Init SessionContext Set oUtilCode = oReservices 'Either with Windows authentication - use your Windows login credentials Set rs = oUtilCode.CreateDisconnectedADORecordset(sSQL) 'Or with SQL Server authentication - use a SQL Server user name and password 'Set rs = oUtilCode.CreateDisconnectedADORecordset(sSQL, "Username", "Password") 'Then once you have your record set you can loop the record set and load the records Do While Not rs.EOF Set oConstit = New CRecord oConstit.Init SessionContext oConstit.Load (rs("ID")) 'Do something with the constituent ProcessConstit oConstit oConstit.CloseDown Set oConstit = Nothing Loop 'and now clean up If Not rs Is Nothing Then If rs.State = ADODB.adStateOpen Then rs.Close End If Set rs = Nothing End If oReservices.CloseDown Set oReservices = Nothing
You will have to add the reference: Microsoft ActiveX Data Objects 2.x Libary (x will vary depending on what you currently have installed on your machine).
There is quite a bit more code here than simply using a where clause. However on large databases and with complex queries the savings are considerable. If you were to do the above in a where clause then you would have to write the following:
sSQL = "ID IN (SELECT RECORDS.ID " & _
"FROM CONSTITUENT_BANK INNER JOIN " & _
"BANK ON CONSTITUENT_BANK.BRANCH_ID = BANK.ID INNER JOIN " & _
"RECORDS ON CONSTITUENT_BANK.CONSTIT_ID = RECORDS.ID " & _
"WHERE (BANK.POST_CODE = '94122')) "
This would clearly take a lot longer than using a direct query