INF: Use of SQLPutData() in Visual Basic

• Microsoft Open Database Connectivity, version 2.0

SUMMARY

When sending long data values for binary large objects (BLOBs), you should use SQLPutData() and send the data in chunks. This is primarily due to the limitation(s) imposed by the ODBC drivers in sending BLOBs.

For more information on calling SQLPutData(), refer to the "Microsoft ODBC 2.0 Programmer's Reference and SDK Guide."

The following is a small code sample written in Visual Basic that illustrates the calling of the ODBC API SQLPutData() to add data to a column.

MORE INFORMATION

' Items found in (general) (declarations) in module
'
'Declarations required to call ODBC API from VB 4.0 and 3.0:
'
'If the application is 16-bit, include the following
'function declaration

Declare Function GetFocus Lib "User" () As Integer
Declare Function GetParent Lib "User" (ByVal hWnd%) As

Declare Function SQLAllocConnect Lib "odbc.dll" (ByVal

Declare Function SQLAllocEnv Lib "odbc.dll" (phenv&) As

Declare Function SQLAllocStmt Lib "odbc.dll" (ByVal hdbc&,

Declare Function SQLDisconnect Lib "odbc.dll" (ByVal hdbc&)

Declare Function SQLError Lib "odbc.dll" (ByVal henv&, ByVal

Declare Function SQLExecDirect Lib "odbc.dll" (ByVal hstmt&,

Declare Function SQLFreeConnect Lib "odbc.dll" (ByVal hdbc&)

Declare Function SQLFreeEnv Lib "odbc.dll" (ByVal henv&) As

Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hstmt&,

Declare Function SQLBindParameter Lib "odbc.dll" (ByVal

Declare Function SQLDriverConnect Lib "odbc.dll" (ByVal

Declare Function SQLParamData Lib "odbc.dll" (ByVal hstmt&,

Declare Function SQLPutData Lib "odbc.dll" (ByVal hstmt&,

'If the application is 32-bit, include the following
'function declaration

Declare Function GetFocus Lib "User32" () As Integer
Declare Function GetParent Lib "User32" (ByVal hWnd%) As

Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal

Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As

Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hdbc&,

Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal

Declare Function SQLError Lib "odbc32.dll" (ByVal henv&,

Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal

Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal

Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv&)

Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hstmt&,

Declare Function SQLBindParameter Lib "odbc32.dll" (ByVal

Declare Function SQLDriverConnect Lib "odbc32.dll" (ByVal

Declare Function SQLParamData Lib "odbc32.dll" (ByVal

Declare Function SQLPutData Lib "odbc32.dll" (ByVal hstmt&,

'Since this is in module, the variables need to be global

Sub Command3D1_Click ()
'
' This sample VB code illustrates the use of the ODBC API
' call  SQLPutData()
'
' It has been kept as simple as possible with a minimal
' amount of code and declarations.

    Dim henv As Long
    Dim hdbc2 As Long
    Dim hstmt2 As Long
    Dim nstatus%
    Dim ptoken As Integer

    nstatus = SQLAllocEnv(henv)

    nstatus = SQLAllocConnect(henv, hdbc2)
    If (nstatus = SQL_ERROR) Then
        MsgBox "Couldn't allocate memory for connection"
    End If

' The parameters passed to SQLDriverConnect() after the
' second one are not really used for anything. The call
' expects the variables as output variables so we pass them
' to the call to avoid problems.
'
    nstatus = SQLDriverConnect(hdbc2, GetParent(GetFocus()),

    If (nstatus = SQL_ERROR) Then
        MsgBox "Couldn't connect"
    End If

' In this sample, I am connecting to a SQL Server database
' and this table is defined:
' CREATE TABLE model01 (col1 char(30),col2 char(30))
' INSERT INTO model01 VALUES('Kate Moss', 'Kate Moss')

    sSQLString = "update model01 set col2 = ? where col1 =
'Kate Moss'"

    leng = Len(sSQLString)

    nstatus = SQLAllocStmt(hdbc2, hstmt2)
    If (nstatus = SQL_ERROR) Then
        MsgBox "Couldn't allocate memory for statement"
    End If

'
' In this particular call to SQLBindParmeter, we are binding
' a VB string variable to a column on the SQL Server
' database server that we know to be a char(30). The '1' in
' the third parameter of this call indicates that this
' parameter is of type SQL_PARAM_INPUT. Please refer to the
' definition of fParamType in the description of
' SQLBIndParameter() in the ODBC SDK Programmer's Reference
' for more information. The sixth parameter, cbColDef , is
' indicating that we will be inserting 22 characters into
' this column via the parameter marker. 22 will be the most
' that we can insert into this column now, because of how we
' have defined cbColDef. The 7th parameter (ibScale) is 0
' because we know from Appendix D of the ODBC 2.0
' Programmer's Reference that this is the case for char
' datatypes. If you've read this far into the comment and
' still don't have the Prog. Ref., please call 1-800-MSP-
' RESS or visit your local computer book store to order it.
' ISBN number = 1-55615-658-8. The 8th parameter is a 1
' instead of a pointer to a memory area because we know that
' we are going to be using SQLParamData()/SQLPutData() to
' load the parameter. The 1 is just meant as a placeholder
' in this case. For the same reason, the 9th parameter is 0.
' The 10th parameter needs to be the result of the
' computation (-100 - (number of bytes being passed)). In
' this case cbColDef is 22, which is why the 10th parameter,
' pcbValue, is -122. The -100 in the computation represents
' the #define in SQLEXT.H called
' SQL_LEN_DATA_AT_EXEC_OFFSET. The bind is done this way so
' that later on, when the sql statement is executed, the
' ODBC driver manager knows more information will need to be
' prompted for via the parameter marker.
'

    nstatus = SQLBindParameter(hstmt2, 1, 1, SQL_C_CHAR,

    If (nstatus = SQL_ERROR) Then
        MsgBox "error on Bind"
    End If


    nstatus = SQLExecDirect(hstmt2, sSQLString, leng)
    If (nstatus = SQL_ERROR) Then
        MsgBox "Error on execdirect"
    End If

    Dim lenhy As Integer
    lenhy = 0

'
' The use of SQLParamData() is simplified here to only
' account for use of one parameter marked in the SQL
' statement being executed. If there had been more than one
' parameter marker, we would have needed to call
' SQLParamData() more. In general, if there
' are n parameter markers (?) contained in the SQL statement
' being processed, then SQLParamData() must be called n + 1
' times to successfully move through the parameters.
'
     nstatus = SQLParamData(hstmt2, ptoken)
     If (nstatus = SQL_NEED_DATA) Then
        ' An assumption is made here that we know that the
        ' length of the data that we are inserting in this
        ' particular parameter marker is 22.
        While (lenhy < 22)
          buffer = "Some String"
          '
          ' It is quite inefficient in this particular case
          ' to initialize the buffer in this part of the
          'code. In most regular situations though, the
          ' value of the buffer inserted into the column
          ' with SQLPutData() will change
          ' before each call to SQLPutData(), so I left the
          'code this way.
          nstatus = SQLPutData(hstmt2, buffer, Len(buffer))
          If (nstatus = SQL_ERROR) Then
            MsgBox "Error calling putdata"
          End If
          lenhy = lenhy + Len(buffer)
        Wend
     End If
    '
    ' As mentioned before, the last call to SQLParamData()
    'is required to properly
    ' finish processing parameters.

    nstatus = SQLParamData(hstmt2, ptoken)

    nstatus = SQLDisconnect(hdbc2)
    nstatus = SQLFreeConnect(hdbc2)
    nstatus = SQLFreeEnv(henv)

    MsgBox "Finished"

End Sub