vcg02.htm

Visual C++与数据库的连接经典实例

<BR>

<P>The database attribute functions are used to provide information to the application about the connection, driver, and datasource. These functions are often used in front-end applications. Other functions in this group set options for the datasource for the application. The following list shows the database attribute functions. The functions in the first column are supported by all datasources, and those in the second column might not be supported by all datasources.

<BR>

<BR>

<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Supported by All Datasources</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Not Supported by All Datasources</I>

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<A NAME="I2"></A>GetConnect()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetLoginTimeout()

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

IsOpen()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetQueryTimeout()

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

GetDatabaseName()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SetSynchronousMode()

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

CanUpdate()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

CanTransact()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

InWaitForDataSource()

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>

</TABLE></CENTER><P>The GetConnect() function is used to return the ODBC connect string that was used to connect the CDatabase object to a datasource. There are no parameters to the GetConnect() function, and it returns a CString object reference. The GetConnect() function's prototype is

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">const CString&amp; GetConnect();</FONT></PRE>

<P>If there is no current connection, the returned CString object will be empty.

<BR>

<P>The IsOpen() function is used to determine whether a datasource is currently connected to the CDatabase object. This function returns a nonzero value if there is currently a connection and a zero value if no connection is currently open. For an example of IsOpen()<B>,</B> see the earlier discussion of the Open() function.

<BR>

<P>The GetDatabaseName() function returns the name of the database currently in use. GetDatabaseName()<B> </B>returns a CString object. Its prototype is

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">CString GetDatabaseName();</FONT></PRE>

<P>The GetDatabaseName() function returns the database name if there is one. Otherwise, it returns an empty CString object.

<BR>

<P>The CanUpdate() function returns a nonzero value if the database can be updated (by either modifying records or adding new records). If the database can't be modified, the CanUpdate() function returns a zero value. CanUpdate()<B> </B>takes no parameters and has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">BOOL CanUpdate();</FONT></PRE>

<P>The ability to update a database is based both on how it was opened (how you set the read-only parameter in Open()) and on the capabilities of the ODBC driver. Not all ODBC drivers support the updating of databases.

<BR>

<P>The CanTransact() function returns a nonzero value if the datasource supports transactions. (See the section &quot;Database Operations&quot; for more information about transactions with the CDatabase object.) The CanTransact() function takes no parameters and has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">BOOL CanTransact();</FONT></PRE>

<P>The ability to support transactions is based on ODBC driver support.

<BR>

<P>The InWaitForDataSource() function returns a nonzero value if the application is waiting for the database server to complete an operation. If the application isn't waiting for the server, the InWaitForDataSource() function returns a zero. InWaitForDataSource()<B> </B>takes no parameters and has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">static BOOL PASCAL InWaitForDataSource();</FONT></PRE>

<P>This function is often called in the framework to disable the user interface while waiting for the server to respond. This is done to prevent the user from stacking unwanted commands or operations while the application waits for the server.

<BR>

<P>The SetLoginTimeout() function is used to set the amount of time that the system will wait before timing out the connection. This option must be set before a call to Open() is made; it will have no effect if it's called after a database has been opened. This function has no return value. SetLoginTimeout() takes one parameter&#151;the number of seconds after which a datasource connection attempt will time out. SetLoginTimeout() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void SetLoginTimeout(DWORD dwSeconds);</FONT></PRE>

<P>The default login timeout is 15 seconds, an acceptable value for most applications. For applications that might be running on slow systems (perhaps where there are many other connections), the login timeout value might need to be set to a larger value.

<BR>

<P>The SetQueryTimeout() function is used to set the amount of time that the system will wait before timing out the query. This option must be set before you open the recordset. It will have no effect if it's called after the recordset has been opened. This function has no return value. SetQueryTimeout() takes one parameter&#151;the number of seconds after which a datasource connection attempt will time out. SetQueryTimeout() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void SetQueryTimeout(DWORD dwSeconds);</FONT></PRE>

<P>The default query timeout is 15 seconds, an acceptable value for most applications. For applications that might be running on slow systems (perhaps where there are many other connections), the query timeout value might need to be set to a larger value.

<BR>

<P><B>WARNING</B>

<BR>

<BR>Setting the query timeout value to zero results in no time-outs and might cause the application to hang if a connection can't be made.

<P>The SetQueryTimeout() function affects all subsequent Open(), AddNew(), Edit(), and Delete() calls.

<BR>

<P>The SetSynchronousMode() function is used to either enable or disable synchronous processing for all recordsets and SQL statements associated with this CDatabase object. SetSynchronousMode()<B> </B>takes one parameter and has no return value. SetSynchronousMode()<B> </B>has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void SetSynchronousMode(BOOL bSynchronousMode);</FONT></PRE>

<P>The default operation is asynchronous processing.

<BR>

<BR>

<A NAME="E70E6"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Database Operations</B></FONT></CENTER></H5>

<BR>

<P>Database operation functions are used to work with the database. The transaction processing functions (used to update the database) and the function used to issue an SQL command are all database operation functions. The database operation functions are

<BR>

<UL>

<UL>

<P>BeginTrans()

</UL></UL>

<UL>

<UL>

<P>CommitTrans()

</UL></UL>

<UL>

<UL>

<P>Rollback()

</UL></UL>

<UL>

<UL>

<P>Cancel()

</UL></UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<P>ExecuteSQL()

<BR>

</BLOCKQUOTE></BLOCKQUOTE>

<P>With the exception of ExecuteSQL(), these functions might not be implemented by all datasources. The BeginTrans() function is used to start a <I>transaction</I> on a database. Transactions are calls to AddNew(), Edit(), Delete()<B>,</B> or Update(). After the application has completed the transaction calls, either CommitTrans() or Rollback() must be called.

