C Function Definition - Analytics Database - Teradata Vantage

/*****  C source file name: sqlxspexample.c  *****/

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <coptypes.h>
#include <coperr.h>
#include <parcel.h>
#include <dbcarea.h>
#include <dbchqep.h>

typedef struct
{
    Word            StatementNo;
    Word            Info;
    Word            Code;
    Word            Length;
    char            Msg[1];         /* 1 to 255 bytes    */
 } PclErrorFailBody, *PclErrorFailBody_pt;

struct DBCAREA DBCArea;
char * pContext;
DBCAREA *pDBCArea;
DBCAREAX *pDbcAreaX;

/******************************************************************
     Initialize()  Initialize DBCAREA.
******************************************************************/
Int32 Initialize()
{
    Int32 Result;

    pDBCArea = &DBCArea;
    pDBCArea->total_len = sizeof(struct DBCAREA);

    DBCHINI(&Result, pContext, &DBCArea);

    pDBCArea->change_opts        = 'Y'  ;
    pDBCArea->req_buf_len        = 1024;
    pDBCArea->resp_buf_len       = 1024;
    pDBCArea->use_presence_bits  = 'N'  ;
    pDBCArea->keep_resp          = 'N'  ;
    pDBCArea->loc_mode           = 'Y'  ;
    pDBCArea->var_len_req        = 'N'  ;
    pDBCArea->save_resp_buf      = 'N'  ;
    pDBCArea->two_resp_bufs      = 'N'  ;
    pDBCArea->ret_time           = 'N'  ;
    pDBCArea->wait_for_resp      = 'Y'  ;
    pDBCArea->req_proc_opt       = 'E'  ;
    pDBCArea->maximum_parcel     = 'H';
    pDBCArea->resp_mode          = 'I';
    pDBCArea->dynamic_result_sets_allowed = 'Y';

    return Result;
}

/******************************************************************
     Logon()  Establish a default connection.
******************************************************************/
Int32 Logon(char *LogonStr)
{
    Int32                   Result;
    pDBCArea->func=DBFCON;
    if (LogonStr == NULL)
    {
       pDBCArea->create_default_connection = 'Y';
    }
    else
    {
        pDBCArea->logon_len = (Int32) strlen(LogonStr);
        pDBCArea->logon_ptr = LogonStr;
    }

    DBCHCL(&Result, pContext, pDBCArea);
    if (Result != EM_OK)
        return Result;

    /* Fetch the response  */
    pDBCArea->i_sess_id = pDBCArea->o_sess_id;
    pDBCArea->i_req_id  = pDBCArea->o_req_id;
    pDBCArea->func      = DBFFET;
    DBCHCL(&Result,pContext,pDBCArea);
    if (Result != EM_OK)
        return Result;

    if ( pDBCArea->fet_parcel_flavor != PclSUCCESS )
    {
        if (   pDBCArea->fet_parcel_flavor == PclFAILURE
            || pDBCArea->fet_parcel_flavor == PclERROR )
        {
            return pDBCArea->fet_parcel_flavor;
        }
    }

    /* End the logon request */
    pDBCArea->i_sess_id = pDBCArea->o_sess_id;
    pDBCArea->i_req_id  = pDBCArea->o_req_id;
    pDBCArea->func      = DBFERQ;
    DBCHCL(&Result, pContext, pDBCArea);
    return Result;
}

/******************************************************************
     Logoff()  Disconnect.
******************************************************************/
Int32 Logoff()
{
    Int32 Result;

    pDBCArea->func = DBFDSC;
    DBCHCL(&Result, pContext, pDBCArea);
    return Result;
}

/******************************************************************
     Execute() Execute the specified request.
******************************************************************/
Int32 Execute(char *ReqStr)
{
    Int32 Result;

    /* Issue the request */
    pDBCArea->func           = DBFIRQ;
    pDBCArea->change_opts    = 'Y';
    pDBCArea->req_ptr        = ReqStr;
    pDBCArea->req_len        = (Int32) strlen(pDBCArea->req_ptr);
    DBCHCL(&Result, pContext, pDBCArea);
    if (Result != EM_OK) return Result;

    /* Fetch the result parcels */
    pDBCArea->func = DBFFET;
    pDBCArea->change_opts    = 'Y';
    pDBCArea->i_req_id  = pDBCArea->o_req_id;

    while (Result == EM_OK)
       DBCHCL(&Result, pContext, pDBCArea);

    /* End the request */
    pDBCArea->func = DBFERQ;
    DBCHCL(&Result, pContext, pDBCArea);
    return Result;
}

