HOWTO: Use Temporary Tables in SQL Server from Visual Basic 3.0

• Microsoft Visual Basic Professional Edition for Windows, version 3.0

SUMMARY

This article explains how to use Visual Basic code to create and use temporary tables. It uses the Pubs sample database that comes with Microsoft SQL Server, but the issues and pitfalls are similar for other client/server databases.

Programmers taking advantage of the performance of a client/server database, such as a Microsoft SQL Server database, occasionally need to use temporary tables on the server to address situations like these:

• Complex queries that if executed in one SQL Select statement would reduce performance because it would join too many tables or be of too large a scope.



• Complex queries with a syntax that is so complex that there will likely be errors in the formulation of the query. Instead, you could place the results of preliminary queries in temporary tables, and use a simpler query to join the preliminary results into a final result.

MORE INFORMATION

Microsoft SQL Server maintains temporary tables created by queries only as long as the SQL session or connection that created them exists. When a Visual Basic program opens a database, the SQL Server session or connection is maintained as long as that database object, representing that database connection, is in scope and valid.

Maintaining the Connection to the Server

Dealing with the Microsoft Access Database Engine's Connection Manager

 [ODBC]
 ConnectionTimeout = 1  ; in seconds, default=600 

  db1.close
  start = Timer
  While Timer < start + 2
  Wend
  FreeLocks 

Q110227 PRB:Closed ODBC Database Stays Open Until Time-Out or VB Ends

Dealing with a Bug in the ODBC Driver for SQL Server (SQLSRVR.DLL)

Q119591 How to Obtain Microsoft Support Files from Online Services

