The update to The Raiser’s Edge version 7.6 SQL Server has caused the schema to change for how SQL statements are written against the database. Generally, this will not affect those clients with VBA or API.  However, for clients using two separate Blackbaud objects and two specific Microsoft objects there are a few changes.  



Below we have provided examples of the Blackbaud objects in version 7.5, and the changes in version 7.6.  The final example is for the Microsoft objects.  



All of the top level objects can be initialized with the "CustomWhereClause"“. This initialize parameter allows you to pass in a string that may evaluate one of the fields directly off of the top level object.
 

Example of 1st Blackbaud Object version 7.5


In The Raiser’s Edge version 7.5 and earlier, the “CustomWhere” clauses reference the following REWIN schema:



Example:

   Dim oRecs As CRecords

   Dim SQL As String

   

   SQL = ""

   SQL = "IS_CONSTITUENT = -1 AND ID IN (SELECT CONSTIT_ID FROM REWIN.PROSPECT INNER JOIN REWIN.PROPOSAL ON PROSPECT.ID = PROPOSAL.PROSPECT_ID WHERE PROPOSAL.DATE_ADDED > '2001-06-01')"

   

   Set oRecs = New CRecords

   oRecs.Init moAPI.SessionContext, tvf_record_CustomWhereClause, SQL

   Debug.Print oRecs.Count

   

   oRecs.CloseDown

   Set oRecs = Nothing


 

Example of 1st Blackbaud Object version 7.6


In The Raiser’s Edge version 7.6 SQL Server, the schema is no longer required but can be added:



   Dim oRecs As CRecords

   Dim SQL As String

   SQL = ""

   SQL = "IS_CONSTITUENT = -1 AND ID IN (SELECT CONSTIT_ID FROM dbo.PROSPECT INNER JOIN dbo.PROPOSAL ON PROSPECT.ID = PROPOSAL.PROSPECT_ID WHERE PROPOSAL.DATE_ADDED > '2001-06-01')"

   

   Set oRecs = New CRecords

   oRecs.Init moAPI.SessionContext, tvf_record_CustomWhereClause, SQL

   Debug.Print oRecs.Count

   

   oRecs.CloseDown

   Set oRecs = Nothing
 

Example of 2nd Blackbaud Object version 7.5




The CStaticQ.AddviaSQLStmt allows a programmer to create a new query via code and use SQL statements to gather records for the query.



Please note that the addviaSQLStment contains 2 parameters:

1. The SQL Statement.

2. The IDFieldIndex.  (Number sequence starts at zero)



In version 7.5 the code looks like this:



Public Sub StaticQuery()

Dim oSQ As CStaticQ

Set oSQ = New CStaticQ

oSQ.Init REApplication.SessionContext



With oSQ

  .CreateWithoutPrompt (SEARCH_CONSTITUENT), "Custom", "Test", , , , , , True 'The Last true overwrites queries with the same 'name

  .AddviaSQLStmt "SELECT RECORDS.ID FROM REWIN.RECORDS WHERE RECORDS.LAST_NAME = 'Smith'", 0

  .EndCreate Nothing

End With



Set oSQ = Nothing



End Sub



In the above example, there is one select field i.e. the id field so the "IDFieldIndex" that corresponds to it is 0. If the id field were the third field in the SQL statement, the IDFieldIndex would be 2.






 

Example of 2nd Blackbaud Object version 7.6




The code below has been modified to work with 7.6 SQL Server. The schema is not necessary, but will work with dbo.RECORDS.



Public Sub StaticQuery()

Dim oSQ As CStaticQ

Set oSQ = New CStaticQ

oSQ.Init REApplication.SessionContext



With oSQ

 .CreateWithoutPrompt (SEARCH_CONSTITUENT), "Custom", "Test1", , , , , , True 'The Last true overwrites queries with the same 'name

 .AddviaSQLStmt "SELECT RECORDS.ID FROM RECORDS WHERE RECORDS.LAST_NAME = 'Smith'", 0

 .EndCreate Nothing

End With



Set oSQ = Nothing



End Sub
 

Two Microsoft Objects




The final objects, ADO and RDO, are from Microsoft and are supported by Microsoft.  The ADO and RDO objects allow you to use Visual Basic or other COM-enable programming language to connect to the database directly (circumventing the objects) through RE:OPEN with the restrictions that RE:OPEN provides.



The ADO and the RDO are objects that allow an application to connect to a database and "talk" to it through SQL Server.  The following are two Web sites Microsoft has published with information on the ADO, Microsoft's ADO Web site and the second is the "User's Guide"/Manual Web site on how to use the ADO




If you have custom code that is using the ADO or RDO objects, the syntax may no longer be correct and could cause your code to no longer work. It is suggested that you contact your programmer to discuss this issue.


 

Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Microsoft Visual Basic and the tools used to create and debug procedures. Our Customer Support can help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality. If you are interested in learning more about the VBA and API modules, contact your account manager.