数据库访问

来源：互联网发布：windows xp原版主题编辑：程序博客网时间：2024/05/15 23:53

http://msdn.microsoft.com/en-us/library/cc811599.aspxHomeLibraryLearnDownloadsSupportCommunity Sign in |中国（简体中文） |Preferences MSDN LibraryOffice Development2007 Microsoft Office SystemAccess 2007Technical ArticlesDeveloping Access 2007 Solutions with Native C or C++Community ContentAdd code samples and tips to enhance this topic.More...Developing Access 2007 Solutions with Native C or C++Office 2007Summary: Learn about the architecture of Microsoft Office Access 2007, its new ACE engine and features, and find out what things that you should consider when you choose the most optimal native data access API for your new or legacy database solution. Get started quickly developing C or Microsoft Visual C++ 2008 solutions for Access databases. (11 printed pages)Aleksandar Jakšić, Microsoft CorporationAugust 2008Applies to: Microsoft Office Access 2007Related Technologies: Microsoft Office Access 2007 Runtime, Microsoft Data Access Components (MDAC) 2.8, MDAC 2.8 Service Pack (SP) 1 for Windows XP SP2, MDAC 2.8 SP2 for Windows Server 2003 SP1, Microsoft Windows Data Access Components (Windows DAC) 6.0, Microsoft Visual C++ 2008ContentsOverviewWhat About the Microsoft JET Engine?Code ExamplesPrerequisitesAccess 2007 File FormatsOverview of Data Access TechnologiesDAOOLE DBADOODBCPerformance ResultsConsiderations when Choosing Data Access TechnologyDeprecated Data Access MethodsConclusionOverviewIf you are a database developer and you want to know more about the options that are available to you when you develop a native solution for Microsoft Office Access 2007 using C or C++, this article is written for you. The subject of data access technologies is large, and because many APIs, DLLs, and frameworks interact with the new engine in Access 2007, which is known as the Microsoft Access Database Engine (ACE Engine), this guide is directed toward a broad developer audience. This article does not give you detailed descriptions of every data access technology mentioned in this article. Instead, it describes the data access options available to you when you work with Access 2007 solutions and the advantage and disadvantage of each approach. The accompanying code examples are intended to help you get started quickly, even if you have never programmed in C or C++ before. This article answers questions such as the following:What’s the fastest way to programmatically work with the Access databases generally?Is there a way to connect to the ACE engine using the C programming language (ANSI/ISO C)?Can I use my legacy Microsoft Foundation Classes (MFC) Data Access Object (DAO) code to work with Access 2007 databases?What About the Microsoft JET Engine?Access 2007 introduces an architectural change that affects all data access technologies that are available to Access developers.Prior to Access 2007, Access used the Microsoft Joint Engine Technology (JET) engine for its data storage and query processing. Even though JET was generally accepted as part of Access, since Microsoft Windows 2000 it was included as part of the Windows operating system. However, the JET engine is no longer distributed with the latest Microsoft Data Access Components (MDAC). As a result, Access 2007 provides a new engine based on JET, which is known as the Microsoft Access Engine (ACE Engine), instead of using the deprecated Microsoft JET engine.The ACE engine is fully backward-compatible with previous versions of the JET engineso that it reads and writes (.mdb) files from earlier Access versions. Access 2007 also introduces a new default file format (.accdb) that supports many product improvements. Because the Access team now owns its engine, developers can be confident that their Access solutions will continue to work in the future.Because the ACE engine is not part of the Windows operating system, application users must have Access 2007 or download the free Access 2007 Download: Access Runtime. You can also use the 2007 Office System Driver: Data Connectivity Components installed on your computer. Code ExamplesThis article presents five complete programs created by using Microsoft Visual C++ 2008 that are available for download. Source code is compatible with older versions of Visual C++. However, you might have to create additional project files for the Visual C++ version with which you want to compile this code.All programs perform semantically-identical steps by demonstrating syntactical differences in the data access technologies that are used. Moreover, the console output of each program is almost identical. Examples present the basics of data access that include how to connect to a database, and how to work with a recordset on an SQL query.Download sample files: Access 2007 Sample: Developing Access 2007 Solutions with Native C or C++Here is the list of steps that are performed in each code example:1.Define the connection string.2.Connect to the C:/Northwind 2007.accdb database.3.Verify the connection and display data source name.4.Build the SQL query.5.Execute the query and create a recordset.6.Retrieve and display a schema of the recordset.7.Retrieve and display the actual data.8.Display the total number of retrieved rows.9.Close all handles. 10.Handle the errors and exceptions when it is required.I used similar code to measure an overall speed against these libraries. Additionally, the performance results are published in this article.Security Note: The code examples show a concept and should not be used in applications or Web sites, as they do not show safe coding practices. For example, all of the code examples hard code the default user name (Admin) and a blank password. This is not recommended. Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access. PrerequisitesIt is assumed that you are comfortable with native C or C++ programming, the Visual Studio environment, and are familiar with the concept of relational databases and SQL.Access 2007 File FormatsThis section summarizes the new and legacy file name extensions that are used by Access 2007. Access 2007 introduces a new default file format, .accdb, that supports several improvements in the product that include complex data types such as multivalue fields, the Attachment data type and history tracking in Memo fields. It also offers better integration with Microsoft Office SharePoint Server 2007 and Microsoft Office Outlook 2007, as well as security and encryption improvements. Although Access 2007 continues to provide support for many of the file formats that were used in previous versions (including .mdb databases) for backward-compatibility, the new .accdb file format is set as the future standard. It is recommended that you use the new file formats to take advantage of the many new features and benefits that they provide.Among the other new file formats is .accde, which is the file name extension for Access 2007 files that are compiled into an "execute only" file. This format replaces the .mde file name extension that is used in previous versions of Access. The .accdr file is a file name extension that enables you to open a database in run-time mode and .accdt is the file name extension for Access Database Templates.For more information about the Access file formats, see Developer Considerations for Choosing File Formats in Access 2007.Overview of Data Access TechnologiesVisual C++ 2008 provides several ways to work with Access databases. The following technologies are traditionally available to the Access developers who are writing their applications in a C or C++ environment:Data Access Object (DAO)OLE DBActiveX Data Objects (ADO)Open Database Connectivity (ODBC)Although Access engine providers (components such as the ACE version of DAO, the ACE OLE DB provider and the ACE ODBC provider) were originally included with Windows as part of Microsoft Data Access Components (MDAC), they are now distributed with Access (except ADO which is still part of the Microsoft Windows DAC). ODBC, OLE DB, and ADO themselves are all system-level components.The following figure presents the architecture of the ACE engine in the native programming environment. From the data access perspective, you can view Access as the way the ACE engine is exposed graphically to users.Figure 1. Architecture of the ACE engine in the native programming environment.The following table summarizes the native data access methods.Table 1. Native Data Access MethodsProvider Data Access Method Header File & Connection Info Supported Language Additional Comments ACE DAO Direct DAO acedao.tlh (generated from acedao.dll); acedao.dll C++ Supports new .accdb format files. This method is recommended for native projects because it exposes many of the new features that are introduced in the new ACE engine. This method is currently available only for the 32-bit version of the Windows operating system. Microsoft Access Engine OLE DB Provider (ACE Provider) ATL OLE DB and ; aceoledb.dllMicrosoft.ACE.OLEDB.12.0 C++ Supports new .accdb format files together with limited support for new features such as complex data and security. This method is currently available only for the 32-bit version of the Windows operating system. ADO ADO msado15.tlh (generated from msado15.dll); Installed with MDAC 2.8 or Windows DAC 6.0. C++ Supports new .accdb format files together with limited support for new features such as complex data and security. This method is available for the 64-bit version of the Windows operating system as part of DAC 6.0. It also serves as an alternative to OLE DB provider. Access ODBC Driver Direct ODBC ; aceodbc.dll;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file C,C++ Supports new .accdb format files. This method is currently available only for the 32-bit version of the Windows operating system. It is recommended only for maintenance of existing applications or when that you need to work with ANSI/ISO C. Access ODBC Driver MFC ODBC ; aceodbc.dll;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file C++ Supports new .accdb format files. This data access method is recommended only for maintenance of the existing applications. Forf a list of data access methods that are deprecated with Access 2007 release, see Deprecated Data Access Methods.DAOPreviously, Data Access Objects (DAO) was the exclusive data access method for Access developers. There are two ways to connect to an Access 2007 database using the DAO API:MFC DAO. The MFC DAO classes give the user a way to use the legacy Microsoft JET Database Engine. However, they are not supported in Access 2007. DAO 3.6 is the final version of this technology. It is not available on the 64-bit Windows operating system. Visual C++ .NET wizards do not generate code that automatically creates and opens recordsets. Direct DAO. This access method works with Access 2007 databases. It provides the best support for new features that are introduced with Access 2007 because it exposes most of the ACE engine functionality.Direct DAO ExampleDirect DAO uses acedao.dll. To compile this code, use the #import macro to generate .tlh header by specifying directive similar to the one shown in the following example.VBC#C++F#JScriptCopy#import rename( "EOF", "AdoNSEOF" )Because we connect directly to the engine, use the database location for the connection information.VBC#C++F#JScriptCopy_bstr_t bstrConnect = "C://Northwind 2007.accdb";The following example fetches the schema and data.VBC#C++F#JScriptCopy// Create an instance of the engine.DAO::_DBEngine* pEngine = NULL;// The CoCreateInstance helper function provides a convenient shortcut by connecting // to the class object associated with the specified CLSID, creating an // uninitialized instance, and releasing the class object. hr = CoCreateInstance( __uuidof(DAO::DBEngine), NULL, CLSCTX_ALL, IID_IDispatch, (LPVOID*)&pEngine);if (SUCCEEDED(hr) && pEngine){ // COM errors are handled by the C++ try/catch block. try { DAO::DatabasePtr pDbPtr = NULL; pDbPtr = pEngine->OpenDatabase(bstrConnect); if (pDbPtr) { cout<GetName()<OpenRecordset(query, _variant_t(DAO::dbOpenDynaset)); if (pRS && 0 < pRS->RecordCount) { cout<GetFields(); if (pFields && pFields->Count > 0) { for (short nIndex=0; nIndex < pFields->Count; nIndex++) { cout<<" | "<GetItem(nIndex)->GetName(); } cout<AdoNSEOF) { for (short nIndex=0; nIndex < pFields->Count; nIndex++) { cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetValue()); } cout<MoveNext(); } cout<RecordCount<Close(); pDbPtr->Close(); pDbPtr = NULL; } else { cout<Release(); pEngine = NULL; cout< cmd; hr = cmd.Open(dbSession, query); DBORDINAL colCount = cmd.GetColumnCount(); if (SUCCEEDED(hr) && 0 < colCount) { cout<* pRS = (CRowset*)&cmd; // Loop through the rows in the result set. while (pRS->MoveNext() == S_OK) { for (int col=1; col <= (int)colCount; col++) { CHAR* szValue = cmd.GetString(col); cout<<" | "< rename( "EOF", "AdoNSEOF" )For connection information, use the Microsoft.ACE.OLEDB.12.0 data provider implemented in aceoledb.dll.VBC#C++F#JScriptCopy_bstr_t bstrConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C://Northwind 2007.accdb;";The following example fetches the schema and data.VBC#C++F#JScriptCopy// Shows the Data Access Method used in this sample.const char* DAM = "ADO";ADODB::_ConnectionPtr pConn("ADODB.Connection");hr = pConn->Open(bstrConnect, "admin", "", ADODB::adConnectUnspecified);if (SUCCEEDED(hr)){ cout<GetConnectionString()<Open(query, _variant_t((IDispatch *) pConn, true), ADODB::adOpenUnspecified, ADODB::adLockUnspecified, ADODB::adCmdText); if (SUCCEEDED(hr)) { cout<get_Fields(&pFields); if (SUCCEEDED(hr) && pFields && pFields->GetCount() > 0) { for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++) { cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetName()); } cout<AdoNSEOF) { for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++) { cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetValue()); } cout<MoveNext(); rowCount++; } cout<Close(); pConn->Close(); cout<.VBC#C++F#JScriptCopychar szDSN[256] = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C://Northwind 2007.accdb;";The following example fetches the schema and data. Note: This code is written in C not Visual C++. VBC#C++F#JScriptCopy/* Data Access Method used in this sample */const char* DAM = "Direct ODBC";HENV hEnv;HDBC hDbc;/* ODBC API return status */RETCODE rc;int iConnStrLength2Ptr;char szConnStrOut[256];unsigned char* query = "SELECT Customers.[Company], Customers.[First Name] FROM Customers;";SQLCHAR chval1[128], chval2[128], colName[128];int ret1;int ret2;/* Number of rows and columns in result set */SQLINTEGER rowCount = 0;SQLSMALLINT fieldCount = 0, currentField = 0;HSTMT hStmt;/* Allocate an environment handle */rc = SQLAllocEnv(&hEnv);/* Allocate a connection handle */rc = SQLAllocConnect(hEnv, &hDbc);/* Connect to the 'Northwind 2007.accdb' database */rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, SQL_NTS, (unsigned char*)szConnStrOut, 255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);if (SQL_SUCCEEDED(rc)) { printf("%s: Successfully connected to database. Data source name: /n %s/n", DAM, szConnStrOut); /* Prepare SQL query */ printf("%s: SQL query:/n %s/n", DAM, query); rc = SQLAllocStmt(hDbc,&hStmt); rc = SQLPrepare(hStmt, query, SQL_NTS); /* Bind result set columns to the local buffers */ rc = SQLBindCol(hStmt, 1, SQL_C_CHAR, chval1, 128, (SQLINTEGER*)&ret1); rc = SQLBindCol(hStmt, 2, SQL_C_CHAR, chval2, 128, (SQLINTEGER*)&ret2); /* Excecute the query and create a record set */ rc = SQLExecute(hStmt); if (SQL_SUCCEEDED(rc)) { printf("%s: Retrieve schema info for the given result set:/n", DAM); SQLNumResultCols(hStmt, &fieldCount); if (fieldCount > 0) { for (currentField=1; currentField <= fieldCount; currentField++) { SQLDescribeCol(hStmt, currentField, colName, sizeof(colName), 0, 0, 0, 0, 0); printf(" | %s", colName); } printf("/n"); } else { printf("%s: Error: Number of fields in the result set is 0./n", DAM); } printf("%s: Fetch the actual data:/n", DAM); /* Loop through the rows in the result set */ rc = SQLFetch(hStmt); while (SQL_SUCCEEDED(rc)) { printf(" | %s | %s/n", chval1, chval2); rc = SQLFetch(hStmt); rowCount++; }; printf("%s: Total Row Count: %d/n", DAM, rowCount); rc = SQLFreeStmt(hStmt, SQL_DROP); }}else{ printf("%s: Couldn't connect to %s./n", DAM, szDSN);}/* Disconnect and free up allocated handles */SQLDisconnect(hDbc);SQLFreeHandle(SQL_HANDLE_DBC, hDbc);SQLFreeHandle(SQL_HANDLE_ENV, hEnv);printf("%s: Cleanup. Done./n", DAM);MFC ODBC ExampleThe following example shows the connection information for MFC ODBC, which uses {Microsoft Access Driver (*.mdb, *.accdb)} driver implemented in aceodbc.dll library by including .VBC#C++F#JScriptCopyLPCTSTR lpszConnect = _T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C://Northwind 2007.accdb;");The following example fetches the schema and data.VBC#C++F#JScriptCopy// Data Access Method used in this sample.const char* DAM = "MFC ODBC";CDatabase db;TRY{ result = db.OpenEx(lpszConnect, CDatabase::openReadOnly | CDatabase::noOdbcDialog); if (FALSE == result) { cout< 0) { for (short nIndex=0; nIndex < sFieldCount; nIndex++) { CODBCFieldInfo fInfo; rs.GetODBCFieldInfo(nIndex, fInfo); cout<<" | "<GetBuffer(var.m_pstring->GetLength())); break; case DBVT_ASTRING: value.Format("%s", var.m_pstringA->GetBuffer(var.m_pstringA->GetLength())); break; case DBVT_WSTRING: value.Format("%s", var.m_pstringW->GetBuffer(var.m_pstringW->GetLength())); break; default: value = ""; } cout<<" | "<GetErrorMessage(errMsg, 255); cout<

数据库访问

访问数据库

JDBC-ODBC访问数据库，JDBC访问数据库

U盘病毒