Accessing SQL Procedures Using the UserConnection Object

Leveraging Skilled SQL Developers When Building Client/Server Applications

William R. Vaughn

This white paper is an excerpt from Hitchhiker's Guide to Visual Basic and SQL Server, 5th Edition by William R. Vaughn (MS Press); used with permission by the author. To order the complete book, contact Microsoft Press® (http://mspress.microsoft.com/).

Sharing Queries When Developing in Teams

As a client/server developer, one of the challenges you have to face is how to leverage the suite of queries built up over the lifetime of your application. These queries take the form of SQL procedures hard-coded into your application or references to stored procedures maintained on the server. Since the code that references these SQL queries can be deeply impacted in an application, it is especially difficult to maintain and keep current.

In Remote Data Objects (RDO) version 1.0, accessing these stored procedures was easier than ever, but they still required each application to include fairly complex Open Database Connectivity (ODBC) call syntax used with balky rdoPreparedStatement objects—hand-coded for each application and each procedure invocation. To get it all working, you had to build a correct SQL property, make sure all of the rdoParameter objects had the right Direction and hoped that the Type property was set correctly. On top of that, your application was responsible for providing all of the parameters to establish the ODBC connection. This means you had to build a correct connect string and set another half-dozen parameters so you could consistently get connected. This also meant that your developers had to learn a new set of skills to know how and when to use a chosen cursor driver, query type, and other settings for each situation.

Now that event-driven programming has arrived with RDO version 2.0 and Microsoft® Visual Basic® version 5.0, connecting is a little easier, but it still involves writing and maintaining event procedures to handle the contingencies. In addition, all of this must be repeated for every application you write. Sure, you can use class modules to help perform these operations in teams, but this too can be a challenge, as you have to define your own standards to do so.

For some reason, the documentation provided with Visual Basic 5.0 often calls this designer the "User Connection" designer. Granted, that's what gets created—a UserConnection object. However, the Visual Basic version 5.0 user interface (UI) calls it a "Query Connection Designer." You pick.

With RDO 2.0, you now have another option when you need to expose stored procedures or canned queries to your applications—the Query Connection designer (QCD). This new feature as exposed by the add-ins menu in Visual Basic version 5.0 Enterprise Edition, walks you through a wizardlike interface that interactively builds UserConnection objects based on your specifications. Using the dialog boxes and drop-down lists exposed by the designer, you simply choose from the available options, or fill in the properties and options as you go. When you are done, a UserConnection object containing one or more pre-defined queries is added to your application. After that, you simply refer to these queries as methods against your UserConnection object. What used to take forty or more lines of code to create and execute now can be done with two or three.

Does this technology work with other ODBC data sources besides those that support stored procedures? Sure. Any query you can define using SQL that your ODBC driver recognizes will work with the QCD. This means that you can code simple or complex procedures with or without parameters using the QCD.

Yes, building rdoQuery objects manually in code is easier than it was with RDO 1.0, but using the Query Connection designer makes the process even easier and it makes you work smarter. For one thing, the Query Connection designer isolates all of the logic needed to expose these objects into one GUI interface. This means that you can create a custom UserConnection object once and include it in every application your company creates—at least every application that needs access to the same data. This way, a central team can manage it, and if there are changes, these can be incorporated by re-compiling the application. However, even recompiling should not be required if the query code is kept server-side in stored procedures—all you have to do is rebuild the SP. This approach also dramatically reduces the number of lines of code you have to write, test, debug and maintain. While the Query Connection designer will not build your application for you, it goes a long way toward making team development easier.

Building Custom UserConnection Objects

Before we start building our own sample UserConnection object with the Query Connection designer (QCD), we need to take a brief look at what is going on under the hood. The QCD accepts a number of parameters through its dialog boxes. These are used to feed a precompiled run-time dynamic-link library (DLL) that:

The dialog boxes are designed to facilitate the collection of these parameters, but they do not make the decisions for you as to suitability of the options when used with one another. It is just as easy (okay, easier) to make a mistake when choosing these options as it was when you had to write code for them all. However, it is also far easier to correct these mistakes later—even much later when the developer has been promoted to management (or marketing) to get him or her out of the trenches.

Basically, the process of creating a custom Query Connection object is simple. All you have to do is:

Each of the queries you added to the control are invoked as methods against the UserConnection object—arguments and all. It couldn't be simpler if I sent one of the Visual Basic version 5.0 developers to your office to do it for you.

Building Custom Query Connection Objects