/******************************************************************
     DoTheWork()  Create the tables, call the stored procedure.
******************************************************************/
void DoTheWork()
{
    Int32       Result;
    Int32       a;
    Int32       CallReqNo;
    Int32       Flavor;
    char        ReqStr[255];
    Int32*      pInt32;
    char*       pChar;

    /* Create a trace table */
    Result = Execute("DROP TABLE TraceTable;");
    if (Result != EM_OK) return;

    Result = Execute("CREATE TABLE TraceTable(SeqNo INT GENERATED BY 
DEFAULT AS IDENTITY, Flavor INT, Text VARCHAR(100));");
    if (Result != EM_OK) return;

    /* Create a table to insert and select rows */
    Result = Execute("DROP TABLE tab1;");
    if (Result != EM_OK) return;

    Result = Execute("CREATE TABLE tab1(a INT, b INT);");
    if (Result != EM_OK) return;

    /* Insert the rows */
    for (a=1;a<=10;a++)
    {
        pDBCArea->using_data_ptr        = (char *) &a;
        pDBCArea->using_data_len        = sizeof(a);
        Result = Execute("USING(a INT) INSERT tab1(:a, :a);");
        if (Result != EM_OK) return;
    }

    /* Call a procedure which will return a result set */
    pDBCArea->func           = DBFIRQ;
    pDBCArea->change_opts    = 'Y';
    pDBCArea->req_ptr        = "CALL p1('SEL * FROM tab1 ORDER BY 1');";
    pDBCArea->req_len        = (Int32) strlen(pDBCArea->req_ptr);
    DBCHCL(&Result, pContext, pDBCArea);
    if (Result != EM_OK) return;

    /* Remember the request number */
    CallReqNo = pDBCArea->o_req_id;

    /* Fetch the result parcels and insert into the trace table */
    while (Result == EM_OK)
    {
        /* Fetch a parcel from the CALL */
        pDBCArea->func = DBFFET;
        pDBCArea->change_opts    = 'Y';
        pDBCArea->i_req_id  = CallReqNo;

        DBCHCL(&Result, pContext, pDBCArea);
        if (Result != EM_OK) return;
        Flavor = pDBCArea->fet_parcel_flavor;

        /* Put this parcel into the trace table */
        pDBCArea->func           = DBFIRQ;
        pDBCArea->change_opts    = 'Y';
        pDBCArea->using_data_ptr = (char *) &Flavor;
        pDBCArea->using_data_len = sizeof(Flavor);

        if (pDBCArea->fet_parcel_flavor == PclSUCCESS)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'Success')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclDATAINFO)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'DataInfo')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclXDIX)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'DataInfoX')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclRECORD)
        {
            /* Point to the integer in the record parcel, */
            /* skip past the indicator byte               */
            pChar = pDBCArea->fet_data_ptr;
            pChar++;
            pInt32 = (Int32 *) pChar;
            sprintf(ReqStr, 
               "USING (a INT) INSERT tracetable(,:a,'Record %d ')",
               *pInt32);
            pDBCArea->req_ptr = ReqStr;
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclENDSTATEMENT)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'EndStatement')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclENDREQUEST)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'EndRequest')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclRESULTSET)
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'ResultSet')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else if (pDBCArea->fet_parcel_flavor == PclFAILURE)
        {
            pChar = pDBCArea->fet_data_ptr;
            pChar += 4;
            pInt32 = (Int32 *) pChar;
            pChar += 4;
            for(a=0;a<strlen(pChar);a++)
               if(pChar[a]==0x27) pChar[a]=' ';
            sprintf(ReqStr,
               "USING (a INT) INSERT tracetable(,:a,'Failure %d %s')",
               (*pInt32)&0x0000ffff, pChar);
            pDBCArea->req_ptr = ReqStr;
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        else
        {
            pDBCArea->req_ptr = 
               "USING (a INT) INSERT tracetable(,:a,'')";
            pDBCArea->req_len = (Int32) strlen(pDBCArea->req_ptr);
        }
        DBCHCL(&Result, pContext, pDBCArea);
        if (Result != EM_OK) return;

        /* Fetch the success etc from the insert into the trace table */
        pDBCArea->func = DBFFET;
        pDBCArea->change_opts = 'Y';
        pDBCArea->i_req_id = pDBCArea->o_req_id;
        while (Result == EM_OK)
            DBCHCL(&Result, pContext, pDBCArea);

        /* End the insert request */
        pDBCArea->func = DBFERQ;
        DBCHCL(&Result, pContext, pDBCArea);

        /* Go and fetch the next parcel from the CALL */
    }
}

/******************************************************************
     Entry point for the external stored procedure.
******************************************************************/
void sqlxspex(
           char      sqlstate[6],
           SQL_TEXT  extname[129],
           SQL_TEXT  specific_name[129],
           SQL_TEXT  error_message[257])
{
    Initialize();

    Logon(NULL);

    DoTheWork();

    Logoff();

}