OLE DB User's Guide
Contents
1. DBMaker D CASEMaker ES CASEMaker Inc Corporate Headquarters 1680 Civic Center Drive Santa Clara CA 95050 U S A www casemaker com www casemaker com support Copyright 1995 2008 by CASEMaker Inc Document No 645049 232007 DBM501 M01312008 OLED Publication Date 2008 01 31 All rights reserved No part of this manual may be reproduced stored in a retrieval system or transmitted in any form without the prior written permission of the manufacturer For a description of updated functions that do not appear in this manual read the file named README TXT after installing the CASEMaker DBMaker software Trademarks CASEMaker the CASEMaker logo and DBMaker are registered trademarks of CASEMaker Inc Microsoft MS DOS Windows and Windows NT are registered trademarks of Microsoft Corp UNIX is a registered trademark of The Open Group ANSI is a registered trademark of American National Standards Institute Inc Other product names mentioned herein may be trademarks of their respective holders and are mentioned only form information purposes SQL is an industry language and is not the property of any company or group of companies or of any organization or group of organizations Notices The software described in this manual is covered by the license agreement supplied with the software Contact your dealer for warranty details Your dealer makes no representations or warranties with respect to the merchant2. Rowsets Rowsets are the central objects that enable OLE DB components to expose and manipulate data in tabular form A Rowset object is a set of rows each having columns of data For example OLE DB Provider for DBMaker presents data and metadata to consumers in the form of rowsets The use of rowsets throughout OLE DB makes it possible to aggregate components that consume or produce data through the same object The Rowset object cotype is defined as follows CoType TRowset mandatory interface lAccessor mandatory interface IColumnsinfo mandatory interface ConvertType mandatory interface IRowset mandatory interface IRowsetinfo optional interface IConnectionPointContainer optional interface IDBAsynchStatus optional interface IRowsetChange optional interface lRowsetFind optional interface lRowsetindex optional interface IRowsetLocate optional interface IRowsetRefresh optional interface IRowsetScroll optional interface RowsetUpdate optional interface IRowsetView Copyright 1995 2008 CASEMaker Inc 3 3 OV OLE DB User s Guide 3 4 Copyright 1995 2008 CASEMaker Inc Interfaces OLE DB 4 4 4 1 Interfaces OLE DB Interfaces are a group of semantically related functions that provide access to a COM object Each OLE DB interface defines a contract that allows objects to interact according to the Component Object Model COM OLE DB provides many interface
3. IOpenRowset plOpenRowset NULL HRESULT hr S_OK DBID TableID 0 WCHAR wszTableName L employee DBPROPSET rgPropSets 1 0 const ULONG cProperties 7 DBPROP rgProp cProperties 0 IRowset pIRowset NULL Create the TableID TableID eKind DBKIND_NAME TableID uName pwszName wszTableName rgProp 0 colid DB NULLID rgProp 0 dwOptions DBPROPOPTIONS REOUIRED rgProp 0 dwStatus 0 rgProp 0 dwPropertyID DBPROP_CANHOLDROWS rgProp 0 vValue vt VT BOOL rgProp 0 v Value boolVal VARIANT TRUE rgProp 1 colid DB NULLID rgProp 1 dwOptions DBPROPOPTIONS REOUIRED rgProp 1 dwStatus 0 rgProp 1 dwPropertyID DBPROP_CANSCROLLBACKWARDS rgProp 1 vValue vt VT BOOL rgProp 1 vValue bool Val VARIANT TRUE rgProp 2 colid DB NULLID rgProp 2 dwOptions DBPROPOPTIONS REOUIRED rgProp 2 dwStatus 0 rgProp 2 dwPropertyID DBPROP_CANFETCHBACKWARDS rgProp 2 vValue vt VT BOOL rgProp 2 v Value boolVal VARIANT TRUE rgProp 3 colid DB NULLID rgProp 3 dwOptions DBPROPOPTIONS REOUIRED rgProp 3 dwStatus 0 rgProp 3 dwPropertyID DBPROP_IRowsetChange rgProp 3 vValue vt VT BOOL rgProp 3 v Value boolVal VARIANT TRUE rgProp 4 colid DB NULLID rgProp 4 dwOptions DBPROPOPTIONS REOUIRED rgProp 4 dwStatus 0 rgProp 4 dwPropertyID DBPROP_UPDATABILITY rgProp 4 vValue vt VT 14 rgProp 4 v Value 1 Val DBPROPVAL UP CHANGE DBP
4. emp fname plAccessor gt ReleaseAccessor hAccessor NULL plRowset gt Release return S_OK Copyright 1995 2008 CASEMaker Inc Manipulate a command object and execute the select command HRESULT My_Sel_Command IDBInitialize pIDBInitialize IDBCreateSession pIDBCreateSession NULL IDBCreateCommand pIDBCreateCommand NULL ICommandText pICommandText NULL WCHAR wSQLSelect L select from employee long cRowsAffected 0 IAccessor plAccessor NULL IRowset pIRowset NULL HACCESSOR hAccessor 0 ULONG I 0 HRESULT hr S_OK DBBINDSTATUS rgStatus 3 0 DBBINDING Bindings 3 0 ULONG acbLengths 9 20 6 Get the session pIDBInitialize gt QueryInterface IID_IDBCreateSession void amp pIDBCreateSession pIDBCreateSession gt CreateSession NULL IID IDBCreateCommand IUnknown amp pIDBCreateCommand pIDBCreateSession gt Release Create the command pIDBCreateCommand gt CreateCommand NULL IID ICommandText IUnknown amp pICommandText pIDBCreateCommand gt Release Set the command text for the first delete statement then execute the command plCommandText gt SetCommandText DBGUID DBSOL wSOL Select plCommandText gt Execute NULL IID IRowset NULL amp cRowsAffected Unknown amp pIRowset for i 0 i lt 3 i Bindings i iOrdinal i 1 Bindings i obLength 0 Bindings i obStatus 0 Bindings i pTypeI
5. 6 1 OLE DB Consumer Application Microsoft Visal C Examples 6 1 6 2 ADO Code Examples in Microsoft Visual Basic 6 14 6 3 ADO NET Code Examples in Visual C 6 16 OCopyright 1995 2008 CASEMaker Inc i Introduction 1 1 Introduction OLE DB is a set of Component Object Model COM interfaces The COM interfaces provide applications with uniform access to data stored in diverse DBMS and non DBMS information sources In addition to supporting many information sources OLE DB also supports the implementation of database services Utilizing these interfaces data consumers easily access data through a consistent method With OLE DB consumers need not consider the storage location of the data the format of the data or the type of data OLE DB Provider for DBMaker is designed for accessing the DBMaker database system OLE DB Provider for DBMaker allows OLE DB programmers to easily develop high performance consumer applications using a host of bundled interfaces OLE DB Provider for DBMaker is specifically designed for use with DBMaker and is incompatible for accessing other information sources Copyright 1995 2008 CASEMaker Inc 1 1 Supported Data Types 2 Supported Data Types The following table shows one way that a DBMaker Provider might map its data types to OLE DB data types DBTYPE_BYTES DBMaker data type OLE DB
6. OleDbType VarWChar 20 Value c_nvarchar myCM Parameters Add date OleDbType DBDate Value c_date myCM Parameters Add int16 OleDbType DBTime Value c_time myCM Parameters Add int16 OleDbType DBTimeStamp Value c_timestamp foreach OleDbParameter para in myCM Parameters Console WriteLine para Value try myCN Open Console WriteLine Connection opened DE int inserted myCM ExecuteNonQuery Console WriteLine 0 rows inserted inserted myCN Close catch Exception ex Console WriteLine ex Message finally if myCN null myCN Close Console WriteLine Je Console WriteLine connection closed Console WriteLine press ENTER to exit Console Read Copyright 1995 2008 CASEMaker Inc
7. c_char AAAAABBBBBCCCCCDDDDD string c_varchar AAAAABBBBBCCCCCDDDDD string c_nchar AAAAABBBBBCCCCCDDDDD string c_nvarchar AAAAABBBBBCCCCCDDDDD DateTimec_date new DateTime 2006 5 22 string c_datel 2006 5 22 TimeSpan c_time new TimeSpan 0 16 35 00 000 string c_timel 16 35 00 DateTimec_timestamp new DateTime 2006 5 22 16 35 00 000 6 16 Copyright 1995 2008 CASEMaker Inc string c _timestamp1 2006 5 22 16 35 00 000 insert data by static SOL command string create a connection string myCNString ProviderDMOLE43 Data Source DBSAMPLE4 myCNString User Id SYSADM Password myCMString insert into OrdinaryType myCMString 001 int16 c02 int32 c03 float c04 double myCMString 005 decimal c06 binary c07 char myCMString c08 varchar c09 nchar c10 nvarchar myCMString cl1_date c12_time c13_timestamp myCMString a c intl6 c int32 myCMString c_float c double myCMString c_decimal myCMString c_char 4 c_varchar myCMString c_nchar c_nvarchar myCMString c_datel c_timel myCMString c timestampl myCMString we establish and open a new connection myCN new OleDbConnection myCNString myCM new OleDbCommand myCMString myCN try myCN Open Console WriteLine Console WriteLine myCMString int inserted myCM Exec
8. exist in one of four states Initial Unprepared Prepared or Executed Parameters are used with commands to bind to consumer variables at execution time A command returns either a single result or multiple results when executed The single result can be either a rowset object or a row count i e the number of rows affected by a command that updates deletes or inserts rows The command can also return multiple results If the command text comprises multiple separate text commands such as a batch of SOL statements or if more than one set of parameters is passed to a command then the results must be returned in a multiple results object The Command object is used to execute an OLE DB Provider for DBMaker text command Text commands are expressed in the OLE DB Provider for DBMaker language and are generally used for creating a rowset for example executing an SOL SELECT statement The Command object cotype is defined as follows OCopyright 1995 2008 CASEMaker Inc COM Object Definitions 3 3 4 CoType TCommand mandatory interface lAccessor mandatory interface IColumnsinfo mandatory interface Command mandatory interface ICommandProperties mandatory interface ICommandText mandatory interface ConvertType optional interface IColumnsRowset optional interface CommandPersist optional interface CommandPrepare optional interface CommandWithParameters optional interface ISupportErrorlnfo
9. type indicator SQL type integer DBTYPE 14 SOL INTEGER smallint DBTYPE 12 SOL SMALLINT float DBTYPE R4 SOL REAL double DBTYPE R8 soL DOUBLE decimal DBTYPE NUMERIC SQL_DECIMAL serial DBTYPE 14 SOL INTEGER DBTYPE BSTR char n DBTYPE WSTR SOL CHAR DBTYPE BSTR varchar n DBTYPE WSTR SOL VARCHAR binary DBTYPE BYTES SOL BINARY varbinary SOL VARBINARY Long varchar n DBTYPE_WSTR SQL_LONGVARCHAR Long varbinary DBTYPE_BYTES SQL_LONGVARBINARY file DBTYPE_BYTES SQL_LONGVARBINARY SQL_FILE DBTYPE DATE DBTYPE BYTES date DBTYPE DBDATE SOL TYPE DATE DBTYPE_DATE time DBTYPE DBTIME SOL TYPE TIME hoc DBTYPE DATE SOL TYPE TIMESTAM p DBTYPE DBTIMESTAMP P DBTYPE BSTR nchar DBTYPE WSTR SQL_WCHAR DBTYPE_BSTR nvarchar DBTYPE WSTR SQL_WVARCHAR blob PRIVEE ESTA SQL_LONGVARBINARY Copyright 1995 2008 CASEMaker Inc 2 1 OV OLE DB User s Guide DBMaker data type OLE DB type indicator SQL type DBTYPE_BSTR clob DBTYPE WSTR SQL_LONGVARCHAR DBTYPE_BSTR nclob DBTYPE WSTR SQL_WLONGVARCHAR NOTE 1 OLE DB Provider for DBMaker supports 38 levels of precision for the decimal data type 2 The data type mapping varies in OLE DB Provider for DBMaker according to the method For example consider the ADO and the ADO NET methods The char data type is mapped to DBTYPE_BSTR using the ADO method however using the ADO NET method the same data type is m
10. 26 2626 26 26 26 26 26 26 26 26 26 26 26 26 26 26 2626 26 262626 26 The table schema used in this sample as following shows create table SYSADM OrdinaryType C00 ID SERIAL 1 C01 INT16 SMALLINT default null C02 INT32 INTEGER default null C03 FLOAT FLOAT default null C04 DOUBLE DOUBLE default null C05 DECIMAL DECIMAL 20 4 default null C06 BINARY BINARY 10 default null C07 CHAR CHAR 20 default null C08 VARCHAR VARCHAR 20 default null C09 NCHAR NCHAR 20 default null C10 NVARCHAR NVARCHAR 20 default null C11_DATE DATE default null C12_TIME TIME default null C13 TIMESTAMP TIMESTAMP default null in DEFTABLESPACE lock mode page fillfactor 100 ke ak aa k k a a ahaa 26 26 26 26 26 26 26 26 26 26 26 26 26 3e 26 26 26 262626 2 3e 262626 26 2626 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 26 262626262 gt K using System using System Data using System Data OleDb This namespaces declarations OLE DB Provider public class InsOrdinaryType_1 i public static void Main string myCNString string myCMString OleDbConnection myCN OleDbCommand myCM short c_intl6 12345 int c_int32 123456 float c_float 12345678 9012F double C double 1234567890 1234567 decimal c_decimal 1234567890123 4567M string binary AAAAABBBBB string c binaryl 41414141414242424242 x byte c binary2 new byte 10 for int i 0 i lt 10 i c_binary2 i byte A string
11. CmdWithParams gt Release 6 12 Copyright 1995 2008 CASEMaker Inc Prepare the command hr pICommandText gt Ouerylnterface IID ICommandPrepare void amp pICommandPrepare if FAILED pICommandPrepare gt Prepare 0 plCommandPrepare gt Release plCommandText gt Release return E FAIL plCommandPrepare gt Release Create parameter accessors if FAILED myCreateParamAccessor pICommandText amp hParamAccessor amp plAccessor plCommandText gt Release return E_FAIL Params pData amp Temp pData is the buffer pointer Params cParamSets 1 Number of sets of parameters Params hAccessor hParamAccessor Accessor to the parameters Specify the parameter information for UINT nCust 0 nCust lt 3 nCust Temp ajob_id aEmployee nCust szjob_id Execute the command hr plCommandText gt Execute NULL II NULL amp Params amp cRowsAffected NULL printf ld rows updated n cRowsAffected plAccessor gt ReleaseAccessor hParamAccessor NULL plAccessor gt Release plCommandText gt Release return NOERROR int main int argc char argv IDBInitialize pIDBInitialize NULL HRESULT hr S_OK static LCID Icid GetSystemDefaultLCIDQ Colnitialize NULL If FAILED CoGetMalloc MEMCTX TASK amp g pIMalloc goto EXIT If FAILED InitDSO amp pIDBInitialize goto EXIT if FAILED TestProperty pIDBInitialize goto EX
12. ColumnsInfo gt Release IAccessor plAccessor NULL Copyright 1995 2008 CASEMaker Inc 6 5 OV OLE DB User s Guide 6 6 HACCESSOR hAccessor 0 DBBINDSTATUS rgStatus 3 0 DBBINDING Bindings 3 0 ULONG acbLengths 9 20 6 for 1 0 i lt 3 i Bindings i iOrdinal i 1 Bindings i obLength 0 Bindings i obStatus 0 Bindings i pTypeInfo NULL Bindings i pObject NULL Bindings i pBindExt NULL Bindings i dwPart DBPART_VALUE Bindings i dwMemOwner DBMEMOWNER_CLIENTOWNED Bindings i eParamIO DBPARAMIO_OUTPUT Bindings i cbMaxLen acbLengths i Bindings i dwFlags 0 Bindings i wType DBTYPE STR 1f i 2 Bindings i wType DBTYPE DBDATF Bindings i bPrecision 0 Bindings i bScale 0 Bindings 0 ob Value offsetof Employee id Bindings 1 obValue offsetof Employee fname Bindings 2 obValue offsetof Employee hire_date plRowset gt QueryInterface IID_IAccessor void amp plAccessor hr plAccessor gt CreateAccessor DBACCESSOR_ROWDATA 3 Bindings 0 amp hAccessor rgStatus plAccessor gt Release Employee emp 0 ULONG cRowsObtained 0 HROW rghRows 100 0 HROW phRows rghRows hr pIRowset gt GetNextRows NULL 0 21 amp cRowsObtained amp phRows for i 0 i lt cRowsObtained i hr pIRowset gt GetData rghRows i hAccessor amp emp if hr S_OK break printf s t s n emp id
13. IT Copyright 1995 2008 CASEMaker Inc 6 13 OV OLE DB User s Guide if FAILED DispalyRowset pIDBInitialize goto EXIT if FAILED My_Sel_Command pIDBInitialize goto EXIT if FAILED Insert WithParameters pIDBInitialize goto EXIT EXIT Clean up and disconnect if pIDBInitialize NULL hr pIDBInitialize gt Uninitialize pIDBInitialize gt Release if g pIMalloc NULL g_pIMalloc gt Release CoUninitialize return 0 6 2 ADO Code Examples in Microsoft Visual Basic Use the following code example to learn how to create a connection via DBMaker OLE DB driver when writing in Visual Basic BeginNewConnection Private Function GetNewConnection As ADODB Connection Dim oCn As New ADODB Connection Dim sCnStr As String establish the connection sCnStr Provider DMOLE43 Data Source oledbtest User Id SYSADM Pwd oCn Open sCnStr If oCn State adStateOpen Then Set GetNewConnection oCn End If End Function EndNewConnection Private Sub Sel Para On Error GoTo ErrHandler Dim objConn As New ADODB Connection Dim objCmd As New ADODB Command Dim objParam As New ADODB Parameter Dim objRs As New ADODB Recordset 6 14 Copyright 1995 2008 CASEMaker Inc Connect to the data source objConn CursorLocation adOpenDynamic Set objConn GetNewConnection objCmd ActiveConnection objConn objCmd Prepared False Set the CommandText as a par
14. OPTIONS_REQUIRED InitProperties i colid DB NULLID Level of prompting that will accompany the connection process InitProperties 0 dwPropertyID DBPROP_INIT_PROMPT InitProperties 0 v Value vt VT_I2 InitProperties 0 v Value iVal DBPROMPT_NOPROMPT Data source name please refer to the sample source included with the OLE DB SDK InitProperties 1 dwPropertyID DBPROP_INIT_DATASOURCE InitProperties 1 v Value vt VT BSTR InitProperties 1 v Value bstrVal SysAllocString OLESTR oledbtest User ID InitProperties 2 dwPropertyID DBPROP_AUTH_USERID InitProperties 2 v Value vt VT BSTR InitProperties 2 v Value bstrVal SysAllocString OLESTR sysadm Password InitProperties 3 dwPropertyID DBPROP_AUTH_PASSWORD InitProperties 3 v Value vt VT BSTR InitProperties 3 v Value bstr Val SysAllocString OLESTR rgInitPropSet guidPropertySet DBPROPSET_DBINIT rgInitPropSet cProperties nProps rgInitPropSet rgProperties InitProperties Set initialization properties Copyright 1995 2008 CASEMaker Inc hr pIDBInitialize gt QueryInterface IID_IDBProperties void amp pIDBProperties hr pIDBProperties gt SetProperties 1 amp rgInitPropSet SysFreeString InitProperties 1 v Value bstrVal SysFreeString InitProperties 2 v Value bstrVal SysFreeString InitProperties 3 vValue bstrVal pIDBProperties gt Release return hr Initi
15. ROPVAL UP INSERT IDBPROPVAL UP DELETE OCopyright 1995 2008 CASEMaker Inc rgProp 5 colid DB_NULLID rgProp 5 dwOptions DBPROPOPTIONS_REQUIRED rgProp 5 dwStatus 0 rgProp 5 dwPropertyID DBPROP_ACCESSORDER rgProp 5 vValue vt VT 14 rgProp 5 vValue I Val DBPROPVAL AO RANDOM rgProp 6 colid DB NULLID rgProp 6 dwOptions DBPROPOPTIONS REOUIRED rgProp 6 dwStatus 0 rgProp 6 dwPropertyID DBPROP IConnectionPointContainer rgProp 6 v Value vt VT BOOL rgProp 6 v Value boolVal VARIANT TRUE hr pIDBInitialize gt QueryInterface IID_IDBCreateSession void amp pIDBCreateSession hr pIDBCreateSession gt CreateSession NULL IID IOpenRowset IUnknown amp plOpenRowset pIDBCreateSession gt Release rgPropSets gt rgProperties rgProp rgPropSets gt cProperties cProperties rgPropSets gt guidPropertySet DBPROPSET ROWSET hr plOpenRowset gt OpenRowset NULL amp TableID NULL IID_IRowset 1 rgPropSets IUnknown amp pIRowset plOpenRowset gt Release if pIRowset IColumnsInfo pIColumnsInfo NULL DBORDINAL cColumns 0 DBCOLUMNINFO prgInfo NULL OLECHAR pstrBuf NULL ULONG i 0 return hr plRowset gt Ouerylnterface IID IColumnslnfo void amp pIColumnsInfo if pIColumnsInfo hr pIColumnsInfo gt GetColumnInfo amp cColumns amp prgInfo amp pstrBuf if SUCCEEDED hr printf GetColumnInfo OK n pI
16. ability or fitness of this computer product for any particular purpose Your dealer is not responsible for any damage caused to this computer product by external forces including sudden shock excess heat cold or humidity nor for any loss or damage caused by incorrect voltage or incompatible hardware and or software Information in this manual has been carefully checked for reliability however no responsibility is assumed for inaccuracies This manual is subject to change without notice Contents 1 Introduction 1 2 2 Supported Data Types snnnrrsnrnenns 2 1 3 COM Object Definitions 3 1 3 17 Data SOU ine ahh Na 3 1 9 2 SESSIONS 4 Late tenant en ne nt EE RS eh vere ANSELM as en 3 1 3 3 COMMANAS 4 E ee RE ns ee ste nn nee te 3 2 I L __ _ _ _ 3 3 4 Interfaces OLE DB 4 1 4 1 OLE DB Provider for DBMaker Supported Interfaces 4 1 5 How to Create an OLE DB Application 5 1 5 1 Establishing a New Connection to a Data Source eeren 5 1 5 2 Executing a Command via OLE DB Driver ecese 5 2 5 3 Processing the Returned Results cuuuuuussssses ona n n aa a e aan nanna eanenn 5 2 G SAampIeSs 5 nn de net 6 1
17. alize a data source HRESULT InitDSO IDBInitialize ppIDBInitialize CoCreatelnstance CLSID DMOLE43 NULL CLSCTX INPROC SERVER IID IDBInitialize void ppIDBInitialize if ppIDBInitialize NULL return E FAIL if FAILED SetInitProps ppIDBInitialize return E FAIL if FAILED ppIDBInitialize gt Initialize return E FAIL return S OK Test property and return its property values in the Data Source HRESULT TestProperty IDBInitialize pIDBInitialize IDBProperties pIDBProperties NULL IRowset pIRowset NULL DBPROPSET rgPropSet NULL DBPROPIDSET rgPropIDSet 1 0 DBPROPID rgProplD 0 HRESULT hr S_OK ULONG cPropSets 0 pIDBInitialize gt QueryInterface IID_IDBProperties void amp pIDBProperties rgPropID DBPROP_CANSCROLLBACKWARDS rgPropIDSet gt cPropertylDs 1 rgPropIDSet gt rgPropertyIDs amp rgPropID rgPropIDSet gt guidPropertySet DBPROPSET_ROWSET if hr pIDBProperties gt GetProperties 1 rgPropIDSet amp cPropSets amp rgPropSet S_OK printf DBPROP_CANSCROLLBACKWARDS failed n return hr printf DBPROP_CANSCROLLBACKWARDS OK n Copyright 1995 2008 CASEMaker Inc 6 3 OV OLE DB User s Guide 6 4 return hr Test rowset and open and return a rowset that includes all rows from a single base table HRESULT DisplayRowset IDBInitialize pIDBInitialize IDBCreateSession pIDBCreateSession NULL
18. ameterized SQL query objCmd CommandText SELECT test char amp _ FROM test_datatype amp _ WHERE test char Char Create new parameter for Test Char Initial value is TestO Set objParam objCmd CreateParameter Test Char adChar _ adParamInput 5 test0 objCmd Parameters Append objParam Execute once and display Set objRs objCmd Execute Txt Rst Text Txt_Rst Text amp vbCrLf amp Char Para amp objParam Value Do While Not objRs EOF Txt_Rst Text Txt_Rst Text amp vbTab amp Result amp objRs 0 objRs MoveNext Loop clean up objRs Close Set objCmd Nothing objConn Close Set objRs Nothing Set objConn Nothing Set objParam Nothing Exit Sub ErrHandler clean up If objRs State adStateOpen Then objRs Close End If If objConn State adStateOpen Then objConn Close End If Set objRs Nothing Set objConn Nothing Set objCmd Nothing If Err lt gt 0 Then MsgBox Err Source gt amp Err Description Error End If End Sub Copyright 1995 2008 CASEMaker Inc 6 15 CI OLE DB User s Guide 6 3 ADO NET Code Examples in Visual C This example demonstrates how to accesses DBMaker via OLE DB provider for DBMaker when writing in C Note This example uses OleDbCommand method to show insert ordinary type data into the database of DBMaker 78 PR FR ARS Se HR eaa a 26 2 26 3e 26 26 26 26 26 jaaha 26 26 262626 262626 2 3e 2626 262626 26
19. apped to DBTYPE WSTR 2 9 Copyright 1995 2008 CASEMaker Inc COM Object Definitions 3 3 1 3 2 COM Object Definitions OLE DB uses Microsoft Corporation s standard for Universal Data Access This is called the COM infrastructure Similar to the ODBC system OLE DB provides a set of APIs however unlike ODBC OLE DB APIs are based entirely on COM In other words operations on abstract objects such as Data source Session Command and Rowset can be accessed via COM OLE DB Provider for DBMaker supports four objects Data source Object Session Object Command Object and Rowset Object These objects are described in the following section of this chapter Data Source A Data source Object is a COM object through which a consumer connects to a provider s underlying data store OLE DB Provider for DBMaker defines its own Data source Object class To connect to the provider a consumer must create and initialize an instance of this class Data source Objects are like factories for session objects The Data Source Object cotype is defined following table CoType TDataSource mandatory interface IDBCreateSession mandatory interface IDBlnitialize mandatory interface IDBProperties mandatory interface IPersist optional interface IConnectionPointContainer optional interface IDBlnfo optional interface IPersistFile Sessions A Session object represents a single connection to a DBMaker databas
20. chStatus No IDBAsynchNotify No IDBinitialize No IRowset Yes IRowsetChange Yes IRowsetFind No IRowsetldentity No IRowsetindex No IRowsetinfo Yes IRowsetLocate No IRowsetRefresh No IRowsetScroll No IRowsetUpdate No IRowsetView No ISupportErrorinfo No Session lAlterindex No IAlterTable No IBindResource No IConnectionPointContainer No ICreateRow No IDBASynchStatus No IDBCreateCommand Yes IDBlnitialize No IDBSchemaRowset Yes IGetDataSource Yes Copyright 1995 2008 CASEMaker Inc CI OLE DB User s Guide 4 4 ITransactionJoin Object Interface Supported Session IlndexDefinition No lOpenRowset Yes ISessionProperties Yes ISupportErrorinfo No ITableDefinition No ITransaction Yes Yes ITransactionLocal Yes ITransactionObject OCopyright 1995 2008 CASEMaker Inc How to Create an OLE DB Application 5 5 1 How to Create an OLE DB Application Programming an OLE DB application involves three steps 1 Establishing a new connection to a data source 2 Executing a command via OLE DB driver 3 Processing the returned results Establishing a New Connection to a Data Source Creating an instance of the data source object of the provider is the first task of an OLE DB consumer The basic steps for creating a data source are 1 Initialize the COM library by calling Colnitialize NULL 2 Create an instance of a data source object by callin
21. ct from employee where job_id WCHAR wSOI String L update employee set fname LingAn where job id DBPARAMS Params 0 HRESULT hr S_OK long cRowsAffected 0 HACCESSOR hParamAccessor 0 TRowset pIRowset NULL DBORDINAL rgParamOrdinals 1 0 DBPARAMBINDINFO rgParamBindInfo 1 0 employee aEmployee 5 6 7 employee Temp 0 ULONG nParams 1 rgParamOrdinals 0 1 rgParamBindInfo 0 bPrecision 0 rgParamBindInfo 0 bScale 0 rgParamBindInfo 0 dwFlags DBPARAMFLAGS_ISINPUT rgParamBindInfo 0 pwszDataSourceType unsigned short L DBTYPE_I2 rgParamBindInfo 0 pwszName NULL rgParamBindInfo 0 ulParamSize sizeof SHORT Get the session hr pIDBInitialize gt Ouerylnterface IID IDBCreateSession void amp pIDBCreateSession hr pIDBCreateSession gt CreateSession NULL IID IDBCreateCommand IUnknown amp pIDBCreateCommand pIDBCreateSession gt Release Create the command hr pIDBCreateCommand gt CreateCommand NULL IID ICommandText IUnknown amp plCommandText pIDBCreateCommand gt Release The command requires the actual text and a language indicator hr plICommandText gt SetCommandText DBGUID DBSOL wSOL String Set parameter information hr pICommandText gt Ouerylnterface IID ICommandWithParameters void amp pICmdWithParams hr pICmdWithParams gt SetParameterInfo nParams rgParamOrdinals rgParamBindInfo pl
22. e The Session object exposes the interfaces that allow data access and data manipulation A single Data source Object may be able to create multiple sessions Session objects are factories for Command and Rowset objects which provide methods for creating Command objects and rowsets and modifying tables and indexes Session objects can also function as factories for transaction objects Transaction objects are used for controlling nested transactions OCopyright 1995 2008 CASEMaker Inc 3 1 OV OLE DB User s Guide 3 3 3 2 The Session object is removed from memory and the connection is dropped after all references to the Session object are released The Session object cotype is defined beow CoType TSession mandatory interface GetDataSource mandatory interface IOpenRowset mandatory interface ISessionProperties optional interface IAlterlndex optional interface lAlterTable optional interface IBindResource optional interface ICreateRow optional interface IDBCreateCommand optional interface IDBSchemaRowset optional interface IIndexDefinition optional interface ISupportErrorlnfo optional interface ITableCreation optional interface ITableDefinition optional interface ITableDefinitionWithConstraints optional interface ITransaction optional interface ITransactionJoin optional interface ITransactionLocal optional interface ITransactionObject Commands Commands
23. ecute an insert command with parameter HRESULT InsertWithParameters IDBInitialize pIDBInitialize IDBCreateSession pIDBCreateSession NULL IDBCreateCommand pIDBCreateCommand NULL ICommandText pICommandText NULL ICommandPrepare pICommandPrepare NULL ICommandWithParameters pICmdWithParams NULL IAccessor plAccessor NULL WCHAR wSQLString TEXT insert into eemployee values DBPARAMS Params 0 HRESULT hr S_OK long cRowsAffected 0 HACCESSOR hParamAccessor 0 EEmployee aEmployee 1001 Terrible Fang 1002 David Chen 1003 Alen Wu EEmployee Temp 0 ULONG nParams 3 Copyright 1995 2008 CASEMaker Inc OV OLE DB User s Guide 6 10 pIDBInitialize gt QueryInterface IID_IDBCreateSession void amp pIDBCreateSession pIDBCreateSession gt CreateSession NULL IID IDBCreateCommand IUnknown amp pIDBCreateCommand pIDBCreateSession gt Release Create the command pIDBCreateCommand gt CreateCommand NULL IID ICommandText IUnknown amp pICommandText pIDBCreateCommand gt Release The command requires the actual text and a language indicator plCommandText gt SetCommandText DBGUID DBSOL wSOL String Prepare the command hr plCommandText gt Ouerylnterface IID ICommandPrepare void amp pICommandPrepare if FAILED pICommandPrepare gt Prepare 0 plCommandPrepare gt Release plCommandText gt Relea
24. erface The consumer can execute the IDBCreateCommand CreateCommand method to create a command object and then reguest the ICommandText interface The ICommandText SetCommandText method is used for specifying the command to be executed Lastly the command is executed using the Execute command Commands like SELECT FROM Authors produce a result set rowset object The consumer requests the lOpenRowset interface for working directly with individual tables or indexes The lOpenRowset OpenRowset method opens and returns a rowset that includes all rows from a single base table or index Processing the Returned Results The consumer must retrieve and access data in a rowset when the rowset object is produced by either the execution of a command or the generation of a rowset object directly by the provider Rowsets are central objects enabling all OLE DB data providers to expose data in tabular form The rowset comprises a set of rows Each row contains column data A rowset object facilitates access by exposing various interfaces For example IRowset is an interface containing methods for sequentially fetching rows from the rowset IAccessor is an interface for defining a group of column bindings describing how tabular data is bound to consumer program variables The IColumnlnfo interface provides information about columns in the rowset The IRowsetlnfo interface provides information about rowset The consumer can call the IRowset GetData met
25. g the CoCreatelnstance method The syntax is TDAPI CoCreatelnstance REFCLSID rclsid LPUNKNOWN pUnkOuter DWORD dwClsContext REFIID riid LPVOID ppv A unigue class identifier CLSID identifies each OLE DB provider For DMOLE43 the class identifier is CLSID DMOLE43 3 Thedata source object exposes the IDBProperties interface The consumer uses the IDBProperties to provide basic authentication information such as server name database name user ID and password These properties are set by calling the IDBProperties SetProperties method 4 The data source object also exposes the IDBlnitialize interface Establish a connection to the data source by calling the IDBlnitialize Initialize method Copyright 1995 2008 CASEMaker Inc 5 1 CI OLE DB User s Guide 5 2 5 3 5 2 Executing a Command via OLE DB Driver The consumer calls the IDBCreateSession CreateSession method to create a session after the connection to a data source is established The session functions as a command rowset or transaction factory Session objects can create Command objects The command object of OLE DB Provider for DBMaker supports the execution of SQL commands Additionally the Command object of OLE DB Provider for DBMaker supports multiple parameters Consider the following example of executing a command A consumer wants to execute the command SELECT FROM Authors To begin the consumer requests the IDBCreateCommand int
26. hod to retrieve a row of data from the rowset into a buffer The consumer must describe the buffer using a set of DBBINDING structures before GetData is called During data retrieval the provider uses information in each binding to determine where and how to retrieve data from the consumer buffer When setting data in the consumer buffer the provider uses information in each binding to determine where and how to return data in the consumer buffer After the DBBINDING structures are specified an accessor is created by calling the IAccessor CreateAccessor method An accessor is a collection of bindings and is used to retrieve or set the data in the consumer buffer OCopyright 1995 2008 CASEMaker Inc 6 1 Samples The sample provided here demonstrates rowset programming and an object model for an OLE DB consumer The sample creates a data source a session and rowset objects allows the user to display and navigate the rows in the rowset and handles errors Command line switches are used to specify when an enumerator class ID user prompt or connection string is used to create the data source object a command is used to create the rowset and so on NOTE There are three code examples in this chapter The C sample program shows a basic implementation of the OLE DB Provider for DBMaker The Visual Basic sample program accesses OLE DB Provider for DBMaker through ADO methods The C sample program accesses OLE DB Provider for DBMake
27. implementations Most interfaces can also be implemented by developers designing OLE DB applications This chapter summarizes the OLE DB interfaces that are supported by the current version of the OLE DB Provider for DBMaker OLE DB Provider for DBMaker Supported Interfaces The following table summarizes the OLE DB interfaces that are supported by the current version of the OLE DB Provider for DBMaker For more information about the interfaces please refer to MSDN Copyright 1995 2008 CASEMaker Inc 4 CI OLE DB User s Guide 4 2 Object Interface Supported Command lAccessor Yes IColumnsinfo Yes IColumnsRowset Yes ICommand Yes ICommandPersist No ICommandPrepare Yes ICommandProperties Yes ICommandText Yes ICommandWithParameters Yes IConvertType Yes IDBInitialize No ISupportErrorinfo No Data Source IConnectionPointContainer No IDBAsynchStatus No IDBAsynchNotify No IDBCreateSession Yes IDBInfo Yes IDBInitialize Yes IDBProperties Yes IPersist Yes IPersistFile No ISupportErrorinfo No Error IErrorinfo No OCopyright 1995 2008 CASEMaker Inc Interfaces OLE DB 4 Object Interface Supported Rowset lAccessor Yes IColumnsinfo Yes IColumnsRowset Yes IConnectionPointContainer No IConvertType Yes IDBAsyn
28. nfo NULL Bindings i pObject NULL Bindings i pBindExt NULL Bindings i dwPart DBPART_VALUE Bindings i dwMemOwner DBMEMOWNER_CLIENTOWNED Bindings i eParamIO DBPARAMIO_OUTPUT Bindings i cbMaxLen acbLengths i Bindings i dwFlags 0 Bindings i wType DBTYPE STR if i 2 Bindings i wType DBTYPE_DBDATE Bindings i bPrecision 0 Bindings i bScale 0 Bindings 0 ob Value offsetof Employee id Bindings 1 ob Value offsetof Employee fname Bindings 2 ob Value offsetof Employee hire_date Copyright 1995 2008 CASEMaker Inc 6 7 OV OLE DB User s Guide 6 8 plRowset gt QueryInterface IID_IAccessor void amp plAccessor hr plAccessor gt CreateAccessor DBACCESSOR_ROWDATA 3 Bindings 0 amp hAccessor rgStatus Employee emp 0 ULONG cRowsObtained 0 HROW rghRows 100 0 HROW phRows rghRows plRowset gt GetNextRows DB_NULL_HCHAPTER 1 1 amp cRowsObtained amp phRows for i 0 i lt cRowsObtained i pIRowset gt GetNextRows DB NULL HCHAPTER 0 1 2 amp cRowsObtained amp phRows hr pIRowset gt GetData rghRows i hAccessor amp emp if hr S_OK break printf s n emp id plAccessor gt ReleaseAccessor hAccessor NULL plAccessor gt Release plRowset gt Release plCommandText gt Release return S_OK Create accessor HRESULT CreateParamAccessor ICommand plCmd in HACCESSOR phAcces
29. ngs i obLength 0 Bindings i obStatus 0 Bindings i pTypeInfo NULL Bindings i pObject NULL Bindings i pBindExt NULL Bindings i dwPart DBPART_VALUE Bindings i dwMemOwner DBMEMOWNER_CLIENTOWNED Bindings i eParamIO DBPARAMIO INPUT Bindings i cbMaxLen acbLengths i Bindings i dwFlags 0 Bindings i wType DBTYPE_I2 Bindings i bPrecision 0 Bindings i bScale 0 Bindings 0 ob Value offsetof employee ajob id pICmd gt Ouerylnterface IID IAccessor void amp plAccessor hr plAccessor gt CreateAccessor DBACCESSOR PARAMETERDATA Accessor for specifying parameter data nParams Number of parameters being bound Bindings Structure containing bind information sizeof employee Size of parameter structure amp hAccessor Returned accessor handle rgStatus Information about binding validity pplAccessor plAccessor phAccessor hAccessor return hr Execute a command with a parameter HRESULT My Command Para IDBlnitialize pIDB Initialize Copyright 1995 2008 CASEMaker Inc 6 11 OV OLE DB User s Guide IDBCreateSession pIDBCreateSession NULL IDBCreateCommand pIDBCreateCommand NULL ICommandText pICommandText NULL ICommandPrepare pICommandPrepare NULL ICommandWithParameters pICmdWithParams NULL IAccessor plAccessor NULL WCHAR wSOI String L delete from employee where job_id WCHAR wSOI String L sele
30. r through ADO NET methods OLE DB Consumer Application Microsoft Visal C Examples This example demonstrates how to initialize a data source and how to accesses database of DBMaker by OLE DB provider for DBMaker in C include stdafx h define UNICODE define UNICODE define DBINITCONSTANTS define INITGUID define BLOCK_SIZE 512 define DMOLE43 include lt windows h gt include lt stdio h gt Input and output functions include lt stddef h gt for macro offset include lt oledb h gt OLE DB include files include lt oledberr h gt OLE DB Errors include lt Ks h gt include lt Guiddef h gt include lt comsvcs h gt include lt atlbase h gt include dmdasgl h static IMalloc g pIMalloc NULL typedef struct LONG bookmark Copyright 1995 2008 CASEMaker Inc 6 1 CI OLE DB User s Guide 6 2 char id 9 char fname 20 DBDATE hire date Employee typedef struct char id 10 char fname 20 char Iname 20 EEmployee typedef struct tagemployeel short szjob_id employee1 HRESULT SetInitProps IDBlnitialize pIDB Initialize const ULONG nProps 4 IDBProperties pIDBProperties NULL DBPROP InitProperties nProps 0 DBPROPSET rglnitPropSet 0 HRESULT hr S_OK Initialize common property options for ULONG i 0 i lt nProps i VariantInit amp InitProperties i v Value InitProperties i dwOptions DBPROP
31. se return E FAIL plCommandPrepare gt Release Create parameter accessors if FAILED CreateParamAccessor plCommandText amp hParamAccessor amp plAccessor plCommandText gt Release return E FAIL Params pData amp Temp pData is the buffer pointer Params cParamSets 1 Number of sets of parameters Params hAccessor hParamAccessor Accessor to the parameters Specify the parameter information for UINT nCust 0 nCust lt 3 nCust strcpy Temp id aEmployee nCust id strcpy Temp fname aEmployee nCust fname strcpy Temp lname aEmployee nCust Iname Execute the command hr plCommandText gt Execute NULL IID NULL amp Params amp cRowsAffected NULL printf ld rows inserted n cRowsAffected plAccessor gt ReleaseAccessor hParamAccessor NULL plAccessor gt Release plCommandText gt Release return S_OK Copyright 1995 2008 CASEMaker Inc Create accessor HRESULT myCreateParamAccessor ICommand pICmd in HACCESSOR phAccessor out IAccessor pplAccessor out IAccessor plAccessor NULL HACCESSOR hAccessor 0 const ULONG nParams 1 DBBINDING Bindings nParams 0 DBBINDSTATUS rgStatus nParams 0 Return information for individual binding validity HRESULT hr S_OK ULONG acbLengths 2 for ULONG i 0 i lt nParams i Bindings i iOrdinal i 1 Bindi
32. sor out IAccessor pplAccessor out IAccessor plAccessor NULL HACCESSOR hAccessor NULL const ULONG nParams 3 DBBINDING Bindings nParams 0 DBBINDSTATUS rgStatus nParams 0 HRESULT hr S_OK ULONG acbLengths 10 20 20 for ULONG i 0 i lt nParams i Bindings i iOrdinal i 1 Bindings i obLength 0 Bindings i obStatus 0 Bindings i pTypeInfo NULL Copyright 1995 2008 CASEMaker Inc Bindings i pObject NULL Bindings i pBindExt NULL Bindings i dwPart DBPART_VALUE Bindings i dwMemOwner DBMEMOWNER_CLIENTOWNED Bindings i eParamIO DBPARAMIO_INPUT Bindings i cbMaxLen acbLengths i Bindings i dwFlags 0 Bindings i wType DBTYPE STR Bindings i bPrecision 0 Bindings i bScale 0 Bindings 0 ob Value offsetof EEmployee id Bindings 1 obValue offsetof EEmployee fname Bindings 2 ob Value offsetof EEmployee Iname pICmd gt Ouerylnterface IID IAccessor void amp plAccessor hr plAccessor gt CreateAccessor DBACCESSOR_PARAMETERDATA Accessor used to specify parameter data nParams Number of parameters being bound Bindings Structure containing bind information sizeof EEmployee Size of parameter structure amp hAccessor Returned accessor handle rgStatus Information about binding validity pplAccessor plAccessor phAccessor hAccessor return hr Ex
33. uteNonQuery Console WriteLine 0 rows inserted inserted myCN Close catch Exception ex Console WriteLine ex Message finally if myCN null myCN Close Console WriteLine Console WriteLine connection closed Console WriteLine press ENTER to continue Console Read insert data by SQL command with parameter myCMString insert into OrdinaryType myCMString c0l_int16 c02_int32 c03_float c04_double myCMString c05_decimal c06_binary c07_char myCMString c08 varchar c09 nchar c10 nvarchar myCMString cl1_date c12_time c13 me myCMString values 7 7 2 2 7 7 7 2 7 7 7 2 7 myCM new OleDbCommand myCMString myCN myCM Parameters Add int16 OleDbType SmallInt Value c intl6 myCM Parameters Add int32 OleDbType Integer Value c int32 myCM Parameters Add float OleDbType Single Value c float myCM Parameters Add double OleDbType Double Value c double myCM Parameters Add amp decimal OleDbType Decimal Value c decimal myCM Parameters Add amp binary OleDbType Binary 10 Value OCopyright 1995 2008 CASEMaker Inc OV OLE DB User s Guide 6 18 c binary2 myCM Parameters Add char OleDbType Char 20 Value c char myCM Parameters Add varchar OleDbType VarChar 20 Value c_varchar myCM Parameters Add nchar OleDbType WChar 20 Value c_nchar myCM Parameters Add nvarchar
Download Pdf Manuals
Related Search