home *** CD-ROM | disk | FTP | other *** search
/ Total C++ 2 / TOTALCTWO.iso / borland / 32snipit.pak / KEYUPDT.C < prev    next >
C/C++ Source or Header  |  1997-05-06  |  15KB  |  384 lines
  1. // BDE32 3.x - (C) Copyright 1996 by Borland International
  2.  
  3. // keyupdt.c
  4. #include "snipit.h"
  5.  
  6. static pCHAR szTblName = "KEYUPDT";
  7.  
  8. // Field Descriptor used in creating a table.
  9. static SNIPFAR FLDDesc fldDesc[] =
  10.                         {
  11.                           { // Field 1 - FRSTNAME
  12.                             1,              // Field Number
  13.                             "ID",           // Field Name
  14.                             fldFLOAT,       // Field Type
  15.                             fldUNKNOWN,     // Field Subtype
  16.                             0,              // Field Size ( 1 or 0, except
  17.                                             //     BLOb or CHAR field )
  18.                             0,              // Decimal places ( 0 )
  19.                                             //     computed
  20.                             0,              // Offset in record ( 0 )
  21.                             0,              // Length in Bytes  ( 0 )
  22.                             0,              // For Null Bits    ( 0 )
  23.                             fldvNOCHECKS,   // Validiy checks   ( 0 )
  24.                             fldrREADWRITE   // Rights
  25.                           },
  26.                           { // Field 2 - LASTNAME
  27.                             2, "FRSTNAME", fldZSTRING, fldUNKNOWN,
  28.                             10, 0, 0, 0, 0,
  29.                             fldvNOCHECKS, fldrREADWRITE
  30.                           },
  31.                           { // Field 2 - LASTNAME
  32.                             3, "LASTNAME", fldZSTRING, fldUNKNOWN,
  33.                             12, 0, 0, 0, 0,
  34.                             fldvNOCHECKS, fldrREADWRITE
  35.                           },
  36.                           { // Field 3 - Info1
  37.                             4, "INFO1", fldZSTRING, fldUNKNOWN,
  38.                             100, 0, 0, 0, 0,
  39.                             fldvNOCHECKS, fldrREADWRITE
  40.                           },
  41.                           { // Field 4 - INFO2
  42.                             5, "INFO2", fldZSTRING, fldUNKNOWN,
  43.                             100, 0, 0, 0, 0,
  44.                             fldvNOCHECKS, fldrREADWRITE
  45.                           },
  46.                           { // Field 2 - INFO3
  47.                             6, "INFO3", fldZSTRING, fldUNKNOWN,
  48.                             100, 0, 0, 0, 0,
  49.                             fldvNOCHECKS, fldrREADWRITE
  50.                           },
  51.                           { // Field 2 - INFO4
  52.                             7, "INFO4", fldZSTRING, fldUNKNOWN,
  53.                             100, 0, 0, 0, 0,
  54.                             fldvNOCHECKS, fldrREADWRITE
  55.                           },
  56.                           { // Field 2 - INFO5
  57.                             8, "INFO5", fldZSTRING, fldUNKNOWN,
  58.                             100, 0, 0, 0, 0,
  59.                             fldvNOCHECKS, fldrREADWRITE
  60.                           }
  61.                        };
  62.  
  63. // Index Descriptor - describes the index associated with the table.
  64. static IDXDesc IdxDesc =
  65.                     {
  66.                         { "KEYUPIDX" },     // Name
  67.                         1,                  // Number
  68.                         { NULL },           // Tag name (dBASE only)
  69.                         { NULL },           // Optional format
  70.                         FALSE,              // Primary?
  71.                         TRUE,               // Unique?
  72.                         FALSE,              // Descending?
  73.                         TRUE,               // Maintained?
  74.                         FALSE,              // SubSet?
  75.                         FALSE,              // Expression index?
  76.                         NULL,               // for QBE only
  77.                         1,                  // Fields in key
  78.                         NULL,               // Length in bytes
  79.                         FALSE,              // Index out of date?
  80.                         0,                  // Key Type of Expression
  81.                         { 1 },              // Array of field numbers
  82.                         { NULL },           // Key expression
  83.                         { NULL },           // Key Condition
  84.                         FALSE,              // Case insensitive
  85.                         0,                  // Block size in bytes
  86.                         0                   // Restructure number
  87.                     };
  88.  
  89. // Function prototypes
  90. static DBIResult CreateSQLTable(hDBIDb hDb, pCHAR pszTblName);
  91. static DBIResult AddRecord(hDBICur hCur, int i, pCHAR pszFirst, pCHAR pszLast,
  92.                            pCHAR pszInfo1, pCHAR pszInfo2, pCHAR pszInfo3,
  93.                            pCHAR pszInfo4, pCHAR pszInfo5);
  94.  
  95. static const UINT16 uNumFields = sizeof(fldDesc) / sizeof (fldDesc[0]);
  96.  
  97. //=====================================================================
  98. //  Function:
  99. //          KeyUpdate();
  100. //
  101. //  Description:
  102. //          This example shows how to limit the fields that are used to
  103. //          identify the record on the SQL Server. By default, all
  104. //          fields of a record are compared to determine which record
  105. //          to UPDATE or DELETE. Limiting the number of fields that are
  106. //          used in the search will speed up performance, but could result
  107. //          in data being overwritten. Data could be overwritten if another
  108. //          user modifies a field value that is not a part of the unique
  109. //          index for that table.
  110. //
  111. //          The speed increase depends on a number of issues. In general,
  112. //          the more fields in the table, and the smaller the key, the
  113. //          faster it will be when only the key fields are used in the
  114. //          search.
  115. //
  116. //          Note: This functionality is only supported for SQL Databases.
  117. //=====================================================================
  118. void
  119. KeyUpdate (void)
  120. {
  121.     DBIResult       rslt;           // Return value from IDAPI functions
  122.     hDBIDb          hDb;            // Handle to the database
  123.     hDBICur         hCur;           // Handle to the result set
  124.     hDBIXact        hTran = 0;      // Transaction Handle
  125.     UINT16          uLength;        // Length of returned property
  126.     CURProps        TblProps;       // Used to determine the size of the
  127.                                     //   Record buffer
  128.     pBYTE           pRecBuf;        // Pointer to the record buffer
  129.     CHAR            szDbType[DBIMAXNAMELEN];    // Type of the connection
  130.     CHAR            szNewFirst[20] = "Joe";     // New First name
  131.  
  132.     Screen("*** Keyed update Example ***\r\n");
  133.  
  134.     BREAK_IN_DEBUGGER();
  135.  
  136.     Screen("    Initializing IDAPI...");
  137.     if (InitAndConnect2(&hDb) != DBIERR_NONE)
  138.     {
  139.         Screen("\r\n*** End of Example ***");
  140.         return;
  141.     }
  142.  
  143.     rslt = DbiGetProp(hDb, dbDATABASETYPE, szDbType, sizeof(DBINAME),
  144.                       &uLength);
  145.     ChkRslt(rslt, "GetProp");
  146.  
  147.     // Make certain that the database supports this opperation.
  148.     if (!strcmp(szDbType, "STANDARD"))
  149.     {
  150.         Screen("        Error - This example only works on SQL Databases.");
  151.         CloseDbAndExit(&hDb);
  152.         Screen("\r\n*** End of Example ***");
  153.         return;
  154.     }
  155.  
  156.     // Create the table
  157.     if (CreateSQLTable(hDb, szTblName)
  158.         != DBIERR_NONE)
  159.     {
  160.         CloseDbAndExit(&hDb);
  161.         Screen("\r\n*** End of Example ***");
  162.         return;
  163.     }
  164.  
  165.    // Start a transaction to handle the update to the table
  166.     rslt = DbiBeginTran(hDb, xilREADCOMMITTED, &hTran);
  167.     if (ChkRslt(rslt, "BeginTran"))
  168.     {
  169.         rslt = DbiDeleteTable(hDb, szTblName, NULL);
  170.         ChkRslt(rslt, "DeleteTable");
  171.         CloseDbAndExit(&hDb);
  172.         Screen("\r\n*** End of Example ***");
  173.         return;
  174.     }
  175.  
  176.     rslt = DbiOpenTable(hDb, szTblName, NULL, "KEYUPIDX", NULL, 0,
  177.                         dbiREADWRITE, dbiOPENSHARED, xltFIELD, TRUE, NULL,
  178.                         &hCur);
  179.     ChkRslt(rslt, "OpenTable");
  180.  
  181.     rslt = DbiGetCursorProps(hCur, &TblProps);
  182.     ChkRslt(rslt, "GetCursorProps");
  183.  
  184.     pRecBuf = (pBYTE)malloc(TblProps.iRecBufSize * sizeof(BYTE));
  185.  
  186.     Screen("\r\n    Get the 15th record in the cursor (set the current"
  187.            " position in the cursor...)");
  188.     rslt = DbiGetRelativeRecord(hCur, 15,  dbiWRITELOCK, pRecBuf, NULL);
  189.     ChkRslt(rslt, "GetNextRecord");
  190.  
  191.     rslt = DbiPutField(hCur, 2, pRecBuf, (pBYTE)szNewFirst);
  192.     ChkRslt(rslt, "PutField");
  193.  
  194.     Screen("\r\n    Update using only the key fields and modified fields for"
  195.            " comparison...");
  196.     rslt = DbiSetProp(hCur, curUPDLOCKMODE, updWHEREKEYCHG);
  197.     ChkRslt(rslt, "SetProp");
  198.  
  199.     Screen("    Modify the record...");
  200.     rslt = DbiModifyRecord(hCur, pRecBuf, TRUE);
  201.     ChkRslt(rslt, "ModifyRecord");
  202.  
  203.     Screen("\r\n    Record updated...");
  204.  
  205.     Screen("\r\n    Close the cursor...");
  206.     rslt = DbiCloseCursor(&hCur);
  207.     ChkRslt(rslt, "CloseCursor");
  208.  
  209.     Screen("    Commit the changes...");
  210.     rslt = DbiEndTran(hDb, hTran, xendCOMMIT);
  211.     ChkRslt(rslt, "EndTran");
  212.  
  213.     rslt = DbiDeleteTable(hDb, szTblName, NULL);
  214.     ChkRslt(rslt, "DeleteTable");
  215.  
  216.     if (pRecBuf)
  217.     {
  218.         free(pRecBuf);
  219.     }
  220.  
  221.     Screen("    Close the database and exit IDAPI...");
  222.     CloseDbAndExit(&hDb);
  223.  
  224.     Screen("\r\n*** End of Example ***");
  225. }
  226.  
  227. //=====================================================================
  228. //  Function:
  229. //          CreateSQLTable(hDb, pszTblName);
  230. //
  231. //  Input:  phDb        - Pointer to the database handle.
  232. //          pszTblName  - The name of the table to create.
  233. //
  234. //  Return: Result returned by IDAPI.
  235. //
  236. //  Description:
  237. //          This function will create a table and add records to that
  238. //          table.
  239. //=====================================================================
  240. DBIResult
  241. CreateSQLTable (hDBIDb hDb, pCHAR pszTblName)
  242. {
  243.     DBIResult   rslt;           // Value returned from IDAPI functions
  244.     CRTblDesc   crTblDesc;      // Table Descriptor
  245.     hDBICur     hCur;           // Cursor used for adding records
  246.     int         i;              // Loop Counter
  247.     hDBIXact    hTran;          // Transaction Handle
  248.  
  249.     // Initialize the Table Create Descriptor.
  250.     memset(&crTblDesc, 0, sizeof(CRTblDesc));
  251.  
  252.     strcpy(crTblDesc.szTblName, pszTblName);
  253.     crTblDesc.iFldCount     = uNumFields;
  254.     crTblDesc.pfldDesc      = fldDesc;
  255.     crTblDesc.iIdxCount     = 1;
  256.     crTblDesc.pidxDesc      = &IdxDesc;
  257.  
  258.     Screen("    Creating the table...");
  259.     rslt = DbiCreateTable(hDb, TRUE, &crTblDesc);
  260.     if (ChkRslt(rslt, "CreateTable") != DBIERR_NONE)
  261.     {
  262.         return rslt;
  263.     }
  264.  
  265.     rslt = DbiBeginTran(hDb, xilREADCOMMITTED, &hTran);
  266.     ChkRslt(rslt, "BeginTran");
  267.  
  268.     rslt = DbiOpenTable(hDb, pszTblName, NULL,
  269.                         NULL, NULL, 0, dbiREADWRITE, dbiOPENEXCL,
  270.                         xltFIELD, TRUE, NULL, &hCur);
  271.     if (ChkRslt(rslt, "OpenTable") != DBIERR_NONE)
  272.     {
  273.         rslt = DbiEndTran(hDb, hTran, xendABORT);
  274.         ChkRslt(rslt, "EndTran");
  275.  
  276.         rslt = DbiDeleteTable(hDb, pszTblName, NULL);
  277.         ChkRslt(rslt, "DeleteTable");
  278.         return rslt;
  279.     }
  280.  
  281.  
  282.     // Add records to the table.
  283.     Screen("    Adding records to the table...");
  284.     for (i=0; i < 20; i++)
  285.     {
  286.         rslt = AddRecord(hCur, i, "Tom", "Smith",
  287.                          "Test data to Write to the table",
  288.                          "Test data to Write to the table",
  289.                          "Test data to Write to the table",
  290.                          "Test data to Write to the table",
  291.                          "Test data to Write to the table");
  292.         ChkRslt(rslt, "AddRecord");
  293.     }
  294.  
  295.     rslt = DbiCloseCursor(&hCur);
  296.     ChkRslt(rslt, "CloseTable");
  297.  
  298.     rslt = DbiEndTran(hDb, hTran, xendCOMMIT);
  299.     ChkRslt(rslt, "EndTran");
  300.  
  301.     return rslt;
  302. }
  303.  
  304. //=====================================================================
  305. //  Function:
  306. //          AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  307. //                     pCHAR pszInfo1, pCHAR pszInfo2, pCHAR pszInfo3,
  308. //                     pCHAR pszInfo4, pCHAR pszInfo5)
  309. //
  310. //  Input:  hCur        - The table handle
  311. //          pFirst      - First Name
  312. //          pLast       - Last Name
  313. //          pszInfo1    - Data to write to the table
  314. //          pszInfo2    - Data to write to the table
  315. //          pszInfo3    - Data to write to the table
  316. //          pszInfo4    - Data to write to the table
  317. //          pszInfo5    - Data to write to the table
  318. //
  319. //  Return: Result of adding the record to the table
  320. //
  321. //  Description:
  322. //          Insert a record into the table.
  323. //=====================================================================
  324. DBIResult
  325. AddRecord (hDBICur hCur, int i, pCHAR pszFirst, pCHAR pszLast,
  326.            pCHAR pszInfo1, pCHAR pszInfo2, pCHAR pszInfo3,
  327.            pCHAR pszInfo4, pCHAR pszInfo5)
  328. {
  329.     DBIResult   rslt;       // Return value from IDAPI functions
  330.     pBYTE       pRecBuf;    // Record buffer
  331.     CURProps    TblProps;   // Table properties
  332.     DFLOAT      fTemp;      // Temporary variable for the float.
  333.  
  334.     // Allocate a record buffer.
  335.     rslt = DbiGetCursorProps(hCur, &TblProps);
  336.     ChkRslt(rslt, "GetCursorProps");
  337.  
  338.     pRecBuf = (pBYTE) malloc(TblProps.iRecBufSize * sizeof(BYTE));
  339.     if (pRecBuf == NULL)
  340.     {
  341.         Screen("    Error - Out of memory");
  342.         return DBIERR_NOMEMORY;
  343.     }
  344.  
  345.     // Clear the record buffer, then add the data.
  346.     rslt = DbiInitRecord(hCur, pRecBuf);
  347.     ChkRslt(rslt, "InitRecord");
  348.  
  349.     fTemp = i;
  350.  
  351.     rslt = DbiPutField(hCur, 1, pRecBuf, (pBYTE) &fTemp);
  352.     ChkRslt(rslt, "PutField");
  353.  
  354.     rslt = DbiPutField(hCur, 2, pRecBuf, (pBYTE) pszFirst);
  355.     ChkRslt(rslt, "PutField");
  356.  
  357.     rslt = DbiPutField(hCur, 3, pRecBuf, (pBYTE) pszLast);
  358.     ChkRslt(rslt, "PutField");
  359.  
  360.     rslt = DbiPutField(hCur, 4, pRecBuf, (pBYTE) pszInfo1);
  361.     ChkRslt(rslt, "PutField");
  362.  
  363.     rslt = DbiPutField(hCur, 5, pRecBuf, (pBYTE) pszInfo2);
  364.     ChkRslt(rslt, "PutField");
  365.  
  366.     rslt = DbiPutField(hCur, 6, pRecBuf, (pBYTE) pszInfo3);
  367.     ChkRslt(rslt, "PutField");
  368.  
  369.     rslt = DbiPutField(hCur, 7, pRecBuf, (pBYTE) pszInfo4);
  370.     ChkRslt(rslt, "PutField");
  371.  
  372.     rslt = DbiPutField(hCur, 8, pRecBuf, (pBYTE) pszInfo5);
  373.     ChkRslt(rslt, "PutField");
  374.  
  375.     rslt = DbiInsertRecord(hCur, dbiNOLOCK, pRecBuf);
  376.     ChkRslt(rslt, "InsertRecord");
  377.  
  378.     free(pRecBuf);
  379.  
  380.     return rslt;
  381. }
  382.  
  383.  
  384.