home *** CD-ROM | disk | FTP | other *** search
/ C Programming Starter Kit 2.0 / SamsPublishing-CProgrammingStarterKit-v2.0-Win31.iso / bde / snipit.pak / STORPROC.C < prev    next >
Encoding:
C/C++ Source or Header  |  1997-07-24  |  18.7 KB  |  547 lines
  1. // BDE - (C) Copyright 1995 by Borland International
  2.  
  3. // storproc.c
  4. #include "snipit.h"
  5.  
  6. static pCHAR szTblName = "STORPROC";
  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.                             12,             // 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.                         { "PROCIDX" },      // 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. static DBIResult CreateProcedure(hDBIDb hDb);
  63.  
  64. static const UINT16 uNumFields = sizeof(fldDesc) / sizeof (fldDesc[0]);
  65.  
  66. //=====================================================================
  67. //  Function:
  68. //          StoredProcedures();
  69. //
  70. //  Description:
  71. //          This file shows how to create, use, and get information on
  72. //          stored procedures. Note that the syntax for stored procedure
  73. //          creation is specific to a given server.
  74. //=====================================================================
  75. void
  76. StoredProcedures (void)
  77. {
  78.     hDBIDb          hDb;            // Handle to the database.
  79.     DBIResult       rslt;           // Return value from IDAPI functions.
  80.     BOOL            bProcs;         // Supports stored procedures?
  81.     hDBICur         hCur;           // Cursor Handle.
  82.     SPParamDesc     spParamDesc;    // Used to determine size of record Buffer,
  83.                                     //   and number of paramaters.
  84.     pSPParamDesc    pspParamDesc;   // Holds info on all parameters.
  85.     pBYTE           pRecBuf;        // Record buffer.
  86.     UINT16          iCount;         // Current Parameter
  87.     UINT16          iRecBufSize;    // Size of the record
  88.     DBINAME         szProcName;     // Name of the procedure
  89.     hDBIStmt        hStmt;          // Statement handle     
  90.     UINT16          uLength;        // Length of the property
  91.     int             i;              // Loop counter
  92.     CHAR            szDelete[] = "DROP PROCEDURE PROCTEST";
  93.                                     // Statement to delete the procedure
  94.                                                                         
  95.     Screen("*** Stored Procedure Example ***\r\n");
  96.  
  97.     BREAK_IN_DEBUGGER();
  98.  
  99.     Screen("    Initializing IDAPI...");
  100.     if (InitAndConnect2(&hDb) != DBIERR_NONE)
  101.     {
  102.         Screen("\r\n*** End of Example ***");
  103.         return;
  104.     }
  105.  
  106.     rslt = DbiGetProp(hDb, dbPROCEDURES, &bProcs, sizeof(bProcs),
  107.                       &uLength);
  108.     ChkRslt(rslt, "GetProp");
  109.  
  110.     if (!bProcs)
  111.     {
  112.         Screen("\r\n    Error - specified server does not support stored"
  113.                " procedures.");
  114.         Screen("    Close the database and exit IDAPI...");
  115.         CloseDbAndExit(&hDb);
  116.         Screen("\r\n*** End of Example ***");
  117.         return;
  118.     }
  119.  
  120.     rslt = CreateSQLTable(hDb, szTblName);
  121.     if (rslt)
  122.     {
  123.         Screen("    Close the database and exit IDAPI...");
  124.         CloseDbAndExit(&hDb);
  125.         Screen("\r\n*** End of Example ***");
  126.         return;
  127.     }
  128.  
  129.     rslt = DbiSetProp(hDb, dbPARAMFMTQMARK, TRUE);
  130.     ChkRslt(rslt, "SetProp");
  131.  
  132.     rslt = CreateProcedure(hDb);
  133.     if (rslt)
  134.     {
  135.         Screen("\r\n    Drop the procedure...");
  136.         rslt = DbiQExecDirect(hDb, qrylangSQL, szDelete, NULL);
  137.         ChkRslt(rslt, "QExecDirect");
  138.  
  139.         Screen("    Delete the table...");
  140.         rslt = DbiDeleteTable(hDb, szTblName, NULL);
  141.         ChkRslt(rslt, "DeleteTable");
  142.  
  143.         Screen("    Close the database and exit IDAPI...");
  144.         CloseDbAndExit(&hDb);
  145.         Screen("\r\n*** End of Example ***");
  146.         return;
  147.     }
  148.  
  149.     Screen("    Display the list of procedures...");
  150.     rslt = DbiOpenSPList(hDb, FALSE, TRUE, NULL, &hCur);
  151.     ChkRslt(rslt, "OpenSPList");
  152.  
  153.     if (hCur)
  154.     {
  155.         DisplayInMemoryTable(hCur, 0);
  156.  
  157.         rslt = DbiCloseCursor(&hCur);
  158.         ChkRslt(rslt, "CloseCursor");
  159.     }
  160.  
  161.     strcpy(szProcName, "PROCTEST");
  162.  
  163.     Screen("\r\n    Display information about the parameters to"
  164.            " the procedure...");
  165.     rslt = DbiOpenSPParamList(hDb, szProcName, FALSE, 0, &hCur);
  166.     ChkRslt(rslt, "OpenSPParamList");
  167.  
  168.     if (hCur)
  169.     {
  170.         DisplayInMemoryTable(hCur, 0);
  171.  
  172.         rslt = DbiSetToEnd(hCur);
  173.         ChkRslt(rslt, "SetToEnd");
  174.  
  175.         // Get the last property to determine the count of properties...
  176.         rslt = DbiGetPriorRecord(hCur, dbiNOLOCK, (pBYTE)&spParamDesc, NULL);
  177.         ChkRslt(rslt, "GetPriorRecord");
  178.  
  179.         pspParamDesc = (pSPParamDesc)malloc((spParamDesc.uParamNum + 1) *
  180.                                             sizeof(SPParamDesc));
  181.  
  182.         memset((pVOID)pspParamDesc, 0, spParamDesc.uParamNum *
  183.                sizeof(SPParamDesc));
  184.  
  185.         rslt = DbiSetToBegin(hCur);
  186.         ChkRslt(rslt, "SetToBegin");
  187.  
  188.         // Get descriptors for each field
  189.         iRecBufSize = 0;
  190.         iCount = 0;
  191.         while ((rslt = DbiGetNextRecord(hCur, dbiNOLOCK,
  192.                                        (pBYTE)&(pspParamDesc[iCount]), NULL))
  193.                == DBIERR_NONE)
  194.         {
  195.             // Oracle CHAR parameters are unsized...
  196.             if (pspParamDesc[iCount].uFldType == fldZSTRING)
  197.             {
  198.                 if (pspParamDesc[iCount].iUnits1 == 0)
  199.                 {
  200.                     pspParamDesc[iCount].iUnits1 = 12;
  201.                 }
  202.  
  203.                 pspParamDesc[iCount].uLen = pspParamDesc[iCount].iUnits1 + 1;
  204.             }
  205.  
  206.             if (pspParamDesc[iCount].uFldType == fldBOOL)
  207.             {
  208.                 pspParamDesc[iCount].uLen = 2;
  209.             }
  210.  
  211.             if (pspParamDesc[iCount].uFldType == fldINT32)
  212.             {
  213.                 pspParamDesc[iCount].uLen = 4;
  214.             }
  215.  
  216.             iRecBufSize = iRecBufSize + pspParamDesc[iCount].uLen;
  217.             pspParamDesc[iCount].szName[0] = 0;
  218.             if (iCount)
  219.             {
  220.                 pspParamDesc[iCount].uOffset = pspParamDesc[iCount - 1].uOffset
  221.                                                +
  222.                                                pspParamDesc[iCount - 1].uLen;
  223.             }
  224.  
  225.             iCount++;
  226.         }
  227.  
  228.         if (rslt != DBIERR_EOF)
  229.         {
  230.             Screen("\r\n");
  231.             ChkRslt(rslt, "GetNextRecord");
  232.         }
  233.  
  234.         // Allocate space for the record buffer and parameters
  235.         pRecBuf = (pBYTE)malloc(iRecBufSize * sizeof(CHAR));
  236.         if (!pRecBuf)
  237.         {
  238.             rslt = DbiCloseCursor(&hCur);
  239.             ChkRslt(rslt, "CloseCursor");
  240.  
  241.             Screen("\r\n    Drop the procedure...");
  242.             rslt = DbiQExecDirect(hDb, qrylangSQL, szDelete, NULL);
  243.             ChkRslt(rslt, "QExecDirect");
  244.  
  245.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  246.             ChkRslt(rslt, "DeleteTable");
  247.             Screen("    Error - Out of memory");
  248.             CloseDbAndExit(&hDb);
  249.             return;
  250.         }
  251.  
  252.         memset((pVOID)pRecBuf, 0, iRecBufSize);
  253.  
  254.         // Set the input parameter - this is done for Sybase, as
  255.         //   in/out information is not returned for parameters to
  256.         //   stored procedures on Sybase.
  257.  
  258.         i = 0;
  259.         while ((i < iCount) && ((pspParamDesc[i].eParamType != paramIN)
  260.                && (pspParamDesc[i].eParamType != paramINOUT)
  261.                && (pspParamDesc[i].eParamType != paramUNKNOWN)))
  262.         {
  263.             i++;
  264.         }
  265.  
  266.         if (pspParamDesc[i].eParamType == paramUNKNOWN)
  267.         {
  268.             pspParamDesc[i].eParamType = paramIN;
  269.         }
  270.  
  271.         if (i < iCount)
  272.         {
  273.             memcpy((pVOID)(pRecBuf + pspParamDesc[i].uOffset), "Wong", 4);
  274.  
  275.             rslt = DbiCloseCursor(&hCur);
  276.             ChkRslt(rslt, "CloseCursor");
  277.  
  278.             Screen("\r\n    Execute the procedure...");
  279.  
  280.             // Could also use DbiQExecProcDirect:
  281.             //   rslt = DbiQExecProcDirect(hDb, szProcName, iCount, pspParamDesc,
  282.             //                             pRecBuf, &hCur);
  283.  
  284.             rslt = DbiQPrepareProc(hDb, szProcName, iCount, pspParamDesc, NULL,
  285.                                    &hStmt);
  286.             ChkRslt(rslt, "QPrepareProc");
  287.  
  288.             rslt = DbiQSetProcParams(hStmt, iCount, pspParamDesc, pRecBuf);
  289.             ChkRslt(rslt, "QSetProcParams");
  290.  
  291.             // For InterBase only:
  292.             //    Can also use the following syntax to get the results
  293.             //    of a procedure in the forum of a cursor:
  294.             //
  295.             //       'select * from <procedure_name>;
  296.             
  297.             rslt = DbiQExec(hStmt, &hCur);
  298.             ChkRslt(rslt, "QExec");
  299.  
  300.             if (hCur)
  301.             {
  302.                 Screen("\r\n    Return Values:");
  303.                 DisplayTable(hCur, 0);
  304.  
  305.                 rslt = DbiCloseCursor(&hCur);
  306.                 ChkRslt(rslt, "CloseCursor");
  307.             }
  308.             else if (rslt == DBIERR_NONE)
  309.             {
  310.                 Screen("\r\n    Return Values:\r\n");
  311.                 Screen("        First Name: %s",
  312.                        &(pRecBuf[pspParamDesc[1].uOffset]));
  313.                 Screen("        Last Name: %s",
  314.                        &(pRecBuf[pspParamDesc[2].uOffset]));
  315.             }
  316.  
  317.             rslt = DbiQFree(&hStmt);
  318.             ChkRslt(rslt, "QFree");
  319.         }
  320.         else
  321.         {
  322.             Screen("\r\n        Error - no input parameter...");
  323.         }
  324.     }
  325.  
  326.     if (pRecBuf)
  327.     {
  328.         free(pRecBuf);
  329.     }
  330.  
  331.     if (pspParamDesc)
  332.     {
  333.         free(pspParamDesc);
  334.     }
  335.  
  336.     Screen("\r\n    Drop the procedure...");
  337.     rslt = DbiQExecDirect(hDb, qrylangSQL, szDelete, NULL);
  338.     ChkRslt(rslt, "QExecDirect");
  339.  
  340.     rslt = DbiDeleteTable(hDb, szTblName, NULL);
  341.     ChkRslt(rslt, "DeleteTable");
  342.  
  343.     Screen("    Close the database and exit IDAPI...");
  344.     CloseDbAndExit(&hDb);
  345.  
  346.     Screen("\r\n*** End of Example ***");
  347. }
  348.  
  349. //=====================================================================
  350. //  Function:
  351. //          CreateSQLTable(hDb, pszTblName);
  352. //
  353. //  Input:  phDb        - Pointer to the database handle.
  354. //          pszTblName  - The name of the table to create.
  355. //
  356. //  Return: Result returned by IDAPI.
  357. //
  358. //  Description:
  359. //          This function will create a table and add records to that
  360. //          table.
  361. //=====================================================================
  362. DBIResult
  363. CreateSQLTable (hDBIDb hDb, pCHAR pszTblName)
  364. {
  365.     DBIResult   rslt;           // Value returned from IDAPI functions
  366.     CRTblDesc   crTblDesc;      // Table Descriptor
  367.     hDBICur     hCur;           // Cursor used for adding records
  368.  
  369.     // Initialize the Table Create Descriptor.
  370.     memset(&crTblDesc, 0, sizeof(CRTblDesc));
  371.  
  372.     strcpy(crTblDesc.szTblName, pszTblName);
  373.     crTblDesc.iFldCount     = uNumFields;
  374.     crTblDesc.pfldDesc      = fldDesc;
  375.     crTblDesc.iIdxCount     = 1;
  376.     crTblDesc.pidxDesc      = &IdxDesc;
  377.  
  378.     Screen("    Creating the table...");
  379.     rslt = DbiCreateTable(hDb, TRUE, &crTblDesc);
  380.     if (ChkRslt(rslt, "CreateTable") != DBIERR_NONE)
  381.     {
  382.         return rslt;
  383.     }
  384.  
  385.     rslt = DbiOpenTable(hDb, pszTblName, NULL,
  386.                         NULL, NULL, 0, dbiREADWRITE, dbiOPENSHARED,
  387.                         xltFIELD, FALSE, NULL, &hCur);
  388.     if (ChkRslt(rslt, "OpenTable") != DBIERR_NONE)
  389.     {
  390.         rslt = DbiDeleteTable(hDb, pszTblName, NULL);
  391.         ChkRslt(rslt, "DeleteTable");
  392.         return rslt;
  393.     }
  394.  
  395.     // Add records to the table.
  396.     Screen("    Adding records to the table...");
  397.     rslt = AddRecord(hCur, "Tom", "Smith");
  398.     rslt = AddRecord(hCur, "Jim", "Jones");
  399.     rslt = AddRecord(hCur, "Larry", "Peterson");
  400.     rslt = AddRecord(hCur, "Jane", "Jackson");
  401.     rslt = AddRecord(hCur, "Mary", "Wong");
  402.  
  403.     rslt = DbiCloseCursor(&hCur);
  404.     ChkRslt(rslt, "ClostTable");
  405.  
  406.     return rslt;
  407. }
  408.  
  409. //=====================================================================
  410. //  Function:
  411. //          AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  412. //
  413. //  Input:  hCur    - The table handle
  414. //          pFirst  - First Name
  415. //          pLast   - Last Name
  416. //
  417. //  Return: Result of adding the record to the table
  418. //
  419. //  Description:
  420. //          Insert a record into the table.
  421. //=====================================================================
  422. DBIResult
  423. AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  424. {
  425.     DBIResult   rslt;       // Return value from IDAPI functions
  426.     pBYTE       pRecBuf;    // Record buffer
  427.     CURProps    TblProps;   // Table properties
  428.  
  429.     // Allocate a record buffer.
  430.     rslt = DbiGetCursorProps(hCur, &TblProps);
  431.     ChkRslt(rslt, "GetCursorProps");
  432.  
  433.     pRecBuf = (pBYTE) malloc(TblProps.iRecBufSize * sizeof(BYTE));
  434.     if (pRecBuf == NULL)
  435.     {
  436.         Screen("    Error - Out of memory");
  437.         return DBIERR_NOMEMORY;
  438.     }
  439.  
  440.     // Clear the record buffer, then add the data.
  441.     rslt = DbiInitRecord(hCur, pRecBuf);
  442.     ChkRslt(rslt, "InitRecord");
  443.  
  444.     rslt = DbiPutField(hCur, 1, pRecBuf, (pBYTE) pFirst);
  445.     ChkRslt(rslt, "PutField");
  446.  
  447.     rslt = DbiPutField(hCur, 2, pRecBuf, (pBYTE) pLast);
  448.     ChkRslt(rslt, "PutField");
  449.  
  450.     rslt = DbiInsertRecord(hCur, dbiNOLOCK, pRecBuf);
  451.     ChkRslt(rslt, "InsertRecord");
  452.  
  453.     free(pRecBuf);
  454.  
  455.     return rslt;
  456. }
  457.  
  458. //=====================================================================
  459. //  Function:
  460. //          CreateProcedure (hDBIDb hDb)
  461. //
  462. //  Input:  hDb -   Handle to the Database
  463. //
  464. //  Return: Result of creating the procedure
  465. //
  466. //  Description:
  467. //          This function is used to create a stored procedure on a
  468. //          given server. As each server has it's own specific syntax
  469. //          for creating stored procedures, this function determines
  470. //          the type of the server and uses the appropriate syntax.
  471. //=====================================================================
  472. DBIResult
  473. CreateProcedure (hDBIDb hDb)
  474. {
  475.     DBIResult   rslt = DBIERR_NONE; // Return value from BDE
  476.     DBINAME     szDbType;           // Type of the server
  477.     UINT16      uLength;            // Length of the property
  478.     pCHAR       szCreate = NULL;    // SQL Statment to pass to
  479.                                     //   DbiQExecDirect
  480.     CHAR szCreateIB[] =             // Syntax for InterBase
  481.             "CREATE PROCEDURE PROCTEST (LAST VARCHAR(12))\r\n"
  482.             "RETURNS (FIRSTNAME VARCHAR(10),\r\n"
  483.             "         LASTNAME VARCHAR(12))\r\n"
  484.             "AS\r\n"
  485.             "BEGIN\r\n"
  486.             "   FOR select * from STORPROC s\r\n"
  487.             "   where s.LASTNAME = :LAST\r\n"
  488.             "   INTO :FIRSTNAME, :LASTNAME\r\n"
  489.             "   DO\r\n"
  490.             "     SUSPEND;\r\n"
  491.             "END;\r\n";
  492.  
  493.     CHAR szCreateOra [] =           // Syntax for Oracle
  494.             "CREATE PROCEDURE PROCTEST (LNAME IN VARCHAR,\r\n"
  495.             "                           FRST OUT VARCHAR,\r\n"
  496.             "                           LST OUT VARCHAR)\r\n"
  497.             "AS BEGIN\r\n"
  498.             "   select FRSTNAME, LASTNAME into FRST, LST from STORPROC\r\n"
  499.             "     where LASTNAME = LNAME;\r\n"
  500.             "END PROCTEST;\r\n";
  501.  
  502.     CHAR szCreateSyb [] =           // Syntax for Sybase
  503.             "CREATE PROCEDURE PROCTEST @LNAME varchar(12)\r\n"
  504.             "as\r\n"
  505.             "   select s.FRSTNAME, s.LASTNAME from STORPROC s\r\n"
  506.             "     where s.LASTNAME = @LNAME\r\n";
  507.  
  508.     rslt = DbiGetProp(hDb, dbDATABASETYPE, &szDbType, DBIMAXNAMELEN,
  509.                       &uLength);
  510.     ChkRslt(rslt, "GetProp");
  511.  
  512.     if (!strcmp(szDbType, "INTRBASE"))
  513.     {
  514.         szCreate = szCreateIB;
  515.     }
  516.     else
  517.     {
  518.         if (!strcmp(szDbType, "ORACLE"))
  519.         {
  520.             szCreate = szCreateOra;
  521.         }
  522.         else
  523.         {
  524.             if (!strcmp(szDbType, "SYBASE"))
  525.             {
  526.                 szCreate = szCreateSyb;
  527.             }
  528.         }
  529.     }
  530.  
  531.     if (szCreate)
  532.     {
  533.         Screen("\r\n    Create the procedure with the following query:"
  534.                "\r\n\r\n%s...", szCreate);
  535.         rslt = DbiQExecDirect(hDb, qrylangSQL, szCreate, NULL);
  536.         ChkRslt(rslt, "QExecDirect");
  537.     }
  538.     else
  539.     {
  540.         Screen("    Error - server not supported.");
  541.         // Force an error
  542.         rslt = 1;
  543.     }
  544.  
  545.     return rslt;
  546. }
  547.