IT-Consultant: José Roca (PBWIN 10+/PBCC 6+) (Archive only) > COM Programming

ADO/ADOX Examples

(1/16) > >>

José Roca:
 
ADO

Microsoft ActiveX Data Objects (ADO) enable your client applications to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

ADOX

Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.

ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example illustrates the use of the AbsolutePage property.
The cursor location must be set to adUseClient.


--- Code: ---' ########################################################################################
' Microsoft Windows
' File: ADOEX_AbsolutePage.bas
' Contents: ADO example
' Demonstrates the use of the AbsolutePage, PageCount and PageSize properties.
' The cursor location must be set to %adUseClient.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL nPageCount AS LONG
   LOCAL nPageSize AS LONG
   LOCAL i AS LONG
   LOCAL x AS LONG
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Use client cursor to enable AbsolutePosition property
      pRecordset.CursorLocation = %adUseClient
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Display five records at a time
      pRecordset.PageSize = 5
      ' // Retrieve the number of pages
      nPageCount = pRecordset.PageCount
      ' // Parse the recordset
      FOR i = 1 TO nPageCount
         ' // Set the cursor at the beginning of the page
         pRecordset.AbsolutePage = i
         ' // Retrieve the number of records of the page
         nPageSize = pRecordset.PageSize
         FOR x = 1 TO nPageSize
            ' // Get the content of the "Name" column
            vRes = pRecordset.Collect("Name")
            ? VARIANT$$(vRes)
            ' // Fetch the next row
            pRecordset.MoveNext
            IF pRecordset.EOF THEN EXIT FOR
         NEXT
         WAITKEY$
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example illustrates the use of the AbsolutePosition property.
The cursor location must be set to adUseClient.


--- Code: ---' ########################################################################################
' Microsoft Windows
' File: ADOEX_AbsolutePosition.bas
' Contents: ADO example
' This example demonstrates how the AbsolutePosition property can track the progress of a
' loop that enumerates all the records of a Recordset. It uses the CursorLocation property
' to enable the AbsolutePosition property by setting the cursor to a client cursor.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the cursor location
      pRecordset.CursorLocation = %adUseClient
      ' // Open the recordset
      SqlStr = "Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdTable
      DO
        ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the absolute position
         PRINT "Position:" & STR$(pRecordset.AbsolutePosition) " ";
         ' // Get the Publisher's name
         vRes = pRecordset.Collect("Name")
         PRINT VARIANT$$(vRes)
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
This example illustrates the ActiveCommand property.

A subroutine is given a Recordset object whose ActiveCommand property is used to display the command text and parameter that created the recordset.


--- Code: ---' ########################################################################################
' Microsoft Windows
' File: ADOEX_ActiveCommand.bas
' Contents: ADO example
' This example demonstrates the use of the ActiveCommand property.
' A subroutine is given a Recordset object whose ActiveCommand property is used to display
' the command text and parameter that created the Recordset.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' The ShowActiveCommand routine is given only a Recordset object, yet it must print the
' command text and parameter that created the Recordset. This can be done because the
' Recordset object's ActiveCommand property yields the associated Command object.
' The Command object's CommandText property yields the parameterized command that was
' substituted for the command's parameter placeholder ("?").
' ========================================================================================
SUB ShowActiveCommand (BYVAL pConnection AS ADOConnection, BYVAL pRecordset AS ADORecordset)

   LOCAL bstrPrmName AS WSTRING
   LOCAL pCommand AS ADOCommand
   LOCAL bstrCommandText AS WSTRING
   LOCAL pParameters AS ADOParameters
   LOCAL pParameter AS ADOParameter
   LOCAL vValue AS VARIANT
   LOCAL bstrAuID AS WSTRING
   LOCAL bstrAuName AS WSTRING

   TRY
      pCommand = pRecordset.ActiveCommand
      bstrCommandText = pCommand.CommandText
      pParameters = pCommand.Parameters
      pParameter = pParameters.Item("Name")
      vValue = pParameter.Value
      bstrPrmName = VARIANT$$(vValue)
      PRINT "Command text: " & bstrCommandText
      PRINT "Parameter: " & bstrPrmName
      IF pRecordset.BOF THEN
         PRINT "Name = '" & bstrPrmName & "', not found"
      ELSE
         vValue = pRecordset.Collect("Author")
         bstrAuName = VARIANT$$(vValue)
         vValue = pRecordset.Collect("Au_ID")
         bstrAuID = STR$(VARIANT#(vValue))
         PRINT "Name = " & bstrAuName & ", ID = " & bstrAuID
      END IF
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   END TRY

END SUB
' ========================================================================================

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL pCommand AS ADOCommand
   LOCAL pRecordset AS ADORecordset
   LOCAL pParameters AS ADOParameters
   LOCAL pParameter AS ADOParameter

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create an ADO command object
   pCommand = NEWCOM "ADODB.Command"
   IF ISFALSE ISOBJECT(pCommand) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ADOCommand active connection
      pCommand.putref_ActiveConnection = pConnection
      ' // Set the command text
      pCommand.CommandText = "SELECT * FROM Authors WHERE Author = ?"
      ' // Create the parameter
      pParameter = pCommand.CreateParameter("Name", %adChar, %adParamInput, 255, "Bard, Dick")
      ' // Add the parameter to the collection
      pParameters = pCommand.Parameters
      pParameters.Append pParameter
      ' // Create the recordset by executing the command string
      pRecordset = pCommand.Execute
      ' // Display the results
      ShowActiveCommand pConnection, pRecordset
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF ISOBJECT(pRecordset) THEN
         IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      END IF
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:
Sorry, your browser doesn't seem to support iframes.
The following example illustrates the use of the ActualSize property.


--- Code: ---' ########################################################################################
' Microsoft Windows
' File: ADOEX_ActualSize.bas
' Contents: ADO example
' This example uses the ActualSize and DefinedSize properties to display the defined size
' and actual size of a field.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ADO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pConnection AS ADOConnection
   LOCAL pRecordset AS ADORecordset
   LOCAL pFields AS ADOFields
   LOCAL pField AS ADOField
   LOCAL ConStr AS WSTRING
   LOCAL SqlStr AS WSTRING
   LOCAL vRes AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Recordset object
   pRecordset = NEWCOM "ADODB.Recordset"
   IF ISNOTHING(pRecordset) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Open the recordset
      SqlStr = "SELECT * FROM Publishers"
      pRecordset.Open SqlStr, pConnection, %adOpenStatic, %adLockOptimistic, %adCmdText
      ' // Get a reference to the Fields collection
      pFields = pRecordset.Fields
      DO
         ' // While not at the end of the recordset...
         IF pRecordset.EOF THEN EXIT DO
         ' // Get the Publisher's name
         pField = pFields.Item("Name")
         vRes = pField.Value
         PRINT "Name: " & VARIANT$$(vRes) " - ";
         PRINT "Actual size:" & STR$(pField.ActualSize) " - ";
         PRINT "Defined size:" & STR$(pField.DefinedSize)
         pField = NOTHING
         ' // Fetch the next row
         pRecordset.MoveNext
      LOOP
      ' // Release the collection
      pFields = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Close the recordset
      IF pRecordset.State = %adStateOpen THEN pRecordset.Close
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

Navigation

[0] Message Index

[#] Next page

Go to full version