The following section describes step-by-step how to build a typical Query Connection object. We will be using the sample Biblio database for this example, which was constructed from the Jet BIBLIO.MDB database that ships with Microsoft SQL Server™. You could also run the example against your own database if that seems like too much trouble—or you don't have the space to devote to another test database.

  1. Start with a new project. Choose Standard EXE as the template.

  2. Select the Projects menu and select the Components tab. From this dialog box, choose the Designers tab and select Microsoft UserConnection designer. Close this tab and wait a moment while the designer is installed.

  3. Next, use the Project menu and select the Add ActiveX designer and the Microsoft UserConnection designer. At this point, a Designers entry should be added to your project explorer and the UserConnection1 designer window should be open. On top of that, the designer automatically launches a dialog box to help you choose your data source.

  4. At this point, you need to either create a new domain service name (DSN) or use an existing one. My system has a DSN named BIBLIO setup to point to the Biblio test database. Later on you can try to set up DSN-less or file-based connections, but for now, just use a DSN.

  5. Click on the Authentication tab and fill in a valid User Name and Password. I don't fill these in as I use domain-managed security. Next, set the ODBC Prompt Behavior to Never so that we can trap all of the errors instead of letting our user see the ODBC log-on dialog boxes. During development, I like to check the two option boxes to save the connection authentication property settings—you should too.

    Due to concerns about security and password protection, there are two levels of persistence available for the UserConnection object's UserID and Password properties. By default both of these levels are turned off, so that no caching or persistence of these properties occurs—so no one can dump your code and see these sensitive parameters.

    If Save Connection Information for new Run-Mode Class is checked, the user name and password properties are stored in the properties of the actual class and are persisted in the built executable or DLL.

    If Save Connection Information for Design Time is checked, the user name and password properties persist only during design-time, and are not written into the built .exe or .dll file.

  6. Click on the Miscellaneous tab and choose Use Client-Batch Cursors. You can tune the Login Timeout and Query Timeout settings later if you want to. Leave them alone for now.

  7. Click OK at the bottom of the dialog box. At this point you should be confronted with the basic UserConnection dialog box—ready to accept new queries. It looks like Figure 1.

    Figure 1. Insert multiple queries dialog box—Query Connection designer

  8. Select the second icon on the UserConnection1 dialog box—Insert Multiple Queries. At this point RDO establishes a live connection to the SQL Server based on the parameters you provided earlier, executes an ODBC query that exposes all of the stored procedures in the database selected. If you don't get a list of stored procedures that looks like the picture below, you either don't have a valid DSN setup or the database does not have any stored procedures. Go back and fix this situation before going on. You should now see something that looks like Figure 2 (actually, at this point, I have already selected my TitlesByAuthor stored procedure).

    Figure 2. Insert stored procedures dialog box—Query Connection designer

  9. Sure, the names of your stored procedures might be different than what is shown here in the list on the left, but the dialog box should be as shown. Choose one of these queries to test by selecting it and clicking on the right arrow. We will use one of these and write our own query as examples. At this point, the designer queries the database for the intimate details of the chosen query. In my case, I chose the TitlesByAuthor example (as shown in the previous picture).

  10. We are now ready to explore down a little further and check out the query parameters so we know what to expect. Click OK on the query selection dialog box. Now, I happen to know what this query looks like this on the server (because I just wrote it): 
    CREATE PROCEDURE TitlesByAuthor @AuthorWanted VARChar(30)
