oradyn.pc

TUXEDO编程例子

/********************************************************************
Author:sylpjx           Email:[e-mail]
MODIFY TIME: 2003-3            dev2dev ID: sylpjx
PROGRAM NAME: OraDyn.pc                                       
程序说明：这个程序是ORACLE的动态方法四的测试程序，
采用的是TUXEDO中的TPCALL（同步通信方式）
*********************************************************************/
#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <stdlib.h>
#include <ctype.h>
#include <atmi.h>	/* TUXEDO Header File */
#include <userlog.h>	/* TUXEDO Header File */
#include <fml.h>
#include "userfml.h"

EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC SQL INCLUDE sqlcpr;


/* Maximum number of select-list items or bind variables.
定义选择表项和绑定变量最大的个数*/
#define MAX_ITEMS         40

/* Maximum lengths of the _names_ of the
   select-list items or indicator variables. 
   定义选择表项变量名和指示器变量名的最大长度。*/
#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30


int alloc_descriptors(int, int, int);
void process_select_list();
void sql_error();
char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
                        "UPDATE", "update", "DELETE", "delete"};

EXEC SQL BEGIN DECLARE SECTION;
    char    dyn_statement[1024];
    EXEC SQL VAR dyn_statement IS STRING(1024);/*利用这个语句可以去掉dyn_statement多余的空格*/
EXEC SQL END DECLARE SECTION;
 
SQLDA *bind_dp;
SQLDA *select_dp;

FBFR * SndBuf;
long row_len,col_len;
char * tmpbuf;
FLDOCC OccCount;

void DYN4(rqst)
TPSVCINFO *rqst;
{
	int i;

	EXEC SQL BEGIN DECLARE SECTION;
		char userid[12] = "SCOTT/TIGER";
	EXEC SQL END DECLARE SECTION;
	/*连接数据库，未加入出错处理，请自行加入*/
	EXEC SQL CONNECT :userid;
	userlog ("Connected.\n");
	/*分配接受和发送的缓冲区*/
	if((tmpbuf = (char *) tpalloc("STRING", NULL, 1024)) == NULL) 
	{
		userlog (" allocate send buffer failure: tmpbuf.\n");
		tpreturn(TPFAIL,0,NULL,0L,0);
	}

	if((SndBuf = (FBFR *) tpalloc("FML", NULL, 8*1024)) == NULL) 
	{
		userlog ("Error allocating FML buffer\n");
		tpfree(tmpbuf);
		tpreturn(TPFAIL,0,NULL,0L,0);
	}

	strcpy(tmpbuf,rqst->data); /*接受客户端的数据*/
	userlog("tmpbuf =%s",tmpbuf);
	strcpy(dyn_statement,tmpbuf);
	userlog("dyn_statement=%s\n",dyn_statement);

	OccCount=0;/*初始化变量*/
	
    /* Only support SELECT statement,程序只支持选择语句 */
    if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
        (strncmp(dyn_statement, "select", 6) != 0))
    {
		userlog ("'%s' isn't a SELECT statement\n",dyn_statement);
		tpreturn(TPFAIL,0,NULL,0L,0);
    }

    /* Allocate memory for the select and bind descriptors.
	分配内存为选择和绑定描述符*/
    if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
    {
		userlog ("Error alloc_descriptor.\n");
		tpreturn(TPFAIL,0,NULL,0L,0);
    }
    
    /* Prepare the statement and declare a cursor. 
	声明游标*/
    EXEC SQL WHENEVER SQLERROR DO sql_error();

    EXEC SQL PREPARE S FROM :dyn_statement;
   
    EXEC SQL DECLARE C CURSOR FOR S;

    /* Describe any bind variables (input host variables) 
	声明绑定变量*/
    EXEC SQL WHENEVER SQLERROR DO sql_error();

    bind_dp->N = MAX_ITEMS;  /* Initialize count of array elements.初始化最大绑定变量个数 */
    EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

    /* Don't support bind variable */
    if (bind_dp->F != 0)
    {
		userlog ("\n Don't support bind variable\n");
		tpreturn(TPFAIL,0,NULL,0L,0);
    }
    bind_dp->N=0;

    /* Open the cursor and execute the statement.
     * If the statement is not a query (SELECT), the
     * statement processing is completed after the
     * OPEN.
     */
	 /* 打开游标和执行该语句，如果该语句是SELECT，则在OPEN之后处理结合*/

    EXEC SQL OPEN C USING DESCRIPTOR bind_dp;

    /* Call the function that processes the select-list.
     * If the statement is not a query, this function
     * just returns, doing nothing.
     */
	 /*调用函数process_select_list()来处理选择表项，如果该语句不是SELECT，则不作任何操作*/
    process_select_list();

    /* Tell user how many rows processed. 
	计算处理了多少条记录*/
    for (i = 0; i < 8; i++)
    {
       if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
       {
           userlog("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
                   sqlca.sqlerrd[2] == 1 ? '\0' : 's');
           
		   break;
       }
    }
    row_len = sqlca.sqlerrd[2];

    /* When done, free the memory allocated for
       pointers in the bind and select descriptors. 
	   释放结合和选择描述区中为指针分配的存储空间*/
    for (i = 0; i < MAX_ITEMS; i++)
    {    
        if (bind_dp->V[i] != (char *) 0)
            free(bind_dp->V[i]);
        free(bind_dp->I[i]);   /* MAX_ITEMS were allocated. */
        if (select_dp->V[i] != (char *) 0)
            free(select_dp->V[i]);
        free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
    }

    /* Free space used by the descriptors themselves. 
	释放SQLDA所用的空间*/
    sqlclu(bind_dp);
    sqlclu(select_dp);

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    /* Close the cursor. */
    EXEC SQL CLOSE C;

	/*提交并释放数据库*/
    EXEC SQL COMMIT WORK RELEASE;
	
    /*在FML缓冲区SndBuf中加入处理记录的条数、表的字段的个数、和请求的SQL语句*/
    userlog ("input FML with ROWLEN :%d\n",ROWLEN);
    Fchg(SndBuf, ROWLEN, 0, (char *)&row_len, (FLDLEN)0);
    userlog ("input FML with COLLEN :%d\n",COLLEN);
    Fchg(SndBuf, COLLEN, 0, (char *)&col_len, (FLDLEN)0);
    userlog ("input FML with dyn_statement : %s\n",dyn_statement);
    Fchg(SndBuf, STRSQL, 0, dyn_statement, (FLDLEN)0);
    userlog ("\nOraDyn4 is ok!\n");
    tpreturn(TPSUCCESS,0,(char *)SndBuf,0L,0);
}

alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
    int i;
    if ((bind_dp = sqlald(size, max_vname_len, max_iname_len)) == (SQLDA *) 0)
    {
        userlog("Cannot allocate memory for bind descriptor.");
        return -1;  /* Have to exit in this case. */
    }

    if ((select_dp =
        sqlald (size, max_vname_len, max_iname_len)) == (SQLDA *) 0)
    {
        userlog("Cannot allocate memory for select descriptor.");
        return -1;
    }
    select_dp->N = MAX_ITEMS;

    for (i = 0; i < MAX_ITEMS; i++) {
        bind_dp->I[i] = (short *) malloc(sizeof(short));
        select_dp->I[i] = (short *) malloc(sizeof(short));
        bind_dp->V[i] = (char *) malloc(1);
        select_dp->V[i] = (char *) malloc(1);
    }
    return 0;
}

void process_select_list()
{
    int i, null_ok, precision, scale;
    char title[MAX_VNAME_LEN]; 

    if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
        (strncmp(dyn_statement, "select", 6) != 0))
    {
        select_dp->F = 0;
        return;
    }

    select_dp->N = MAX_ITEMS;
    
    EXEC SQL WHENEVER SQLERROR DO sql_error();
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

    if (select_dp->F < 0)
    {
        userlog("\nToo many select-list items (%d), maximum is %d\n",	-(select_dp->F), MAX_ITEMS);
				return;
    }

    select_dp->N = select_dp->F;
    col_len = select_dp->F;

    
    userlog("\n begin process_select_list:\n");
    for (i = 0; i < select_dp->F; i++)
    {
        /* Turn off high-order bit of datatype (in this example,
           it does not matter if the column is NOT NULL). */
        sqlnul ((unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok);

        switch (select_dp->T[i])
        {
            case  1 : /* CHAR datatype: no change in length
                         needed, except possibly for TO_CHAR
                         conversions (not handled here). */
                break;
            case  2 : /* NUMBER datatype: use sqlprc() to
                         extract precision and scale. */
                sqlprc ((unsigned long *)&(select_dp->L[i]), &precision, &scale);
                      /* Allow for maximum size of NUMBER. */
                if (precision == 0) precision = 40;
                      /* Also allow for decimal point and
                         possible sign. */
                /* convert NUMBER datatype to FLOAT if scale > 0,
                   INT otherwise. */
                if (scale > 0)
                    select_dp->L[i] = sizeof(float);
                else
                    select_dp->L[i] = sizeof(int);
                break;

            case  8 : /* LONG datatype */
                select_dp->L[i] = 240;
                break;

            case 11 : /* ROWID datatype */
            case 104 : /* Universal ROWID datatype */
                select_dp->L[i] = 18;
                break;

            case 12 : /* DATE datatype */
                select_dp->L[i] = 19;					//SunSulin Modified 2001.12.5
                break;
 
            case 23 : /* RAW datatype */
                break;

            case 24 : /* LONG RAW datatype */
                select_dp->L[i] = 240;
                break;
        } /* end switch  */


         if (select_dp->T[i] != 2)
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i] + 1);  
         else
           select_dp->V[i] = (char *) realloc(select_dp->V[i],
                                    select_dp->L[i]);  


		memset(title, ' ', MAX_VNAME_LEN);
		sprintf(title,"%.*s",select_dp->C[i],select_dp->S[i]);
		userlog("title =%s",title);
		userlog("get number %d field name\n",i); 
		
		/* 向缓冲区SndBuf中写入EMP表的字段名*/
		{
			userlog ("input number %d field into SndBuf buffer.\n",i);
			userlog ("input FML EMPSTR with field name:%s\n",title);
			Fchg(SndBuf,EMPSTR,OccCount,title,(FLDLEN)0);
			OccCount++;
		}

        /* Coerce ALL datatypes except for LONG RAW and NUMBER to
           character.除了LONG RAW and NUMBER外，所有的数据类型都转换成为字符型 */
        if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
            select_dp->T[i] = 1;

        /* Coerce the datatypes of NUMBERs to float or int depending on
           the scale. */
        if (select_dp->T[i] == 2)
          if (scale > 0)
             select_dp->T[i] = 4;  /* float */
          else
             select_dp->T[i] = 3;  /* int */

    }/* end for */

    /* FETCH each row selected and print the column values.
	返回每一行的值，并将值放入到缓冲区中*/

    EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;

    userlog("get emp table record values. \n"); 
    for (;;)
    {
        EXEC SQL FETCH C USING DESCRIPTOR select_dp;
        for (i = 0; i < select_dp->F; i++)
        {
            if (*select_dp->I[i] < 0)
                if (select_dp->T[i] == 4) 
                  userlog("%-*c",(int)select_dp->L[i]+3, ' ');
                else
                  userlog("%-*c ",(int)select_dp->L[i], ' ');
            else
                if (select_dp->T[i] == 3)     /* int datatype */
                  userlog("%*d ", (int)select_dp->L[i], 
                                 *(int *)select_dp->V[i]);
                else if (select_dp->T[i] == 4)     /* float datatype */
                  userlog("%*.2f ", (int)select_dp->L[i], 
                                 *(float *)select_dp->V[i]);
                else                          /* character string */
                  userlog("%-*.*s ", (int)select_dp->L[i],
                            (int)select_dp->L[i], select_dp->V[i]);
			/* 向缓冲区SndBuf中写入EMP表的字段值 */
			{
				userlog ("input number %d field into SndBuf buffer.\n",i);
				userlog ("input FML EMPSTR with current record I value %d\n",*select_dp->I[i]);
				sprintf(tmpbuf,"%d",*select_dp->I[i]);
				userlog("%s\n",tmpbuf);
				Fchg(SndBuf,EMPSTR,OccCount,tmpbuf,(FLDLEN)0);
				OccCount++;
				if (strcmp(tmpbuf,"0"))
				{
					userlog("true\n");
					sprintf(tmpbuf,"%s","NULL");
					userlog ("input FML EMPSTR with NULL value %s",tmpbuf);  // current record (type=3) V value %s",tmpbuf);
					Fchg(SndBuf,EMPSTR,OccCount,tmpbuf,(FLDLEN)0);
					OccCount++;
				}
				else if (select_dp->T[i] == 3)
				{
					sprintf(tmpbuf,"%*d",(int)select_dp->L[i],*(int *)select_dp->V[i]);
					userlog ("input FML EMPSTR with current record (type=3) V value %s",tmpbuf);
					Fchg(SndBuf,EMPSTR,OccCount,tmpbuf,(FLDLEN)0);
					OccCount++;
				}
				else if (select_dp->T[i] == 4)
				{
					sprintf(tmpbuf,"%*.2f", (int)select_dp->L[i],*(float *)select_dp->V[i]);
					userlog ("input FML EMPSTR current with record (type=4) V value %s",tmpbuf);
					Fchg(SndBuf,EMPSTR,OccCount,tmpbuf,(FLDLEN)0);
					OccCount++;
				}
				else
				{
					userlog("false\n");
					sprintf(tmpbuf,"%-*.*s",(int)select_dp->L[i],(int)select_dp->L[i],select_dp->V[i]);
					userlog ("input FML EMPSTR current with record (other types) V value %s",tmpbuf);
					Fchg(SndBuf,EMPSTR,OccCount,tmpbuf,(FLDLEN)0);
					OccCount++;
				}
			}/* end append */
        }/*inner loop end*/
    }/*outer loop end*/
end_select_loop:
    return;

}

void sql_error()
{
	userlog("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
	userlog("Parse error at character offset %d in SQL statement.\n",sqlca.sqlerrd[4]);
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK;
	tpreturn(TPFAIL,0,NULL,0L,0);

}