17.00 - 17.05 - C関数定義 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1147-170K-JPN
Language
日本語 (日本)
/*****  C source file name: ET001_xsp.c  *****/

#define SQL_TEXT Latin_Text

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

#define CONNECTED 0
#define NOT_CONNECTED 1
#define OK 0
#define STOP 1
#define FAILED -1
#define MAX_SESSIONS 1

#define D8CAIRXSIZ 24
#define FIXED_ELM_LEN_8 42
#define D8XILMNTSIZE 4
#define SPOPTIONSSIZE 2

struct ERROR_FAIL_Type
{
    Word StatementNo;
    Word Info;
    Word Code;
    Word Length;
    char Msg[255];
} ;

/******************************************************************
     dbc_init()  Initialize DBCAREA.
******************************************************************/
static Int16
dbc_init(DBCAREA *dbcarea_pt,
         SQL_TEXT *error_message)
{
    Int32 result;
    char cnta[4];

    dbcarea_pt->total_len = sizeof(struct DBCAREA);
    DBCHINI(&result, cnta, dbcarea_pt);
    if (result != EM_OK)
    {
        sprintf((char *) error_message,
                "Error in initialization --%s", dbcarea_pt->msg_text);
        return result;
    }
    return EM_OK;
}

/******************************************************************
     set_options()  Set DBCAREA options.
******************************************************************/
static void
set_options(DBCAREA *dbcarea_pt)
{
    dbcarea_pt->change_opts               = 'Y';
    dbcarea_pt->use_presence_bits         = 'N';
    dbcarea_pt->keep_resp                 = 'N';
    dbcarea_pt->loc_mode                  = 'Y';
    dbcarea_pt->var_len_req               = 'N';
    dbcarea_pt->save_resp_buf             = 'N';
    dbcarea_pt->two_resp_bufs             = 'N';
    dbcarea_pt->ret_time                  = 'N';
    dbcarea_pt->wait_for_resp             = 'Y';
    dbcarea_pt->req_proc_opt              = 'E';
    dbcarea_pt->req_buf_len               = 1024;
    dbcarea_pt->resp_buf_len              = 1024;
    dbcarea_pt->data_encryption           = 'N';
    dbcarea_pt->create_default_connection = 'Y';
}

/******************************************************************
     dbc_con()  Establish a default connection.
******************************************************************/
static Int16
dbc_con(DBCAREA *dbcarea_pt,
        SQL_TEXT *error_message)
{
    char cnta[4];
    Int32 result;

    dbcarea_pt->func=DBFCON;
    DBCHCL(&result, cnta, dbcarea_pt);
    if (result != EM_OK)
    {
        sprintf((char *)error_message,
                "Error in Logon--%s\n", dbcarea_pt->msg_text);
        return result;
    }
    return EM_OK;
}

/******************************************************************
     dbc_irq()  Initiate request.
******************************************************************/
static Int16
dbc_irq(DBCAREA *dbcarea_pt,
        SQL_TEXT *error_message,
        char *str)
{
    Int32 result;
    char cnta[4];
    dbcarea_pt->func = DBFIRQ;
    dbcarea_pt->req_ptr = str;
    dbcarea_pt->req_len = strlen(str);
    DBCHCL(&result, cnta, dbcarea_pt);
    if (result != EM_OK)
    {
        sprintf((char *)error_message,
                "Error in initiating a request--%s",
                dbcarea_pt->msg_text);
        return result;
    }
    return EM_OK;

}

/******************************************************************
     fetch_request()  Fetch parcels and check activity type.
******************************************************************/
static Int16
fetch_request(DBCAREA *dbcarea_pt,
              int stmnt_no,
              char *resultx,
              SQL_TEXT *error_message)
{

    long request, session;

    Int32 result;
    char cnta[4];
    union
      {
        int  acc_value;
        unsigned char  acc_c[4];
    }  acc;
    char strptr[256];
    int count, i, status, rowcount;
    int ShowFlag = 0;
    char ctc[6400];
    struct CliSuccessType *SuccPcl;
    struct CliOkType *OKPcl;
    struct CliFailureType *FailPcl;

    request = dbcarea_pt->o_req_id;
    session = dbcarea_pt->o_sess_id;

    dbcarea_pt->i_sess_id = session;
    dbcarea_pt->i_req_id = request;
    dbcarea_pt->func = DBFFET;
    status=OK;
    rowcount=1;
    while (status == OK)
    {
      DBCHCL(&result, cnta, dbcarea_pt);
      count=1;
      if (result == REQEXHAUST) status = STOP;
      else if (result != EM_OK) status = FAILED;
      else
      {
        switch(dbcarea_pt->fet_parcel_flavor)
        {
          case PclSUCCESS :
             SuccPcl =
                (struct CliSuccessType *)dbcarea_pt->fet_data_ptr;
             memcpy(acc.acc_c, SuccPcl->ActivityCount, 4);
             sprintf(strptr, "[%d]Succ:Act(%d)|\0",
                stmnt_no, SuccPcl->ActivityType);
             strcat(resultx, strptr);
             break;
          case PclOK :
             OKPcl = (struct CliOkType *)dbcarea_pt->fet_data_ptr;
             sprintf(strptr, "[%d]OK:Act(%d)|\0",
                stmnt_no, OKPcl->ActivityType);
             strcat(resultx, strptr);
             break;
          case PclRECORD :
             memcpy(&ctc[0], &dbcarea_pt->fet_data_ptr[0],1);
             memcpy(&ctc[1], &dbcarea_pt->fet_data_ptr[1],1);
             memcpy(&ctc[2], &dbcarea_pt->fet_data_ptr[2],1);
             break;
          case PclENDSTATEMENT :
             break;
          case PclFAILURE :
             FailPcl =
                (struct CliFailureType *)dbcarea_pt->fet_data_ptr;
             sprintf(strptr, "[%d]Fail:Cde(%d)|\0",
                stmnt_no, FailPcl->Code);
             strcat(resultx, strptr);
             break;
          case PclERROR :
             sprintf(strptr, "[%d]PclError");
             strcat(resultx, strptr);
             status = STOP;
             return -1;
        } /* end of switch */
      } /* end of else */
    } /* end of while */
    if (status == FAILED)
    {
        sprintf((char *)error_message,
                "Error in fetching a request--%s",dbcarea_pt->msg_text);
        return status;
    }
    return EM_OK;
}