Error number 3146 on line 1001 ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] There is already an
object named '#sqltemp1' in the database. (#2714)

Error number 3146 on line 1003 ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] There is already an
object named '#sqltemp2' in the database. (#2714)

Despite the occurrence of the trappable errors, the program will work correctly if you trap the errors.

Setting Tab Stops by Using Dialog Box Base Units

   DlgWidthUnits = LOWORD(GetDialogBaseUnits()) / 4 

Steps to Create the Example Program

1. Start a new project in Visual Basic. Form1 is created by default.



2. Add three list boxes (List1, List2, and List3), one text box (Text1) and one command button (Command1) to the form. Size and placement will be set by the code in form load procedure.



3. Set the following properties at design time:




   Control   Property     Setting
   --------------------------------------------------
   Text1     MultiLine    True
   Text1     Scrollbars   3 - Both
   Command1  Caption      "Query Using Temp Tables"
 

4. Add the following code to the Form Load event:


NOTE: Some lines of code are wrapped to fit into the article space. Be sure to correct all lines so that each complete code statement is on one, single line.

   Sub Form_Load ()

      Dim fwidth As Integer, fheight As Integer
      ' Position and size the form regardless of screen resolution:
      Me.Move 0, 0, screen.Width, screen.Height * .89

      fwidth = Me.ScaleWidth
      fheight = Me.ScaleHeight
      ' Position and size the controls regardless of screen resolution:
      list1.Move 0, 0, fwidth / 3, fheight / 2
      list2.Move fwidth / 3, 0, fwidth / 3, fheight / 2
      list3.Move 2 * fwidth / 3, 0, fwidth / 3, fheight / 2
      text1.Move 0, list1.Height, fwidth

      ' Size and center the command button:
      ' Enter the following three lines as one, single line of code:
      command1.Move (fwidth - Me.TextWidth((command1.Caption))) / 2,
         list1.Height + text1.Height, Me.TextWidth((command1.Caption)),
         2 * Me.TextHeight((command1.Caption))
   End Sub
 

5. Add the following code to the Command1_Click event:




   Sub Command1_Click ()
      Dim NL As String, sql As String
      ' Line labels are included on data operations to help locate the
      ' statements where errors may occur.
      On Error GoTo localerrhandler
      ' Define newline string:
      NL = Chr$(13) & Chr$(10)
      ' Open a connection to a SQL Server database:
      ' Enter the following two lines as one, single line of code:
      Set db1 =
         OpenDatabase("", 0, 0,"odbc;dsn=texas;database=pubs;uid=sa;pwd=;")

      ' Define first preliminary query, putting the result into temp table
      ' #sqltemp1. Return a list of all the titles with their associated
      ' title_id, price last name, and au_id fields. If the title has
      ' multiple authors, return only the first author listed in title
      ' author table:
      ' Enter the following two lines as one, single line of code:
      sql = "select titles.title, price, au_lname, authors.au_id,
         titles.title_id"

      sql = sql & " into #sqltemp1 "
      sql = sql & " from authors, titles, titleauthor, titleauthor"
      sql = sql & " where authors.au_id = titleauthor.au_id "
      sql = sql & " and titles.title_id=titleauthor.title_id"
      sql = sql & " and titleauthor.au_ord= 1"

      ' Create a snapshot to execute the action query:
   1001 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' Create a snapshot to check the results of the query:
      sql = "select * from #sqltemp1"
   1002 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' Call Sub procedure to fill list box:
      Fill_List list1

      ' Define second preliminary query, putting the result into temp table
      ' #sqltemp2. Return a list of all store names with their associated
      ' stor_address, title, title_id, price, ytd_sales fields, and show
      ' which had year-to-date sales greater than $10,000.00
      ' for particular titles.

      sql = "SELECT stor_name, stor_address, sales.title_id,"
      sql = sql & " title, ytd_sales, price"
      sql = sql & " into #sqltemp2 "
      sql = sql & " from sales, stores, titles"
      sql = sql & " where titles.title_id=sales.title_id"
      sql = sql & " and sales.stor_id=stores.stor_id"
      sql = sql & " and titles.ytd_sales > 10000.00"

      ' Create a snapshot to execute the action query:
   1003 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' Create a snapshot to check the results of the query:
      sql = "select * from #sqltemp2"
   1004 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' Call Sub procedure to fill the list box:
      Fill_List list2

      ' Define a simple query that joins
      ' the two temp tables, #sqltemp1, #sqltemp2:
      sql = "select #sqltemp1.au_lname, #sqltemp2.ytd_sales,"
      sql = sql & " #sqltemp2.stor_name, #sqltemp2.title"
      sql = sql & " from #sqltemp1 , #sqltemp2"
      sql = sql & " where #sqltemp1.title_id= #sqltemp2.title_id"

      ' Create a snapshot to fetch the results of the query:
   1005 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' call the Sub procedure to fill the list box:
      Fill_List list3

      ' To demonstrate the fact that temp tables
      ' on the SQL Server only persist during the
      ' lifetime of an active session or connection,
      ' execute the following code, and then attempt
      ' to access the temp tables. See the text of
      ' this article for more info.
      '
      '   Set sn = Nothing
      '   1011 : db1.Close
      '   start = Timer
      '   While Timer < start + 2
      '   Wend
      '   FreeLocks
      '
      '   conn$ = "odbc;dsn=texas;database=pubs;uid=sa;pwd=;"
      '1006 : Set db1 = OpenDatabase("", 0, 0, conn$)

      ' Clean up temp tables in case you need to re-create
      ' them while this current connection is alive.
      ' If you close the connection, SQL Server will
      ' destroy the temp tables automatically
      sql = "drop table #sqltemp1"
   1007 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      sql = "drop table #sqltemp2"
   1008 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)

      ' void the pointer
      Set sn = Nothing
   1009 : db1.Close

      Exit Sub
      localerrhandler:
      text1 = text1 & "Error number " & Err & " on line labeled " & Erl
      text1 = text1 & " " & Error$ & NL
      Resume Next

   End Sub
 

