/*------------------------------------------------------------------------------* * File Name: ADOSample.c * * Creation: 7/31/2002 * * Purpose: OriginC Source C file * * Copyright (c) Originlab Corp. 2002 * * All Rights Reserved * * * * Modification Log: * *------------------------------------------------------------------------------*/ //////////////////////////////////////////////////////////////////////////////////// // you can include just this typical header file for most Origin built-in functions and classes // and it takes a reasonable amount of time to compile, #include // this file include most of the other header files except the NAG header, which takes longer to compile // NAG routines //#include // this contains all the NAG headers, //////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////// // This sample shows how to use through OriginC COM interface to access // ADO object. // Through ADO, SQL and Access database can be imported into worksheet and // The modification of data in the worksheet can be put back into database. //////////////////////////////////////////////////////////////////////////////////// enum CursorLocationEnum { adUseNone = 1, adUseServer = 2, adUseClient = 3, adUseClientBatch = 3 }; typedef enum LockTypeEnum { dbOptimisticValue = 1, dbPessimistic = 2, dbOptimistic = 3, dbOptimisticBatch = 5 } LockTypeEnum; //global object to be shared between functions static Object ocrs; //function to retrieve data and put into a worksheet void GetData(string strWks = "Data1") { //create the ADODB.Recorset object ocrs = CreateObject("ADODB.Recordset"); if( !ocrs ) return; string strConn; // prepare database connection string // the connection string for MSSQL database strConn = "Provider=SQLOLEDB; Data Source=server name; Initial Catalog=database name; User ID=login; Password=password;"; //OLEDB driver for Access database access //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path to the access mdb file"; //prepare query string to retrieve data from database //this sample is to query from customers table in northwind sample database string strQuery = "select * from customers"; //CursorLocation properties //adUseClient 3 // Uses client-side cursors supplied by a local cursor library. Local cursor services // often will allow many features that driver-supplied cursors may not, so using this // setting may provide an advantage with respect to features that will be enabled. // For backward compatibility, the synonym adUseClientBatch is also supported. //adUseNone 1 // Does not use cursor services. (This constant is obsolete and appears solely for the // sake of backward compatibility.) //adUseServer 2 // Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes // very flexible and allow for additional sensitivity to changes others make to the data source. // However, some features of the Microsoft Cursor Service for OLE DB (such as disassociated // Recordset objects) cannot be simulated with server-side cursors and these features will be // unavailable with this setting. ocrs.CursorLocation = 3; //open method prototype and its explaination // recordset.Open(QueryString, ActiveConnection, CursorType, LockType, Options) // explaination: // QueryString : strQuery // Build by user using SQL language // ActiveConnection : strConn // The dtring to indicate the connection method to a database. This sample only shows a string // useing SQLOLEDB as provider to connect to a MS SQL database. // CursorType: optional //adOpenDynamic 2 // Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, // and all types of movement through the Recordset are allowed, except for bookmarks, // if the provider doesn't support them. //adOpenForwardOnly 0 // Default. Uses a forward-only cursor. Identical to a static cursor, except that you // can only scroll forward through records. This improves performance when you need to // make only one pass through a Recordset. //adOpenKeyset 1 // Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that // other users add, although records that other users delete are inaccessible from your Recordset. // Data changes by other users are still visible. //adOpenStatic 3 // Uses a static cursor. A static copy of a set of records that you can use to find data // or generate reports. Additions, changes, or deletions by other users are not visible. //adOpenUnspecified -1 // Does not specify the type of cursor. //LockType : optional //adLockBatchOptimistic 4 // Indicates optimistic batch updates. Required for batch update mode. //adLockOptimistic 3 // Indicates optimistic locking, record by record. The provider uses optimistic locking, // locking records only when you call the Update method. //adLockPessimistic 2 // Indicates pessimistic locking, record by record. The provider does what is necessary // to ensure successful editing of the records, usually by locking records at the data source // immediately after editing. //adLockReadOnly 1 // Indicates read-only records. You cannot alter the data. //adLockUnspecified -1 // Does not specify a type of lock. For clones, the clone is created with the same lock // type as the original. ocrs.open( strQuery, strConn, 1, 3); //initialize the worksheet object Worksheet wks(strWks); //put data into the worksheet. BOOL bRet = wks.PutRecordset(ocrs); out_int("bRet = ", bRet); } // Put update back to the database. // this sample handles only string fields void Update(string strWks = "Data1") { Object ocfield; Worksheet wks(strWks); //move the record index to the first one ocrs.MoveFirst(); int nRowIndex = 0; out_int("Total records = ", ocrs.RecordCount); //loop through the recordset and compare the value between //recordset and Origin worksheet. If data is different, then, //update that field while( !ocrs.eof ) { out_int("nRowIndex = ", nRowIndex); if( nRowIndex > ocrs.RecordCount ) break; for(int ii = 0; ii < ocrs.fields.Count; ii++) { ocfield = ocrs.fields(ii); //////Possible data types for a database field // String types //adLongVarChar 201 Indicates a long string value (Parameter object only). //adLongVarWChar 203 Indicates a long null-terminated Unicode string value (Parameter object only). //adVarChar 200 Indicates a string value (Parameter object only). //adVarNumeric 139 Indicates a numeric value (Parameter object only). //adVarWChar 202 Indicates a null-terminated Unicode character string. //adWChar 130 Indicates a null-terminated Unicode character string (DBTYPE_WSTR). //adBSTR 8 Indicates a null-terminated character string (Unicode) (DBTYPE_BSTR). //adChapter 136 Indicates a four-byte chapter value that identifies rows in a child rowset (DBTYPE_HCHAPTER). //adChar 129 Indicates a string value (DBTYPE_STR). //Integer types //AdArray (Does not apply to ADOX.) 0x2000 A flag value, always combined with another data type constant, that indicates an array of that other data type. //adBigInt 20 Indicates an eight-byte signed integer (DBTYPE_I8). //adInteger 3 Indicates a four-byte signed integer (DBTYPE_I4). //adSmallInt 2 Indicates a two-byte signed integer (DBTYPE_I2). //adTinyInt 16 Indicates a one-byte signed integer (DBTYPE_I1). //adUnsignedBigInt 21 Indicates an eight-byte unsigned integer (DBTYPE_UI8). //adUnsignedInt 19 Indicates a four-byte unsigned integer (DBTYPE_UI4). //adUnsignedSmallInt 18 Indicates a two-byte unsigned integer (DBTYPE_UI2). //adUnsignedTinyInt 17 Indicates a one-byte unsigned integer (DBTYPE_UI1). //float or bouble //adDecimal 14 Indicates an exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL). //adVarNumeric 139 Indicates a numeric value (Parameter object only). //adDouble 5 Indicates a double-precision floating-point value (DBTYPE_R8). //adNumeric 131 Indicates an exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC). //adSingle 4 Indicates a single-precision floating-point value (DBTYPE_R4). // Binary type //adBinary 128 Indicates a binary value (DBTYPE_BYTES). //adLongVarBinary 205 Indicates a long binary value (Parameter object only). //adChapter 136 Indicates a four-byte chapter value that identifies rows in a child rowset // (DBTYPE_HCHAPTER). // BOOL type //adBoolean 11 Indicates a boolean value (DBTYPE_BOOL). // currency type //adCurrency 6 Indicates a currency value (DBTYPE_CY). Currency is a fixed-point number with // four digits to the right of the decimal point. It is stored in an eight-byte signed integer // scaled by 10,000. // date/time types //adDate 7 Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of // which is the number of days since December 30, 1899, and the fractional part of which is // the fraction of a day. //adDBDate 133 Indicates a date value (yyyymmdd) (DBTYPE_DBDATE). //adDBTime 134 Indicates a time value (hhmmss) (DBTYPE_DBTIME). //adDBTimeStamp 135 Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) // (DBTYPE_DBTIMESTAMP). //adFileTime 64 Indicates a 64-bit value representing the number of 100-nanosecond intervals // since January 1, 1601 (DBTYPE_FILETIME). // misc //adEmpty 0 Specifies no value (DBTYPE_EMPTY). //adError 10 Indicates a 32-bit error code (DBTYPE_ERROR). //adGUID 72 Indicates a globally unique identifier (GUID) (DBTYPE_GUID). //adIDispatch 9 Indicates a pointer to an IDispatch interface on a COM object (DBTYPE_IDISPATCH). // Note This data type is currently not supported by ADO. Usage may cause unpredictable results. //adIUnknown 13 Indicates a pointer to an IUnknown interface on a COM object (DBTYPE_IUNKNOWN). // Note This data type is currently not supported by ADO. Usage may cause unpredictable results. //adPropVariant 138 Indicates an Automation PROPVARIANT (DBTYPE_PROP_VARIANT). //adUserDefined 132 Indicates a user-defined variable (DBTYPE_UDT). //adVarBinary 204 Indicates a binary value (Parameter object only). //adVariant 12 Indicates an Automation Variant (DBTYPE_VARIANT). // Note This data type is currently not supported by ADO. Usage may cause unpredictable results. //get the field type int nFieldType = ocfield.Type; //if the field type is a string type, do the update if( nFieldType == 200 || nFieldType == 201 || nFieldType == 202 || nFieldType == 203 || nFieldType == 129 || nFieldType == 130 || nFieldType == 8 ) { string strValue, strCell; strValue = ""; //get value from the recordset if( ocfield.Value != NULL ) strValue = (string)ocfield.Value; //get value from worksheet if( wks.GetCell(nRowIndex, ii, strCell) ) { //if two values do not match, update the database if( strValue.CompareNoCase(strCell) != 0 ) ocrs.Update(ocfield.Name, strCell); } } } //advance the index ocrs.MoveNext(); nRowIndex++; if( ocrs.eof ) break; } }