Microsoft KB Archive/115237

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

Article ID: 115237

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q115237





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
The Microsoft Access database engine that is built into Visual Basic creates a separate connection whenever an ExecuteSQL statement is used. Therefore, to have a persistent connection, you must use the CreateDynaset or CreateSnapshot methods with the DB_SQLPASSTHROUGH option to execute both Select and Action queries. The CreateDynaset or CreateSnapshot methods provide a persistent connection to the server and DB_SQLPASSTHROUGH allows you to execute a Select Into query against an ODBC data source.

Dealing with the Microsoft Access Database Engine's Connection Manager
If you close the database by using the Close method or by allowing the database object variable to go out of scope, the connection to the server is broken. This causes the server to erase the temporary tables. Then when you open the database again, the new connection will not see the temporary tables because they no longer exist, so you will get ODBC or SQL Server errors if you try to use the temporary tables.

However, because of the way the connection manager works, this is only true if the connection has actually been dropped by the Microsoft Access database engine. The database engine's connection manager maintains connections for an interval based on the ConnectionTimeout setting in the VB.INI for VB.EXE or the .INI file in the case of an .EXE file.

Example of .INI file contents: [ODBC] ConnectionTimeout = 1 ; in seconds, default=600 Even with the setting at the minimum of 1 second, you need to allow the database engine some background processing time to actually drop the connection. The database engine must enter an idle loop to actually close the connection. To ensure that this happens, place the following code after the database close method: db1.close start = Timer While Timer < start + 2 Wend FreeLocks The timer loop ensures that enough time has elapsed since the database close statement, and the FreeLocks statement allows the Microsoft Access database engine to enter an idle loop to finish background processing.

For additional information on [ODBC] section settings, please see the following article in the Microsoft Knowledge Base:

110227 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)
The version of the ODBC driver for SQL Server (SQLSRVR.DLL) that is located in the \WINDOWS\SYSTEM directory can have a significant impact on the technique described below. The version of SQLSRVR.DLL dated 28-April-1993 that shipped with Visual Basic version 3.0 works correctly as does the version dated 24-May-1993 that shipped with Microsoft Access version 1.1.

However, newer versions of SQLSRVR.DLL, like the one that shipped with Microsoft Excel version 5.0 and the one that shipped with Microsoft Access version 2.0, have a bug that causes a trappable error when using the technique described in this article. It is a trappable error, and error traps are indispensable when dealing with databases, so the bug should not cause a problem. The bug will be addressed in future versions of SQLSRVR.DLL.

If you choose, you can download the older version of SQLSRVR.DLL that shipped with Microsoft Access 1.1.

The following file is available for download from the Microsoft Download Center:

Sqlupdt.exe

Release Date: August 21, 2000

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The installation program will not copy over a newer version of the DLL, so the possibility exists that even if you ship the older driver (the one that works correctly) with your project, the user may have the newer version already installed. For example, if the newer version is 1.02.3231 and the one you ship is 1.01.2920, the installation program will leave the newer version 1.02.3231 on your customer's computer.

Therefore, the example program shown below has error trapping in place so that it will bypass the error caused by the bug should it occur. The errors will appear in a multiple-line text box with ScrollBars. If the SQLSRVR.DLL that has the bug is used, the following errors will occur when the Select Into query is executed through CreateSnapshot:

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
You can set tap stops in the list box to align the fields in a snapshot into columns and add a horizontal ScrollBar to the list box by using dialog box base units. Dialog box base units are used to obtain dialog box units. The code below calls the Windows API GetDialogBaseUnits function to get the dialog box base units used by Windows when creating dialog boxes.

You can use the dialog box base units to calculate the average width of characters in the system font. Then you can use that result as a standard to calculate the sizing of controls, and to set tab stops in the list box in this case. The low-order word of the return value contains the width, in pixels, of the current dialog box base-width unit (this base unit is derived from the system font), and the high-order word of the return value contains the height, in pixels.

The values returned represent dialog box base units before being scaled to dialog box units. The dialog box unit in the x-direction is one-fourth of the width returned by the GetDialogBaseUnits function. The dialog box unit in the y-direction is one-eighth of the height returned.

The example code gets the dialog box units from the Windows API; uses a user-defined function LOWORD to obtain DlgWidthUnits; and then divides by it to get dialog box units: DlgWidthUnits = LOWORD(GetDialogBaseUnits) / 4 The tab stops are specified in dialog units. On the average, each character is about four horizontal dialog box units in width. The tab stop values must be in increasing order. The tabs work the way typewriter tab stops work. Once a tab stop is overrun, a tab character moves the cursor to the next tab stop. If the tab stop list is overrun (that is, if the current position is greater than the last tab stop value), the default tab of eight characters is used.

Steps to Create the Example Program
 Start a new project in Visual Basic. Form1 is created by default. 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.  Set the following properties at design time:   Control   Property     Setting --  Text1     MultiLine    True Text1    Scrollbars   3 - Both Command1 Caption      "Query Using Temp Tables"

  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

  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

  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                    </li> 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.</li></ol>

Additional query words: Sqlupdt

Keywords: kbhowto kbdownload KB115237

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.