Finally, an ADO connection

[petlin]

Hi Guys,
Let me pass along some code that now allows me to connect to any data strore (using ADO - ActiveX Data Objects). With help from you folks I discovered PureDishelper v.1.5. After modifying the example and installing their library I was abl to connect to Northwind.mdb (and Access database) found on any Access samples directory. The principle of connecting to SQLServer would be similar. As a help to others wanting to do the same I pass on the code for what its worth.

Code: Select all

; This is a program to test the connection to Northwind.mdb database using ADO

; example by Peter Cedeno

;Global DbName.s = "Northwind.mdb" ;assuming that it is in the current directory, else add path

Global DbName.s = "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"

XIncludeFile "adoconstants.pbi"

dhToggleExceptions(#True)


Procedure OpenDb()

  Protected oCN.l               ; Connection-Object
  Protected ConnectionString.s
  Protected SQL.s               ; Syntax for use in query
  Protected oRS.l               ; RecordSet-Object

  ; Engine Type=4: <= Access 97 Database (JET 3.5)
  ; Engine Type=5: >= Access 2000 Database (JET 4.0)
  ; Standardvalue: Engine Type=5
  ; Connection string.
  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DbName + ";Jet OLEDB:Engine Type=5;"

  ; Create the required ADO-Connection-Object
  oCN = dhCreateObject("ADODB.Connection")

  If oCN = 0
    MessageRequester("Connection", "Couldn't create ADODB.Connection")
    ProcedureReturn
  EndIf

  ; Open the connection.
  If Not dhCallMethod(oCN, ".Open(%T)", @ConnectionString)
  
    MessageRequester("Connection","The Connection is made")

    SQL = "Select * FROM Customers"

    dhGetValue("%o", @oRS, oCN, ".Execute(%T)", @SQL)

    Repeat
    
      dhGetValue("%b", @EOF, oRS, ".EOF")
      
      If EOF   ; end of file
        Break
      EndIf
      
      dhGetValue("%T", @szResponse, oRS, ".Fields(%T).Value", @"CustomerID")
      If szResponse : Debug "ID: " + PeekS(szResponse) : EndIf
      dhFreeString(szResponse) : szResponse = 0
  
      dhGetValue("%T", @szResponse, oRS, ".Fields(%T).Value", @"CompanyName")
      If szResponse : Debug "Company: " + PeekS(szResponse) : EndIf
      dhFreeString(szResponse) : szResponse = 0

      dhCallMethod(oRS, ".MoveNext")

    ForEver

  Else
    MessageRequester("Connection", "Couldn't make a Connection")
  EndIf

  ; Close and remove the recordset and connection.
  dhCallMethod(oRS, ".Close")
  dhCallMethod(oCN, ".Close")
  dhReleaseObject(oRS)
  dhReleaseObject(oCN)

EndProcedure

OpenDb()

[netmaestro]

I don't think this is kicking around here anywhere, so here's the includefile for the ADO constants you'll need to use ADO:

Code: Select all

; ADOConstants.pbi

#adOpenForwardOnly = 0 
#adOpenKeyset      = 1 
#adOpenDynamic     = 2 
#adOpenStatic      = 3 

;---- CursorOptionEnum Values ---- 
#adHoldRecords    = $00000100 
#adMovePrevious   = $00000200 
#adAddNew         = $01000400 
#adDelete         = $01000800 
#adUpdate         = $01008000 
#adBookmark       = $00002000 
#adApproxPosition = $00004000 
#adUpdateBatch    = $00010000 
#adResync         = $00020000 
#adNotify         = $00040000 

;---- LockTypeEnum Values ---- 
#adLockReadOnly        = 1 
#adLockPessimistic     = 2 
#adLockOptimistic      = 3 
#adLockBatchOptimistic = 4 

;---- ExecuteOptionEnum Values ---- 
#adRunAsync = $00000010 

;---- ObjectStateEnum Values ---- 
#adStateClosed     = $00000000 
#adStateOpen       = $00000001 
#adStateConnecting = $00000002 
#adStateExecuting  = $00000004 

;---- CursorLocationEnum Values ---- 
#adUseServer = 2 
#adUseClient = 3 

;---- DataTypeEnum Values ---- 
#adEmpty            = 0 
#adTinyInt          = 16 
#adSmallInt         = 2 
#adInteger          = 3 
#adBigInt           = 20 
#adUnsignedTinyInt  = 17 
#adUnsignedSmallInt = 18 
#adUnsignedInt      = 19 
#adUnsignedBigInt   = 21 
#adSingle           = 4 
#adDouble           = 5 
#adCurrency         = 6 
#adDecimal          = 14 
#adNumeric          = 131 
#adBoolean          = 11 
#adError            = 10 
#adUserDefined      = 132 
#adVariant          = 12 
#adIDispatch        = 9 
#adIUnknown         = 13 
#adGUID             = 72 
#adDate             = 7 
#adDBDate           = 133 
#adDBTime           = 134 
#adDBTimeStamp      = 135 
#adBSTR             = 8 
#adChar             = 129 
#adVarChar          = 200 
#adLongVarChar      = 201 
#adWChar            = 130 
#adVarWChar         = 202 
#adLongVarWChar     = 203 
#adBinary           = 128 
#adVarBinary        = 204 
#adLongVarBinary    = 205 

;---- FieldAttributeEnum Values ---- 
#adFldMayDefer         = $00000002 
#adFldUpdatable        = $00000004 
#adFldUnknownUpdatable = $00000008 
#adFldFixed            = $00000010 
#adFldIsNullable       = $00000020 
#adFldMayBeNull        = $00000040 
#adFldLong             = $00000080 
#adFldRowID            = $00000100 
#adFldRowVersion       = $00000200 
#adFldCacheDeferred    = $00001000 

;---- EditModeEnum Values ---- 
#adEditNone       = $0000 
#adEditInProgress = $0001 
#adEditAdd        = $0002 
#adEditDelete     = $0004 

;---- RecordStatusEnum Values ---- 
#adRecOK                   = $0000000 
#adRecNew                  = $0000001 
#adRecModified             = $0000002 
#adRecDeleted              = $0000004 
#adRecUnmodified           = $0000008 
#adRecInvalid              = $0000010 
#adRecMultipleChanges      = $0000040 
#adRecPendingChanges       = $0000080 
#adRecCanceled             = $0000100 
#adRecCantRelease          = $0000400 
#adRecConcurrencyViolation = $0000800 
#adRecIntegrityViolation   = $0001000 
#adRecMaxChangesExceeded   = $0002000 
#adRecObjectOpen           = $0004000 
#adRecOutOfMemory          = $0008000 
#adRecPermissionDenied     = $0010000 
#adRecSchemaViolation      = $0020000 
#adRecDBDeleted            = $0040000 

;---- GetRowsOptionEnum Values ---- 
#adGetRowsRest = -1 

;---- PositionEnum Values ---- 
#adPosUnknown = -1 
#adPosBOF     = -2 
#adPosEOF     = -3 

;---- enum Values ---- 
#adBookmarkCurrent = 0 
#adBookmarkFirst   = 1 
#adBookmarkLast    = 2 

;---- MarshalOptionsEnum Values ---- 
#adMarshalAll          = 0 
#adMarshalModifiedOnly = 1 

;---- AffectEnum Values ---- 
#adAffectCurrent = 1 
#adAffectGroup   = 2 
#adAffectAll     = 3 

;---- FilterGroupEnum Values ---- 
#adFilterNone            = 0 
#adFilterPendingRecords  = 1 
#adFilterAffectedRecords = 2 
#adFilterFetchedRecords  = 3 
#adFilterPredicate       = 4 

;---- SearchDirection Values ---- 
#adSearchForward  = 1 
#adSearchBackward = -1 

;---- ConnectPromptEnum Values ---- 
#adPromptAlways           = 1 
#adPromptComplete         = 2 
#adPromptCompleteRequired = 3 
#adPromptNever            = 4 

;---- ConnectModeEnum Values ---- 
#adModeUnknown        = 0 
#adModeRead           = 1 
#adModeWrite          = 2 
#adModeReadWrite      = 3 
#adModeShareDenyRead  = 4 
#adModeShareDenyWrite = 8 
#adModeShareExclusive = $c 
#adModeShareDenyNone  = $10 

;---- IsolationLevelEnum Values ---- 
#adXactUnspecified     = $ffffffff 
#adXactChaos           = $00000010 
#adXactReadUncommitted = $00000100 
#adXactBrowse          = $00000100 
#adXactCursorStability = $00001000 
#adXactReadCommitted   = $00001000 
#adXactRepeatableRead  = $00010000 
#adXactSerializable    = $00100000 
#adXactIsolated        = $00100000 

;---- XactAttributeEnum Values ---- 
#adXactCommitRetaining = $00020000 
#adXactAbortRetaining  = $00040000 

;---- PropertyAttributesEnum Values ---- 
#adPropNotSupported = $0000 
#adPropRequired     = $0001 
#adPropOptional     = $0002 
#adPropRead         = $0200 
#adPropWrite        = $0400 

;---- ErrorValueEnum Values ---- 
#adErrInvalidArgument     = $bb9 
#adErrNoCurrentRecord     = $bcd 
#adErrIllegalOperation    = $c93 
#adErrInTransaction       = $cae 
#adErrFeatureNotAvailable = $cb3 
#adErrItemNotFound        = $cc1 
#adErrObjectInCollection  = $d27 
#adErrObjectNotSet        = $d5c 
#adErrDataConversion      = $d5d 
#adErrObjectClosed        = $e78 
#adErrObjectOpen          = $e79 
#adErrProviderNotFound    = $e7a 
#adErrBoundToCommand      = $e7b 
#adErrInvalidParamInfo    = $e7c 
#adErrInvalidConnection   = $e7d 
#adErrStillExecuting      = $e7f 
#adErrStillConnecting     = $e81 

;---- ParameterAttributesEnum Values ---- 
#adParamSigned   = $0010 
#adParamNullable = $0040 
#adParamLong     = $0080 

;---- ParameterDirectionEnum Values ---- 
#adParamUnknown     = $0000 
#adParamInput       = $0001 
#adParamOutput      = $0002 
#adParamInputOutput = $0003 
#adParamReturnValue = $0004 

;---- CommandTypeEnum Values ---- 
#adCmdUnknown    = $0008 
#adCmdText       = $0001 
#adCmdTable      = $0002 
#adCmdStoredProc = $0004 

;---- SchemaEnum Values ---- 
#adSchemaProviderSpecific      = -1 
#adSchemaAsserts               = 0 
#adSchemaCatalogs              = 1 
#adSchemaCharacterSets         = 2 
#adSchemaCollations            = 3 
#adSchemaColumns               = 4 
#adSchemaCheckConstraints      = 5 
#adSchemaConstraintColumnUsage = 6 
#adSchemaConstraintTableUsage  = 7 
#adSchemaKeyColumnUsage        = 8 
#adSchemaReferentialContraints = 9 
#adSchemaTableConstraints      = 10 
#adSchemaColumnsDomainUsage    = 11 
#adSchemaIndexes               = 12 
#adSchemaColumnPrivileges      = 13 
#adSchemaTablePrivileges       = 14 
#adSchemaUsagePrivileges       = 15 
#adSchemaProcedures            = 16 
#adSchemaSchemata              = 17 
#adSchemaSQLLanguages          = 18 
#adSchemaStatistics            = 19 
#adSchemaTables                = 20 
#adSchemaTranslations          = 21 
#adSchemaProviderTypes         = 22 
#adSchemaViews                 = 23 
#adSchemaViewColumnUsage       = 24 
#adSchemaViewTableUsage        = 25 
#adSchemaProcedureParameters   = 26 
#adSchemaForeignKeys           = 27 
#adSchemaPrimaryKeys           = 28 
#adSchemaProcedureColumns      = 29 

[petlin]

Hi Netmaestro,
Those constants you provided come with the Disphelper that one needs to download. but thanks for thinking about it.

[netmaestro]

Oh, very good. I hadn't noticed it.

[JCV]

placing your code in code tag will be more readable.

Code: Select all

 [color=olive]code here[/color] [/codé]

[Kiffi]

Code: Select all

; example by Peter Cedeno

why do you adorn yourself with borrowed plumes?

Your 'example' is part of the disphelper-package from ts-soft.
the included ado-example is written by me.

[ts-soft]

@kiffi
Is not the same
The difference is minimal, but not the same

[petlin]

Hi Kiffi,
I didn't mean to offend. I tried using the code as you had it for the Northwind DB and it didn't quite work, so I had to modify it. Anyway, you definitely get the credit for the basic approach. I'm just glad someone figured out a way to incorporate ADO into PB. I fought with this for 3 weeks until I came accross your jewel. My compliments.

[Fangbeast]

why do you adorn yourself with borrowed plumes?

Borrowed plumes?? Kiffi, you are a poet!!

[Fred]

[Kiffi]

Kiffi, you are a poet!!

thanks for your compliment!

Greetings ... Kiffi