home *** CD-ROM | disk | FTP | other *** search
/ C Programming Starter Kit 2.0 / SamsPublishing-CProgrammingStarterKit-v2.0-Win31.iso / bde / snipit.pak / LSQLLIVE.C < prev    next >
Encoding:
C/C++ Source or Header  |  1997-07-24  |  15.8 KB  |  452 lines
  1. // BDE - (C) Copyright 1995 by Borland International
  2.  
  3. // lsqllive.c
  4. #include "snipit.h"
  5.  
  6. static pCHAR szTblName = "SQLLIVE";
  7.  
  8. // Field Descriptor used in creating a table.
  9. static SNIPFAR FLDDesc fldDesc[] =
  10.                         {
  11.                           { // Field 1 - FRSTNAME
  12.                             1,              // Field Number
  13.                             "FRSTNAME",     // Field Name
  14.                             fldZSTRING,     // Field Type
  15.                             fldUNKNOWN,     // Field Subtype
  16.                             10,             // 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, "LASTNAME", fldZSTRING, fldUNKNOWN,
  28.                             12, 0, 0, 0, 0,
  29.                             fldvNOCHECKS, fldrREADWRITE
  30.                           }
  31.                        };
  32.  
  33. // Index Descriptor - describes the index associated with the table.
  34. static IDXDesc IdxDesc =
  35.                     {
  36.                         { "LIVEIDX" },      // Name
  37.                         1,                  // Number
  38.                         { NULL },           // Tag name (dBASE only)
  39.                         { NULL },           // Optional format
  40.                         FALSE,              // Primary?
  41.                         TRUE,               // Unique?
  42.                         FALSE,              // Descending?
  43.                         TRUE,               // Maintained?
  44.                         FALSE,              // SubSet?
  45.                         FALSE,              // Expression index?
  46.                         NULL,               // for QBE only
  47.                         2,                  // Fields in key
  48.                         NULL,               // Length in bytes
  49.                         FALSE,              // Index out of date?
  50.                         0,                  // Key Type of Expression
  51.                         { 2, 1 },           // Array of field numbers
  52.                         { NULL },           // Key expression
  53.                         { NULL },           // Key Condition
  54.                         FALSE,              // Case insensitive
  55.                         0,                  // Block size in bytes
  56.                         0                   // Restructure number
  57.                     };
  58.  
  59. // Function prototypes
  60. static DBIResult CreateSQLTable(hDBIDb hDb, pCHAR pszTblName);
  61. static DBIResult AddRecord(hDBICur hCur, pCHAR pFirst, pCHAR pLast);
  62.  
  63. static const UINT16 uNumFields = sizeof(fldDesc) / sizeof (fldDesc[0]);
  64.  
  65. //=====================================================================
  66. //  Function:
  67. //          LiveSQL();
  68. //
  69. //  Description:
  70. //          This example shows how to generate a live result set,
  71. //          so that changes to the query are reflected in the table
  72. //          on the server. This example allows the user to connect
  73. //          to any data source which has an alias defined in IDAPI.CFG.
  74. //
  75. //          Note: You need write access to the SQL Server in order to
  76. //          run this example remotely. Also, the unique index is required
  77. //          on remote tables for the BDE to have a method of uniquely
  78. //          identifying a record.
  79. //=====================================================================
  80. void
  81. LiveSQL (void)
  82. {
  83.     DBIResult       rslt;       // Return value from IDAPI functions
  84.     hDBIDb          hDb;        // Handle to the database
  85.     hDBICur         hCur;       // Handle to the answer table
  86.     hDBIStmt        hStmt;      // Handle to the SQL statement
  87.     hDBICur         hMCur;      // Handle to the master table
  88.     CHAR            szQry[100] = { // The text of the SQL statement
  89.                 "SELECT FRSTNAME, LASTNAME \r\n"
  90.                 "FROM SQLLIVE \r\n"
  91.                                   };
  92.     UINT16          uLength;
  93.     CURProps        TblProps;        // Table descriptor
  94.     CURProps        TblMProps;       // Table descriptor
  95.     pBYTE           pRecBuf = NULL;  // Pointer to the record buffer
  96.     pBYTE           pMRecBuf = NULL; // Pointer to the record buffer
  97.     BOOL            bBlank;          // Determine if the field is blank
  98.     BYTE            szFirst[20];     // Variable to store the current first name
  99.     BYTE            szNewFirst[]="Bob";      // Name to change to
  100.     CHAR            szDbType[DBIMAXNAMELEN]; // Type of the connection
  101.  
  102.     Screen("*** Live Answer Table Example ***\r\n");
  103.  
  104.     BREAK_IN_DEBUGGER();
  105.  
  106.     Screen("    Initializing IDAPI...");
  107.     if (InitAndConnect2(&hDb) != DBIERR_NONE)
  108.     {
  109.         Screen("\r\n*** End of Example ***");
  110.         return;
  111.     }
  112.  
  113.     rslt = DbiGetProp(hDb, dbDATABASETYPE, szDbType, sizeof(DBINAME),
  114.                       &uLength);
  115.     ChkRslt(rslt, "GetProp");
  116.  
  117.     // Only use Order By for remote tables only.
  118.     //   Local tables do not allow the use of 'ORDER BY' for
  119.     //   live tables.
  120.     if (strcmp(szDbType, "STANDARD"))
  121.     {
  122.         strcat(szQry, "ORDER BY LASTNAME, FRSTNAME");
  123.     }
  124.  
  125.     // Create the table
  126.     if (CreateSQLTable(hDb, szTblName)
  127.         != DBIERR_NONE)
  128.     {
  129.         CloseDbAndExit(&hDb);
  130.         Screen("\r\n*** End of Example ***");
  131.         return;
  132.     }                
  133.  
  134.     Screen("    Perform the following SQL statement on the table:\r\n");
  135.     Screen(szQry);
  136.  
  137.     Screen("\r\n    Prepare the SQL statement...");
  138.     rslt = DbiQPrepareExt(hDb, qrylangSQL, szQry, qprepFORUPDATE, &hStmt);
  139.     if (ChkRslt(rslt, "QPrepareExt") != DBIERR_NONE)
  140.     {
  141.         rslt = DbiDeleteTable(hDb, szTblName, NULL);
  142.         ChkRslt(rslt, "DeleteTable");
  143.         CloseDbAndExit(&hDb);
  144.         Screen("\r\n*** End of Example ***");
  145.         return;
  146.     }
  147.  
  148.     Screen("    Execute the SQL statement...");
  149.     rslt = DbiQExec(hStmt, &hCur);
  150.     if (ChkRslt(rslt, "QExec") != DBIERR_NONE)
  151.     {
  152.         rslt = DbiDeleteTable(hDb, szTblName, NULL);
  153.         ChkRslt(rslt, "DeleteTable");
  154.         CloseDbAndExit(&hDb);
  155.         Screen("\r\n*** End of Example ***");
  156.         return;
  157.     }
  158.  
  159.     // Check for a valid cursor.
  160.     if (hCur)
  161.     {
  162.         rslt = DbiGetCursorProps(hCur, &TblProps);
  163.         ChkRslt(rslt, "GetCursorProps");
  164.  
  165.         // Check if the answer set is live (exit example if not).
  166.         if (TblProps.bTempTable == TRUE)
  167.         {
  168.             Screen("\r\n    Could not get a live answer set...");
  169.             rslt = DbiCloseCursor(&hCur);
  170.             ChkRslt(rslt, "CloseCursor");
  171.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  172.             ChkRslt(rslt, "DeleteTable");
  173.             CloseDbAndExit(&hDb);
  174.             Screen("\r\n*** End of Example ***");
  175.             return;
  176.         }
  177.  
  178.         rslt = DbiOpenTable(hDb, (pCHAR) szTblName, NULL,
  179.                             NULL, NULL, 0, dbiREADONLY, dbiOPENSHARED,
  180.                             xltFIELD, FALSE, NULL, &hMCur);
  181.         if (ChkRslt(rslt, "OpenTable") != DBIERR_NONE)
  182.         {
  183.             rslt = DbiCloseCursor(&hCur);
  184.             ChkRslt(rslt, "CloseCursor");
  185.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  186.             ChkRslt(rslt, "DeleteTable");
  187.             CloseDbAndExit(&hDb);
  188.             Screen("\r\n*** End of Example ***");
  189.             return;
  190.         }
  191.  
  192.         // Get the table properties to allocate memory for the record
  193.         //   buffer.
  194.         rslt = DbiGetCursorProps(hMCur, &TblMProps);
  195.         ChkRslt(rslt, "GetCursorProps");
  196.  
  197.         // Allocate memory for the record buffer.
  198.         pMRecBuf = (pBYTE) malloc(TblMProps.iRecBufSize * sizeof(CHAR));
  199.         if (!pMRecBuf)
  200.         {
  201.             rslt = DbiCloseCursor(&hCur);
  202.             ChkRslt(rslt, "CloseCursor");
  203.             rslt = DbiCloseCursor(&hMCur);
  204.             ChkRslt(rslt, "CloseCursor");
  205.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  206.             ChkRslt(rslt, "DeleteTable");
  207.             CloseDbAndExit(&hDb);
  208.             Screen("\r\n*** End of Example ***");
  209.             return;
  210.         }
  211.  
  212.         rslt = DbiInitRecord(hMCur, pMRecBuf);
  213.         ChkRslt(rslt, "InitRecord");
  214.  
  215.         Screen("\r\n    Display the table using the cursor aqcuired through"
  216.                " DbiOpenTable:"); 
  217.         DisplayTable(hMCur, 0);
  218.  
  219.         // Allocate memory for the record buffer.
  220.         pRecBuf = (pBYTE) malloc(TblProps.iRecBufSize * sizeof(CHAR));
  221.         if (!pRecBuf)
  222.         {
  223.             free(pMRecBuf);
  224.             rslt = DbiCloseCursor(&hCur);
  225.             ChkRslt(rslt, "CloseCursor");
  226.             rslt = DbiCloseCursor(&hMCur);
  227.             ChkRslt(rslt, "CloseCursor");
  228.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  229.             ChkRslt(rslt, "DeleteTable");
  230.             CloseDbAndExit(&hDb);
  231.             Screen("\r\n*** End of Example ***");
  232.             return;
  233.         }
  234.  
  235.         // Get the first record in the live query.
  236.         rslt = DbiSetToBegin(hCur);
  237.         ChkRslt(rslt, "SetToBegin");
  238.  
  239.         rslt = DbiGetNextRecord(hCur, dbiNOLOCK, pRecBuf, NULL);
  240.         ChkRslt(rslt, "GetNextRecord");
  241.  
  242.         rslt = DbiGetField(hCur, 1, pRecBuf, szFirst, &bBlank);
  243.         ChkRslt(rslt, "GetField");
  244.  
  245.         // Modify the First Name of the first record.
  246.         rslt = DbiPutField(hCur, 1, pRecBuf, szNewFirst);
  247.         ChkRslt(rslt, "PutField");
  248.  
  249.         Screen("\r\n    Modifying the first record in the query result:"
  250.                "\r\n      change the FRSTNAME field from %s to %s....",
  251.                szFirst, szNewFirst);
  252.  
  253.         rslt = DbiModifyRecord(hCur, pRecBuf, TRUE);
  254.         ChkRslt(rslt, "ModifyRecord");
  255.  
  256.         // Refresh the cursor - only required for remote tables
  257.         Screen("\r\n    Resynch the cursor to the table...");
  258.         rslt = DbiForceReread(hMCur);
  259.         ChkRslt(rslt, "ForceReread");
  260.  
  261.         rslt = DbiSetToBegin(hMCur);
  262.         ChkRslt(rslt, "SetToBegin");
  263.  
  264.         Screen("\r\n    Again, display the table opened using DbiOpenTable");
  265.         Screen("      (Notice that the first record reflects the changes made"
  266.                " to the query)");
  267.         DisplayTable(hMCur, 0);
  268.  
  269.         Screen("\r\n    Close the cursors...");
  270.         rslt = DbiCloseCursor(&hCur);
  271.         ChkRslt(rslt, "CloseCursor");
  272.  
  273.         rslt = DbiCloseCursor(&hMCur);
  274.         ChkRslt(rslt, "CloseCursor");   
  275.     }
  276.     else
  277.     {
  278.         Screen("        Could not get cursor to the answer table.");
  279.     }
  280.  
  281.     Screen("    Release memory allocated for the query...");
  282.     rslt = DbiQFree(&hStmt);
  283.     ChkRslt(rslt, "QryFree");
  284.  
  285.     // Cleanup
  286.     Screen("    Delete the table...");
  287.     if (!strcmp(szDbType, "STANDARD"))
  288.     {
  289.         rslt = DbiDeleteTable(hDb, szTblName, szPARADOX);
  290.     }
  291.     else
  292.     {
  293.         rslt = DbiDeleteTable(hDb, szTblName, NULL);
  294.     }
  295.     ChkRslt(rslt, "DeleteTable");
  296.  
  297.     if (pRecBuf)
  298.     {
  299.         free(pRecBuf);
  300.     }
  301.  
  302.     if (pMRecBuf)
  303.     {
  304.         free(pMRecBuf);
  305.     }
  306.  
  307.     Screen("    Close the database and exit IDAPI...");
  308.     CloseDbAndExit(&hDb);
  309.  
  310.     Screen("\r\n*** End of Example ***");
  311. }
  312.  
  313. //=====================================================================
  314. //  Function:
  315. //          CreateSQLTable(hDb, pszTblName, phCur);
  316. //
  317. //  Input:  phDb        - Pointer to the database handle.
  318. //          pszTblName  - The name of the table to create.
  319. //          phCur       - Handle to the table
  320. //
  321. //  Return: Result returned by IDAPI.
  322. //
  323. //  Description:
  324. //          This function will create a table and add records to that
  325. //          table.
  326. //=====================================================================
  327. DBIResult
  328. CreateSQLTable (hDBIDb hDb, pCHAR pszTblName)
  329. {
  330.     DBIResult   rslt;           // Value returned from IDAPI functions
  331.     CRTblDesc   crTblDesc;      // Table Descriptor
  332.     DBINAME     szDbType;       // Type of the table
  333.     UINT16      uLength;        // Length of the string
  334.     hDBICur     hCur;           // Cursor used for adding records
  335.  
  336.     // Initialize the Table Create Descriptor.
  337.     memset(&crTblDesc, 0, sizeof(CRTblDesc));
  338.  
  339.     strcpy(crTblDesc.szTblName, pszTblName);
  340.     crTblDesc.iFldCount     = uNumFields;
  341.     crTblDesc.pfldDesc      = fldDesc;
  342.     crTblDesc.iIdxCount     = 1;
  343.     crTblDesc.pidxDesc      = &IdxDesc;
  344.  
  345.     rslt = DbiGetProp(hDb, dbDATABASETYPE, szDbType, sizeof(DBINAME),
  346.                       &uLength);
  347.     ChkRslt(rslt, "GetProp");
  348.  
  349.     // Create a Paradox table when working locally
  350.     if (!strcmp(szDbType, "STANDARD"))
  351.     {
  352.         rslt = DbiGetProp(hDb, dbDEFAULTDRIVER, szDbType, sizeof(DBINAME),
  353.                           &uLength);
  354.         ChkRslt(rslt, "GetProp");
  355.  
  356.         // If no default driver is set
  357.         if (!strcmp(szDbType, ""))
  358.         {
  359.             strcpy(szDbType, szPARADOX);
  360.         }
  361.  
  362.         // Local tables do not require an index, and as indexes are
  363.         //   created differently for local tables, an index is not created.
  364.         crTblDesc.iIdxCount     = 0;
  365.         crTblDesc.pidxDesc      = NULL;
  366.  
  367.         // Set the Table Type.
  368.         strcpy(crTblDesc.szTblType, szDbType);
  369.     }
  370.  
  371.     Screen("    Creating the table...");
  372.     rslt = DbiCreateTable(hDb, TRUE, &crTblDesc);
  373.     if (ChkRslt(rslt, "CreateTable") != DBIERR_NONE)
  374.     {
  375.         return rslt;
  376.     }
  377.  
  378.     rslt = DbiOpenTable(hDb, pszTblName, NULL,
  379.                         NULL, NULL, 0, dbiREADWRITE, dbiOPENSHARED,
  380.                         xltFIELD, FALSE, NULL, &hCur);
  381.     if (ChkRslt(rslt, "OpenTable") != DBIERR_NONE)
  382.     {
  383.         rslt = DbiDeleteTable(hDb, pszTblName, NULL);
  384.         ChkRslt(rslt, "DeleteTable");
  385.         return rslt;
  386.     }
  387.  
  388.     // Add records to the table.
  389.     Screen("    Adding records to the table...");
  390.     rslt = AddRecord(hCur, "Tom", "Smith");
  391.     rslt = AddRecord(hCur, "Jim", "Jones");
  392.     rslt = AddRecord(hCur, "Larry", "Peterson");
  393.     rslt = AddRecord(hCur, "Jane", "Jackson");
  394.     rslt = AddRecord(hCur, "Mary", "Wong");
  395.  
  396.     rslt = DbiCloseCursor(&hCur);
  397.     ChkRslt(rslt, "ClostTable");
  398.  
  399.     return rslt;
  400. }
  401.  
  402. //=====================================================================
  403. //  Function:
  404. //          AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  405. //
  406. //  Input:  hCur    - The table handle
  407. //          pFirst  - First Name
  408. //          pLast   - Last Name
  409. //
  410. //  Return: Result of adding the record to the table
  411. //
  412. //  Description:
  413. //          Insert a record into the table.
  414. //=====================================================================
  415. DBIResult
  416. AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  417. {
  418.     DBIResult   rslt;       // Return value from IDAPI functions
  419.     pBYTE       pRecBuf;    // Record buffer
  420.     CURProps    TblProps;   // Table properties
  421.  
  422.     // Allocate a record buffer.
  423.     rslt = DbiGetCursorProps(hCur, &TblProps);
  424.     ChkRslt(rslt, "GetCursorProps");
  425.  
  426.     pRecBuf = (pBYTE) malloc(TblProps.iRecBufSize * sizeof(BYTE));
  427.     if (pRecBuf == NULL)
  428.     {
  429.         Screen("    Error - Out of memory");
  430.         return DBIERR_NOMEMORY;
  431.     }
  432.  
  433.     // Clear the record buffer, then add the data.
  434.     rslt = DbiInitRecord(hCur, pRecBuf);
  435.     ChkRslt(rslt, "InitRecord");
  436.  
  437.     rslt = DbiPutField(hCur, 1, pRecBuf, (pBYTE) pFirst);
  438.     ChkRslt(rslt, "PutField");
  439.  
  440.     rslt = DbiPutField(hCur, 2, pRecBuf, (pBYTE) pLast);
  441.     ChkRslt(rslt, "PutField");
  442.  
  443.     rslt = DbiInsertRecord(hCur, dbiNOLOCK, pRecBuf);
  444.     ChkRslt(rslt, "InsertRecord");
  445.  
  446.     free(pRecBuf);
  447.  
  448.     return rslt;
  449. }
  450.  
  451.  
  452.