AS  Select Title, Author
From Titles T, Title_Author TA, Authors A
Where T.ISBN = TA.ISBN and
TA.Au_ID = A.Au_ID and
Author like @AuthorWanted
Return @@ROWCOUNT
  11. Since the query expects one input argument (the Author's name) and returns a return status (the number of rows found), we should expect to see these parameters exposed by the Query Connection designer. The designer now exposes the properties for our TitlesByAuthor query as shown below. We can select any of the parameters to garner additional details:

    Figure 3. Query parameters dialog box—Query Connection designer

  12. Notice that both the RETURN_VALUE and the AuthorWanted parameters are automatically recognized—by name, direction, ODBC binding data type and Visual Basic data type. You didn't have to do anything (this time). However, if the query is a little more complex, you might have to go in and tune the data type. For example, if instead of a LIKE expression in the query, I used a CHARINDEX expression, the designer might think that the parameter was an integer. That's why you have to double-check to ensure that the right data type is chosen.

  13. Next, we visit the Advanced tab. This tab lets us set parameters for each query we define. In this case, I know that I don't want to see more than 20 hits from the query, so I set Max Rows to 20. The database is fairly large, but 30 seconds should be plenty of time to get the job done. I don't care about the Bind Threshold as I am not working with TEXT or IMAGE data types (BLOBs) in this query. I set the Rowset size to 20 as I know I won't need to scroll around in the keyset. We don't use keyset size. I also deselected Prepare Query Before Execution as this is a stored procedure and it is already "prepared." This option sets the Prepared property on the rdoQuery object we are creating.

    Figure 4. Query definition advanced tab dialog box—UserConnection designer

  14. Click OK to close the TitlesByAuthor Properties dialog box. Notice that the query has been added to the designer's explorer window, so we can examine the properties again later if need be.

We are done with the designer for now, so we can close the designer dialog box. Notice that we now have a UserConnection1 class under the Designers heading in the Project window. You might also discover that the project also includes a reference to RDO 2.0—this was added automatically by the designer. It builds code to handle all of the properties and events associated with the object in RDO. Yes, even the event prototypes are ready to go—all you have to do is supply the code. That's next on the tour.

Note   When you build a new table or stored procedure, don't forget to grant permissions on them so the intended user can access or execute them. While your program might work if you log in as system administrator, it won't when you log in as a "normal" user. Sure, even the stored procedures that you don't have permission to access are listed by the UserConnection designer—it's like looking through Tiffany's window at the tiaras.

Executing Queries and Stored Procedures as Methods

Now that the UserConnection object is created, we are ready to take it out for a spin. The first thing we need to do is add some code to instantiate the object from its base class. First, create an instance of the UserConnection object we built using the New operator. We also need an rdoResultset object to hold the result set we are about to fetch.

Dim ucTest As New UserConnection1
Dim rs As rdoResultset

Next, we need to open the connection. You can do this anywhere, but in this case it is a one-time operation, so we put the code in the Form_Load event procedure.

Private Sub Form_Load()
ucTest.EstablishConnection
End Sub

Note   If you don't want a forward-only, read-only cursor, now is the time to reset the rdoQueries(n).CursorType to some other type of cursor. In addition you'll need to set the rdoQueries(n).LockType to some other type of concurrency too—unless you want rdConcurReadOnly. Once the query is executed, it will be too late.

At this pont the connection is open—if all is going as planned, and we are ready to try executing the query. Since each of the stored procedures we added with the designer are now exposed as methods on the UserConnection object that was created, they are really easy to code. After we declare the return-value variable, start typing the next line of code as shown following . . .

Dim lrc As Long
lrc = ucTest

You should see something similar to Figure 5.

Figure 5. IntelliSense enumerating properties, methods and events

Notice that the stored procedure that we chose is now in the enumerated list of methods. Now select the TitlesByAuthor method (you can just press the space bar to select it) and type the opening parenthesis and see what happens next. Part of Visual Basic version 5.0's Intellisense knows that this method takes a single input argument (a String) and returns an integer (Long). Because of this, Visual Basic can help you write the code for the method by exposingthe following code in Figure 6.

Figure 6. IntelliSense exposing method arguments

We complete the line by filling in the argument to pass to our TitlesByAuthor query. For our example, I am simply hard-coding the parameter. You would probably pass in a parmeter via a Text box or somesuch.

lrc = ucTest.TitlesByAuthor("%Vaughn%")

When this line executes, it runs the query and returns a return status and result set as described in the UserConnection properties. As a word of caution, I have seen a number of strange instances where I trip an "Invalid use of Nulls" error when using the syntax shown above. Apparenty, some of the cursor drivers do not provide the return status value until the query is fully populated and return a Null until that value is available. Well, there are a couple of ways around this. First, you can code the call without the return value:

ucTest.TitlesByAuthor "%Vaughn%"

and fetch it later when the rdoResulset is at end of file (EOF). At this point the rdoQuery object under the UserConnection object contains the return-status value.

lrc = ucTest.rdoQueries(0).rdoParameters(0)

To access the result set, you must address the rdoResultset object just created which is passed in the LastQueryResults property as shown below.

Set rs = ucTest.LastQueryResults

WARNING   You can't treat the LastQueryResults property like any rdoResultset object—it is only a temporary pointer to the result set. As soon as it is referenced it is lost. This means that if you use the DataTip pointer to examine its contents, it will be lost. You must set an rdoResultset variable to this property to use it—or you lose it.

At this point you have an rdoResultset object—just as if you had created it with an OpenResultset—so the same rules and techniques apply. The result set might not be fully populated, so you will want to do a MoveLast on it as soon as possible. And no, you can't use the method or the UserConnection again without first dealing with the result set you just created. This means that you have to use the Close method on the rdoResultset or the LastQueryResults property (which exposes the underlying rdoResultset object built by the UserConnection.) To see what ReturnStatus was sent back, we can examine the variable we used when executing the method (lrc). Notice that the RowCount property returns a -1 as it is not (yet) available.

Debug.Print rs.RowCount, lrc

Wait a minute! There's something missing here. When I create a result set, I like to choose the type of cursor. For some situations, I use cursorless result sets, but other times I need a keyset, static, forward-only or if I have gone completely bonkers, I use a dynamic cursor. Actually, that's a lie. I have never been crazy enough to use a dynamic cursor. Well, anyway, to set the type of cursor that the UserConnection method is to create can be set by changing the CursorType property of the rdoQuery that the UserConnection object creates for the chosen stored procedure. RDO and Visual Basic will help you choose one as it enumerates your choices when you address this property as illustrated in Figure 7.

Figure 7. DataTips exposing enumerated constants

Note   I discovered a bug when using this feature. It seems that each time this feature is invoked, the enumerated list box appeared a little more to the right. Apparently, by choosing a proportional font in the Visual Basic Options, I confused the code that positions the dialog boxes. It'll be fixed in the next version.

While this is not as easy as clicking on a tab and selecting a cursor type from the UserConnection designer dialog boxes, it isn't that hard to do in code. Notice that I have addressed the rdoQuery object using the bang (!) syntax. This causes an "early" binding of the rdoQueries collection member and speeds up your code (a tad). I would not like to reference this member by its ordinal number, because the code would break if I added another stored procedure or query ahead of it.

Building Your Own Queries

Generally, I recommend that any queries that your application executes should be in the form of stored procedures. In cases where this is not possible, you can use the Query Connection designer's ability to manage queries you provide in the form of T-SQL queries. Since Microsoft Query is cross-connected to the Query Connection designer, it can be used to help you build your own custom queries interactively through a GUI interface. You might also consider using the new Microsoft Visual Database Tools to build your queries. Frankly, I prefer the latter as it has a much better, albeit more complex, interface.

The steps you need to take to get to the Microsoft Query window are fairly straightforward. Let's step through them to make sure you are ready to use this interface when the time comes to build a complex query. Incidentally, once your query has been developed and tested, it would still be a good idea to move it over to SQL Server as an stored procedure. To try out this interface, let's create a variation on our previous query, but this time, let's add the Publishers table. Since we already have a UserConnection object created, we can go right to the designer's main form.

  1. Click the first icon, Insert Query, to build a new query. Again, a connection to SQL Server is established to populate the list of stored procedures—which we won't be using this time.

  2. Type in the name of the new query. Let's call it TitlesPubsByAuthor. This is a Visual Basic method name, so it can't have imbedded spaces. At this point you should see something like Figure 8.

    Figure 8. Defining your own SQL query for a user-defined UserConnection object

  3. Wait! Don't just click the Build... button. This launches Microsoft Query (MSQ), but we aren't ready to do that just yet. For some reason, MSQ expects something to be entered into the Text box at the bottom of the dialog box. If you don't enter anything, it returns the error:" . . . unable to start." For now, just enter Select * from Authors so we can get into MSQ.

  4. Now, Click the Build... button. At this point the designer launches MSQ, but it executes the query right away. So, we have just locked up the entire Authors table. Not good. We either need to get out of this immediately or populate the result set to free locks on the table. By default, there is no upper limit on the number of rows that MSQ pulls down, but you can set this limit by going into the MSQ Options dialog box and setting the number of rows to 10 or so. Do this now so we don't have to worry about this again.

  5. Use the interactive dialog boxes to add the Titles, Publishers, and Title_Authors tables to the already present Authors table. Use the Table/Add Tables menu for this, or the Add Table(s) icon. Unfortunately, as you add the tables, MSQ merrily tries to join them all—whether or not they are related. It gets kinda silly after awhile as MSQ constantly complains that all of the rows could not be shown. It would probably work better if you added the tables in order. That is, first Title_Authors, because it has a link to the Authors table. Next, add Titles and then Publishers so that MSQ can attempt to figure out the relationships more easily. Once you are done adding tables you should have a screen that looks like the one in Figure 9.

    Figure 9. Using MS Query to define your SQL query—choosing the tables

  6. I rearranged the table dialog boxes so that their relationship lines could be seen. We are now ready to choose the columns to appear in our query, but before we take this next step, click on the Records menu and deselect the Automatic Query option. This will prevent MSQ from attempting to execute the query we are building as we work on it. It's almost as if the people developing this weren't aware that these tables could be used in production and doing random queries is not such a good idea.

  7. Using the drag-and-drop techniques you learned when using Microsoft Access, drag the ISBN, Title, and Company_Name columns to the columns area at the bottom of the query window—where you see the Authors column now. At this point, you should have something like Figure 10 on your screen.

     Figure 10. Using MS Query to define your SQL query—choosing the columns

  8. If you want to, you can add sort criteria, change the way the joins work, or preview the SQL query, but for now, let's just keep it simple and exit back to Visual Basic and the Query Connection Designer. What you might do before you leave is test the query by clicking the ! button. I did, and the query ran, but returned another "Can't show all records" error again for some reason. Let's just click the little door icon and get back to Visual Basic.

  9. When we return to Visual Basic, MSQ has passed back the SQL query needed to execute our query. No, there is no vertical scroll bar, so you will just have to walk the cursor down with the arrow keys to see the bottom of it. At this point you should see the dialog box shown in Figure 11.

    Figure 11. User-defined SQL query built by MS Query

  10. While MSQ does not seem to have a way to add parameters to our user-written queries, we can do so fairly easily by editing the SQL query it generated. As with RDO 1.0, we can insert a question mark (?) where we want RDO and ODBC to insert and manage a query parameter for us. These parameters can only be input-type as return value, output or input/output parameters are not supported. Let's try to add a parameter and see what happens. Edit the SQL query shown in the User-Defined SQL dialog box and add "WHERE Author = ?" to the end.  Press the Parameters tab when you are finished editing the SQL query. You should now see something like Figure 12.

    Figure 12. Tuning query parameters using the Query Connection designer

  11. Notice that this Parameters dialog box is not filled in nearly as intelligently as the one we saw earlier. That's because the designer and RDO have no way to tell what the question mark is supposed to mean. It simply guessed that it was meant to represent an integer. However, in our case, we will be providing a string, so we have to go in and change the settings.

  12. Change the Name to "AuthorWanted". This value will show up when we code the method. Change the ODBC Binding Data Type to "SQL_VARCHAR". Change the Visual Basic Data Type to "String". We don't have to change the Direction, it is already set at "Input". Once these values are set, our query is ready to save. No, it has not been saved and won't be until we press OK or Apply. Click OK.

  13. Click the View Code button on the designer. Notice that the Event handlers are already filled in. Remember that these handlers apply to all of the queries you define on this connection. You might also want to visit the Advanced tab to set some of the other properties. I didn't bother this time, as we won't test this new query. That's something else you might want to do on your own.

Note   It seems to me that Microsoft Visual Database provides a better way to create these queries.

Of course, you know that SELECT queries aren't the only kind of queries in use today—no, far from it. While fetching rows is important, many of our client/server front-end operations use action queries too. No, I don't think the interactive query development strategy used here in MSQ would be much good for this type of work. However, there is nothing to stop you from creating your own action queries on your own and adding them on to your user connection. Again, Visual Database Tools might also be helpful here.

Tips on Using Your Own SQL Queries and Parameters

You can include your own queries instead of pointing to a stored procedure. You can even code queries that contain parameters. I often take this back-road approach when I am developing a new application—before I commit the query to a stored procedure. There are a couple of chuckholes here that you need to watch out for.

Make sure you know what you are asking for when you make a parameter a CHAR as opposed to a VARCHAR. Remember that CHAR expressions are fixed in length, and VARCHAR is variable in length. This means if the length of the parameter you are passing changes, you should be using a VARCHAR.

Using the UserConnection Object's Events

The UserConnection object exposes an entire set of events that can help you fine-tune your custom UserConnection object for special circumstances. The events exposed are the same as for the rdoConnection object plus the Terminate event, which is always associated with user-created objects.

At a minimum, I would also add an error-handling routine to deal with the stuff that happens when we work with SQL Server. Since the UserConnection object exposes all of the rdoConnection events, you can simply add some code to the QueryComplete event handler. Consider that this error handler becomes part of the UserConnection object and is carried around in the .dsr file. This means other developers can use your custom UserConnection object and get all of the error-handling code as well.

Private Sub UserConnection_QueryComplete _
  (ByVal Query As RDO.rdoQuery,  _
   ByVal ErrorOccurred As Boolean)
Dim er As rdoError
Dim m As String
If ErrorOccurred Then
    For Each er In rdoErrors
        m = m & er & vbCrLf
    Next er
    MsgBox "Something went wrong with the query"  _
& vbCrLf & m
End If