/******************************************************************
     end_request()  End request.
******************************************************************/
static Int16
end_request(DBCAREA *dbcarea_pt,
            SQL_TEXT *error_message)
{
    Int32 result;
    char cnta[4];

    dbcarea_pt->i_sess_id = dbcarea_pt->o_sess_id;
    dbcarea_pt->i_req_id = dbcarea_pt->o_req_id;
    dbcarea_pt->func = DBFERQ;
    DBCHCL(&result, cnta, dbcarea_pt);

    if (result != EM_OK)
    {
        sprintf((char *)error_message,
                 "Error in EndRequest--%s", dbcarea_pt->msg_text);
        return result;
    }
    return EM_OK;

}

/******************************************************************
     Entry point for external stored procedure.
******************************************************************/
void
ET001_xsp1(VARCHAR_LATIN   *A_Name,
           VARCHAR_LATIN   *result,
           int             *a_name_i,
           int             *result_i,
           char             sqlstate[6],
           SQL_TEXT         extname[129],
           SQL_TEXT         specific_name[129],
           SQL_TEXT         error_message[257])
{
  DBCAREA dbcarea;

  char str1[200];  /* DELETE USER request */
  char str2[200];  /* DROP USER request */
  char str3[200];  /* DROP ROLE R_001 request */
  char str4[200];  /* CREATE ROLE R_001 request */
  char str5[200];  /* GRANT ... to R_001 request */
  char str6[200];  /* DROP PROFILE request */
  char str7[200];  /* CREATE PROFILE P_001 request */
  char str8[200];  /* CREATE USER request */

  if (dbc_init(&dbcarea, error_message) != EM_OK)
  {
      strcpy (sqlstate, "U0005");
      return;
  }
  set_options(&dbcarea);
  if (dbc_con(&dbcarea, error_message) != EM_OK)
  {
     strcpy (sqlstate, "U0006");
     return;
  }
  if (fetch_request(&dbcarea, 0, (char *) result, error_message) 
    != EM_OK)
  {
      strcpy (sqlstate, "U0007");
      return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
     strcpy(sqlstate, "U0008");
     return;
  }

  /***** DELETE USER request *****/
  memset(&str1, ' ', 100);
  sprintf(str1, "Delete User %s;", A_Name);
  if (dbc_irq(&dbcarea, error_message, str1) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
          return;
  }
  if (fetch_request(&dbcarea, 1, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** DROP USER request *****/
  memset(&str2, ' ', 100);
  sprintf(str2, "Drop User %s;", A_Name);
  if (dbc_irq(&dbcarea, error_message, str2) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
          return;
  }
  if (fetch_request(&dbcarea, 2, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** DROP ROLE request *****/
  memset(&str3, ' ', 100);
  sprintf(str3, "Drop Role R_001;");
  if (dbc_irq(&dbcarea, error_message, str3) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
           return;
  }
  if (fetch_request(&dbcarea, 3, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** CREATE ROLE request *****/
  memset(&str4, ' ', 100);
  sprintf(str4, "CREATE ROLE R_001;");
  if (dbc_irq(&dbcarea, error_message, str4) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
          return;
  }
  if (fetch_request(&dbcarea, 4, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** GRANT request *****/
  memset(&str5, ' ', 100);
  sprintf(str5,
    "GRANT ALL BUT CREATE USER, DROP USER ON CliXSP_ETUser TO R_001;");
  if (dbc_irq(&dbcarea, error_message, str5) != EM_OK)
  {
           strcpy(sqlstate, "U0009");
           return;
  }
  if (fetch_request(&dbcarea, 5, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** DROP PROFILE request *****/
  memset(&str6, ' ', 100);
  sprintf(str6, "DROP PROFILE P_001;");
  if (dbc_irq(&dbcarea, error_message, str6) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
           return;
  }
  if (fetch_request(&dbcarea, 6, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** CREATE PROFILE request *****/
  memset(&str7, ' ', 100);
  sprintf(str7, "CREATE PROFILE P_001 AS ACCOUNT = '&M', SPOOL = 100000, TEMPORARY = 100000;");
  if (dbc_irq(&dbcarea, error_message, str7) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
           return;
  }
  if (fetch_request(&dbcarea, 7, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
        {
          strcpy(sqlstate, "U0011");
          return;
  }

  /***** CREATE USER request *****/
  memset(&str8, ' ', 100);
  sprintf(str8, "CREATE USER %s from CliXSP_ETUser AS PERM = 100000, 
PASSWORD = u001, DEFAULT ROLE = R_001, PROFILE = P_001;", A_Name);
  if (dbc_irq(&dbcarea, error_message, str8) != EM_OK)
  {
          strcpy(sqlstate, "U0009");
          return;
  }
  if (fetch_request(&dbcarea, 8, (char*)result, error_message) != EM_OK)
  {
          strcpy (sqlstate, "U0010");
          return;
  }
  if (end_request(&dbcarea, error_message) != EM_OK)
  {
          strcpy(sqlstate, "U0011");
          return;
  }

}