6. Add the following code to the general declarations level of the form:




   Option Explicit
   ' Enter the following Declare statement as one, single line:
   Declare Function SendMessage Lib "User" (ByVal hWnd As Integer,
      ByVal wMsg As Integer, ByVal wParam As Integer,
      lparam As Any) As Long
   Declare Function GetDialogBaseUnits Lib "User" () As Long

   Const WM_USER = &H400
   Const LB_SETTABSTOPS = WM_USER + 19
   ' Add a horizontal scrollbar to the list boxes:
   Const LB_SETHORIZONTALEXTENT = (WM_USER + 21)
   Const DB_SQLPASSTHROUGH = 64

   Dim biggest_value() As Integer
   Dim tabstops() As Integer
   Dim db1 As database
   Dim sn As snapshot

   Function LOWORD (lparam As Long) As Integer
      LOWORD = CInt((lparam And &HFFFF&))
   End Function

   Sub Fill_List (l As Control)
      Dim i As Integer, listline$
      Dim retval&, DBWidthUnits As Single, fieldpiece As String
      Dim dlgratio As Single, accumpixels As Integer
      Dim TabSpace As Integer, charwidth_pixels  As Integer
      Const numchars = 6   'number of blank characters between columns

      ' This Sub accepts a list box control as a parameter
      ' and fills it with the contents of a global snapshot.

      ' Set tap stops in the list box to align the fields in the snapshot
      ' into columns by using dialog box units. Then add a horizontal
      ' scrollbar to the list box.

      ' The following formula gives DBWidthUnits given pixels
      ' DBWidthUnits= numpixels * 4/LOWORD(GetDialogBaseUnits())
      ' Rearranging the above formula gives pixels given DBWidthUnits
      ' numpixels= DBWidthUnits * LOWORD(GetDialogBaseUnits())/4
      ' For more efficient calculation, calculate a multiplier, based on
      ' the above formulas. Use it to convert pixels to DBWidthUnits:
      dlgratio = 4 / LOWORD(GetDialogBaseUnits())

      ' Return the DBunits for width:
      DBWidthUnits = LOWORD(GetDialogBaseUnits()) / 4

      ' One character averages 4 * DBWidthUnits in pixels:
      charwidth_pixels = numchars * 4 * DBWidthUnits

      ' Calculate the extra space between tabbed fields:
      TabSpace = charwidth_pixels * dlgratio

      ' Loop through and display the records:
      l.Clear
      ReDim biggest_value(0 To sn.Fields.Count - 1)
      ReDim tabstops(1 To sn.Fields.Count)
      While Not sn.EOF
         For i = 0 To sn.Fields.Count - 1
            ' In case of nulls in field, promote the NULL to empty string:
            fieldpiece = sn(i) & ""

            ' Enter the following two lines as one, single line:
            If Me.TextWidth(fieldpiece) / 
               screen.TwipsPerPixelX > biggest_value(i) Then

               ' Enter the following two lines as one, single line:
               biggest_value(i) = Me.TextWidth(fieldpiece) / 
                  screen.TwipsPerPixelX

            End If
            listline$ = listline$ & fieldpiece & Chr$(9)
         Next i
         l.AddItem listline$
         listline$ = ""
         sn.MoveNext
      Wend

      For i = 0 To sn.Fields.Count - 1
         accumpixels = accumpixels + biggest_value(i)
         tabstops(i + 1) = accumpixels * dlgratio + (i + 1) * TabSpace
      Next i

      ' Send a message to l:
      retval& = SendMessage(l.hWnd, LB_SETTABSTOPS, i, tabstops(1))

      ' Add a horizontal scrollbar to single select list box.
      ' The message LB_SETHORIZONTALEXTENT expects the extent to be
      ' specified in pixels. Exploit the fact that the last tabstop
      ' element is not needed (tabs needed are one less than the number
      ' of fields to be tabbed. Store the accumulated length of the entire
      ' line in the last tab stop array element. The length is stored in
      ' DBWidthUnits. Convert it to pixels by dividing with the dlgratio
      ' calculated above.
      ' Enter the following two lines as one, single line of code:
      retval& = SendMessage(l.hWnd, LB_SETHORIZONTALEXTENT, tabstops(i) / 
         dlgratio, ByVal 0&)
   End Sub 

7. Save the project and run it. If you are using the SQL Server ODBC driver (SQLSRVR.DLL) that shipped with Visual Basic version 3.0, no error will appear in the text box. Look at the contents of the List1 and List2 boxes to see the contents of the temporary tables. Then look at the List3 box to see the results of the query based on the two temporary tables.

Additional query words:

Keywords          : kbVBp300 kbDSupport 
Version           : WINDOWS:3.0
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 19, 1999