IT-Consultant: José Roca (PBWIN 10+/PBCC 6+) (Archive only) > COM Programming
ADO/ADOX Examples
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