home *** CD-ROM | disk | FTP | other *** search
/ QBasic & Borland Pascal & C / Delphi5.iso / C / BC_502 / 32SNIPIT.PAK / STORPROC.C < prev    next >
Encoding:
C/C++ Source or Header  |  1997-05-06  |  18.5 KB  |  539 lines
  1. // BDE32 3.x - (C) Copyright 1996 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 =
  204.                       (UINT16)(pspParamDesc[iCount].iUnits1 + 1);
  205.             }
  206.  
  207.             if (pspParamDesc[iCount].uFldType == fldBOOL)
  208.             {
  209.                 pspParamDesc[iCount].uLen = 2;
  210.             }
  211.  
  212.             if (pspParamDesc[iCount].uFldType == fldINT32)
  213.             {
  214.                 pspParamDesc[iCount].uLen = 4;
  215.             }
  216.  
  217.             iRecBufSize =
  218.                 (INT16)(iRecBufSize + pspParamDesc[iCount].uLen);
  219.             pspParamDesc[iCount].szName[0] = 0;
  220.             if (iCount)
  221.             {
  222.                 pspParamDesc[iCount].uOffset =
  223.                     (UINT16)(pspParamDesc[iCount - 1].uOffset +
  224.                              pspParamDesc[iCount - 1].uLen);
  225.             }
  226.  
  227.             iCount++;
  228.         }
  229.  
  230.         if (rslt != DBIERR_EOF)
  231.         {
  232.             Screen("\r\n");
  233.             ChkRslt(rslt, "GetNextRecord");
  234.         }
  235.  
  236.         // Allocate space for the record buffer and parameters
  237.         pRecBuf = (pBYTE)malloc(iRecBufSize * sizeof(CHAR));
  238.         if (!pRecBuf)
  239.         {
  240.             rslt = DbiCloseCursor(&hCur);
  241.             ChkRslt(rslt, "CloseCursor");
  242.  
  243.             Screen("\r\n    Drop the procedure...");
  244.             rslt = DbiQExecDirect(hDb, qrylangSQL, szDelete, NULL);
  245.             ChkRslt(rslt, "QExecDirect");
  246.  
  247.             rslt = DbiDeleteTable(hDb, szTblName, NULL);
  248.             ChkRslt(rslt, "DeleteTable");
  249.             Screen("    Error - Out of memory");
  250.             CloseDbAndExit(&hDb);
  251.             return;
  252.         }
  253.  
  254.         memset((pVOID)pRecBuf, 0, iRecBufSize);
  255.  
  256.         // Set the input parameter - this is done for Sybase, as
  257.         //   in/out information is not returned for parameters to
  258.         //   stored procedures on Sybase.
  259.  
  260.         i = 0;
  261.         while ((i < iCount) && ((pspParamDesc[i].eParamType != paramIN)
  262.                && (pspParamDesc[i].eParamType != paramINOUT)
  263.                && (pspParamDesc[i].eParamType != paramUNKNOWN)))
  264.         {
  265.             i++;
  266.         }
  267.  
  268.         if (pspParamDesc[i].eParamType == paramUNKNOWN)
  269.         {
  270.             pspParamDesc[i].eParamType = paramIN;
  271.         }
  272.  
  273.         if (i < iCount)
  274.         {
  275.             memcpy((pVOID)(pRecBuf + pspParamDesc[i].uOffset), "Wong", 4);
  276.  
  277.             rslt = DbiCloseCursor(&hCur);
  278.             ChkRslt(rslt, "CloseCursor");
  279.  
  280.             Screen("\r\n    Execute the procedure...");
  281.  
  282.             // Could also use DbiQExecProcDirect:
  283.             //   rslt = DbiQExecProcDirect(hDb, szProcName, iCount, pspParamDesc,
  284.             //                             pRecBuf, &hCur);
  285.  
  286.             rslt = DbiQPrepareProc(hDb, szProcName, iCount, pspParamDesc, NULL,
  287.                                    &hStmt);
  288.             ChkRslt(rslt, "QPrepareProc");
  289.  
  290.             rslt = DbiQSetProcParams(hStmt, iCount, pspParamDesc, pRecBuf);
  291.             ChkRslt(rslt, "QSetProcParams");
  292.  
  293.             // For InterBase only:
  294.             //    Can also use the following syntax to get the results
  295.             //    of a procedure in the forum of a cursor:
  296.             //
  297.             //       'select * from <procedure_name>;
  298.             
  299.             rslt = DbiQExec(hStmt, &hCur);
  300.             ChkRslt(rslt, "QExec");
  301.  
  302.             if (hCur)
  303.             {
  304.                 Screen("\r\n    Return Values:");
  305.                 DisplayTable(hCur, 0);
  306.  
  307.                 rslt = DbiCloseCursor(&hCur);
  308.                 ChkRslt(rslt, "CloseCursor");
  309.             }
  310.             else if (rslt == DBIERR_NONE)
  311.             {
  312.                 Screen("\r\n    Return Values:\r\n");
  313.                 Screen("        First Name: %s",
  314.                        &(pRecBuf[pspParamDesc[1].uOffset]));
  315.                 Screen("        Last Name: %s",
  316.                        &(pRecBuf[pspParamDesc[2].uOffset]));
  317.             }
  318.  
  319.             rslt = DbiQFree(&hStmt);
  320.             ChkRslt(rslt, "QFree");
  321.         }
  322.         else
  323.         {
  324.             Screen("\r\n        Error - no input parameter...");
  325.         }
  326.     }
  327.  
  328.     Screen("\r\n    Drop the procedure...");
  329.     rslt = DbiQExecDirect(hDb, qrylangSQL, szDelete, NULL);
  330.     ChkRslt(rslt, "QExecDirect");
  331.  
  332.     rslt = DbiDeleteTable(hDb, szTblName, NULL);
  333.     ChkRslt(rslt, "DeleteTable");
  334.  
  335.     Screen("    Close the database and exit IDAPI...");
  336.     CloseDbAndExit(&hDb);
  337.  
  338.     Screen("\r\n*** End of Example ***");
  339. }
  340.  
  341. //=====================================================================
  342. //  Function:
  343. //          CreateSQLTable(hDb, pszTblName);
  344. //
  345. //  Input:  phDb        - Pointer to the database handle.
  346. //          pszTblName  - The name of the table to create.
  347. //
  348. //  Return: Result returned by IDAPI.
  349. //
  350. //  Description:
  351. //          This function will create a table and add records to that
  352. //          table.
  353. //=====================================================================
  354. DBIResult
  355. CreateSQLTable (hDBIDb hDb, pCHAR pszTblName)
  356. {
  357.     DBIResult   rslt;           // Value returned from IDAPI functions
  358.     CRTblDesc   crTblDesc;      // Table Descriptor
  359.     hDBICur     hCur;           // Cursor used for adding records
  360.  
  361.     // Initialize the Table Create Descriptor.
  362.     memset(&crTblDesc, 0, sizeof(CRTblDesc));
  363.  
  364.     strcpy(crTblDesc.szTblName, pszTblName);
  365.     crTblDesc.iFldCount     = uNumFields;
  366.     crTblDesc.pfldDesc      = fldDesc;
  367.     crTblDesc.iIdxCount     = 1;
  368.     crTblDesc.pidxDesc      = &IdxDesc;
  369.  
  370.     Screen("    Creating the table...");
  371.     rslt = DbiCreateTable(hDb, TRUE, &crTblDesc);
  372.     if (ChkRslt(rslt, "CreateTable") != DBIERR_NONE)
  373.     {
  374.         return rslt;
  375.     }
  376.  
  377.     rslt = DbiOpenTable(hDb, pszTblName, NULL,
  378.                         NULL, NULL, 0, dbiREADWRITE, dbiOPENSHARED,
  379.                         xltFIELD, FALSE, NULL, &hCur);
  380.     if (ChkRslt(rslt, "OpenTable") != DBIERR_NONE)
  381.     {
  382.         rslt = DbiDeleteTable(hDb, pszTblName, NULL);
  383.         ChkRslt(rslt, "DeleteTable");
  384.         return rslt;
  385.     }
  386.  
  387.     // Add records to the table.
  388.     Screen("    Adding records to the table...");
  389.     AddRecord(hCur, "Tom", "Smith");
  390.     AddRecord(hCur, "Jim", "Jones");
  391.     AddRecord(hCur, "Larry", "Peterson");
  392.     AddRecord(hCur, "Jane", "Jackson");
  393.     AddRecord(hCur, "Mary", "Wong");
  394.  
  395.     rslt = DbiCloseCursor(&hCur);
  396.     ChkRslt(rslt, "ClostTable");
  397.  
  398.     return rslt;
  399. }
  400.  
  401. //=====================================================================
  402. //  Function:
  403. //          AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  404. //
  405. //  Input:  hCur    - The table handle
  406. //          pFirst  - First Name
  407. //          pLast   - Last Name
  408. //
  409. //  Return: Result of adding the record to the table
  410. //
  411. //  Description:
  412. //          Insert a record into the table.
  413. //=====================================================================
  414. DBIResult
  415. AddRecord (hDBICur hCur, pCHAR pFirst, pCHAR pLast)
  416. {
  417.     DBIResult   rslt;       // Return value from IDAPI functions
  418.     pBYTE       pRecBuf;    // Record buffer
  419.     CURProps    TblProps;   // Table properties
  420.  
  421.     // Allocate a record buffer.
  422.     rslt = DbiGetCursorProps(hCur, &TblProps);
  423.     ChkRslt(rslt, "GetCursorProps");
  424.  
  425.     pRecBuf = (pBYTE) malloc(TblProps.iRecBufSize * sizeof(BYTE));
  426.     if (pRecBuf == NULL)
  427.     {
  428.         Screen("    Error - Out of memory");
  429.         return DBIERR_NOMEMORY;
  430.     }
  431.  
  432.     // Clear the record buffer, then add the data.
  433.     rslt = DbiInitRecord(hCur, pRecBuf);
  434.     ChkRslt(rslt, "InitRecord");
  435.  
  436.     rslt = DbiPutField(hCur, 1, pRecBuf, (pBYTE) pFirst);
  437.     ChkRslt(rslt, "PutField");
  438.  
  439.     rslt = DbiPutField(hCur, 2, pRecBuf, (pBYTE) pLast);
  440.     ChkRslt(rslt, "PutField");
  441.  
  442.     rslt = DbiInsertRecord(hCur, dbiNOLOCK, pRecBuf);
  443.     ChkRslt(rslt, "InsertRecord");
  444.  
  445.     free(pRecBuf);
  446.  
  447.     return rslt;
  448. }
  449.  
  450. //=====================================================================
  451. //  Function:
  452. //          CreateProcedure (hDBIDb hDb)
  453. //
  454. //  Input:  hDb -   Handle to the Database
  455. //
  456. //  Return: Result of creating the procedure
  457. //
  458. //  Description:
  459. //          This function is used to create a stored procedure on a
  460. //          given server. As each server has it's own specific syntax
  461. //          for creating stored procedures, this function determines
  462. //          the type of the server and uses the appropriate syntax.
  463. //=====================================================================
  464. DBIResult
  465. CreateProcedure (hDBIDb hDb)
  466. {
  467.     DBIResult   rslt;               // Return value from BDE
  468.     DBINAME     szDbType;           // Type of the server
  469.     UINT16      uLength;            // Length of the property
  470.     pCHAR       szCreate = NULL;    // SQL Statment to pass to
  471.                                     //   DbiQExecDirect
  472.     CHAR szCreateIB[] =             // Syntax for InterBase
  473.             "CREATE PROCEDURE PROCTEST (LAST VARCHAR(12))\r\n"
  474.             "RETURNS (FIRSTNAME VARCHAR(10),\r\n"
  475.             "         LASTNAME VARCHAR(12))\r\n"
  476.             "AS\r\n"
  477.             "BEGIN\r\n"
  478.             "   FOR select * from STORPROC s\r\n"
  479.             "   where s.LASTNAME = :LAST\r\n"
  480.             "   INTO :FIRSTNAME, :LASTNAME\r\n"
  481.             "   DO\r\n"
  482.             "     SUSPEND;\r\n"
  483.             "END;\r\n";
  484.  
  485.     CHAR szCreateOra [] =           // Syntax for Oracle
  486.             "CREATE PROCEDURE PROCTEST (LNAME IN VARCHAR,\r\n"
  487.             "                           FRST OUT VARCHAR,\r\n"
  488.             "                           LST OUT VARCHAR)\r\n"
  489.             "AS BEGIN\r\n"
  490.             "   select FRSTNAME, LASTNAME into FRST, LST from STORPROC\r\n"
  491.             "     where LASTNAME = LNAME;\r\n"
  492.             "END PROCTEST;\r\n";
  493.  
  494.     CHAR szCreateSyb [] =           // Syntax for Sybase
  495.             "CREATE PROCEDURE PROCTEST @LNAME varchar(12)\r\n"
  496.             "as\r\n"
  497.             "   select s.FRSTNAME, s.LASTNAME from STORPROC s\r\n"
  498.             "     where s.LASTNAME = @LNAME\r\n";
  499.  
  500.     rslt = DbiGetProp(hDb, dbDATABASETYPE, &szDbType, DBIMAXNAMELEN,
  501.                       &uLength);
  502.     ChkRslt(rslt, "GetProp");
  503.  
  504.     if (!strcmp(szDbType, "INTRBASE"))
  505.     {
  506.         szCreate = szCreateIB;
  507.     }
  508.     else
  509.     {
  510.         if (!strcmp(szDbType, "ORACLE"))
  511.         {
  512.             szCreate = szCreateOra;
  513.         }
  514.         else
  515.         {
  516.             if (!strcmp(szDbType, "SYBASE"))
  517.             {
  518.                 szCreate = szCreateSyb;
  519.             }
  520.         }
  521.     }
  522.  
  523.     if (szCreate)
  524.     {
  525.         Screen("\r\n    Create the procedure with the following query:"
  526.                "\r\n\r\n%s...", szCreate);
  527.         rslt = DbiQExecDirect(hDb, qrylangSQL, szCreate, NULL);
  528.         ChkRslt(rslt, "QExecDirect");
  529.     }
  530.     else
  531.     {
  532.         Screen("    Error - server not supported.");
  533.         // Force an error
  534.         rslt = 1;
  535.     }
  536.  
  537.     return rslt;
  538. }
  539.