<BR>

<P>The BeginTrans() function takes no parameters and returns a nonzero value if the call is successful. BeginTrans() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void BeginTrans(BOOL <I>bSynchronousMode</I>);</FONT></PRE>

<P>BeginTrans() should never be called prior to opening a recordset; otherwise, there might be problems when calling Rollback(). Each BeginTrans() call must be matched to a CommitTrans() or Rollback() prior to a subsequent call to BeginTrans(), or an error will occur. If there are pending transactions when the datasource is closed, they are discarded, much as if there had been a call to Rollback() prior to closing the datasource.

<BR>

<P>The CommitTrans() function is used to complete a transaction set begun with a call to BeginTrans(). CommitTrans() tells the datasource to accept the changes that were specified. CommitTrans() takes no parameters and returns a nonzero value if the call is successful. CommitTrans() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">BOOL CommitTrans();</FONT></PRE>

<P>You can discard the transaction by calling Rollback().

<BR>

<P>The Rollback() function is used to end a transaction processing operation, discarding the transaction. Rollback()<B> </B>takes no parameters and returns a nonzero value if the call was successful. Rollback()<B> </B>has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void Rollback();</FONT></PRE>

<P>You can accept the transaction by using the CommitTrans() function.

<BR>

<P>The Cancel() function is used to terminate an asynchronous operation that is currently pending. This function causes the OnWaitForDataSource() function to be called until it returns a value other than SQL_STILL_EXECUTING. Cancel()<B> </B>takes no parameters and has no return value. Cancel()<B> </B> has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void Cancel();</FONT></PRE>

<P>If no asynchronous operation is pending, this function simply returns.

<BR>

<P>The ExecuteSQL() function is used to execute an SQL command. The SQL command is contained in a NULL-terminated string. A CString object may also be passed to the ExecuteSQL() function if desired. ExecuteSQL()<B> </B>takes one parameter and has no return value. ExecuteSQL()<B> </B>has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void ExecuteSQL(LPCSTR szSQLCommand);</FONT></PRE>

<P>The ExecuteSQL() function throws a CDBException if there is an error in the SQL statement. ExecuteSQL() won't return any data records to the application. Use the CRecordset object to obtain records instead.

<BR>

<BR>

<A NAME="E70E7"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Database Overridables</B></FONT></CENTER></H5>

<BR>

<P>The overridable functions OnSetOptions() and OnWaitForDataSource() are used to allow the framework to set options and control the operation of the application. Neither of these functions is mandatory. If the programmer elects not to code these functions, a default operation will take place.

<BR>

<P>The OnSetOptions() function is called when the ExecuteSQL() function is being used to execute an SQL statement. OnSetOptions() takes one parameter and has no return value. OnSetOptions() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void OnSetOptions(HSTMT hstmt);</FONT></PRE>

<P>The default OnSetOptions() function is shown in the following code fragment. You could use this code in your handler as an example of how to code an OnSetOptions() function. The default implementation sets the query timeout value and the processing mode to either asynchronous or synchronous. Your application can set these options prior to the ExecuteSQL() function call by calling SetQueryTimeout() and SetSynchronousMode(). Microsoft uses the calls to AFX SQL SYNC() in its database code.

<BR>

<PRE>

<FONT COLOR="#000080">void CDatabase::OnSetOptions(HSTMT hstmt)

{

    RETCODE nRetCode;

    ASSERT_VALID(this);

    ASSERT(m_hdbc != SQL_NULL_HDBC);

    if (m_dwQueryTimeout != -1)

    {

        // Attempt to set query timeout.  Ignore failure

        AFX_SQL_SYNC(::SQLSetStmtOption(hstmt, SQL_QUERY_TIMEOUT,

            m_dwQueryTimeout));

        if (!Check(nRetCode))

            // don't attempt it again

            m_dwQueryTimeout = (DWORD)-1;

    }

    // Attempt to set AFX_SQL_ASYNC.  Ignore failure

    if (m_bAsync)

    {

        AFX_SQL_SYNC(::SQLSetStmtOption(hstmt, SQL_ASYNC_ENABLE, m_bAsync));

        if (!Check(nRetCode))

            m_bAsync = FALSE;

    }

}</FONT></PRE>

<P>The OnWaitForDataSource() function is called to allow the application to yield time to other applications while waiting for asynchronous operations. OnWaitForDataSource() takes one parameter and has no return value. OnWaitForDataSource() has the following prototype:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">void OnWaitForDataSource(BOOL bStillExecuting);</FONT></PRE>

<P>The bStillExecuting parameter is set to TRUE for the first call to OnWaitForDataSource() when it's called prior to an asynchronous operation.

<BR>

<P>The following code fragment shows the default OnWaitForDataSource() function. You could use this code in your handler as an example of how to code an OnWaitForDataSource() function if your application requires one.

<BR>

<PRE>

<FONT COLOR="#000080">void CDatabase::OnWaitForDataSource(BOOL bStillExecuting)

{

    ASSERT_VALID(this);

    ASSERT(m_hdbc != SQL_NULL_HDBC);

    _AFX_THREAD_STATE* pThreadState = AfxGetThreadState();

    CWinApp* pApp = AfxGetApp();

    if (!bStillExecuting)

    {

        // If never actually waited...

        if (m_dwWait == 0)

            return;

        if (m_dwWait == m_dwMaxWaitForDataSource)

            pApp-&gt;DoWaitCursor(-1);      // EndWaitCursor

        m_dwWait = 0;

        pThreadState-&gt;m_bWaitForDataSource--;

#ifdef _DEBUG

        if (afxTraceFlags &amp; traceDatabase)